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:
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!
37 Comments - Bulk Check Google Results Count with Google Docs
reJoy (not verified) January 16, 2013
ReplyHello,
Can u help me with formula for "polish local results"?
Andy Langton February 26, 2013
ReplyApologies - only just saw this comment. The parameter for Polish local is as below:
tbs=lr:lang_1pl&lr=lang_pl (for Polish language)
cr=countryPL (for pages located in Poland)
HTH!
Andy
Emil Petkov (not verified) April 16, 2013
ReplyGreat article, great document... but sometimes when you copy/paste the keywords it shows N/A as a result, however there are results existent. Why this could be happening?
Andy Langton April 19, 2013
ReplyIf you do a large number of searches (particularly with advanced operators in them) then Google will periodically block the results - and you'll see N/A. No solution to this other than to avoid advanced operators (i.e. when your search query contains a colon - in some cases you can use URL parameters instead) or to wait a little while.
Steven Jonker (not verified) April 18, 2013
ReplyGreat post Andy,
I find this extremely useful. However, I am stuck on the following:
1. I need to append words to my keyword. So if I type in "red tennis shoes", it must search for - allintitle: "red tennis shoes".
I'm trying to find a function that would be best suited for this - have been looking at REGEXREPLACE.
I would greatly appreciate it if you can point me in the right direction.
Steve
Andy Langton April 19, 2013
ReplyThere are two approaches you could take. If the string is always the same, then merely place it directly after the q= section in the target URL.
Alternatively, if the string is changeable, add a cell reference in the same position. E.g. for simplified examples where the keyword is in column "A"
Original:
importxml("http://www.google.com/search?q="&A2,XPATH_QUERY)
Method 1:
importxml("http://www.google.com/search?q=allintitle:"&A2,XPATH_QUERY)
Method 2, assuming your new string is in column C:
importxml("http://www.google.com/search?q="&C2&A2,XPATH_QUERY)
Kevin Mata (not verified) June 28, 2013
ReplyWow, this is very clever, thank you some much for this! It's going to save me a lot of time!
Laura Scott (not verified) October 16, 2013
ReplyGreat tool and much appreciate the extra help! For anyone else reading this, Andy has provided me with an expression for allintitle searches:
=REGEXREPLACE(importxml("http://www.google.com/search?as_occt=title&q="&CHAR(34)&A2&CHAR(34),"//div[@id='resultStats']"),".*?([0-9,]+) (w|r)esults?","$1")
Such a great help!!
Thank you :)
Ed (not verified) April 05, 2014
ReplyHi Andy, just wondered whether you experience any inaccuracies when scraping google.co.uk's results page relating specifically to the <div Id="resultStats"> part of the code. For me the xpath returns much more results than the query string displays when loading live in the browser. Is this because there are issues with scraping google.co.uk? Just wondered what your take is on this please. I can provide more information if that helps,..
Mark Hazeldine (not verified) October 23, 2014
ReplyFirstly, Andy, thanks. This is an awesome time-saver!
Secondly, I am experiencing the same thing as Ed. I have a whole bunch of keywords that are returning a results count of 1,920,000,000 and an allintitle results count of 21,200,000. When I go into a browser and search manually, I am getting figures like results: 868,000 and allintitle results: 1960.
This is a huge discrepancy and mean that I am then having to manually do those ones, which kind of sucks!
Noor Basheer (not verified) May 09, 2014
ReplyI am going to try out these tricks. Thank You author.And can you say is there anyway to get the exact search count of a particular keywords ?
Alex (not verified) June 15, 2014
ReplyHi Andy! Thank you very much for the great article! Was looking for that all around the web and finally found what I was looking for. And your code still works! Amazing.
Do you know if there is any opportunity to exclude text around the number of results in other languages?
(w|r)esults?","$1")
for RU for example?
Otherwise I always get this phrase "Результатов: примерно 368 000".
I tried putting instead of "r)esults" - Russian "р)езультат" but it didn't work out...
Thank you!
zxc (not verified) September 19, 2014
ReplyHow to make functions were handled from within normal Excel (Excel does not support regular expressions and plug 'seo for excel' this does not fix)?
Google has just Doc is limited to 50 checks and managing it is not convenient
Pat Snippley (not verified) January 08, 2015
ReplyHiya Andy - firstly thanks so much for this, it has been a very valuable tool for me for about a year now.
However, recently (last coupla' months?), it has failed to work at all...I am wondering has there been a change in the google query syntax or something that would require a tweak to the function?
thanks!
Pat
Andy Langton April 15, 2015
ReplyHi Pat - glad this has proved useful.
I've upgraded the spreadsheet to the newer Google Docs format. If nothing else, this removes the 50 limit on lookups. However, I'm hoping it will solve some of the lookup issues also. In addition, I've added a configuration section which allows you to change Google domain (sometimes this seems to fix problems with Google blocking docs) and also add a 'fixed' parameter to searches (you can use this to retrigger the formula without changing domains).
Hope that's of some use. Unfortunately, the failed searches are not something I can control, since they're as a result of Google blocking 'automated' searches (even from their own systems!).
lorembolo (not verified) April 14, 2015
ReplyHi .
This is very very helpfull.
I'm trying to use this spreadsheet for checking results in French / France.
I put this in the "serach parameters colmun" : cr=countryFR&tbs=ctr:countryFR,lr:lang_1fr&lr=lang_fr
Unfortunately, the number of results is not exactly the same
Forr example, with te keyword request "encadrement tableau" I manually got 464000 results and your formula returns 532000.
Is this normal ?
Thanks
Andy Langton April 15, 2015
ReplyThe results are certainly accurate. However, Google's count of results is not (it's an estimate). the most likely reason for the different number you're seeing is the Google domain you're using. The below searches *should* be the same:
https://www.google.ie/search?q=encadrement+tableau&lr=lang_fr&cr=country...
https://www.google.fr/search?q=encadrement+tableau&lr=lang_fr&cr=country...
https://216.58.208.46/search?q=encadrement+tableau&lr=lang_fr&cr=country...
However, you'll likely find that the results are slightly different for each. The problem is worse the higher the number of results (since Google's estimates are broader). What this means:
- There is no "true" count of results
- This doesn't matter, as long as you're using the results to compare things
- Using the same domain to search on will ensure consistency
Note that you can change the domain used for searching in the 'configuration' worksheet within the spreadsheet itself.
lorembolo (not verified) April 15, 2015
ReplyThanks for your answer. As you wrote, as long as I use this for comparison, no problem.
BTW, I got another question : in your spreadsheet the thousands are comma separated. When I cut&paste in excel, Some are considered as numbers, som as texts... Is ther a way to avoid comma thousand separation.
Many thanks and congrats for this really useful file :)
Andy Langton April 15, 2015
ReplyYou can fix the comma issue by using the Format >> number format menu in Google Docs. I'm using the UK format for both Google docs and Excel, so if you use a different one, this might be the issue. If you're starting with your own copy of the file as a template, this change will remain for any future docs you make based off this template. You may need to just check back here for any updates to the formula etc.
SEMGirl (not verified) October 09, 2015
ReplyAndy, this tool is incredibly helpful. I've been able to modify it a little to suit my needs and it has saved me a TON of time! Incredible! I've been trying to find a more efficient way to do certain tasks and this is more amazing than any other solution I have come up with or thought possible.
I've been trying to find a way to modify this post the URL of the top result in Google in another column. I'm not having any luck, any ideas?
SEOThing October 17, 2015
ReplyThanks for the comment! Do you mean to 'scrape' the top result for a query?
SEMGirl (not verified) October 21, 2015
ReplyYes, I would like to scrape the url of the top result and show it in another column...is that possible?
SEOThing October 24, 2015
ReplyIt's certainly possible. The functions to scrape result details are in our latest post:
https://seothing.co.uk/blog/scrape-top-100-google-results
If you want to combine both the result count and other data, this would require some fairly complex XPath queries, and also reformatting of the results which is a little beyond the scope of what this particular spreadsheet is for. Let us take a look and get back to you.
SEMGirl (not verified) November 05, 2015
ReplyThanks so much. I took a look at the post - seems a bit beyond my abilities but I'll give it my best shot. If you come up with a solution please let me know - this would be a game changer. Thanks again for the awesome tool and the support!
SEOThing November 10, 2015
ReplyHi again,
Try this:
https://docs.google.com/spreadsheets/d/17WRhuMi0T23g9HM3qfBAW2mJyZnisdSQ...
:)
SEMGirl (not verified) December 03, 2015
ReplyThank you! You are a genius! I'm very impressed with your skills and grateful for the help.
TommyLi (not verified) December 14, 2015
ReplyHi Andy,
I tried your impressive tool but (maybe due to language set-up german?) there are no results, but the one with tbm=isch.
What could be the reason? Thanks in advance.
TommyLi
Andy Langton December 14, 2015
ReplyHi TommyLi! Unfortunately, Google seems to have increased the frequency with which they block Google Docs from accessing search result pages. It's not a problem with the tool, but rather a general issues with Google Docs and importxml of Google pages.
Other than to keep trying (and trying different Google domains to retrieve results from) We don't have a fix for this yet. I know this isn't ideal :(
TommyLi (not verified) December 14, 2015
Replyok, thanks for fast reply. I will keep trying and see ;-)
Anonymous (not verified) March 01, 2016
Replyfor example: site:buzzfeed.com "how to cure dandruff"
Andy Langton March 01, 2016
ReplyThat should just work - enter the above into the keyword section and it should work fine.
Lee (not verified) March 01, 2016
ReplyHi Andy thank you for your comment.
Which search Parameter would work?? I've tried all it keeps saying N/A?
Andy Langton March 01, 2016
ReplyHi Lee - you would add your search query in column A, and omit any parameters.
However, it may be that Google is blocking Google Docs (i.e. blocking itself!) from accessing search results. You can try changing the "Google domain" in the configuration section if that occurs, but unfortunately there's no way to control this, other than to try differernt Google domains or wait until Google unblocks itself.
Eugenio (not verified) July 04, 2016
ReplyHey Andy thanks for this guide.
I use importxml as well, also for rank tracking etc
I've seen that since a while now all of the importxml for google search does not work anymore. Did you also found out that they are blocking google docs from getting results from them?
Did you find any good solution to that? I think there must be a solution! D
Thanks
Eugenio
Brian (not verified) August 11, 2016
ReplyI used this trick for a side project a couple years ago and it worked perfectly (Thank you Andy!)
Now it looks like Google is fully blocking results (or I'm doing something wrong).
To test this, I've simply been changing the queries in the example workbook, which throws an error.
This is the error I get after changing "funnel web spider" to "funnel web":
>> Could not fetch url: https://www.google.ie/search?q=funnel web &&as_oq=www
Any thoughts?
SEOThing August 12, 2016
ReplyI don't think there's an answer to this, unfortunately. The 'industrial strength' answer is to use proxies, which is not really much use for 'casual' users who just want simple data gathering.
If I do come up with something, I'll be sure to update the various spreadsheets.
Igor (not verified) November 18, 2016
ReplyHi Andy!
I've found your query extremely useful for me but I have a question - how often are these results updated? Or you should reload your page to update the results?
Best,
Igor.
Leave a comment