google search results in google sheets or excel

[SOLVED] How to Get Google search results in Sheets


Here are steps to get google search results in google sheets or excel. You can also purchase this google sheet with the complete set up from the link below for $10 from here.

[SOLVED] How to Get Google search results in Sheets 1

Get Google search results in Sheets – Download

  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://aozata.com/importjson-gs/
  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")

     

  11. Here are the search results in google sheets and also a link to the sheet where you can see the search in work.google search results in google sheets

 

14 thoughts on “[SOLVED] How to Get Google search results in Sheets”

  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. Muthali Ganesh

      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. Muthali Ganesh

      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?

  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?

Leave a Comment

Your email address will not be published. Required fields are marked *