Trying to compile a Cragslist URL list to scrape data can be a daunting task in itself. We’ve put together a list of Craigslist URLs by location in an editable spreadsheet. The list contains 417 URLs that encompass all of the United States locations that are currently available on Craigslist. The list groups cities by state.
Here’s some quick instructions on how to get the URL list and append your query parameters to all of the URLs in the sheet within a couple of minutes. We decided to use Google Sheets to accomplish the task because it’s free for anyone to access but the same tasks can be handled in Excel, Libre, OpenOffice or any other spreadsheet program that you may prefer to work wit
We made the Google Sheet “Read Only” to eliminate the possibility of the URLs being changed. You’ll have to copy and paste the links into a new sheet. If you’re new to Google Sheets here’s some help for copying and pasting the Craigslist URLs into a new sheet:
1. Once you have the Google Sheet link in your email, click on the link to open the file in Google Sheets.
2. Left click on the first row, scroll down to the last row. While holding the Shift button left click the last row to highlight all of the rows in between the first and last row.
3. Once all of the rows are highlighted, hold down the CTRL button and hit the “C” key to copy the rows to your clipboard.
4. Open up a new sheet in your Google Sheets (or any other spreadsheet program). Left click on the first row or column to highlight. Hit CTRL + “V” to paste your selection into the new sheet.
In our example we’ve set up the URLs to point to the Craigslist job postings in all US cities. Once you’ve pasted the URLs into a new sheet, you can edit the URL parameters quite easily to match your search.
The first step is understanding how Craigslist structures their URLs:
In your new Google Sheet, left click on the empty box in the upper left hand corner of the sheet. This will select all of the rows and columns and edit all selected cells at once.
In the top menu, click on the “Edit” dropdown and choose “Find and Replace”. If you prefer to use hotkeys you can use CTRL + “H” to initiate the find and replace window.
In this next find and replace window you will be able to edit any url parameters you want throughout the entire sheet without editing each cell individually. This is a great trick for making batch data edits and we use this one all of the time!
In our example we are scraping job data for the restaurant industry to determine trends in compensation throughout the US. Since our URL lists already has the “jjj?” to designate the search be conducted within the job category, all we need to do is append our search keyword “restaurants”.
Type the equals sign in the “Find” field. This is the part of the URL where we’ll begin to replace the empty query with the keyword so we want to start at the equals symbol in the existing URL.
Not in the “Replace With” field type “restaurants”. Click on the “Find” button in the bottom left and then the “Replace All” button. Next, click “Done”.
You’ll notice that once you’ve clicked the done button your URLs now have the keyword appended. If you want to append multiple keywords that make up a phrase like “car mechanic”, be sure to enter each keyword with the plus symbol in between each word, i.e.; “car+mechanic”.
If you want to edit the category you can start further back in the URL. Say you wanted to search the events category for medical conventions.
Under the “Find” field in Find and Replace, you would enter “jjj?query=”. In the “Replace” field enter “eee?query=medical+conventions” to get the desired page.
Enter your name and email address. We’ll send you a link to the Google Sheet with the URLs to copy and paste without downloading any files.