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.

---

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets