Search API Connector Documentation

Print

Import Google Analytics (GA4) Data to Google Sheets

premium

In this guide, we’ll walk through how to pull Google Analytics GA4 data directly into Google Sheets, using the API Connector add-on for Sheets.

Contents

Before You Begin

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

Part 1: Connect 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 GA's 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.
    ga4-connect
  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.

Part 2: Sample Google Analytics Data API (GA4) Request

GA4 is the newest version of GA. Confusingly, it 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.

Request configuration example

  • Application: Custom
  • Method: POST
  • Request URL: https://analyticsdata.googleapis.com/v1beta/properties/250094890:runReport
  • OAuth: Google Analytics
  • Headers:
    • Content-Type : application/json
  • Request Body: {"dateRanges":[{"startDate":"2022-06-01","endDate":"2022-06-30"}],"dimensions":[{"name":"date"},{"name":"deviceCategory"},{"name":"sessionSource"},{"name":"sessionMedium"},{"name":"sessionCampaignName"}],"metrics":[{"name":"sessions"},{"name":"transactions"}],"limit":20000}
  • JMESPath: rows[].{dimensionValues:dimensionValues,metricValues:metricValues}

JMESPath is optional but produces a much neater output. Just paste the above snippet into the JMESPath input field (under Output options).
googleanalytics4-jmespath

The whole request should look like this:

Notes

  • For the Request URL, substitute in your own property ID where it says 250094890. To find your property ID, open Google Analytics and navigate to Admin > Property > Property Settings > Property ID.
    googleanalytics4-propertyid
  • The URL stays the same for all GA4 requests, as parameters are set through the request body. To customize the request body, check Google's API documentation for examples and a list of available metrics.
  • Click Edit Fields to open the field editor and give the headers more user-friendly names.
    googleanalytics4-editfields

Part 3: API Documentation

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

Full list of available dimensions and metrics: https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema#metrics

Leave a Comment

Jump To...