Andy Langton 37 Comment(s)

Update: we've updated this spreadsheet to the new Google Docs format - no more 50 limit on importxml lookups (and hopefully, more reliable results).

If you like this, also check out our spreadsheet to scrape the top 100 results and other Google Docs spreadsheets for SEO.

Knowing the number of results returned for a given search query has a number of uses - from keyword analysis to market research and, perhaps most importantly, when looking in depth at how a site is indexed in Google. But unless you have the right tools in your armoury, you may be relying on manual checks. Here's a way to instantly check the result count for up to 50 keywords using only Google Docs.

For the curious, here's what the spreadsheet will output:

Sample of spreadsheet results

Handy, isn't it? Well without further ado, you can grab the spreadsheet from the following URL:

https://docs.google.com/spreadsheets/d/1-ByE5xg_IWeEfAuUq037TWkK6GoK_klx1d6tMEtkG0Y/

Note that Google Spreadsheets have a hard-coded limit of 50 lookups per sheet, so to go beyond that you would need to copy and paste formula results into static values. You can do this by selecting, right-clicking and choosing paste >> values. If you regularly go beyond 50 lookups, then to be blunt, you need a more industrial-strength solution than Google Docs! More than 50 lookups now supported!

For those curious about how the spreadsheet was created, the gory detail is below.

Building the spreadsheet to scrape Google results

Google Doc's IMPORTXML function is extremely useful, and allows you to retrieve the contents of a remote URL easily from with a cell in your spreadsheet. You can then manipulate the output and get your desired element from the target URL using XPATH.

The basic function we're using here is:

importxml("http://www.google.com/search?q="&A2,XPATH_QUERY)

This grabs Google's URL results page, appending the contents of the adjacent cell (A2) to the results. Of course, we then need to identify the part of Google's result page that contains the count of results, and build an XPATH query around that. Fortunately, this is currently trivial, as Google wrap the 'about nnn results' text in a DIV container with an ID of resultStats. This makes the XPATH query very simple:

//div[@id='resultStats'] 

Put simply, select a DIV element that has an ID of 'resultStats'. Easy (until Google change their HTML again)! Our function is now as below:

importxml("http://www.google.com/search?q="&A3,"//div[@id='resultStats']") 

So far, our result cells would contain the complete output of the results text, which could be something like "About 874 results" but could also be "1 result" or "2,000 results". Stripping the surrounding text out will make the spreadsheet easier to use, and the best way to do this is via a regular expression.

Google Docs does have regular expressions built in, including the one we'll use here, REGEXREPLACE. Unfortunately, this function is both poorly documented and very finnicky to use. To work around this, we used a very 'loose' regular expression pattern:

.*?([0-9,]+) results? 

In plain English - find text that starts with anything, followed by numbers and commas, and then followed by the phrase result(s). This isn't pretty regex, or as specific as we'd like, but it does work (well OK, it breaks with Elmer Fudd output, but then, we like seeing that there are "20,000 wesults").

For extra flexibility, we also added an additional option to include Google search URL parameters, by adding a reference to an additional cell within the formula. This means you can use country targeting, verbatim or whatever else you might like in your queries.

Putting it all together, the final function to output the result count in human-readable numbers is as below:

=REGEXREPLACE(importxml("http://www.google.com/search?q="&A2&"&"&C2,"//div[@id='resultStats']"),".*?([0-9,]+) results?","$1") 

It's not refined, but it gets the job done. To see the full working spreadsheet and grab your own copy, just visit the Google Docs URL below:

https://docs.google.com/spreadsheets/d/1-ByE5xg_IWeEfAuUq037TWkK6GoK_klx1d6tMEtkG0Y/

Any requests/comments/suggestions? Just let us know!