Advertisement

How To Extract Google Results Into a Spreadsheet

Article main image
Nov 10, 2015
This article is part of a series called Editor's Pick.

Irina Shamaeva recently posted a link to this page which talks about converting your Google search results to an RSS feed.

I decided to explore taking this a step further – if I can convert it to RSS, then can I import these results into a spreadsheet?
With the assistance from Aaron Lintz and David Galley (to bounce ideas off of), I took a look at Excel and Google Docs to do this.
First Things First – 
  • You need to use a Google Custom Search Engine (Google CSE) for this – either create your own or use one created by someone else (like my basic one)
  • You need to complete the steps in this article to setup a Google CSE API key. Copy this to a clipboard, you will need it later. 
STEP 1 – Find the CSE id of your search engine
 
Find the unique identifier your Google CSE of choice. If you have created your own, you can get this in your control panel. If you are using a different one, look at the source code of the page and find the CX number. The id of my search engine is 015706279216314790222:15c3dmuxab8 – it will be in a similar format to that.
STEP 2 –  Plug your CSE id and Google CSE API, and search term into the Google CSE API URL
 
The basic URL is 
 
https://www.googleapis.com/customsearch/v1?alt=atom&cx=[CSE ID]&key=[API KEY]&q=[Query]
 
My example URL is (excluding the API key as this is purely for my use)
 
https://www.googleapis.com/customsearch/v1?alt=atom&cx=000662592711425213713%3Ak1ek3jvsvjg&key=[API KEY]&q=”project+manager”+Glasgow
 
You now have your RSS feed.
 
STEP 3 – For Excel
 
Excel has a facility to import RSS feeds – the XML Import tool. This blog by Diego Oppenheimer explains how to do it far better than what I can, so check that out.
 
Once you have done that though, you will probably start to – like me – get a bit frustrated. The limitations of the Google CSE API mean that you can only view 10 results. You can append the URL to view more of the results (only up to 100 though) by adding “&start=[num] – (thanks Aaron for pointing this one out as I had missed it). 
 
So, can you do a better job in Google Docs? 
 
STEP 4 – Google Docs
 
Turns out you can. 
Check out this sheet I created (not the cleverest, but I’m still learning the best way to do things in Google Docs).
Paste the URL you created in Step 2, and it will parse the data from the feed. This is using the Import XML formula in Sheets. This is far more flexible than Excel.
Summary
 
This is definitely a work in progress but thought I would get my notes so far out for feedback. I’m sure this concept can be taken further.
This article is part of a series called Editor's Pick.