Building a Google SERP extension in Google Sheets (Apps Script + RapidAPI) 📈

Introduction

So you want to monitor some keywords for your next SEO research and analysis and want the data readily available in a Google Sheets file all, without leaving the four walls (corners?) of Google Sheets? 🤔 Say no more -- This article will help you achieve just that. So, in a nutshell, we'll be building a webapp inside Google Sheets that interacts with the same spreadsheet file. We'll use Google Apps Script and RapidAPI platforms to achieve this.

Google Apps Script is a scripting platform developed by Google for light-weight application development, in the Google Workspace platform (wikipedia) and RapidAPI is a marketplace for APIs.

Goal

• Create a custom menu in Google Sheets.
• When that menu is clicked, launch a modal window that accepts Google search parameters and other search options
• Send request to Google Search API hosted on RapidAPI to retrieve results.
• Write the results to a sheet on the same spreadsheet.
• Jump 3 times and sip a glass coffee then sleep? 🤷‍♂️

Folder sctructure

We'll require two files:

• index.gs
• index.html

Create the custom menu

Now let's get our hands dirty with some code. Create a new spreadsheet file in your Google account and locate Extensions from the menu bar. Then select Apps Script from the submenu. That will launch a text editor where we will be working from.

Next is to create our custom menu titled SEO Tools with a submenu called Google SERP. Below is the code for that.

// index.gs
function onOpen(){
  SpreadsheetApp.getUi()
    .createMenu('SEO Tools')
    .addItem('Google SERP', 'openDialog')
    .addToUi()
}

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('index');
  SpreadsheetApp.getUi()
    .showModalDialog(html, 'Google SERP');
}

The onOpen function fires immediately after the spreadsheet file loads hence, injecting our custom menu. When the submenu item is clicked, it triggers the openDialog function which in turn, launches our modal window. The modal window contains our html file.

Send data from HTML to Apps Script

We need a way to send data from our html file to Apps Script. In the index.html file, just before the body closing tag, paste the following script

<!-- index.html -->
...
<script>
  document.querySelector(`#submit-btn`).addEventListener(`click`, () => {
    let query = document.querySelector(`#query`)?.value || `q=tesla+stocks&num=100`;
    let rapid_api_key =
        document.querySelector(`#rapid_api_key`)?.value || null;
    let proxy_location =
        document.querySelector(`#proxy_location`)?.value || `United States`;
    let device = document.querySelector(`#device`)?.value || `desktop`;
    let domain = document.querySelector(`#domain`)?.value || `google.com`;

    let params = {
      query,
      rapid_api_key,
      proxy_location,
      device,
      domain
    }

    google.script.run.handleSubmission(params)
  })
</script>
...

Form parameters

The form accepts the following parameters:

• query
• rapid_api_key
• proxy_location
• device
• domain

query is any valid Google search parameters. Example:

• q=seo+use+cases&num=100&ie=UTF-8
• q=serp&num=20&ie=UTF-8&start=100
• q=google+sheets&num=100&ie=UTF-8&start=100
• q=javascript&num=50&ie=UTF-8

You can find a list of some of the possible parameters here: The Ultimate Guide to the Google Search Parameters. Big shouts-out to the folks at Moz 🙌

Searches are geographically bounded, hence, you can search from up to 127 countries/regions by selecting the desired proxy location on the form. You also need to subscribe to Google Search API to obtain your free API key for this. Since results may vary between desktop and mobile devices, you can also choose the type of device to be used for the search. Lastly, all Google domains are supported and you can simply select the domain from the list of all the available options.

After the form has been filled and the button clicked, it will run the function handleSubmission in Apps Script via google.script.run. That will get all the search parameters we provided in the form and our script will have access to them at this point, to process it further.

// index.gs
async function handleSubmission(params){
  try {
    let data = await search(params)
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet()

    // organic result sheet
    writeOrganicResults(data, spreadsheet)
  } catch (error) {
    // Handle error as desired
    Logger.log(error)
  }
}

The handleSubmission function does two things:

• Make an API call to Google Search API on RapidAPI
• Write the returned data to the designated sheet. You can find a sample of the Google SERP data here.

Make the API request

The search function accepts the parameters that we have already obtained and then it makes the request. A little 'gotcha!' with Apps Script is that it does not provide the URL object 😯 and we need to pass those parameters to our endpoint's URL https://google-search65.p.rapidapi.com/search. So the next hurdle to cross is cracking the cocunut fruit with our bare hands! 😆 Ready for that? Actually, what we need to do is to somehow append the parameters (of type object) to the url string as query params. There are many ways to achieve this but I find the following solution very elegant, and efficient.

