Using Google Suggest API with Apps Scripts to automate long-tail keyword research in Sheets

Share this:

With the festive season done, it’s time to get back into the regular SEO grind—and in particular, getting prep work out of the way in preparation for next year.

A sizeable part of my plans for the coming year involve launching brand new sites, which due to their being brand new, I’ll be focusing primarily on creating and ranking content for zero-volume, long-tail keywords, as given the lack of age and authority, these are always going to be your best bet when it comes to early success.

Which brings us to the topic of this post—using the Google Suggest API with Apps Scripts to automate much of your keyword research, generating relevant autocomplete suggestions based on keywords using a simple custom function in a Google Sheet.

What the output looks like

The video below shows you how to use the script and the output you can expect from it.

Demo of the Google Suggest API Apps Script in action

The code

There are three parts to the code, which are broken down into the following functions:

KEYWORDS()—this is the Google Sheets Custom Function, which you call within your spreadsheet.

This function takes two parameters—your main “query” or keyword, and a two-letter country code used to localize the results returned from the Suggest API.

It then loads the list of modifiers (mentioned in the next function), looping through and processing these to return Autocomplete suggestions for each, putting them into an array and returning these for display in the Google Sheet.

function KEYWORDS(q,c) {
  var k = [];
  modifiers = modifiers();
  modifiers.forEach(function(m) {
    let r = googleAutocomplete(q, m);
    k.push(r);
  });
  k = [...new Set(k.flat())];
  var d = [];
  k.forEach(function(k) {
    d.push(Array(k));
  });
  return d;
}

Modifiers()—this is where your keyword modifiers are kept and called from, used to generate keywords from Google’s autocomplete suggestions.

Currently, I’m using a total of 38 one and two-word modifiers, although I plan to increase this. To give you a good reference point, Low Fruits uses some 200 modifiers—so for similar results, you’re going to want to be aiming for this sort of figure.

function modifiers() {
  var modifiers = [
    'can', 
    'do',
    'does',
    'how',
    'is',
    'what',
    'where',
    'which',
    'why',
    'will',
    'how to',
    'why is',
    'why are',
    'what are',
    'how does',
    'how much',
    'what does',
    'what is',
    'what do',
    'why do',
    'how long',
    'what type',
    'which of',
    'how many',
    'can you',
    'how did',
    'what size',
    'where do',
    'when did',
    'how big',
    'what not',
    'what to',
    'do you',
    'when can',
    'when to',
    'when should',
    'what can',
    'how do',
  ]
  return modifiers;
}

googleAutocomplete()—this is where fetch requests are made to the Suggest API, the XML responses parsed, and the suggestions returned to the main KEYWORDS() function.

function googleAutocomplete(q, m, c) {
  var r = UrlFetchApp.fetch(`https://suggestqueries.google.com/complete/search?output=toolbar&q=${m} ${q}&gl=${c}`);
  var xml = r.getContentText();
  var doc = XmlService.parse(xml);
  var root = doc.getRootElement(); 
  var suggestions = root.getChildren('CompleteSuggestion');
  var suggestionsArr = [];
  suggestions.forEach(function(autocomplete) {
   var suggestion = autocomplete.getChild('suggestion').getAttribute('data').getValue();
   suggestionsArr.push(suggestion);
  });
  return suggestionsArr;
}

The full code for the Apps Script can be found below, which you’ll need to copy and paste into your own Google sheet.

function KEYWORDS(q,c) {
  var k = [];
  modifiers = modifiers();
  modifiers.forEach(function(m) {
    let r = googleAutocomplete(q, m);
    k.push(r);
  });
  k = [...new Set(k.flat())];
  var d = [];
  k.forEach(function(k) {
    d.push(Array(k));
  });
  return d;
}

function modifiers() {
  var modifiers = [
    'can', 
    'do',
    'does',
    'how',
    'is',
    'what',
    'where',
    'which',
    'why',
    'will',
    'how to',
    'why is',
    'why are',
    'what are',
    'how does',
    'how much',
    'what does',
    'what is',
    'what do',
    'why do',
    'how long',
    'what type',
    'which of',
    'how many',
    'can you',
    'how did',
    'what size',
    'where do',
    'when did',
    'how big',
    'what not',
    'what to',
    'do you',
    'when can',
    'when to',
    'when should',
    'what can',
    'how do',
  ]
  return modifiers;
}

function googleAutocomplete(q, m, c) {
  var r = UrlFetchApp.fetch(`https://suggestqueries.google.com/complete/search?output=toolbar&q=${m} ${q}&gl=${c}`);
  var xml = r.getContentText();
  var doc = XmlService.parse(xml);
  var root = doc.getRootElement(); 
  var suggestions = root.getChildren('CompleteSuggestion');
  var suggestionsArr = [];
  suggestions.forEach(function(autocomplete) {
   var suggestion = autocomplete.getChild('suggestion').getAttribute('data').getValue();
   suggestionsArr.push(suggestion);
  });
  return suggestionsArr;
}

How to use it

It’s easy to use and setup, simply follow the steps I’ve outlined below—it shouldn’t take more than a minute to have it up and running.

  1. Create a new Google Sheet

  2. From the menu, select “Extensions” and then “Apps Script


  3. Create a new script using the “+” button then selection “Script

  4. Copy the full code for the script from the “The code” section of this post and paste into the new Apps Script

  5. Click the save icon to save the Apps Script

  6. Input a two-letter country code and keyword into two separate cells, then use the “=KEYWORDS()” custom function, including these two cells as parameters—with keyword first and country second.

And that’s it—pretty simple right? As always, if you have any questions or run into any difficulty when setting it up, let me know in the comments and I’ll do my best to give you hand.

Summing it up and next steps

This is very much the first step of my keyword research / content planning Google Sheet, and I intend to work on it over the next week, adding more functionality to it before we start the new year, so that I’m raring to go for the handful of sites I have in the works for 2023.

The first of these additions is going to be to add difficulty scoring—much like Low Fruits has—using a SERP API to pull ranked competitors and calculating what percentage of these are sites like Reddit, Quora, and forums.

I’ve already got a fair bit of this code knocking around from a half-finished project last year as well as other posts on this blog, like my recent post on how to check if Reddit or Quora are ranking for a given keyword, so this should be fairly easy to revisit and revise to implement as an extra Apps Script in the Google Sheet.

Beyond this, I’ve seen a people pulling People Also Ask (PPA) and Related Searches from the SERPs, to help populate and create a structure for articles—which makes sense to do, as I’m going to be using tokens anyway, so I’ll have this data at no additional cost.

There’s also the question of combining this with OpenAI for things like keyword clustering and/or to get a good part of the way to automating content creation. I’m not sure to what extent I’ll work to incorporate that into it right now, but it’s certainly worth a look, and could go a long way to maximizing automation and driving down costs associated with copy.

If you found this useful and are interested in using a similar sheet as part of your own content planning, be sure to come back over the coming days for a progress update and additional Apps Scripts that you can copy and paste for use in your own Google Sheets.

Additionally, if you’ve got any thoughts or feedback, you’re always welcome to share these in the comments below. Thanks for reading!

Share this:

Leave a Comment