How to easily automate your keyword research in Google Sheets
November 22, 2022

Share this post:

The days of inputting single keywords into Google’s Keyword Planner to carry out keyword research are over. Now, with a bit of programming know-how, you can write simple programs to perform keyword research at speed and at scale – and all in Google Sheets. Here’s how …

How it works

To be able to do this we need access to a keyword provider with an API for our script to communicate with.

Our script in this case is App Script, a JavaScript console built into Google’s workspace that lets you talk to Google products like Google Sheets, and Google Docs, among others.

There are many keyword providers with API’s, such as SEMrush and Ahrefs, which will charge you per request.

But did you know that Google Suggest has an open API endpoint for getting all those valuable “related searches”, as shown below?

Tools like AnswerThePublic, Ubersuggest, AlsoAsked and other keyword suggestion tools, make use of this API within their application.

At Melt Digital, we use a function within Google Sheets to grab related search terms directly in the sheet.

This means we can do all our keyword research in one sheet – meaning no more copying, pasting or downloading CSV’s from third party tools!

Here are a couple of examples:

1. Generating related search terms from Google for the footballer “beth mead” in the UK market

2. Getting related search terms for “pizza” in the UK – with additional modifiers like “sauce” and “dough” added into the mix.

Behind the scenes, the App Script is combining the seed word with modifier words (i.e. we are doing keyword research for  ‘pizza dough’ and ‘pizza sauce’).

This means we can do keyword research for a huge number of keywords in a single function all inside of Google Sheets.

Getting started

Using Google Sheets in this way has had a huge impact on the way we work – and we want others to benefit too, which is why we’ve created a free template.

To get started, simply make a copy of our Google Sheet template. Within this sheet we have created a custom function called =googleSuggest().

To run this function, type “=googleSuggest(keyword, language, modifiers)”. It then accepts a keyword, a language – such as ‘en’ for English, ‘fr’ for French or ‘de’ for German – and an optional array of additional keywords to modify our keyword with.

For example:

=googleSuggestions(“beth mead” , ”uk”)

=googleSuggestions(“pizza”, “uk”, {“dough”, “sauce”})

Behind the scenes

Behind the scenes our formula calls a custom function in Apps Script, which fetches data from the Google Suggest API.

If you want to view the code behind our custom function, navigate to the menu and tap on “Extensions” and “Apps Script”.

You’ll then see the following code:

/**
* Get suggestions from Google Suggest - provide a seed keyword, a language and words to concatenate.
*
* @customfunction
*/
function googleSuggestions(seed_keyword='football', lang='uk', concat=[['who', 'what', 'where', 'why', 'when', 'vs', 'how']]) {
 outputArr = [];
 concat = concat[0]
 for (let i = 0; i < concat.length; i++) {
   let keyword = `${seed_keyword}+${concat[i]}`;
   let api_uri = `https://suggestqueries.google.com/complete/search?&q=${keyword}&gl=${lang}&client=chrome&_=1663410981189`
   let data = UrlFetchApp.fetch(api_uri).getContentText();
   data = JSON.parse(data)
   data = data[1];
   for(let j =0; j < data.length; j++) {
     outputArr.push([seed_keyword, concat[i], data[j]])
   }
 }
 return outputArr;
}

Conclusion

And there you have it: keyword research with a custom function in App Script – and all within Google Sheets.

To break that down. With this template, you can now easily do keyword research at scale, streamline all your keyword research activities, and speed up the entire process while you’re at it.

Note

You’ll notice that the search volume for the keywords returned via the function is missing. Unfortunately, the Google Suggest API doesn’t provide this data – you would need to use Google’s Keyword Planner or a third party provider like Ahrefs or Semrush to get this information.

But, as we noted before, we’re not restricted to doing keyword research with the Google Suggest API. You can connect to any third party API you like (assuming you have an account and API keys that is) and update the App script code to pull in additional keyword data (such as search volume, CPC, keyword difficulty etc.)

Further reading

At Melt Digital, we have written a number of custom functions in App Script to add innovative functionality to Google Sheets to help automate daily SEO and content requirements.

For example, in our post on App Script SEO automation, we look at how you can crawl websites, get search volume for keywords and do simple forecasting all within Google Sheets.