Search API Connector Documentation

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 get started digging into the Google Ads API, including campaign performance and keyword ideas.

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 Extensions > 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/v10/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.

  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 grid or compact report style (this makes the output easier to read).
    google-as-img8
  7. Click Edit Fields and set your column order.
  8. 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 using a formula like =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).
    google-as-img14
  • 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

Previous Import GoHighLevel Data to Google Sheets
Next Import Google Ads Keyword Planner Data to Google Sheets

21 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. 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
  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

Leave a Comment

Table of Contents