Google Sheets Cheat Sheet

Google Sheets Keyword Research

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:

You can narrow your search using:

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:

shows the main interface of the keyword research google sheet
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
shows how to enter a keyword into the required field in the google sheet

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:

shows how to shortlist dropdown options in google sheets by typing into the relevant cell

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:

shows the script's required permissions when authorizing

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:

shows the script running popup that appears in google sheets whenever attached apps script runs in the background

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

shows the autocomplete data output that a user sees after a search is completed

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:

shows a new sheet created using the export me script attached to the button of the same name

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

The Google Autocomplete API puts a limit on how many searches you can do before it stops providing the results for a while.

Based on my testing this is around 15-20 little searches or 1-2 big searches.

When this happens the sheet will stop working so take a 30 minute break before you try again.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

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:

With that context, here's the code:

Dark theme
Copy 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:

Dark theme
Copy code
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

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

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.