get google search results in sheets

How to Get Google Search Results in Sheets for Free | GCSE


Here are steps to get Google search results in Google Sheets or excel.

get google search results in sheets

  1. Create a Google sheet in your Google Drive.google search results in google sheets or excel
  2. Create a Google custom search from here.
  3. Enable search the entire web.google search results in google sheets or excel
  4. Copy your google custom search engine id.google search results in google sheets or excel
  5. Get your google custom search api key from heregoogle search results in google sheets or excel
  6. Formulate your api key in this format.
    https://www.googleapis.com/customsearch/v1?key={YOUR_API_KEY}&cx={CUSTOM_SEARCH_ENGINE_ID}&q={KEYWORD}

    key is your API key, cx is your google custom search id.

  7. In your google sheets, go to tools-> script editor-> paste the following code->save the file as ImportJSON.gs. The ImportJSON.gs can be downloaded from here. https://gist.github.com/paulgambill/cacd19da95a1421d3164
  8. Enter the cx, key, search query (q), API URL in different cells.
  9. Use the concatenate function to join all these 4 variables to get your final API URL. For example
    =concatenate(D4,D2,"&cx=",D1,"&q=",D3)
  10. Use the ImportJSON function to import the google search results. for example to get the title of the search results
    =ImportJSON($D$5,"/items/title","noHeaders")

15 thoughts on “How to Get Google Search Results in Sheets for Free | GCSE”

  1. Hi,

    This is a very valuable piece of information. Thank you for it. However I am not able to implement it. Initially I got ‘Loading’ message for very long which I thought would be because there are too many results and second time I tried then I am getting reference error.

    To give more details, I am basically trying to extract details like Name, Phone, Number and address from Google search or from specific website such as https://www.grotal.com into my excel/google sheet when I enter a query such as Karate Classes in Navi Mumbai. Can you please help me out here?

  2. Thanks, your instructions are clear and solution works great. What I am still trying to figure out is where (in importJSON.gs) do I limit the number of results to 1?

  3. Hi, thanx for info. To make it work for me I had to substitute “,” for “;” in all formulas. It works now, but I get only 10 results. Is there a way to show 100 lines of results? Thanx for answer.

    1. I currently dont know an answer to this. I think google gives a max of 10 results for every query. Try using the num function

  4. Hi, This is a great article and is very helpful.

    I just wanted to ask, Do I need to create new API key for every site I am making? if not. I am not sure why it is giving me an error 400.

    {
    “error”: {
    “errors”: [
    {
    “domain”: “usageLimits”,
    “reason”: “keyInvalid”,
    “message”: “Bad Request”
    }
    ],
    “code”: 400,
    “message”: “Bad Request”
    }
    }

    1. Can you please share the API link you have formulated? Maybe you have exhausted your 100 searches limit per day

  5. Hi,

    Thanks for this. This is really helpful. Would you by chance know a way to search through multiple queries at the same time using this function of google sheets? Rather than displaying 10 results for 1 query, is there a way to display 1 result for 10 queries?

      1. Hi – I have the same question, which I think was only partially answered above. I would like to use your tool to return 100’s of individual searches, each returning the #1 result. Are you able to give advice as to how I could implement this?

        I am trying to replicate the functionality of Blockspring’s ‘Top Bing Result’ natively, using your code so that I can develop my application more specifically. https://open.blockspring.com/bs/web-search-top-bing

  6. I already set to num=1 and num=10 everything ok but when I set bigger than 10 it turn to error. Do you have any way to set limit to 100?

  7. I a trying to use multiple queries for the same keyword search to get more than 10 results. What would be the code look like within your example? How do I code for google to start results with page 1 and go to next pages for results? Thank you

  8. Hi – does this work for excel too or just google docs? If it does work for excel – what do I need to different?

Comments are closed.