Import Google Analytics (UA) Data to Google Sheets

In this guide, we’ll walk through how to pull Google Analytics (UA) data directly into Google Sheets, using the API Connector add-on for Sheets. Specifically, we'll show how to fetch data from the following two APIs:

• Core Reporting API v3 (Universal Analytics)
• Reporting API v4 (Universal Analytics)

Both of these APIs have been deprecated, so you may also want to check out the following article: Import Google Analytics (GA4) Data to Google Sheets

Contents

• Before You Begin
• Part 1: Connect to Google Analytics
• Part 2: Pull Data from Google Analytics into Sheets
• Part 3: Create a Custom Request to the V3 Google Analytics API
• Part 4: V3 Pagination
• Part 5: Create a Custom Request to the V4 Core Reporting API
• Part 6: API Documentation

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to Google Analytics

The easiest way to get started with the Google Analytics API is through API Connector’s built-in integration.

1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
2. Select Google Analytics (UA) from the drop-down list of applications
   
3. Under Authorization, click Connect to Google Analytics (UA)
   
4. You will be asked to allow API Connector to view your Google Analytics data. Click Allow.
   
5. You’ll then be returned to your Google Sheet, and can verify that your Google Analytics connection is active.

Part 2: Pull Data from Google Analytics into Sheets

Now that we’re connected, let’s pull some data into Sheets.

1. Under Endpoint, choose the data you’d like to retrieve. The /data/ga endpoint will return your GA data.
   
2. Set all required parameters, including your view ID, the fields in your report, date range, and so on. Get your view ID while logged into the admin section of GA, or by running a request to the /management/accountSummaries endpoint,
   
3. Optionally set any optional parameters such as filters and sorting.
4. Select a destination sheet, name your request, and click Run.
   
5. To give your column headers more reader-friendly names, click Edit Fields and name your fields in the field editor.

Part 3: Create a Custom Request to the V3 Core Reporting API

Alternatively, you can run a custom request instead of using API Connector’s built-in integration, using any of the parameters shown in GA’s API documentation.  Here is a sample request to get Universal Analyics data from the V3 Google Analytics API. Substitute in your own property ID where it says 111111111.

• Application: Custom
• Method: GET
• Request URL: https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11111111&start-date=2021-09-01&end-date=2021-10-01&metrics=ga:users,ga:newusers,ga:sessions,ga:transactions,ga:transactionRevenue&dimensions=ga:isoYearIsoWeek,ga:channelGrouping
• OAuth: Google Analytics
• Headers
  • Content-Type: application/json
• JMESPath (under Output options): {id:id,profileInfo:profileInfo,rows:rows}

Notes

• To give your column headers more reader-friendly names, click Edit Fields and name your fields in the field editor.
• Reasons to use V3 instead of V4:
  • Easier to construct queries. Fields are listed as URL parameters rather than in the request body.
  • Access metadata like the view and property ID. The V4 API doesn't return these fields.
  • Migrate from or merge data with other systems pulling from GA. Google supports both V3 and V4, so some BI tools still provide data through the V3 API.

Part 4: V3 Pagination

By default, Google Analytics' V3 API sends back just 10,000 records at a time. To get more, set the limit parameter to 10000 and use the next page link in their response, like this:

• Pagination type: next page URL
• Next page path: nextLink
• Run until: choose when to stop fetching data
  

Part 5: Create a Custom Request to the V4 Google Analytics API

Here is a sample request to get Universal Analyics data from the V4 Google Analytics API. The v4 API works by including parameters in the request body instead of the URL. Again, substitute in your own view ID.

• Application: Custom
• Method: POST
• Request URL: https://analyticsreporting.googleapis.com/v4/reports:batchGet
• OAuth: Google Analytics
• Headers
  • Content-Type: application/json
• Request Body: {"reportRequests":[{"viewId":"111111111","dateRanges":[{"startDate":"2021-09-01","endDate":"2021-09-30"}],"metrics":[{"expression":"ga:users"},{"expression":"ga:sessions"}],"dimensions":[{"name":"ga:browser"},{"name":"ga:country"}],"pageSize":5000}]}
• JMESPath (under Output options): reports[].data.rows[]

Notes:

1. To give your column headers more reader-friendly names, click Edit Fields and name your fields in the field editor.
2. Filters can be added to the request body like this:
   {"reportRequests":[{"viewId":"111111111","dateRanges":[{"startDate":"2021-09-01","endDate":"2021-09-30"}],"metrics":[{"expression":"ga:users"},{"expression":"ga:sessions"}],"dimensions":[{"name":"ga:browser"},{"name":"ga:country"}],"dimensionFilterClauses":[{"filters":[{"dimensionName":"ga:browser","operator":"EXACT","expressions":["Chrome"]}]}],"pageSize":5000}]}

Part 6: API Documentation

Official Universal Analytics API V4 documentation: https://developers.google.com/analytics/devguides/reporting/core/v4/basics

Official Core API Reporting V3 documentation:
https://developers.google.com/analytics/devguides/reporting/core/v3/reference