image
Optimization Up
Building Keywords Generator with Google Sheets & Keyword Planner API
Optimize

Building Keywords Generator with Google Sheets & Keyword Planner API

Google Ads Keyword Planner is a powerful tool that can help you discover new keywords for your paid campaigns and even improve your SEO ranking. However, the process of keyword research can be tedious and time-consuming, especially if you’re managing multiple campaigns at once.

But what if you could automate the process of keyword research and generate hundreds of keywords in just a few clicks? That’s where Google Sheets comes in. By combining the power of Google Ads Keyword Planner with the automation capabilities of Google Sheets, you can create a keyword generator that will save you time and effort.

In this article, I’ll show you how to use Google Ads Keyword Planner API and Google Sheets together to create a keyword generator that will help you find the best keywords for your campaigns in no time. Whether you’re a marketer, SEO specialist, or PPC expert, this guide will help you optimize your campaigns and achieve better results. Let’s get started!

Before we dive into the tutorial on how to use Google Ads Keyword Planner in Google Sheets, there are a few prerequisites you need to follow along.

  • Firstly, you’ll need access to Google Ads API to use the keyword planner.
  • Additionally, some familiarity with Python is necessary, but don’t worry, I will be providing you with all the code you need.
  • Furthermore, while JavaScript knowledge is a plus, it’s not necessary to follow along with this tutorial.


Assuming you have all the prerequisites covered, we will be following these steps

  1. Use Google Ads API to generate keyword ideas: We’ll be using the Google Ads API to generate keyword ideas for your campaigns. This API lets us fetch data from Google Ads accounts, such as keyword suggestions and search volume.
  2. Create a Cloud Function as an API endpoint: Once we have the keywords generated, we’ll create a Cloud Function in Google Cloud Platform to serve as an API endpoint. This endpoint will allow us to fetch the data from the Google Ads API and use it in Google Sheets.
  3. Call the API endpoint from Google Apps Script: With the API endpoint in place, we can call it from Google Apps Script. We’ll create custom functions and formulas in Google Sheets to analyze the keyword suggestions and generate additional keywords based on our criteria.

Step 1: Generate keywords ideas through Google Ads API

Before we can generate keyword ideas using Google Ads Keyword Planner, we need to authenticate our Google Ads API client. To do this, we need to obtain a refresh token and use it to get an access token, which we’ll use to make requests to the API. If you don’t have a refresh token yet, you can generate one using the script provided in Google’s documentation.

Once you have your refresh token, and all auth details like client_id, and client_secret. You can add the details to the following python function.

The generate_keyword_ideas() function is used for t several parameters:

  • The keyword_text parameter allows you to specify the seed keywords for the keyword ideas.
  • The page_url parameter allows you to specify a landing page to generate keyword ideas
  • The location parameter allows you to specify the geographical location to generate keyword ideas. The function accepts only a limited set of location codes. The location codes represent the geographical locations that the Google Ads Keyword Planner supports. By default, the function uses the location code, which represents the United States. And supports 5 locations 'AE', 'US', 'ES', 'FR', 'IT' If you want to generate keyword ideas for different locations, you’ll need to provide the corresponding location code from this reference list.
  • The language parameter allows you to specify the language of the keyword ideas. The function accepts only five language codes, which are 'AR', 'EN', 'ES', 'FR', 'IT' These codes represent the languages that the Google Ads Keyword Planner supports. If you need to generate keyword ideas in a language that’s not listed here, you can include the corresponding language code from this reference list.
  • The search_volume parameter, when set to True, returns the search volume data for the generated keyword ideas.
  • Finally, the size parameter specifies the maximum number of keyword ideas to generate.


import google.ads.googleads.client


