Search API Connector Documentation

Print

Import Google Ads Keyword Planner Data to Google Sheets

Last Updated On
byAna
< Knowledge Base
premium

In this guide, we’ll walk through how to pull Keyword Planner data from the Google Ads / AdWords API data directly into Google Sheets, using the API Connector add-on for Sheets.

Google's Keyword Planner tool provides several different functions. In this article, we'll show how to discover new keywords and get keyword search volume through the API.

google-a_ds-keyword-planner-features

One of the best things about getting this data from the API vs. the interface is that the data is more precise. For example, where Keyword Planner says Avg. Monthly Searches = 1K – 10K, the API gives a number like 2400. It also enables automated keyword research through your sheet.

Related article: Import Google Ads Data to Sheets

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your Account IDs

Target Site ID

You'll need the ID of the site for which you'd like to retrieve data, so let's get that first from the login menu or the top navigation bar in your Google Ads account.

google-as-img3

That ID will be entered into the URL without dashes, e.g. https://googleads.googleapis.com/v13/customers/2668258503:generateKeywordIdeas

Manager Account ID

If your access to a customer account is through a manager account, you will also need to get the ID of that manager account (info).

This manager ID will be entered into a login-customer-id header, like this:

google-as-img14

In short, the request URL contains the ID of the account for which you'd like to fetch data, while the manager ID goes in the header as shown above.

Part 2: Pull Google Ads Keyword Ideas into Google Sheets

For this first request, we'll pull in a list of keyword ideas for your website. Here's an example request setup, just substitute your own account ID in the request URL where it says your_account_id, and your own website of interest.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • Application: Custom
    • Method: POST
    • Request URL: https://googleads.googleapis.com/v13/customers/target_account_id:generateKeywordIdeas
    • OAuth: Google Ads
    • Headers
      • content-type: application/json
      • login-customer-id: manager_id (if you're connecting through a manager account)
    • Request body: {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS", "urlSeed": {"url":"https://mixedanalytics.com"}}
  3. When you select Google Ads from the OAuth menu, you'll see a blue Connect button if you haven't already authorized the connection. Click through to enable.
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see a list of keyword ideas and associated metrics populate your sheet. google-a_ds-keyword-planner-response
  6. (Optional) Click Edit Fields and use the visual field editor to filter out all the fields but these: avgMonthlySearches, competitionIndex, lowTopOfPageBidMicros, highTopOfPageBidMicros, and text
Variation 1: get ideas with a keyword seed

Set this up exactly the same as above, but instead of adding a seed URL, enter a seed keyword like this:

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","keywordSeed":{"keywords":["my keyword"]},"pageSize":"1000"}
Variation 2: target a locale

Target locales through the geo_target_constants parameter. A list of geo target IDs (i.e. country codes) is provided here.

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"urlSeed":
{"url":"https://mixedanalytics.com"},
"geo_target_constants":["geoTargetConstants/2158","geoTargetConstants/2392"]}
Variation 3: target a language

Target a language through the language parameter. Language constant IDs are provided here.

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"keywordSeed":{"keywords":["水果"]},
"language":"languageConstants/1018"}

Part 3: Get Search Volume for a List of Keywords

