How to Automate Your Keyword Research in Google Sheets

The days of manual inputting single keywords into Google’s Keyword Planner to carry out keyword research are over. Currently, with a bit of programming know-how, it is possible to write simple programs (scripts) to perform keyword research at speed and at scale – and all within Google Sheets.

How it works

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

The 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.

It is possible to 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.

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

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 the formula calls a custom function in Apps Script, which fetches data from the Google Suggest API.

If you want to view the code behind the 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

So, that’s it, using the tool above, you can perform keyword research with a custom function in App Script – and all within Google Sheets.

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

Note

Please note 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 noted before, it’s 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.)