Search API Connector Documentation

Print

Import Google Ads Data to Google Sheets

In this guide, we’ll walk through how to pull Google Ads data data 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. This API only supports authentication via OAuth2, so we’ll be connecting using API Connector’s built-in OAuth2 integration for Google Ads. This is a paid feature; please install API Connector for a free trial or upgrade to access.

CONTENTS

WHAT DOES THIS INTEGRATION DO?

This integration connects the Google Ads API to Google Sheets. It enables the following scope

https://www.googleapis.com/auth/adwords

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 Add-ons > 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

  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/v7/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 and start importing Google Ads data into Google Sheets.

  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 headers enter Key = content-type, Value = application/json
    google-as-img5
  3. In the POST Body section, paste in the POST body we created above.
    google-as-img6
  4. Choose ‘Google Ads’ from the authentication dropdown.
    google-as-img7
  5. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  6. Under Output Options, choose compact report style (this isn’t strictly necessary but makes the output easier to read).
    google-as-img8
  7. Name your request and click Run. A moment later you’ll see ad performance data populate your sheet.
    google-as-img9

PART 4: OTHER API REQUESTS

  • 1. To get a list of keywords used by your account, try the following POST body:
{
"query": "SELECT ad_group_criterion.keyword.text FROM ad_group_criterion WHERE ad_group_criterion.type = 'KEYWORD' AND ad_group_criterion.status ='ENABLED'"
}
  • 2. To generate keyword ideas (similar to Google Ads Keyword Planner), enter the following:

URL:
https://googleads.googleapis.com/v8/customers/2668258503:generateKeywordIdeas

POST Body:
{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"keywordSeed":
{"keywords": ["hello"]}
}

Substitute in your own customer ID where it says 2668258503, and your own seed keyword where it says “hello”.
google-as-img11

To seed with a URL instead, change the POST body to your URL, like this:

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"urlSeed":
{"url":"https://mixedanalytics.com"}
}

All available keyword planner targeting fields are documented here: https://developers.google.com/google-ads/api/reference/rpc/v8/GenerateKeywordIdeasRequest

Check the full Google Ads API documentation for other examples and instructions.

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

3 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 🙂

      Reply

Leave a Comment