// index.gs
// Object to querystring - https://gist.github.com/tanaikech/70503e0ea6998083fcb05c6d2a857107
String.prototype.addQuery = function(obj) {
  return this + Object.keys(obj).reduce(function(p, e, i) {
    return p + (i == 0 ? "?" : "&") +
      (Array.isArray(obj[e]) ? obj[e].reduce(function(str, f, j) {
        return str + e + "=" + encodeURIComponent(f) + (j != obj[e].length - 1 ? "&" : "")
      },"") : e + "=" + encodeURIComponent(obj[e]));
  },"");
}

What we did above is that, we added a custom method to the Strings object, and can now call that method on every instance of that object. addQuery will now accept our params object (remember the data we got from the form?), and inject the needed query strings to our endpoint url. The code block below shows how to achieve that.

// index.gs
function search(params) {
  return new Promise((resolve, reject) => {
    try {
      // ensure that API key is provided
      if (!params.rapid_api_key) {
        reject(`Please subscribe to https://rapidapi.com/microworlds/api/google-search65 to get a free 'X-RapidAPI-Key' key`)
      }

      const X_RapidAPI_Key = params.rapid_api_key
      delete params.rapid_api_key

      let url = `https://google-search65.p.rapidapi.com/search`
      url = url.addQuery(params)

      let response = UrlFetchApp.fetch(url, {
        method: 'GET',
        headers: {
          'X-RapidAPI-Key': X_RapidAPI_Key,  // guard this API key
          'X-RapidAPI-Host': `google-search65.p.rapidapi.com`
        }
      })

      let data = response.getContentText()
      resolve(JSON.parse(data))
    } catch (error) {
      reject(error)
    }
  })
}

You must have noticed that we are deleting the rapid_api_key param from that object. That is because we do not want to include that API key in the URL, but as a header, hence, storing it in the X_RapidAPI_Key constant.

Write the data to a sheet

Now that we have the SERP data returned as JSON from our API call, we can write that into our spreadsheet. For the sake of this article, we are only interested in the organic search results, but for what it's worth, you may write all the returned data if you so wish.

// index.gs
function writeOrganicResults(data, spreadsheet){
  Logger.log(`Writing data to sheet...📝`)
  let organic_results = data?.data?.organic_results

  if (organic_results.length < 1){
    return
  }

  let organicResultsSheet = spreadsheet.getSheetByName(`organic_results`)

  if (!organicResultsSheet) {
    spreadsheet.insertSheet(`organic_results`)
  }

  // Append search info at top of the file
  writeSearchInfo(data, organicResultsSheet)

  // Append headers row
  organicResultsSheet.appendRow(Object.keys(organic_results[0]))

  // append the rest of the data
  organic_results.forEach((item) => {
    const keys = Object.keys(item)

    let rowData = keys.map((key) => {
      return item[key].toString()
    })

    organicResultsSheet.appendRow(rowData)
  })
  Logger.log(`Finished writing to sheet! ✅`)
}

Once again, you might have noticed another foreign function -- writeSearchInfo. 😁 That will write all the search parameters to the sheet so we can easily know what results we are looking at.

// index.gs
function writeSearchInfo(data, organicResultsSheet){
  let search_query = data?.data?.search_query
  let headerContent = Object.keys(search_query)

  organicResultsSheet.appendRow(headerContent)

  let bodyContent = headerContent.map((item) => {
    return search_query[item]
  })

  organicResultsSheet.appendRow(bodyContent)
}

At this point, it is safe to call ourselves Apps Script heroes of the century, even if Google doesn't recognize that title! 😎 You can test this by reloading the spreadsheet file. After refreshing, our new custom menu item SEO Tools will appear on the menu bar, click on it to launch the app. Google may ask you for permissions to integrate the script with your spreadsheet -- proceed and grant that permission, and viola!!! Says the Spanish? Oh, I think it's the French!

Sample Sheet

Here's an example of how the sheet would look like after running several queries ✅
https://docs.google.com/spreadsheets/d/1UuwWujNyf2g0aMWwX26TUpiKjWvWeVQltL6oqxlMEAc/edit#gid=1142278110

Limitations

• No graceful error handling.
• Only a few data points written to sheet - organic results.
• We are not spreading nested objects on the sheet.

You may proceed to tweak and customize your version as desired.

Source code

The source code is available on Github - google-serp-app-script

Conclusion

That's it! You can see how easy it is to get your Google SERP data from the comfort of your Google Sheets file in a matter of seconds. You can also integrate this API with your other applications, and must not necessarily consume the data on Google Sheets directly as we have done here.

Happy SERPing!!! 🎉