Continuing our series of Google Docs SEO scrapers, this latest entry will show you how to bulk check the top 10 Google rankings for a list of keywords using Google Docs.
In a hurry? Grab your spreadsheet from the link below:
Use File >> Make a copy for your own version to work with.
This spreadsheet will check the top 10 results for numerous keywords, producing results like this:
Grabbing the top 10 results
Once again, we'll be using IMPORTXML to retrieve Google results pages. This time, we want to retrieve both the result title and URL using Xpath (we'll query both at once to reduce the number of requests to Google:
//h3[@class='r'] | //h3[@class='r']/a/@href
We're retrieving all of the matching results from the page, which means Google will return an array, which will put the results in rows below our formula. We don't want this, because we want each result to have its own column - otherwise, our spreadsheet will be difficult to read.
The TRANSPOSE function allows us to place the results in columns rather than rows:
Tidying up the data
Because Google use tracking URLs in search results, the URL will look like this:
We want to grab just the URL using REGEXREPLACE:
Unfortunately, REGEXREPLACE does not work on arrays, so we are forced into a rather messy solution - place the actual results in hidden columns, and process these separately. That said, the method works.
Allowing for configuration
Finally, we want to allow you (as with all our spreadsheets) to adjust the results yourself without needing to wade into formulas. So, we added a configuration worksheet to allow you to choose a Google domain and add custom parameters.
So, there you have it. Get the top 10 results for multiple queries with Google Docs.
Download your spreadsheet here and let us know any issues or requests in the comments below.