def generate_keyword_ideas(keyword_text=None, page_url=None, location=None, language=None, search_volume=False, size=100):
    
    customer_id = 'Your_Customer_ID'
    
    dict_env = {
        "developer_token": 'YOU_DEV_TOKEN',
        "client_id": 'CLIENT_ID',
        "client_secret":  'CLIENT_SECRET',
        "refresh_token":  'REFERSH_TOKEN',
        "login_customer_id":  customer_id,
        "use_proto_plus": False
    }

    location_ids = [format_location(location)]
    language_id = format_language(language)

    if keyword_text:
        keyword_text = [keyword_text]

    client = (google.ads.googleads.client.GoogleAdsClient
              .load_from_dict(dict_env))
    keyword_plan_idea_service = client.get_service("KeywordPlanIdeaService", version="v13")

    keyword_annotation = client.get_type(
        "KeywordPlanKeywordAnnotationEnum").KEYWORD_CONCEPT
    keyword_plan_network = (
        client.enums.KeywordPlanNetworkEnum.GOOGLE_SEARCH_AND_PARTNERS)
    location_rns = _map_locations_ids_to_resource_names(client, location_ids)
    language_rn = client.get_service(
        "GoogleAdsService").language_constant_path(language_id)

    # Either keywords or a page_url are required to generate keyword ideas
    # so this raises an error if neither are provided.
    if not (keyword_text or page_url):
        raise ValueError(
            "At least one of keywords or page URL is required, "
            "but neither was specified."
        )

    request = client.get_type("GenerateKeywordIdeasRequest")
    request.customer_id = customer_id

    request.geo_target_constants.extend(location_rns)
    request.language = language_rn
    request.include_adult_keywords = False
    request.keyword_plan_network = keyword_plan_network
    request.keyword_annotation.extend([keyword_annotation])

    if not keyword_text and page_url:
        request.url_seed.url = page_url

    if keyword_text and not page_url:
        request.keyword_seed.keywords.extend(keyword_text)

    if keyword_text and page_url:
        request.keyword_and_url_seed.url = page_url
        request.keyword_and_url_seed.keywords.extend(keyword_text)

    keyword_ideas = keyword_plan_idea_service.generate_keyword_ideas(
        request=request)

    # check if search volume - and return 2 dimensional array [keyword, search_volume]
    if search_volume:
        keywords = [[keyword.text, keyword.keyword_idea_metrics.avg_monthly_searches]
                    for keyword in keyword_ideas]

    else:
        keywords = [keyword.text for keyword in keyword_ideas]

    if keywords:
        keywords = keywords[:size]

    return keywords

def map_keywords_to_string_values(client, keyword_text):
    keyword_protos = []
    for keyword in keyword_text:
        string_val = client.get_type("StringValue")
        string_val.value = keyword
        keyword_protos.append(string_val)
    return keyword_protos


def _map_locations_ids_to_resource_names(client, location_ids):
    build_resource_name = client.get_service(
        "GeoTargetConstantService"
    ).geo_target_constant_path

    return [build_resource_name(location_id) for location_id in location_ids]

###### all locations ids can be found here: https://developers.google.com/google-ads/api/reference/data/geotargets ######
def format_location(location):
    if location == 'AE':
        location_id = '2784'
    elif location == 'FR':
        location_id = '2250'
    elif location == 'IT':
        location_id = '2380'
    elif location == 'US':
        location_id = '2840'
    elif location == 'ES':
        location_id = '2724'

    return location_id

###### all language ids can be found here: https://developers.google.com/google-ads/api/reference/data/codes-formats#languages ######
def format_language(language):
    if language == 'EN':
        language_id = '1000'
    elif language == 'AR':
        language_id = '1019'
    elif language == 'FR':
        language_id = '1002'
    elif language == 'ES':
        language_id = '1003'
    elif language == 'IT':
        language_id = '1004'

    return language_id


Step 2: Create an API endpoint using Cloud Function

After generating a list of keyword ideas using the generate_keyword_ideas() function, we need to create an API endpoint to make these keyword ideas accessible from Google Sheets. To do this, we’ll use Google Cloud Functions.

Google Cloud Functions is a serverless compute service that lets you run code in response to events without having to manage a server. It’s a great tool for creating lightweight API endpoints that can be accessed from Google Sheets or other applications.

In this step, we’ll create a Cloud Function that takes multiple parameters including the keyword criteria as input and returns a list of keyword ideas generated by the generate_keyword_ideas() function.

  • Create the Cloud Function, navigate to the Google Cloud console, then search for Cloud Functions
  • Click on the “Create Function” button to create a new Cloud Function.
  • Choose a name for your Cloud Function and select the region where it will run. Our name will be: keyword_generator
  • Under the “Trigger” section, select “HTTP” as the trigger type.
  • Set the “Memory allocated” for your Cloud Function – we recommend setting it to 1 GB, which should be more than enough for this job. You can adjust this value later if needed. Finally, click ‘Next’


  • In the “Source code” section, select “Inline editor” as the source code option and select ‘Python 3.10’ as the runtime version:
  • Copy and paste the code for the Cloud Function into the editor. Here’s the full code of our API Endpoint. I just added a main() function where we can accept parameters from the API endpoint. 
import google.ads.googleads.client


def main(request):

    # Get the request arguments from the request
    request_args = request.args

    # If no request args, return error
    if not request_args:
        return 'Error: No request_args provided. Please specify a seed_keyword or page_url.'

    # If no seed keyword or page URL, return error
    if 'seed_keyword' not in request_args and 'page_url' not in request_args:
        return 'Error: No seed_keyword or page_urls field provided. Please specify a seed_keyword or page_url.'

    # Set the default values
    seed_keyword = None
    page_url = None
    location = 'US'
    language = 'EN'
    search_volume = False
    size = 100

    # Get the values from the request
    if 'seed_keyword' in request_args:
        seed_keyword = request_args['seed_keyword']
    elif 'page_url' in request_args:
        page_url = request_args['page_url']
    if 'location' in request_args:
        location = request_args['location']
    if 'language' in request_args:
        language = request_args['language']
    if 'search_volume' in request_args:
        # parse the search_volume value to a boolean
        if request_args['search_volume'] == 'true':
            search_volume = True
        else:
            search_volume = False
    if 'size' in request_args:
        size = request_args['size']

    # Get the keywords from the API
    keywords = generate_keyword_ideas(
        seed_keyword, page_url, location, language, search_volume, size)

    # Return the keywords
    return {'data': keywords}


