Print

Import Google Ads Data to Google Sheets

premium

In this guide, we’ll walk through how to pull advertising metrics from the Google Ads / AdWords API 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 pull out campaign performance data from the Google Ads API.

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

The easiest way to get started with the Google Ads API is through API Connector's built-in integration.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select Google Ads from the drop-down list of applications
    gads-application
  3. Under Authorization, click Connect to Google Ads
    gads-connect
  4. You'll be asked to authorize the connection. Click Allow.
    gads-allow
  5. You'll then be returned to your Google Sheets, and can verify that your Google Ads connection is active.

Part 2: Pull Data from Google Ads to Sheets

Now that we’re connected, let’s pull in ads performance metrics to your sheet.

  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. Enter this ID as the customer_id parameter
    gads-path
  3. In the Body Parameters section, set up your query. First, select a source data table, ad_group_ad, campaign or customer. This will determine whether data is broken out by ad, broken out by campaign, or grouped together at the customer (aka account) level.
    gads-body-table
  4. Next, select the fields you'd like to display in your report. The most common fields are included in the drop down menu, and you can manually enter any field provided by Google Ads API.
    gads-body-fields
  5. Select a date range. DURING will let you select a dynamic range like last month, while BETWEEN will let you select a fixed start and end date.
    gads-body-dates
  6. Choose a destination sheet, name your request, and hit Run to see the report in your sheet.
    gads-response
  7. To clean up the response, hit Edit Fields and run the request. This will open the visual field editor where you can set field order and filter out unwanted columns.

The Google Ads API sends cost data as "costMicros". Use the field editor to add a formula like =$A1/1000000 to see costs in the standard xxx.xx currency format.

Part 3: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and metrics shown in the official API documentation. Here's an example request setup for a custom request to the Google Ads API, just substitute in your own customer ID.

  • Method: POST
  • Request URL: https://googleads.googleapis.com/v15/customers/your_customer_id/googleAds:searchStream
  • OAuth: Google Ads
  • Headers:
    • Content-Type: application/json
  • Request body: {"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' " }
  • Report style: grid (under Output options)

google-as-img9

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: Access Through a Manager Account

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 (source). Include this header as follows, depending on whether you're connecting through the preset integration or your own custom request.

Part 5: API Documentation

Note: using ad blockers may cause the Google Ads developer site to return an error. If you receive a 503 error and are unable to access the above links, try again through an incognito window.

