Search API Connector Documentation

Print

Import Google Analytics Data to Google Sheets

premium

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 Google Analytics’ reporting APIs (both Universal Analytics and the new GA4).

Contents

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:

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find “Google Analytics” and click Connect.
    google-analytics-img1
  3. You will be directed to google.com and asked to allow API Connector to view your Google Analytics data.
    google-analytics-img2
  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: Create Your Google Analytics API Request

For our first request, we’ll pull in data from a Universal Analytics view (GA4 is discussed in the appendix). The 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.

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.

  1. In API Connector, click Create, choose POST from the dropdown menu, and paste in the Request URL we created above.
    google-analytics-img3
  2. Choose Google Analytics from the OAuth dropdown menu.
    google-analytics-img6
  3. Under headers enter Key = content-type, Value = application/json
    google-analytics-img4
  4. In the Request body section, paste in the request body we created above.
    google-analytics-img5
  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.
    google-analytics-img7
  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.
    google-analytics-img8

Part 4: More Example 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
    }
  ]
}
google-analytics-img9

In the above example, I went one step further with the JMESPath to name the columns, like this:
reports[].data.rows[].{browser:dimensions[0],country:dimensions[1],users:metrics[].values[0],sessions:metrics[].values[1]}

  • 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
    }
  ]
}

To see more examples, check the reporting documentation .

Appendix: Connect to the 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 link to the GA4 documentation, and here is 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:

google-analytics-img10
Previous Import Google Ads Data to Google Sheets
Next Import Google Analytics Management Data to Google Sheets

9 thoughts on “Import Google Analytics Data to Google Sheets”

  1. 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?

    Reply
      • Thanks, John! Yeah this article is for Universal Analytics, the Google Analytics Reporting API v4 doesn’t work for the new GA4, which is a bit confusing. GA4 uses the new Data API, which is still in Beta. I’ll investigate it and update this article.

      • Update: API Connector’s Google Analytics connection now supports GA4, I’ve updated the article with some information on how to use it.

  2. New URL: https://analyticsdata.googleapis.com/v1beta/{property=properties/*}:runReport

    Where properties = GA4 Property ID

    JSON Post field sample:
    {
    “dimensions”: [
    {
    object (Dimension)
    }
    ],
    “metrics”: [
    {
    object (Metric)
    }
    ],
    “dateRanges”: [
    {
    object (DateRange)
    }
    ],
    “dimensionFilter”: {
    object (FilterExpression)
    },
    “metricFilter”: {
    object (FilterExpression)
    },
    “offset”: string,
    “limit”: string,
    “metricAggregations”: [
    enum (MetricAggregation)
    ],
    “orderBys”: [
    {
    object (OrderBy)
    }
    ],
    “currencyCode”: string,
    “cohortSpec”: {
    object (CohortSpec)
    },
    “keepEmptyRows”: boolean,
    “returnPropertyQuota”: boolean
    }

    Reply

Leave a Comment

Table of Contents