Print

Import Google Ads Data to Google Sheets

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.

56 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
  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. We are currently utilizing the Google Ads Custom connector to extract data from one of our Google Accounts. Below are the details:

    Request Body:
    {"query": "SELECT metrics.cost_micros, segments.date, campaign.name, metrics.clicks, metrics.impressions, segments.device, metrics.search_impression_share FROM campaign WHERE campaign.name LIKE 'XYZ%' AND segments.date DURING Yesterday" }
    Currently, the output provides multiple rows for each campaign available in the payload, whereas we require a single consolidated row for all campaigns per day. Could you please advise on how to modify the request body to generate a single line item for each day with consolidated data?

    Reply
    • Hey Shivam, your request returns multiple rows per day for a few reasons:
      1) It fetches from the campaign resource. The campaign resource always returns data split by campaign, while the customer resource consolidates all campaigns and returns data on the customer (aka account) level.
      2) It contains the field "campaign.name", which causes data to be split out by campaign name
      3) It contains the field "segments.device", which causes data to be split out by device

      To return a single row of data per day, you would need to use the following request body instead:
      {"query": "SELECT metrics.cost_micros, segments.date, metrics.clicks, metrics.impressions, metrics.search_impression_share FROM customer WHERE segments.date DURING Yesterday" }

      However, that revised request body no longer allows you to filter by campaign name, as you can't filter for a field that's not in the report. If you need to filter for campaign.name LIKE 'XYZ%' you will need to use your original query and sum it yourself with formulas or a pivot table in your sheet.

      Reply
  18. Hi we're an agency which use you're software on tiktok and it works perfect and now we want to do the same in google and facebook.

    this is the request we do on tiktok for the last 30days:
    https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id= 7352547999584894993&report_type=BASIC&dimensions=["stat_time_day","ad_id"]&data_level=AUCTION_AD&start_date=+++Dates!A1+++&end_date=+++Dates!A2+++&metrics=["campaign_name","adgroup_name","ad_name","conversion","spend","impressions","cpm","reach","frequency","clicks","cpc","cost_per_conversion","online_consult","campaign_id","adgroup_id"]&order_field=impressions&page_size=1000

    and those are the dynamic cells for the dates for the request above:
    =text(today()-30,"yyyy-mm-dd")
    =text(today(),"yyyy-mm-dd")

    and this is the request we do on tiktok for the last 31-60 days:
    https://business-api.tiktok.com/open_api/v1.3/report/integrated/get/?advertiser_id= 7352547999584894993&report_type=BASIC&dimensions=["stat_time_day","ad_id"]&data_level=AUCTION_AD&start_date=+++Dates2!A1+++&end_date=+++Dates2!A2+++&metrics=["campaign_name","adgroup_name","ad_name","conversion","spend","impressions","cpm","reach","frequency","clicks","cpc","cost_per_conversion","online_consult","campaign_id","adgroup_id"]&order_field=impressions&page_size=1000

    and those are the dynamic cells for the dates for the request above:
    =text(today()-60,"yyyy-mm-dd")
    =text(today()-31,"yyyy-mm-dd")

    Any way in google and in facebook we would need to get the same metrics in the same time frame (last 30 days + last 31-60days):
    Campaign name
    Date (stat_time_day)
    AD ID
    Conversions
    Cost per conversion
    Spend
    Impressions
    Cpm
    Reach
    Frequency
    Clicks
    Cpc
    Contact (kind of conversion such as phone click/what'sup click on the website)
    Ad set name (in google would be ad group name)
    Ad name
    AD set id (in google would be ad group id)
    Campaign id

    and for example this is the ad account id from google ads:
    515-528-6111

    and this is the ad account id from facebook ads:
    961891521479317

    *hope its find to solve two cases in one ticket (would be easier for me i guess and for you guys) + we just started yesterday and we opened 20-25 dashboards with you guys, if it works we can get to 1000 in less then a year..

    Reply
  19. Hi Ana,
    First off, thank you for this wonderful app - this is incredibly helpful.

    I have a question about pulling certain columns that right now aren't showing as an option in the Edit Fields section.

    Request URL we're using:
    https://googleads.googleapis.com/v15/customers/xxxxxxxxxx:generateKeywordHistoricalMetrics

    Request Body we're using:
    {"keywords":[+++KeywordAnalysis!D9+++],"historicalMetricsOptions":{"yearMonthRange":{"start":{"year":2024,"month":"JANUARY"},"end":{"year":2024,"month":"AUGUST"}},"includeAverageCpc":true},"keywordPlanNetwork":"GOOGLE_SEARCH","geo_target_constants":["geoTargetConstants/2158"]}

    This returns the following columns:
    keywordMetrics.competition keywordMetrics.monthlySearchVolumes.month keywordMetrics.monthlySearchVolumes.year keywordMetrics.monthlySearchVolumes.monthlySearches keywordMetrics.avgMonthlySearches keywordMetrics.competitionIndex

    We are wondering if there's a way we can also pull the CPC bid avg (high and low) as additional columns in this call?

    Reply
    • This request body is correctly formed. However, if there isn't any CPC data for a specific keyword in a specific geo, then the Google Ads API simply won't include CPC fields in the response, rather than including the response fields with null values (as might be expected).

      Reply
    • Hi Axel - you could use BETWEEN e.g. WHERE segments.date BETWEEN '2024-09-01' AND '2024-09-15', where the start date is the start of the month and the end date is today's date - 1.

      Reply

Leave a Comment

Jump To