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 show how to access the following Google Analytics reporting APIs:

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

Contents

• Before You Begin
• Part 1: Authenticate to Google Analytics
• Part 2: V4 Reporting API (Universal Analytics)
  • Pull V4 Google Analytics API Data into Sheets
  • More V4 Example API Requests
• Part 3: V3 Core Reporting API
• Part 4: Google Analytics Data API (GA4)
• Part 5: Handle Non-US Currency Formats
• Part 6: API Documentation

Before You Begin

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

Part 1: Authenticate to Google Analytics

If you haven’t connected API Connector to Google Analytics before, you’ll first need to initiate the connection as follows. Once you’re authenticated, you can connect to any of their reporting APIs.

1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
2. In the list of available connections, find “Google Analytics” and click Connect.
   
   
3. You will be directed to google.com and asked to allow API Connector to view your Google Analytics data.
   
4. 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: V4 Reporting API (Universal Analytics)

For our first request, we’ll pull in data from a Universal Analytics view. The V4 Universal Analytics API 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 September 2021.

{
  "reportRequests":
  [
    {
      "viewId": "XXXX",
      "dateRanges": [{"startDate": "2021-09-01", "endDate": "2021-09-30"}],
      "metrics": [{"expression": "ga:users"}]
    }
  ]
}

Substitute in your own date range and view ID, which you can find in the Google Analytics account menu.

Pull V4 Google Analytics 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.
   
   
2. Choose Google Analytics from the OAuth dropdown menu.
   
   
3. Under headers enter Key = content-type, Value = application/json
   
   
4. In the Request body section, paste in the request body we created above.
   
5. Create a new tab and click Set current to use that tab as your data destination.
6. Name your request and click Run. A moment later you’ll see user counts populate your sheet.
   
7. 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 expression: reports[].data.rows[]. This will become even more useful as our queries become more complex, so we’ll keep a JMESPath filter in place for all our requests.
   

More Example V4 API Requests

• Multiple metrics

{
  "reportRequests":
  [
    {
      "viewId": "100425068",
      "dateRanges": [{"startDate": "2021-09-01", "endDate": "2021-09-30"}],
     "metrics": [
        {"expression": "ga:users"},
        {"expression": "ga:sessions"}
      ],
    }
  ]
}

• Multiple metrics and dimensions (the “pageSize” parameter indicates how many records to retrieve)

{
  "reportRequests":
  [
    {
      "viewId": "100425068",
      "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
    }
  ]
}

To rename your columns, click Edit Fields.

• With filters

{
  "reportRequests":
  [
    {
      "viewId": "100425068",
      "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 3: V3 Core Reporting API

As an alternative to connecting through the V4 API, you can get Universal Analytics data through Google Analytics’ older V3 Core Reporting API.

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.

Instructions:

First, click connect on Google Analytics from the Connections menu, as shown above. Now, set up your request like this:

• Method: GET
• Request URL: https://www.googleapis.com/analytics/v3/data/ga?ids=ga:100425068&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
  ➜substitute in your own property ID where it says 100425068, as well as your metrics and dimensions
• OAuth: Google Analytics
• Headers: Key = Content-Type, Value = application/json
• JMESPath: {id:id,profileInfo:profileInfo,rows:rows}

Part 4: Google Analytics Data API (GA4)

Google is in the process of transitioning to its newest version, GA4. Confusingly, GA4 is not accessed through the Google Analytics v4 API, though it certainly sounds like it should be… instead, GA4 is accessed through the Google Analytics Data API. Here’s a complete sample request to demonstrate how it works.

First, click connect on Google Analytics from the Connections menu, as shown above. Now, set up your request like this:

• Method: POST
• Request URL: https://analyticsdata.googleapis.com/v1beta/properties/250094890:runReport
  ➜substitute in your own property ID where it says 250094890. You’ll use this URL for all your requests.
• OAuth: Google Analytics
• Headers: Key = Content-Type, Value = application/json
• Request Body:
  

  { "dateRanges": [{ "startDate": "2021-05-01", "endDate": "2021-05-31"}], "dimensions": [{ "name": "country" }], "metrics": [{ "name": "activeUsers" }] }

  ➜edit your request body to get the metrics you want. Google’s API documentation shows all the available metrics and gives some examples of how to set up POST bodies.
• JMESPath: rows[].{dimensionValues:dimensionValues,metricValues:metricValues}

The whole thing should look like this:

Part 5: Handle Non-US Currency Formats

The Google Analytics API returns revenue data using the standard American format , where periods . represent the decimal place and commas , separate thousands. This is the case even if data in your view displays in the correct format for your locale.

To address this, navigate to File > Settings > Locale and change your sheet to the United States locale. To convert data back to your local format, you can use a Sheets formula. e.g. =substitute(substitute(substitute(A1,",","#"),".",","),"#",".") would replace commas with periods and periods with commas.

Add this formula to column A and set the data destination to column B to avoid overwriting your formula when you refresh the query.

Part 6: API Documentation

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

Official GA4 API documentation: https://developers.google.com/analytics/devguides/reporting/data/v1/basics

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