API Connector Documentation
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
- Part 1: Connect to the Google Ads API
- Part 2: Pull Data from Google Ads to Sheets
- Part 3: Create a Custom Request
- Part 4: Access through a Manager Account
- Part 5: API Documentation
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.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select Google Ads from the drop-down list of applications
- Under Authorization, click Connect to Google Ads
- You'll be asked to authorize the connection. Click Allow.
- 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.
- 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.
(see this note if you're accessing a client account through a manager account) - Enter this ID as the
customer_idparameter
- In the Body Parameters section, set up your query. First, select a source data table,
ad_group_ad,campaignorcustomer. This will determine whether data is broken out by ad, broken out by campaign, or grouped together at the customer (aka account) level.
- 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.
- Select a date range.
DURINGwill let you select a dynamic range like last month, whileBETWEENwill let you select a fixed start and end date.
- Choose a destination sheet, name your request, and hit Run to see the report in your sheet.
- 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.
=$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)
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
- Official API documentation: https://developers.google.com/google-ads/api/rest/reference/rest
- Query builder tool that lets you set up your request through an interactive form: https://developers.google.com/google-ads/api/fields/v15/campaign_query_builder
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.
works perfectly!
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 🙂
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
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.
Hi there, please make sure you've selected POST from the method dropdown, then you'll see the POST body section.
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
Each API request will send data to a separate tab, but if you want to combine them, you can run a multi-query request.
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
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.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!
If your access to a customer account is through a manager account, you must include a header where Key = login-customer-id, and Value = the customer ID of the manager account. You can see your manager account ID in the top navigation bar when you're logged into Google Ads, and it should be entered without any dashes.
You can see more info about login-customer-id here: https://developers.google.com/google-ads/api/docs/concepts/call-structure#cid
Hi Ana,
Just wondering where do I include the header?
You can add that in the Headers section. I just added a screenshot above to make it more clear.
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
Sure, you'd just list your request URLs one after the other, like this:
https://googleads.googleapis.com/v8/customers/1234567https://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:listAccessibleCustomersHey 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' " }Sure, instead of
segments.date DURING LAST_7_DAYSyou can use something likeWHERE segments.date BETWEEN '2022-01-01' AND '2022-01-31'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?
Extensions run in their own sandbox so you can't hook into them from Apps Script, but you can trigger requests by changing cells via our ImportAPI Custom Function. Let me know if that works for you!
Hi Ana, if i use importAPI, how can i do it if the request method is post with a body. like in this google ads api?
IMPORTAPI uses all the settings from your request, including the request body. So you'd just set it up normally and then call it from the function, e.g.
=IMPORTAPI("Google_Ads").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" } ] } } ]
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.
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?
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:searchStreamRequest 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.
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?
The link works for me, but you may need to use an incognito window to access it. In any case, yep, that page links to a csv file containing all the geo_contant_ids. For your convenience I copied it into a Google sheet here: https://docs.google.com/spreadsheets/d/1r_YVUJIMOzGqK1VK_muNSelNzjuQFe4WjTVJp1T7sEE/edit#gid=1690102748
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'
Sure, you just need to separate each request body with the
:::BREAK:::delimiter. See here for more info.Thank you so much Ana. Will work on these. If I get stuck somewhere i'll message again.
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.
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?
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_adtable directly.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" } ] } } ]
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.
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?
It says "version v10 is deprecated", so you probably can't use it anymore. Can you please try changing
v10tov13in the request URL? Update: API Connector will now automatically route you to the latest version ID.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": "-----" } ] } } ]
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.
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.
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.
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.
I don't think that exists as a metric (you can see all available campaign metrics here: https://developers.google.com/google-ads/api/fields/v14/campaign). All I see there is
cost_microsand similar metrics, so I believe you need to sum this value yourself to get a campaign to-date value.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
WHEREclause. The example custom query has a snippet ofWHERE 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.statusas a metric and enter the following snippet into the JMESPath field:[].results[?campaign.status=='ENABLED']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?
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.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..
For Facebook Ads, I would recommend checking out our Facebook Marketing API Request URL Builder:
https://mixedanalytics.com/tools/facebook-marketing-api-request-url-builder/.
For Google Ads, you should be able to build a query to get those same results using the above guide, and make sure to check out the official documentation for all the fields that are available: https://developers.google.com/google-ads/api/fields/v17/campaign_query_builder.
If you have any specific questions or get stuck, don't hesitate to reach back out.
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?
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).
Hi Ana,
May I know if we could use a query WHERE segments.date DURING THIS_MONTH but we wanted to exclude today's data?
Thanks!
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.