def generate_keyword_ideas(keyword_text=None, page_url=None, location=None, language=None, search_volume=False, size=100):
    
    customer_id = 'Your_Customer_ID'
    
    dict_env = {
        "developer_token": 'YOU_DEV_TOKEN',
        "client_id": 'CLIENT_ID',
        "client_secret":  'CLIENT_SECRET',
        "refresh_token":  'REFERSH_TOKEN',
        "login_customer_id":  customer_id,
        "use_proto_plus": False
    }


    location_ids = [format_location(location)]
    language_id = format_language(language)

    if keyword_text:
        keyword_text = [keyword_text]


    client = (google.ads.googleads.client.GoogleAdsClient
              .load_from_dict(dict_env))
    keyword_plan_idea_service = client.get_service("KeywordPlanIdeaService", version="v13")

    keyword_annotation = client.get_type(
        "KeywordPlanKeywordAnnotationEnum").KEYWORD_CONCEPT
    keyword_plan_network = (
        client.enums.KeywordPlanNetworkEnum.GOOGLE_SEARCH_AND_PARTNERS)
    location_rns = _map_locations_ids_to_resource_names(client, location_ids)
    language_rn = client.get_service(
        "GoogleAdsService").language_constant_path(language_id)

    # Either keywords or a page_url are required to generate keyword ideas
    # so this raises an error if neither are provided.
    if not (keyword_text or page_url):
        raise ValueError(
            "At least one of keywords or page URL is required, "
            "but neither was specified."
        )

    request = client.get_type("GenerateKeywordIdeasRequest")
    request.customer_id = customer_id

    request.geo_target_constants.extend(location_rns)
    request.language = language_rn
    request.include_adult_keywords = False
    request.keyword_plan_network = keyword_plan_network
    request.keyword_annotation.extend([keyword_annotation])

    if not keyword_text and page_url:
        request.url_seed.url = page_url

    if keyword_text and not page_url:
        request.keyword_seed.keywords.extend(keyword_text)

    if keyword_text and page_url:
        request.keyword_and_url_seed.url = page_url
        request.keyword_and_url_seed.keywords.extend(keyword_text)

    keyword_ideas = keyword_plan_idea_service.generate_keyword_ideas(
        request=request)

    # check if search volume - and return 2 dimensional array [keyword, search_volume]
    if search_volume:
        keywords = [[keyword.text, keyword.keyword_idea_metrics.avg_monthly_searches]
                    for keyword in keyword_ideas]

    else:
        keywords = [keyword.text for keyword in keyword_ideas]

    if keywords:
        keywords = keywords[:size]

    return keywords


def map_keywords_to_string_values(client, keyword_text):
    keyword_protos = []
    for keyword in keyword_text:
        string_val = client.get_type("StringValue")
        string_val.value = keyword
        keyword_protos.append(string_val)
    return keyword_protos


def _map_locations_ids_to_resource_names(client, location_ids):
    build_resource_name = client.get_service(
        "GeoTargetConstantService"
    ).geo_target_constant_path

    return [build_resource_name(location_id) for location_id in location_ids]


###### all locations ids can be found here: https://developers.google.com/google-ads/api/reference/data/geotargets ######
def format_location(location):
    if location == 'AE':
        location_id = '2784'
    elif location == 'FR':
        location_id = '2250'
    elif location == 'IT':
        location_id = '2380'
    elif location == 'US':
        location_id = '2840'
    elif location == 'ES':
        location_id = '2724'

    return location_id

###### all language ids can be found here: https://developers.google.com/google-ads/api/reference/data/codes-formats#languages ######
def format_language(language):
    if language == 'EN':
        language_id = '1000'
    elif language == 'AR':
        language_id = '1019'
    elif language == 'FR':
        language_id = '1002'
    elif language == 'ES':
        language_id = '1003'
    elif language == 'IT':
        language_id = '1004'

    return language_id
  • Also, in the requirements.txt file. make sure to add google-ads which is the Python package we need to call Google Ads API.
  • In the “Entry point” field, enter the name of the function that we will be calling from the API endpoint. In our case, we have named our function main(), so enter main in the “Entry point” field. This tells Cloud Functions which function to call when a request is received.


