Search API Connector Documentation

Print

Import Google Ads Data to Google Sheets

In this guide, we’ll walk through how to pull Google Ads / AdWords API data data directly into Google Sheets, using the API Connector add-on for Sheets. Google provides a free add-on for Google Ads here , so you can give that one a try if you’d prefer not to work with the API directly.

If you’re still here, let’s go! We’ll get started digging into the Google Ads API, including campaign performance and keyword ideas. This API only supports authentication via OAuth2, so we’ll be connecting using API Connector’s built-in OAuth2 integration for Google Ads. This is a paid feature; please install API Connector for a free trial or upgrade to access.

Contents

Before You Begin

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

Part 1: Connect to the Google Ads API

If you haven’t connected API Connector to Google Ads before, you’ll first need to initiate the connection as follows:

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find “Google Ads” and click Connect.
    google-as-img1
  3. You will be directed to google.com and asked to allow API Connector to manage your AdWords campaigns:
    google-as-img2
  4. You’ll then be returned to your Google Sheet, and can verify that your Google Ads connection is active in the Connections screen.

Part 2: Create Your Google Ads API Request

For our first request, we’ll pull in ads performance metrics from your account.

  1. First, get your account ID as we’ll need this for subsequent requests. You can get your account ID from the login menu or the top navigation bar in your Google Ads account.
    google-as-img3

    (see this note if you’re accessing a client account through a manager account)

  2. Next, create your request URL. The Google Ads API is accessed by sending POST requests to the following API Request URL, substituting in your own account ID where it says 2668258503 (don’t include the dashes from your account ID).
    https://googleads.googleapis.com/v8/customers/2668258503/googleAds:searchStream
  3. Finally, create your POST body. All the details of the request, e.g. metrics, dimensions, filters, and so on, are specified in the POST body. We’ll start with an example that gets key data about your campaigns.
    {"query": " SELECT campaign.name, campaign_budget.amount_micros, campaign.status, campaign.optimization_score, campaign.advertising_channel_type, metrics.clicks, metrics.impressions, metrics.ctr, metrics.average_cpc, metrics.cost_micros,metrics.conversions,campaign.bidding_strategy_type FROM campaign WHERE segments.date DURING LAST_7_DAYS AND campaign.status != 'REMOVED' " }

Part 3: Pull Google Ads API Data into Sheets

We can now enter our values into API Connector and start importing Google Ads data into Google Sheets.

  1. In API Connector, click Create, choose POST from the dropdown menu, and paste in the Request URL we created above.
    google-as-img4
  2. Under OAuth, choose Google Ads from the dropdown menu.
    google-as-img7
  3. Under headers enter Key = content-type, Value = application/json
    google-as-img5
  4. In the POST Body section, paste in the POST body we created above.
    google-as-img6
  5. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  6. Under Output Options, choose compact report style (this isn’t strictly necessary but makes the output easier to read).
    google-as-img8
  7. Name your request and click Run. A moment later you’ll see ad performance data populate your sheet.
    google-as-img9
The Google Ads API sends cost data as “costMicros”. Divide it by one million (1,000,000) using a formula in your sheet to see costs in the standard $xxx.xx currency format.

Part 4: More Example API Requests

Account summary by day

{"query": "SELECT segments.date,metrics.clicks, metrics.impressions, metrics.ctr, metrics.average_cpc, metrics.cost_micros,metrics.conversions FROM customer WHERE segments.date DURING LAST_30_DAYS" }

List of keywords used by your account

{
"query": "SELECT ad_group_criterion.keyword.text FROM ad_group_criterion WHERE ad_group_criterion.type = 'KEYWORD' AND ad_group_criterion.status ='ENABLED'"
}

Generate keyword ideas (like Google Ads Keyword Planner)

seeded by keyword

Request method

Choose POST from the dropdown menu

Request URL

https://googleads.googleapis.com/v8/customers/2668258503:generateKeywordIdeas

Substitute in your own customer ID where it says 2668258503

Request body

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

Substitute in your own seed keyword where it says “hello”

Output options

