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 from here.
![[SOLVED] How to Get Google search results in Sheets 1](https://media.giphy.com/media/Ouw4HghoMO69a/giphy.gif "[SOLVED] How to Get Google search results in Sheets 3")
- Create a google sheet in your google drive.
- Create a google custom search from here.
- Enable search the entire web.
- Copy your google custom search engine id.
- Get your google custom search api key from here
- 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.
- 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://www.aozata.com/importjson-gs/
- Enter the cx, key, search query (q), API URL in different cells.
- 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)
- 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")
- Here are the search results in google sheets and also a link to the sheet where you can see the search in work.
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?
What you are looking to do is scraping and is not related to this article
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?
Hello Matej, Try using &num=1 in the google custom search api
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.
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
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”
}
}
Can you please share the API link you have formulated? Maybe you have exhausted your 100 searches limit per day
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?
Use the num parameter by appending &num=1 in the api call
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
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?
The maximum limit is 10 results at a time. Google has limited it.
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
Hi – does this work for excel too or just google docs? If it does work for excel – what do I need to different?