Google Sheets Keyword Research

Kieran Dixon Updated:  January 6, 2022

I have a website called You Autocomplete Me.

It's a tool I built that allows online marketers to quickly extract Google's autocomplete data.

It allows you to enter a partial search (like "best dog for") and get a lot of potential keywords in seconds based on extending the search with additional characters:

• "best dog for a"
• "best dog for b"
• "best dog for c"

You can narrow your search using:

• 6 search types in
• 191 geographic regions and
• 228 languages

It does all of this by querying the Google Autocomplete API.

For 'Little' searches it completes 36 requests (A-Z and 0-9).

For 'Big' searches it completes 676 requests (Aa - Zz).

It's a great tool to find keywords quickly… so I made it into a spreadsheet using Google Sheets:

Free Sheet

Google Sheets Keyword Research Template

Find thousands of keywords in seconds using Google's Autocomplete API.

Get Sheet

This sheet has all of the functionality of the online tool but you get to have your own version on your own Google Drive!

Completing Keyword Research

Complete the five fields pointed to by the ➜ character:

1. Enter a partial keyword (let's stick with the example from above: "best dog for")
2. Choose a search type (Google, YouTube, Images, News, Shopping, Books)
3. Choose a location (from the 191 geographic regions listed)
4. Choose a language (from the 228 languages and dialects listed)
5. Decide whether you want a 'Little' search or a 'Big' one

There are a lot of dropdown options for 'Location ➜' and 'Language ➜'.

You can quickly find options by typing into the dropdown cell to see a shortlist of matching options:

Then click the AUTOCOMPLETE ME button to fetch the autocomplete data from Google.

The first time you click a button in this sheet you'll need to authorize the underlying script for the following permissions:

Here's a script authorization guide if you're unsure about this process.

While the script runs you'll see a small popup at the top of the screen that you can ignore:

When the script is finished the autocomplete data should appear in the sheet:

If you want to save this data you can click the EXPORT ME button.

This creates a new sheet with just the data from the most recent search and includes the size of the search (LITTLE or BIG) in the name:

This can be exported and uploaded to other tools to determine search volume and other relevant metrics.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

How It Works

So far you've learned what this tool is and how to use it.

Now comes the technical explanation of the Apps Script code that does all of the hard work.

The most interesting part of the code is fetching the data from the Google Autocomplete API.

The other stuff is getting data out of the sheet and putting it back in based on a specific format. That's cool but it's not the engine of the sheet.

You can review the code in your own version of the sheet, but for this explanation all you need to know is that the searchTerms variable is an array that's either:

• Little = ["a", "b", ..., "8", "9"]
• Big = ["aa", "ab", ..., "zy", "zz"]

With that context, here's the code:

  const output = [];

  searchTerms.forEach(searchTerm => {

    const url = `https://suggestqueries.google.com/complete/search?` +
      `q=${query} ${searchTerm}` +
      `&client=chrome` +
      `&ds=${searchType}` +
      `&gl=${country}` +
      `&hl=${language}`;

    const response = UrlFetchApp.fetch(url, { "muteHttpExceptions": true });
    const result = JSON.parse(response.getContentText());
    const keywords = result[1];

    //For each keyword in the array...
    keywords.forEach((keyword, i) => {

      const heading = i === 0 ? searchTerm.toUpperCase() : '';
      output.push([heading, keyword]);

    });

  });

You iterate over the searchTerms and make a call to the autocomplete API for each combination of the user-entered query and the current searchTerm with the additional search type, location, and language data points appended to the URL.

After parsing the response the result you get looks like this:

const result = ['best dog for a',
  ['best dog for apartment',
    'best dog for apartment living',
    'best dog for allergies',
    'best dog for anxiety',
    'best dog for a family',
    'best dog for autism',
    'best dog for apartment australia',
    'best dog for australian climate'],
  ['', '', '', '', '', '', '', ''],
  [],
  {
    'google:clientdata': { bpc: false, tlw: false },
    'google:suggestrelevance': [1250, 1050, 800, 601, 600, 552, 551, 550],
    'google:suggestsubtypes':
      [[Object],
      [Object],
      [Object],
      [Object],
      [Object],
      [Object],
      [Object],
      [Object]],
    'google:suggesttype':
      ['QUERY',
        'QUERY',
        'QUERY',
        'QUERY',
        'QUERY',
        'QUERY',
        'QUERY',
        'QUERY'],
    'google:verbatimrelevance': 851
  }];

The keywords are located at index one of the result array.

You then iterate through the keywords adding them (along with the heading) to the final output array which is what is placed back into the sheet for the user to see.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

Authorization Required: How To Authorize Google Apps Script Apps

Need to authorize a Google Apps Script app? Here is everything you need to know to have your script running in less than a minute.

Google Sheets Animation

You can animate an image in Google Sheets! Using pixel art and Apps Script you can create a short gif inside a spreadsheet.

How To Convert A Google Sheets Database Into An Array Of Objects Using Apps Script

Here's two Apps Script code snippets that quickly and easily allow you to convert a Google Sheets database into a useful array of objects.