API Connector Documentation

Print

Import Google Ads Keyword Planner Data to Google Sheets

Last Updated OnOctober 9, 2024
byAna
< Knowledge Base

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/v17/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/v17/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", "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/v17/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","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
Variation 1: target a language

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

{"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"],"language":"languageConstants/1000"}

Note: To prevent errors from using deprecated versions of the API, API Connector will automatically route your request to a current Google Ads API version, regardless of the version number in the URL.

Part 4: Reference a List of Keywords in Your Sheet

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","geo_target_constants":["geoTargetConstants/2158","geoTargetConstants/2392"]}
    google-a_ds-keyword-planner-referenceresponse

Part 5: API Documentation

50 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

  10. hello, i was able to make the connection successfully and bring in some basic information. Is there an option to bring in CPC for each keyword?

    Reply

    • I think both of the examples in this article already include this (the keyword ideas request includes bid amounts for each keyword, and the historical search volume request includes average CPC for each keyword). Is that what you're looking for, or something else?

      Reply

  11. The example from your post does not work for me:

    {"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"]}

    It only returns the keywords themselves.
    I manage to pull keyword idea with additional keywords and search volume. So the tool and connection works. But sometimes like this I get too much data (lol). So I wanted to only pull data for the specific keywords (and not related keyword data). However, as described, it does not work with the example above.

    Any idea on how to fix?

    Reply

    • I just retested and it still works on this side (i.e. I get full response data including search volumes, bid amounts, competition amount, etc). If you're not seeing all available fields please check if you've filtered them out. Do you see the missing fields in the field editor?

      Reply

    • The example requests show how to set a geo_target_constants parameter to filter by locale. Do you mean you have multiple locales and you'd like to run a request for each one, i.e. treat geo target as a dimension rather than a filter? Google Ads' API doesn't have this functionality directly, but you could use API Connector's multi-query feature to cycle through a list of geo target parameters and run a request for each one.

      Reply

  13. Hello! Is there any way how to automatically place the search volumes of the keywords fulled in on [+++Blad4!D1+++] to Sheet5 , for example Sheet5!K24 (August) and Sheet5!K25 (September)?

    The idea is that you have to press RUN once per month and it automatically fills in the the the search volume of the keywords per month on a different sheet. (Not only for the past months but also usable for upcoming months that dont have any volume right know)

    Reply

    • Hey Thomas, sorry, API Connector doesn't have any functionality for assigning sheets dynamically. The output of any saved request will print to the same sheet each time it runs, though you can prevent it from overwriting the existing data by using Append mode. That's an interesting idea though, I'll think about whether that's something we could implement in the future.

      Reply

      • Hey Ana, thanks for the quick response. Your answer is crystal clear. I am going to experiment with append mode soon. However, I still run into another problem. I saw that Alec has a geo-target question, and I wanted the same setting for the Netherlands. But I can't figure out the code.

        After adding the mentioned code, I get the error message: Request failed: Invalid JSON in request body.

        Is my numeric incorrect or am I using the wrong code?

        I'm using the following code: "geo_target_constants":["geoTargetConstants/2530", "geoTargetConstants/2530"]}

  14. Hi Ana, thanks for this amazing guide. I've implemented it for my company and i bought a paid plan of the API connector!
    I've just one question. I would like to take only the data for the exact match of my KWs list. How can i do it?

    many thanks

    Reply

    • Thanks, Marco! You're referring to the generateKeywordHistoricalMetrics method, right? There's no option to set broad vs exact match for this, you can see all the available options here. But I don't see any difference in the results via the API from the results in Keyword Planner, do you?

      Reply

  16. Hi, thanks for this great guide - it has been a lifesave. I've just one quick question, I would like to remove geo location in order to get worldwide data, But I can't figure out the code:
    {
    "keywords": ["CAR"],
    "historicalMetricsOptions": {
    "yearMonthRange": {
    "start": {
    "year":2020,
    "month": "MAY"
    },
    "end": {
    "year": 2024,
    "month": "APRIL }
    },
    "includeAverageCpc": true
    },
    "keywordPlanNetwork": "GOOGLE_SEARCH"] }

    Reply

    • You're welcome! I think you just had an extra bracket at the end, please try the following instead: {"keywords":["CAR"],"historicalMetricsOptions":{"yearMonthRange":{"start":{"year":2020,"month":"MAY"},"end":{"year":2024,"month":"APRIL"}},"includeAverageCpc":true},"keywordPlanNetwork":"GOOGLE_SEARCH"}

      Reply

  17. Hi Ana,

    thank you so much for the tutorial! I am a complete beginner and this was so clear.

    I have followed the guide and substituted in my search terms as follows '{"keywords":["balloon skirt","bubble skirt","capri pants","drop waist skirt","drop waist dress","drop waist midi dress","lace maxi skirt","white lace mini skirt","white maxi skirt"],"historicalMetricsOptions":{"yearMonthRange":{"start":{"year":2023,"month":"JULY"},"end":{"year":2024,"month":"JULY"}},"includeAverageCpc":true},"keywordPlanNetwork":"GOOGLE_SEARCH","geo_target_constants":["geoTargetConstants/21135","geoTargetConstants/20339"],"language":"languageConstants/1000"}'

    This all seems to work, however when I go in to Keyword planner and search these terms individually, the Monthly Seach Volumes are not the same as what has been pulled into my sheet.

    Eg, the current av search for 'balloon skirt' for US & UK combined is 6,600 but when I pulled the data using your method the number is 3,600.

    Can you tell me where I'm going wrong please?

    Reply

    • Both the API and keyword planner numbers come directly from Google, so if they're different that means one of the parameters is different. Please double check that you're using the same geoTargetConstant, languageConstant, and same keywordPlanNetwork. Your API query is set to search GOOGLE_SEARCH, is that what your Keyword Planner request is set to? What happens if you switch it to GOOGLE_SEARCH_AND_PARTNERS?

      Reply

  18. Hi Ian, thank you so much! You were right, it was the geoTargetConstant that was incorrect and throwing off the numbers 🙂

    Reply

  19. Hi, is there any way I can format the data with a space in between each keyword? I want to create a conditional formatting code to highlight if the numbers go up or down and when I re-run the API it resets to without spaces, hence the conditional formatting messes up.

    Thanks

    Reply

  20. Hello, I would like to ask how I should structure my JSON if I want the keyword search volume to be the average monthly search volume. I want to obtain this information rather than having a search volume for each month.

    Reply

    • HI Casey - the average monthly search volume should be returned in the following column: "results.keywordMetrics.avgMonthlySearches". You can use the "Edit fields" tool to filter out any unwanted fields. e.g. the search volume for an individual month

      Reply

Leave a Comment

Jump To