Search API Connector Documentation

Print

Import Google Analytics (GA4) Data to Google Sheets

Last Updated On
byAna
< Knowledge Base
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 the Google Analytics API

The easiest way to get started with the Google Analytics Data API (GA4) 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 (GA4) from the drop-down list of applications
    ga4-application
  3. Under Authorization, click Connect to Google Analytics
    ga4-authorization
  4. You'll be asked to authorize the connection. Click Allow.
    google-analytics-img2
  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 GA4 to Sheets

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

  1. Select the runReport endpoint, which allows us to fetch analytics data from GA4
  2. Under Path parameters, select your GA4 property from the dropdown list.
  3. Under Body parameters, enter your date range.
  4. Select or enter the dimensions and metrics you'd like to view in your report. See here for dimension & metric definitions.
  5. Optionally set the limit parameter to retrieve more or fewer than the default 10,000 records, and the orderBys parameter to set the order of results.
  6. Choose a destination sheet, name your request, and hit Run to see the report in your sheet.
    ga4-response
  7. Optionally use the field editor to hide or re-arrange the fields in your report.

Part 3: Create a Custom Google Analytics Data API (GA4) Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation. 

Here's a sample request setup to demonstrate how it works. For the Request URL, substitute in your own property ID where it says your_property_id. (To find your property ID, open Google Analytics and navigate to Admin > Property > Property Settings > Property ID).

Request configuration example

  • Application: Custom
  • Method: POST
  • Request URL: https://analyticsdata.googleapis.com/v1beta/properties/your_property_id: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}

The whole request should look like this:

Notes

  • An earlier version of this article recommended adding a JMESPath snippet of rows[].{dimensionValues:dimensionValues,metricValues:metricValues}. As of 2023-01-18, API Connector will automatically restructure the data such that JMESPath is no longer needed. Requests created prior to the cutoff date will be unaffected.
  • 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.

Part 4: Handle Custom Dimensions/Metrics


For custom metrics/dimensions, use one of the following two inputs:

  1. customEvent:parameter_name for event-scoped Custom Dimensions or Custom Metrics
  2. customUser:parameter_name for user-scoped Custom Dimensions

In each case you'll need to substitute in your own parameter name. For example, if the User Property/Parameter name is registered as "entrances", enter customEvent:entrances and hit return.
ga4-customevents

In a custom request, this would be entered similarly, e.g. {"dateRanges":[{"startDate":"2023-03-01","endDate":"2023-03-31"}],"dimensions":[{"name":"date"},{"name":"customEvent:entrances"}],"metrics":[{"name":"sessions"}],"limit":20000}

Part 5: Handle Pagination

By default, GA4 will return a maximum of 100K records per page (info). To cycle through multiple pages of 100K records, set the initial data fetch to have a limit of 100000 and apply pagination handling with the following settings:

  • Pagination type: offset-limit body
  • Offset body parameter: offset
  • Limit body parameter: limit
  • Limit value: 100000
  • Run until: choose when to stop fetching data
    ga4-pagination

Part 6: List all Available GA4 Properties

This request is listed in its own section because it uses the Google Analytics Admin API rather than the Google Analytics Data API. It returns all available GA4 properties.

  • Application: Custom
  • Method: GET
  • Request URL: https://analyticsadmin.googleapis.com/v1beta/accountSummaries
  • OAuth: Google Analytics
  • Report style: Grid
    ga4-viewproperties

Part 7: API Documentation

Official Google Analytics Data API (GA4) documentation: https://developers.google.com/analytics/devguides/reporting/data/v1/basics

Official Google Analytics Admin API documentation:
https://developers.google.com/analytics/devguides/config/admin/v1

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