We’re now ready to deploy our Cloud Function. Click on the “Deploy” button to start the deployment process. This may take a few moments to complete, so be patient. Once the deployment is complete, you should see a success message confirming that your function has been deployed.

After the deployment is complete, you’ll need to copy the URL of the Cloud Function. You can find the URL in the “Trigger” section of the Cloud Functions page. Click on the name of your function to open its details page, then scroll down to the “Trigger” section. You should see a URL listed under “Trigger URL”.


Copy this URL and save it somewhere – we’ll need it in the next step when we configure our Google Apps Script to call the Cloud Function.


Step 3: Call the API endpoint from Google Apps Script

To create a custom function in Google Sheets, open your spreadsheet and click on the “Tools” menu. Then select “Script editor” to open the Apps Script editor.

We’ll create 3 custom functions/formulas that will help automate the process of keyword research and generate a list of high-quality keywords in just a few clicks:

  1. generateKeywords(SEED_KEYWORD, LOCATION, LANGUAGE): This function accepts a mandatory field of SEED_KEYWORD and two optional parameters, LOCATION and LANGUAGE.
  2. generateKeywordsWithSearchVolume(SEED_KEYWORD, LOCATION, LANGUAGE): This function is similar to the previous one but also includes search volume data.
  3. generateKeywordsFromPageUrl(PAGE_URL, LOCATION, LANGUAGE): This function generates keyword ideas based on a given webpage’s content.

Here’s the full code for the three functions. Copy and paste them into your script editor – make sure to replace function_url with your Cloud Function URL.

var function_url = 'ADD_Your_Cloud_Function_URL'
/**
 * Returns an array of keywords API.
 *
 * @param {string} SEED_KEYWORD - The seed keyword to use for generating related keywords.
 * @param {string} [LOCATION] - (optional) The location to use for generating search volume data.
 * @param {string} [LANGUAGE] - (optional) The language to use for generating search volume data.
 * @return {Array} An array of keyword
 * @customfunction
 */
function generateKeywords(SEED_KEYWORD, LOCATION, LANGUAGE) {
  
  function_url += 'seed_keyword=' + SEED_KEYWORD;
  
  if (LOCATION) {
    function_url += '&location=' + LOCATION;
  }
  
  if (LANGUAGE) {
    function_url += '&language=' + LANGUAGE;
  }
  
  console.log(function_url);
  var res = UrlFetchApp.fetch(function_url);
  console.log(res);
  var data = JSON.parse(res.getContentText()).data;
  var result = [];
  for (var i = 0; i < data.length; i++) {
    result.push([data[i]]);
  }
  return result;
}


/**
 * Returns an array of keywords with their corresponding search volume from a specified API.
 *
 * @param {string} SEED_KEYWORD - The seed keyword to use for generating related keywords.
 * @param {string} [LOCATION] - (optional) The location to use for generating search volume data.
 * @param {string} [LANGUAGE] - (optional) The language to use for generating search volume data.
 * @return {Array} An array of keyword and search volume pairs.
 * @customfunction
 */
function generateKeywordsWithSearchVolume(SEED_KEYWORD, LOCATION, LANGUAGE) {
  
  function_url += 'seed_keyword=' + SEED_KEYWORD;
  function_url += '&search_volume=true'
  if (LOCATION) {
    function_url += '&location=' + LOCATION;
  }
  
  if (LANGUAGE) {
    function_url += '&language=' + LANGUAGE;
  }
  
  console.log(function_url);
  var res = UrlFetchApp.fetch(function_url);
  console.log(res);
  var data = JSON.parse(res.getContentText()).data;
  var result = [];
  for (var i = 0; i < data.length; i++) {
    result.push([data[i][0], data[i][1]]);
  }
  return result;
}


/**
 * Returns an array of keywords from page url.
 *
 * @param {string} PAGE_URL - The seed keyword to use for generating related keywords.
 * @param {string} [LOCATION] - (optional) The location to use for generating search volume data.
 * @param {string} [LANGUAGE] - (optional) The language to use for generating search volume data.
 * @return {Array} An array of keyword and search volume pairs.
 * @customfunction
 */
function generateKeywordsFromPageUrl(Page_URL, LOCATION, LANGUAGE) {
  
  function_url += 'page_url=' + Page_URL;
  if (LOCATION) {
    function_url += '&location=' + LOCATION;
  }
  
  if (LANGUAGE) {
    function_url += '&language=' + LANGUAGE;
  }
  
  console.log(function_url);
  var res = UrlFetchApp.fetch(function_url);
  console.log(res);
  var data = JSON.parse(res.getContentText()).data;
  var result = [];
  for (var i = 0; i < data.length; i++) {
    result.push([data[i]]);
  }
  return result;
}


Once you’ve added the code, save the script file and switch back to your spreadsheet. You should now be able to use your custom function in any cell by simply typing its name and passing in the appropriate arguments.