Search 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 get started digging into the Google Ads API, including campaign performance and keyword ideas.
Contents
- Before You Begin
- Part 1: Connect to the Google Ads API
- Part 2: Create your Google Ads API Request
- Part 3: Pull Google Ads API Data into Sheets
- Part 4: More Example API Requests
- Part 5: Notes
- Part 6: 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
If you haven’t connected API Connector to Google Ads before, you’ll first need to initiate the connection as follows:
- Open up Google Sheets and click Extensions > API Connector > Manage Connections.
- In the list of available connections, find “Google Ads” and click Connect.
- You will be directed to google.com and asked to allow API Connector to manage your AdWords campaigns:
- 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.
- 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) - 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/v10/customers/2668258503/googleAds:searchStream - 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.
- In API Connector, click Create, choose POST from the dropdown menu, and paste in the Request URL we created above.
- Under OAuth, choose Google Ads from the dropdown menu.
- Under headers enter Key = content-type, Value = application/json
- In the POST Body section, paste in the POST body we created above.
- Create a new tab and click Set current to use that tab as your data destination.
- Under Output Options, choose grid or compact report style (this makes the output easier to read).
- Click Edit Fields and set your column order.
- Name your request and click Run. A moment later you’ll see ad performance data populate your sheet.
=J1/1000000 to see costs in the standard xxx.xx currency format. If you’re familiar with array formulas, you can apply that formula to the entire column in one go with a function like =arrayformula(if(J2:J<>"",J2:J/1000000,""))Part 4: More Example API Requests
The Google Ads API provides a lot of different data points and functionality. The cool thing about working with the API directly is that you can access a very wide range of data points, but it requires some digging. I’ve tried to summarize the most useful request types in these examples, but for more complete information, please check the full documentation linked below.
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'"
}
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).
- This integration enables the following scope, which gives Google Sheets read/write access to Google Ads/AdWords:
https://www.googleapis.com/auth/adwords - For Keyword Planner data, see this article: Import Google Ads Keyword Planner Data to Google Sheets
Part 6: API Documentation
- Official API documentation: https://developers.google.com/google-ads/api/docs/reporting/overview
- Query builder tool that lets you set up your request through an interactive form: https://developers.google.com/google-ads/api/fields/v10/campaign_query_builder
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
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.
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. 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.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").