Import Google Analytics Data to Google Sheets
In this guide, we’ll walk through how to pull Google Analytics data data directly into Google Sheets, using the API Connector add-on for Sheets. Google Analytics is, of course, an extremely popular web analytics tool, so there are many other, specialized connection tools like Google’s official Google Analytics spreadsheet add-on. So definitely go give that one a try if you’d prefer not to work with the API directly.
Still here? Nice 🙂 This article will dig into the Google Analytics v4 Reporting API. This API only supports authentication via OAuth2, so we’ll be connecting using API Connector’s built-in OAuth2 integration for Google Analytics. This is a paid feature; please upgrade to access.
CONTENTS
- Before You Begin
- Part 1: Connect to the Google Analytics v4 API
- Part 2: Create your Google Analytics API Request
- Part 3: Pull Google Analytics API Data into Sheets
- Part 4: More Example API Requests
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: CONNECT TO THE GOOGLE ANALYTICS API
If you haven’t connected API Connector to Google Analytics before, you’ll first need to initiate the connection as follows:
- Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
- In the list of available connections, find “Google Analytics” and click Connect.
- You will be directed to google.com and asked to allow API Connector to view your Google Analytics data.
- You’ll then be returned to your Google Sheet, and can verify that your Google Analytics connection is active in the Connections screen.
PART 2: CREATE YOUR GOOGLE ANALYTICS API REQUEST
For our first request, we’ll pull in users for a specific view. The Google Analytics Reporting API v4 is accessed by sending POST requests to this API Request URL:
https://analyticsreporting.googleapis.com/v4/reports:batchGet
All the details of the request, e.g. metrics, dimensions, filters, and so on, are specified in the POST body. We’ll start with a simple request for users in October 2020.
{
"reportRequests":
[
{
"viewId": "XXXX",
"dateRanges": [{"startDate": "2020-10-01", "endDate": "2020-10-31"}],
"metrics": [{"expression": "ga:users"}]
}
]
}
Substitute in your own date range and view ID, which you can find in the Google Analytics account menu.
PART 3: PULL GOOGLE ANALYTICS API DATA INTO SHEETS
We can now enter our values into API Connector and start importing GA data into Google Sheets.
- In API Connector, click Create, choose POST from the dropdown menu, and paste in the Request URL we created above.
- Under headers enter Key = content-type, Value = application/json
- In the POST Body section, paste in the POST body we created above.
- Choose ‘Google Analytics’ from the authentication dropdown.
- Create a new tab and click ‘Set current’ to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see user counts populate your sheet.
- That’s pretty cool.. but also quite messy looking. The GA v4 Reporting API returns data in a rather strange format, where metrics and dimension names are returned separately from the actual values. To simplify, let’s use the following JMESPath:
reports[].data.rows[]
. This will become even more useful as our queries become more complex, so we’ll keep this JMESPath filter in place for all our requests.
PART 4: MORE EXAMPLE API REQUESTS
- Multiple metrics
{
"reportRequests":
[
{
"viewId": "100425068",
"dateRanges": [{"startDate": "2020-10-01", "endDate": "2020-10-30"}],
"metrics": [
{"expression": "ga:users"},
{"expression": "ga:sessions"}
],
}
]
}
- Multiple metrics and dimensions
{
"reportRequests":
[
{
"viewId": "100425068",
"dateRanges": [{"startDate": "2020-10-01", "endDate": "2020-10-30"}],
"metrics": [
{"expression": "ga:users"},
{"expression": "ga:sessions"}
],
"dimensions": [
{"name": "ga:browser"},
{"name": "ga:country"}
],
}
]
}

- With filters
{
"reportRequests":
[
{
"viewId": "100425068",
"dateRanges": [{"startDate": "2020-10-01", "endDate": "2020-10-30"}],
"metrics": [
{"expression": "ga:users"},
{"expression": "ga:sessions"}
],
"dimensions": [{"name": "ga:browser"}, {"name": "ga:country"}],
"dimensionFilterClauses": [
{
"filters": [
{
"dimensionName": "ga:browser",
"operator": "EXACT",
"expressions": ["Chrome"]
}
]
}
]
}
]
}
To see more examples, check the reporting documentation .
How do you extract data from your Google Ads campaign?
Hey Rami, if you’re using Google Ads I’d just use this free add-on from Google, it will be easier than configuring your own API connection:
https://gsuite.google.com/marketplace/app/google_ads/288999541255
My Google analytics Oauth connection seems to be resetting very frequently making me unable to schedule my api to run, do you know why this may be happening?
Sorry, there was an issue – fixed now!
Though if you’re getting data from Google Analytics, you can also try using this specialized GA add-on from Google, it’s free and probably easier than pulling directly from the API anyway.