Get search results for 600 keywords in Google Sheets per month, for free

With this script you can input your keyword and language it will return the top 100 search results. 🚀

Check out the GIF to see it in action 🎥


How to add the Script to Google Sheets

1. Sign up to RapidAPI

Sign up to RapidAPI, an API marketplace, at https://rapidapi.com/auth/sign-up (you can even use your Google Account).


2. Subscribe to the Google Search API

Once you’re all set up on RapidAPI head to https://rapidapi.com/apigeek/api/google-search3/pricing and subscribe to the Basic Plan (Free). This plan has a hard 600 requests per month limit.


3. Get your API Key

The good thing about RapidAPI is that your API key can be used universally across any of the APIs in their marketplace. To copy your API key, go to https://rapidapi.com/developer/dashboard and head down to ‘My Apps’ and then ‘Security’. From there, you can copy your Application Key.


4. Copy the script below:

/**
* Returns the top 100 search results for any given search query and language. 
*
* @param {"SEO Tips"} keyword input the keyword.
* @param {"en-GB"} language input the language eg. en-GB.
* @return search results for search query and language
* @customfunction
*/


function getSERPS(keyword,language) {

  // add API key from https://rapidapi.com/apigeek/api/google-search3
  const apiKey = '';
  keyword = keyword.split(/[ ,]+/).join('+');

  const options = {
        'method': 'GET',
        'contentType': 'application/json',
        'headers': {
            'x-rapidapi-key': apiKey,
            'x-rapidapi-host': 'google-search3.p.rapidapi.com'
        }
    };

    const response = UrlFetchApp.fetch("https://google-search3.p.rapidapi.com/api/v1/search/q="+keyword+"&hl="+language+"&num=100", options);
    const json = response.getContentText();
    const results = JSON.parse(json).results;

    let rows = [],
        data;
    for (i = 0; i < results.length; i++) {
        data = results[i];
        rows.push([i, data.title, data.link,data.description]);
    }

  rows.unshift(["Rank","Title","URL","Description"]);

  return rows;
}

5. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Script editor from the Tools menu.

Paste the script and replace line 14 with your API key:

  const apiKey = 'addapikeyhere';

Save it.


6. Add the formula to any cell in your sheet

=getSERPS("SEO Tips", "en-GB")

You can replace “SEO Tips” with any keyword and “en-GB” with any HTML ISO language code.

*While the script searches from a given language, it doesn’t emulate a location.

ie. ‘bank accounts’ will likely give you non-localised results. In its current form you’d likely need to specify location in the search query: ‘bank accounts UK’. I will be making amendments to the script in the future to improve this.


Thanks for stopping by 👋

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓

Questions? Get in touch with me on social or comment below 👇


More Scripts

Submit your response

Your email address will not be published.

8 Responses on this post

  1. Hi Andrew. Great scripts. Im getting an error on the search results one though: Exception: Request failed for https://google-search3.p.rapidapi.com returned code 403. Truncated server response: {“message”:”You are not subscribed to this API.”} (use muteHttpExceptions option to examine full response) (line 26).

    Ive checked in RapidAPI and i am subscribed.

    1. Hi Ashley, thanks for commenting! 🙂 It looks like you’ve signed up to rapidAPI and have a key, but you also need to subscribe to the specific API. Head to this link and subscribe to the free plan.

  2. Hi Andrew,

    Thanks for your efforts. The scripts are very useful. I’d like to know if we can set up the script to get the SERP results for any particular country?

    Thanks,
    Muhammad (Arbaz)

    1. There’s still some more work to be done to get this working accurately at a country-level – more updates soon 🙂

  3. Hi Andrew
    I am very upset that I can’t get the sheets although I did every step in the script
    Can you help me where the proplem?