52 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 🙂
      Update : I moved Keyword Planning requests into their own article: Import Google Ads Keyword Planner Data to Google Sheets

      Reply
  2. Hi,

    I have tried to get data from my google ads account to google sheets following your instruction. I did not find a section post body to attach body string into it.

    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. This article has some more info on that.

      Reply
  5. Hello! I'm connecting the api to pull data from my client's Good ads account (for youtube) but everytime I run the query it gives an error that I need permission and need to update the login-customer-id.

    I took the account ID and set it up as the login-customer-id but it didn't work.

    I'm a manager in that account.

    Please can you guide me where can I get the correct login-customer-id?

    Is it the client's personal account ID?

    Thanks!

    Reply
  6. Is it possible to instead of creating a single query for the entirety of the accounts associated with my manager account? I am trying to perform an automatic query of all my managed accounts, instead of manually generating a sheet through the google ads GUI

    Reply
    • Sure, you'd just list your request URLs one after the other, like this:
      https://googleads.googleapis.com/v8/customers/1234567
      https://googleads.googleapis.com/v8/customers/9876543
      https://googleads.googleapis.com/v8/customers/3456789

      Then the request will cycle through and run the same query (as defined in the request body) for each account ID.

      If you don't know all your account IDs you can first get them with a request URL of https://googleads.googleapis.com/v8/customers:listAccessibleCustomers

      Reply
  7. Hey Ana, begging you please to have an example body where it's almost the same as below but with a date from and date to filter like in fb ads? please please

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

    Reply
  8. Hi Ana, never mind i figured it out. Just wondering, is there a way to trigger an api connector refresh using appscript or if not maybe a cell value in google sheet?

    Reply
  9. I keep getting this error when I try to run the API. I followed your instructions using the manager ID in the login cutomer id part, and the ID of the google account in the customer id part but the error won't go away.

    - We received an error from googleapis.com (403) show response[{ "error": { "code": 403, "message": "The caller does not have permission", "status": "PERMISSION_DENIED", "details": [ { "@type": "type.googleapis.com/google.ads.googleads.v11.errors.GoogleAdsFailure", "errors": [ { "errorCode": { "authorizationError": "USER_PERMISSION_DENIED" }, "message": "User doesn't have permission to access customer. Note: If you're accessing a client customer, the manager's customer id must be set in the 'login-customer-id' header. See https://developers.google.com/google-ads/api/docs/concepts/call-structure#cid" } ], "requestId": "0aDPJ_wDorQGPExZqOrDdg" } ] } } ]

    Reply
    • Hey Ken, this error could occur from one of the following:
      1) You've authenticated from a different account than the one you're making the API request with. Please make sure you log in through your manager account when you log in to Google Ads after clicking Connect.
      2) You don't have access to the client account. I assume this isn't the case here but make sure you do have access.
      3) There's some typo or mixup in the customer and client IDs. Feel free to message support with a screenshot from Google Ads showing your own customer ID and your client ID, as well as your a screenshot of your setup in API Connector and I'll take a look and see if I can spot the issue.

      Reply
  10. Hi Ana, long time. Can you kindly point me in the right direction on how I can pull records broken down by country or city/region using custom request?

    Reply
    • Hey Felix, Google Ads provides location_view (where ads were targeted) and geographic_view (where ads were viewed) tables. You can click those links to enter the query builder, but here's an example request setup:
      Request URL: https://googleads.googleapis.com/v12/customers/1111111/googleAds:searchStream
      Request body: {"query": "SELECT campaign.name, campaign.status, location_view.resource_name, segments.date, metrics.clicks, metrics.ctr, metrics.impressions, campaign_criterion.location.geo_target_constant FROM location_view WHERE segments.date > '2023-01-01' AND segments.date < '2023-01-31' " }
      Location data is returned using geo_target_constant IDs, so you'll need to convert those into a human-readable location as described here.
      Finally, here's a relevant thread I came across in the Google Ads forum, you may find it helpful.

      Reply
      • Thanks Ana, Is there a way we can merge my query below and the new one you gave me in one query?

        {"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,segments.date FROM campaign WHERE segments.date > '+++Google!B1+++' AND segments.date '2023-01-01' AND segments.date < '2023-01-31' " }

      • You can't merge queries per se, as Google only returns location data in the location_view and geographic_view tables. I think the example I gave you is pretty close to what you're asking for, though. I'd just check those query builder links and set up your query there, you can just click to select the fields you want from the list, and that way you can see exactly which fields are and aren't available.

      • Hi Ana, this link in your last message couldn't be accessed.

        Location data is returned using geo_target_constant IDs, so you'll need to convert those into a human-readable location as described here.

        Is there like a google sheet list of constant IDs that I can just do a vlookup without the use of any other API?

      • Is it possible to run api connector in a loop through a range of cells using this api request from google ads?

        SELECT geo_target_constant.canonical_name,
        geo_target_constant.country_code,
        geo_target_constant.id,
        geo_target_constant.name,
        geo_target_constant.status,
        geo_target_constant.target_type
        FROM geo_target_constant
        WHERE geo_target_constant.resource_name = 'geoTargetConstants/1014044'

      • Sorry for being persistent Ana, with the post body you've given i was able to successfully pull the below data.

        geoTargetConstants/2784 ENABLED 2784 United Arab Emirates AE Country United Arab Emirates geoTargetConstant.canonicalName,geoTargetConstant.countryCode,geoTargetConstant.id,geoTargetConstant.name,geoTargetConstant.status,geoTargetConstant.targetType fItkkUXRrFMcnfjz8R6XzQ

        Is there also a way to get city values instead of country?

      • Did your ads target specific cities? If not, then you'll just see the countries you targeted. If you're asking where people viewed your ad, then you'd use the geographic_view (where ads were viewed) tables instead.

  11. When I connected to the Google Ads application, I am able to fetch the data at a campaign level. However, my granular level is ads which rolls up to campaigns. How can I retrieve ad level data?

    Reply
    • Hi Sayak, our preset integration currently only supports the Google Ads campaign and customer tables, but you can make a custom request to any table available in Google Ads API. Here's a link to their query builder for making a query at the ads level: So for example you could enter request bodies like this:
      Ads table: {"query": "SELECT ad_group_ad.ad.final_urls, ad_group_ad.ad.id,
      ad_group_ad.ad.name, ad_group_ad.ad.type, ad_group_ad.ad_group, ad_group_ad.status, segments.date, metrics.clicks, metrics.cost_micros, metrics.ctr, metrics.impressions, campaign.name, customer.descriptive_name FROM ad_group_ad WHERE segments.date > '2022-09-01' AND segments.date < '2022-12-31' " }

      Ad group table: {"query": "SELECT campaign.name, ad_group.name, metrics.clicks, metrics.cost_micros, metrics.impressions, segments.date FROM ad_group WHERE segments.date > '2023-01-01' AND segments.date < '2023-05-31' " }
      Update: API Connector's preset integration now supports the ad_group_ad table directly.

      Reply
  12. Please help, I've spent hours trying to make this work with the manager account, I keep getting this error using the built-in integration even though I put the manager account ID twice in both sections as instructed:

    Headers
    login-customer-id
    If access to this Google Ads account is through a manager account, enter the manager's account ID, without dashes (info)

    and

    Path parameters
    customer_id
    Enter the ID of the target Google Ads account, without dashes.

    The error message:

    [{ "error": { "code": 400, "message": "Request contains an invalid argument.", "status": "INVALID_ARGUMENT", "details": [ { "@type": "type.googleapis.com/google.ads.googleads.v11.errors.GoogleAdsFailure", "errors": [ { "errorCode": { "queryError": "REQUESTED_METRICS_FOR_MANAGER" }, "message": "Metrics cannot be requested for a manager account. To retrieve metrics, issue separate requests against each client account under the manager account." } ], "requestId": "0lSHkywDEyAy8YIKJ1cY9A" } ] } } ]

    Reply
    • I put the manager account ID twice in both sections as instructed:
      I think that's the problem. The manager account ID should not be entered twice, you need to enter the client ID in the path.
      The error message is saying that metrics can only be requested against the client account, not the manager account.

      Reply
  13. A very strange error that I just got,
    I tried to get keyword from google ads' keyword planner and the input was 50 requests, so always when there is anything wrong in the request, all of the requests are getting an error, but now I got about half of the requests Okay and half of them with this error:

    - We received an error from googleapis.com (400) show response
    { "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": { "requestError": "UNKNOWN" }, "message": " Version v10 is deprecated. Requests to this version will be blocked." } ], "requestId": "xsCaXLTsWWL_B3v0uVzmIA" } ] } }

    Can anyone help with that?

    Reply
    • It says "version v10 is deprecated", so you probably can't use it anymore. Can you please try changing v10 to v13 in the request URL? Update: API Connector will now automatically route you to the latest version ID.

      Reply
  14. Hey Support,
    the license was transfer to my account. I'm trying to use the request but i get the next error:
    Adwords single request: Completed with errors
    - We received an error from googleapis.com (403) show response
    [{ "error": { "code": 403, "message": "The caller does not have permission", "status": "PERMISSION_DENIED", "details": [ { "@type": "type.googleapis.com/google.ads.googleads.v13.errors.GoogleAdsFailure", "errors": [ { "errorCode": { "authorizationError": "USER_PERMISSION_DENIED" }, "message": "User doesn't have permission to access customer. Note: If you're accessing a client customer, the manager's customer id must be set in the 'login-customer-id' header. See https://developers.google.com/google-ads/api/docs/concepts/call-structure#cid" } ], "requestId": "-----" } ] } } ]

    Reply
    • Hey Andres, this is a message from Google Ads saying that your email address doesn't have access.
      Can you please make sure your own email address (not the one you transferred from) has access to the Google Ads account? If you believe it does, you can also try navigating to your Google permissions page, clicking disconnect where you see "API Connector - has access to Google Ads", and then disconnecting/reconnecting through the extension. If you're still having issues, please feel free to reach out to support.

      Reply
  15. HI is there a way to import data from all accounts connected to the user? I can do this using the Facebook connection but the google version only seems to let me connect to one at a time.

    Any help is greatly appreciated.

    Reply
    • Hey Mark, sorry, we just recently started rolling out the ability to select multiple accounts and it's not yet available for Google Ads. However, you can still do this if you create your own custom request . In that case, list out all the request URLs you'd like to query (one for each customer ID), and API Connector will cycle through them, printing out each response into the sheet. Feel free to check this article or reach out to support if you'd like more specific help setting that up.

      Reply
  16. Hello, are you able to point me in the direction of pulling data for campaign spend? Such as the total amount spent on each campaign to date. (Different from the daily cost). Thank you kindly.

    Reply
      • Thank you, I was able to solve it. I'm also trying to filter by ENABLED Status so it would only request for the enabled ads, but I'm not sure if I have to customize that or if there was another way to do it with the JMESPath.

      • To do that, you can run a custom request, as custom requests allow adding in advanced filters through a WHERE clause. The example custom query has a snippet of WHERE segments.date DURING LAST_7_DAYS AND campaign.status != 'REMOVED', and the possible statuses are ENABLED, PAUSED, and REMOVED. You can see/create all the possible filter expressions using the Google Ads query builder tool and then copy/paste it back into API Connector.

        JMESPath is indeed another way of accomplishing this, but the first way is preferable as it filters at the source (JMESPath only filters data once it has already been returned to the Sheet). Still, if you'd like to try this method instead, you'd select campaign.status as a metric and enter the following snippet into the JMESPath field: [].results[?campaign.status=='ENABLED']

  17. Hello - am I understanding this correctly? It appears I am unable to import google ads campaign data because I have a "pro" account not a "business" account. So I'm paying for your service and the basic google ads imports aren't available? Is there a way to authorize Google Ads without the Oauth2 or via the pro plan that I already pay for?

    Reply
    • Hey Nick, OAuth is a Business plan feature as shown on our pricing and features pages, and Google Ads requires authorization via OAuth. It sounds like that's not what you expected, so I apologize for the misunderstanding. Please feel free to message support and we'll refund you right away.

      Reply
      • Thanks for the reply. I think the confusion is coming from Oauth being listed as a "pro" feature on the marketplace page below.

        https://workspace.google.com/marketplace/app/api_connector/95804724197

        Pro features:
        *Schedule API pulls to refresh automatically
        *Set trigger execution order
        *Cycle through a list of stacked requests
        *Automatically handle pagination
        *Higher save/run limits
        *Connect to APIs by clicking a button (OAuth)

        I don't even see a "business" plan on there so I appreciate the new link.

Leave a Comment

Jump To