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 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. Select Google Ads from the drop-down list of applications
    gads-application
  2. Under Authorization, click Connect to Google Ads
    gads-connect
  3. You'll be asked to authorize the connection. Click Allow.
    gads-allow
  4. 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, campaign or customer. This will determine whether data is broken out by campaign or grouped together at the 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". 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 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. To create a custom request, add your complete URL into the Request URL field and select Google Ads from the OAuth menu.google-as-img9

Here's a full example setup for a custom request to the Google Ads API:

  • Method: POST
  • Request URL: https://googleads.googleapis.com/v11/customers/2668258503/googleAds:searchStream
  • OAuth: Google Ads
  • Headers: Key = Content-Type, Value = 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' " }

When running a custom Google Ads request, set the Report Style to grid (this will be set automatically for requests run through the preset integration).
gads-grid

Part 4: 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 (source). Include this header as follows, depending on whether you're connecting through the preset integration or your own custom request.
    • Integration:
      gads-logincustomerid
    • Custom request:
      google-as-img14

Part 5: API Documentation

23 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
  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 [email protected] 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

Leave a Comment

Jump To...