We'll now show how to access Keyword Planner's other main functionality: retrieving historical data for a list of keywords. Below is an example request; substitute in your own account ID, keywords, dates, and geo IDs (here's a list of all geo IDs by country).

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • Application: Custom
    • Method: POST
    • Request URL: https://googleads.googleapis.com/v13/customers/target_account_id:generateKeywordHistoricalMetrics
    • OAuth: Google Ads
    • Headers
      • content-type: application/json
      • login-customer-id: manager_id (if you're connecting through a manager account)
    • Request body: {"keywords":["apples","bananas","cherries","durian","grapes","oranges","plums","strawberries"],"historicalMetricsOptions":{"yearMonthRange":{"start":{"year":2022,"month":"OCTOBER"},"end":{"year":2023,"month":"MARCH"}},"includeAverageCpc":true},"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","geo_target_constants":["geoTargetConstants/2158","geoTargetConstants/2392"]}
  3. When you select Google Ads from the OAuth menu, you'll see a blue Connect button if you haven't already authorized the connection. Click through to enable.
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Under Output options, select the grid style to see all months in one column, or the default (single row) style to see each month in its own column.
  6. Name your request and click Run. A moment later you’ll see a list of keyword ideas and associated metrics populate your sheet.
    google-a_ds-keyword-planner-volume

Reference a List of Keywords in Your Sheet

Tip: If you have a list of keywords in your sheet, you can join them into a single cell using a process like this:

  1. Create a helper column that adds quotes around each item using the function =CHAR(34)&A1&CHAR(34)
    google-a_ds-keyword-planner-addquotes
  2. Now join all those items into a comma separated list using the function =JOIN(",",B1:B3)
    google-a_ds-keyword-planner-join
  3. And now plug that list into your request body by referencing the cell in the request body, e.g. {"keywords":[+++Keywords!D1+++],"historicalMetricsOptions":{"yearMonthRange":{"start":{"year":2022,"month":"OCTOBER"},"end":{"year":2023,"month":"MARCH"}},"includeAverageCpc":true},"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","geo_target_constants":["geoTargetConstants/2158","geoTargetConstants/2392"]}
    google-a_ds-keyword-planner-referenceresponse

Note: API Connector will automatically route your request to the latest Google Ads API version, regardless of the version number in the URL.

Part 4: API Documentation

24 thoughts on “Import Google Ads Keyword Planner Data to Google Sheets”

  1. Can you give an example of adding location to this -

    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
    "keywordSeed":
    {"keywords": ["hello"]}
    }

    Reply

    • Sure, you'd do it like this:
      {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
      "keywordSeed":
      {"keywords": ["hello"]},
      "geo_target_constants":["geoTargetConstants/2158","geoTargetConstants/2392"]
      }

      Location IDs can be found here.

      Reply

  2. Hi Ana,

    Thanks for your amazing work! Would it be possible to start the keyword planner API from a cell input? I would like to start the keyword planner output based on a keyword you give in a certain cell. In your example you used the fixed example with 'hello world' but probably this could change to cell value

    Reply

  3. Hello,

    Is it possible to use this tool to get search volumes for a list of keywords already present in a gsheet? i.e.

    Phrase Avg monthly search volume
    phrase 1? 1000
    phrase 2? 2000

    Thank you

    Chris

    Reply

  4. First of all - THANK YOU SO MUCH FOR THIS - this walkthrough is incredible, and got me up and running on a project that I previously thought was impossible.

    One small hitch though - when pulling Historical Keyword Metrics, I can't get it to target the locale that I want. No matter if I change the Google Ads plan to target the keyword, or try some json in the request body, the tool always pulls the historical metrics from the USA only.

    How do you target a historical keyword metrics request to a different geo target? I'm happy with a manual method, just need to know how to do it!

    Thank you!

    Reply

    • Hi Jersh, glad this was useful. Unfortunately Google doesn't have an option for locale targeting after you've already created your keyword plan. So to do this I think you'd need to create a keyword plan from scratch as described in the appendix.
      Edit: I updated this article with a new/better method, you can now set your locale right in the request body using the geo_target_constants parameter.

      Reply

  5. Hello Ana,
    Thank you for your wonderful app.
    I've been trying to get some results but to no avail.
    Here's the request:
    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","keywordSeed":{"keywords":["credit", "cars"]}}

    And here's the error I get. Please may I request your assistance and pointing out what wrong.

    1) keyword gen: Completed with errors
    - We received an error from googleapis.com (400) show response
    { "error": { "code": 400, "message": "Invalid JSON payload received. Unknown name \"keywordSeed[keywords][0]\": Cannot bind query parameter. Field 'keywordSeed[keywords][0]' could not be found in request message.\nInvalid JSON payload received.

    Thanks!

    Reply

  6. Thank you very much Anna for all this amazing guide!!

    When I try to generate historical metrics I get the following error:

    { "error": { "code": 400, "message": "Request contains an invalid argument.", "status": "INVALID_ARGUMENT", "details": [ { "@type": "type.googleapis.com/google.ads.googleads.v10.errors.GoogleAdsFailure", "errors": [ { "errorCode": { "keywordPlanError": "KEYWORD_PLAN_NOT_FOUND" }, "message": "No plan with planId[*******] exists" } ], "requestId": "******" } ] } }

    I took the planid from the string planId=*******

    Reply

    • Hey Manuel, I believe that error message only displays if the plan ID is incorrect, so I can only suggest double checking the account ID and plan ID. Feel free to message support with a screenshot of your API Connector config, along with the UI where you can see the plan ID, if you'd like me to take a look.

      Reply

      • Hey Ana,

        I have the same Problem. I'm using a MCC Account and I'm not sure how to add the login-customer-id to your request URL accoring to the linked article. Could you provide an updated URL on this?

        Kind regards
        Katharina

      • Hey Katharina, I added a section on manager accounts to this article to make it more clear, please check and just let me know if you run into any issues.

    • Hey Manuel, I had the same problem. It seems that if you have an MCC the query doesn´t find the plan id. I recomend to use an account without MCC

      Reply

      • That's interesting, thanks Alex! If you're connecting through an MCC account, you should be able to retrieve the data by adding a login-customer-id header (info)

  7. Hello,

    That's a beautiful tutorial, But I am getting the following error after executing the call.

    { "error": { "code": 403, "message": "Request had insufficient authentication scopes.", "status": "PERMISSION_DENIED", "details": [ { "@type": "type.googleapis.com/google.rpc.ErrorInfo", "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT", "domain": "googleapis.com", "metadata": { "service": "googleads.googleapis.com", "method": "google.ads.googleads.v10.services.KeywordPlanIdeaService.GenerateKeywordIdeas" } } ] } }

    Reply

  8. Hey!

    Thanks!

    Is it possible to add a date range to:
    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS", "urlSeed": {"url":"https://mixedanalytics.com"}}

    L

    Reply

    • Sure, you can do it with the historicalMetricsOptions parameter like this: {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","urlSeed":{"url":"https://mixedanalytics.com"},"historicalMetricsOptions":{"includeAverageCpc":true,"yearMonthRange":{"start":{"year":"2022","month":"OCTOBER"},"end":{"year":"2022","month":"DECEMBER"}}}}

      Reply

Leave a Reply to Ana Cancel reply