17 thoughts on “Import Google Analytics (GA4) Data to Google Sheets”

  1. Hi,

    In general, everything works so far, but now I have hit a stuck.

    On my site, I have a tag. The event is site_navigation

    The event parameters are (all of them have been defined in GA4 as custom dimensions)

    click_text
    click_url
    page_path
    page_referrer
    click_classes
    click_element
    page_url

    I am trying to follow the https://developers.google.com/analytics/devguides/reporting/data/v1/advanced#event-scoped_custom_dimensions

    It isn't very clear.

    Any ideas?

    The intention is to create some client reporting because of the quotas that Google has put in Google Data Studio. I using my site as the test case

    Thanks,

    Neil

    Reply

    • Hey Neil, I haven't worked much with this API yet so I can't say too much, but what happens if you run a request like this?
      Request method: POST
      Request URL: https://analyticsdata.googleapis.com/v1beta/properties/1111111111111:runReport
      Request body: {
      "dateRanges": [{ "startDate": "2022-09-01", "endDate": "2022-09-15" }],
      "dimensions": [{ "name": "customEvent:click_text" }],
      "metrics": [{ "name": "eventCount" }]
      }

      Reply

  2. Hi Ana,

    This a follow for you and anyone who is using this for GA4.

    Using my site_navigation (tag) example.

    The GA4 tag is built and published, and all the parameters are defined as custom definitions. Not the site_navigation Event Tag. (site_navigation being the event name)

    Looking at another site, I tagged. The only way to extract that (site_navitagion) eventName is if it is defined as a custom definition in Google Analytics 4.
    For another site I had tagged, I defined the site_ navigation event as a custom definition, I ran a test, and I can see it in the listing for all the events.

    Thanks,
    Neil

    Reply

  3. I'm trying to connect GA4 to spreadsheet via your connector. I already export data, but I have problem with order of data. The data is not sorted by date, but by metric. Is there any easy solution to this? I want it order by date.

    Reply

    • Hey Maros, you can use the orderBys parameter as described here.
      So your full request body would be like this:
      {"metrics":[{"name":"sessions"}],"dimensions":[{"name":"date"}],"dateRanges":[{"startDate":"2022-06-01","endDate":"2022-06-30"}],"orderBys":[{"dimension":{"dimensionName":"date"},"desc":false}]}

      Please check if that works for you. By the way, we're working on adding GA4 to our directory of preset APIs, so this should be a bit easier in the near future.
      Update: GA4 is now available in the preset application directory.

      Reply

  4. Hi, thanks for this add on. I tried to pull data from the GA4 merchanidse store and followed all the steps (with json etc.). But I still get a quota error message.

    Reply

    • GA4's quotas hit any tool using their API, which includes API Connector. You can see more about GA4's API quotas here. That page indicates that quota usage depends on the request's complexity to complete, which includes number of rows, number of columns, filter complexity, and reporting date range. If possible I suggest splitting up your request into smaller blocks, and then merging the data at the end.

      Reply

  5. Hi.
    Thanks for this great tool.
    I'm trying to limit the volume of data requests by filtering the sessionSource to google. Is there a method for doing this? I've tried {"name":"sessionSource:google"} but got an error. Can you help?
    Many thanks.

    Reply

    • Hey Magnus, sure, you can use the dimensionFilter field (info). If we modify the example above, a full request body would look like this:
      {"dateRanges":[{"startDate":"2022-12-01","endDate":"2022-12-31"}],"dimensions":[{"name":"date"},{"name":"deviceCategory"},{"name":"sessionSource"},{"name":"sessionMedium"},{"name":"sessionCampaignName"}],"metrics":[{"name":"sessions"},{"name":"transactions"}],"dimensionFilter":{"filter":{"fieldName":"sessionSource","stringFilter":{"value":"google"}}},"limit":20000}

      Reply

      • Me again! What's the best method for adding multiple values to the stringfilter? Filtering for Google & Bing, for example? Should I use an InListFilter or can I separate the values with a comma or backslash? Thanks for your support. M

      • Hello again! 😀 You can use the inListFilter as you mention. So a full example would be like this: {"dateRanges":[{"startDate":"2022-12-01","endDate":"2022-12-31"}],"dimensions":[{"name":"date"},{"name":"deviceCategory"},{"name":"sessionSource"},{"name":"sessionMedium"},{"name":"sessionCampaignName"}],"metrics":[{"name":"sessions"},{"name":"transactions"}],"dimensionFilter":{"filter":{"fieldName":"sessionSource","inListFilter":{"values":["bing","google"]}}},"limit":20000}
        Update: Our GA4 integration now supports filtering, so you don't need a custom request for this anymore.

Leave a Comment

Jump To...