Since the JSON response returns a lot of extra monthly data by default, click “Output options” and add the following JMESPath filter into the JMESPath field
results[].{text:text,keywordIdeaMetrics:keywordIdeaMetrics.{competition:competition,avgMonthlySearches:avgMonthlySearches,competitionIndex:competitionIndex,lowTopOfPageBidMicros:lowTopOfPageBidMicros,highTopOfPageBidMicros:highTopOfPageBidMicros}}
google-as-img13

Results

The output will look like this. In my tests the keywords and bid amounts exactly match the output within the Google Ads keyword planner tool, while the search volumes have more precision, e.g. where Keyword Planner says Avg. Monthly Searches = 1K – 10K, the API gives a number like 2400.
google-as-img11

seeded by URL

To seed with a URL instead, change the POST body to your URL, like this:

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"urlSeed":
{"url":"https://mixedanalytics.com"}
}

Again, add the JMESPath shown above:

results[].{text:text,keywordIdeaMetrics:keywordIdeaMetrics.{competition:competition,avgMonthlySearches:avgMonthlySearches,competitionIndex:competitionIndex,lowTopOfPageBidMicros:lowTopOfPageBidMicros,highTopOfPageBidMicros:highTopOfPageBidMicros}}

All available keyword planner targeting fields are documented here: https://developers.google.com/google-ads/api/reference/rpc/v8/GenerateKeywordIdeasRequest

Part 5: Notes

  • If your access to a customer account is through a manager account, you must include an extra header where Key = login-customer-id, Value = the customer ID of the manager account (info).
  • Check the full Google Ads API documentation for other examples and instructions.
  • This integration enables the following scope, which gives Google Sheets read/write access to Google Ads/AdWords: https://www.googleapis.com/auth/adwords
Previous Import GitHub Data to Google Sheets
Next Import Google Analytics Data to Google Sheets

9 thoughts on “Import Google Ads Data to Google Sheets”

  1. Good morning, I’m new to API Connector so sorry if it is a simple question. There is a way to retrieve data from google ADS (from keyword planner tool)? I’d like to import into Google sheet the average monthly search for a list of keywords. I’m able to connect into Google ads but i don’t understand how to query this tool. Thanks to anyone want help me 🙂

    Reply
    • Haha, this definitely isn’t a simple question. It took me a while to figure it out myself, but I’ve now added in an example of how to get data from the Keyword Planner Tool (search this page for “generate keyword ideas” if you don’t see it). Hopefully that will get you what you’re looking for 🙂

      Reply
  2. Can you give an example of adding location to this –

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

    Reply
  3. Hi, Can I combine data from 2 resources with your api connector ?

    Ex: I wanna get data from gender resource and campaign resource presenting on the same tab

    Reply
    • Generally, each API request will send data to a separate tab. If you want to combine them, I suggest first pulling in your data, then creating a summary tab that uses Sheets functions like VLOOKUP and QUERY to pull data into a summary tab.

      Reply
  4. Hi there, what settings should i make if i’d like to utilise my google sheet as a data warehouse? For example, i’d like pull key historical data and continue adding newer daily data.

    Do i enable timestamp and append settings?

    Thanks in advance

    Reply
    • Hey Joey, you can first grab your historical data by including Google’s date in your response, like this:
      {"query": "SELECT segments.date,metrics.clicks, metrics.impressions, metrics.ctr, metrics.average_cpc, metrics.cost_micros,metrics.conversions FROM customer WHERE segments.date > '2020-01-01' AND segments.date < '2021-08-10'" }

      Then you can switch to append mode and add in a dynamic date into your sheet, e.g. =today()-1, and use that value in your request URL. Each day your date value will automatically update, and you can schedule your request to run and fetch the updated data, so it automatically appends itself to the end of your sheet. There's an example of what I mean in this article: https://mixedanalytics.com/knowledge-base/api-connector-create-api-request-based-on-cell/ (check the section called "Example: dynamic dates"). Let me know if that makes sense or you have follow-up questions.

      Reply

Leave a Comment

Table of Contents