API Connector Documentation

Print

Import Google Analytics (GA4) Data to Google Sheets

Last Updated OnJanuary 3, 2024
byAna
< Knowledge Base

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 one or more of your GA4 properties from the dropdown list.
    ga4-accounts
    If you select multiple properties, the report will run for each property, with the results placed one after another on the same page, so it's best to include the request URLs to differentiate them.
  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, with filters applied for both an event and channel group. 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":"2023-01-01","endDate":"2023-03-31"}],"dimensions":[{"name":"date"},{"name":"eventName"},{"name":"sessionDefaultChannelGroup"}],"metrics":[{"name":"eventCount"}],"dimensionFilter":{"andGroup":{"expressions":[{"filter":{"stringFilter":{"value":"Organic Search","match_type":"EXACT"},"fieldName":"sessionDefaultChannelGroup"}},{"filter":{"stringFilter":{"value":"page_view","match_type":"EXACT"},"fieldName":"eventName"}}]}},"limit":20000}

The whole request should look like this:

ga4-customresponse

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 Values

Google Analytics 4 allows you to create your own custom metrics, dimensions, and channel groupings. Since these are custom to your setup, the API request requires a bit of customization as well.

Custom Metrics & Dimensions

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}

Custom Channel Groupings

  1. First find your custom channel grouping ID using the custom channel grouping API request.
  2. Now plug that into the dimension sessionCustomChannelGroup:1111111111, replacing 11111111 with the Custom Channel Group ID. Enter this dimension by typing it in and clicking Enter, as it won't show up automatically.
    googleanalytics4-customchannel

In a custom request, this would be entered like this: {"dateRanges":[{"startDate":"2023-03-01","endDate":"2023-03-31"}],"dimensions":[{"name":"date"},{"name":"sessionCustomChannelGroup:1111111111"}],"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: GA4 Metadata

These requests are listed in their own section because they use the Google Analytics Admin API and Metadata API rather than the GA4 reporting API.

Fetch all Available GA4 Properties

The following configuration will list all available GA4 properties.

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

Fetch Custom Channel Grouping IDs

The following configuration will fetch all the metrics and dimensions available for your account, including the IDs of any custom channel groupings.

  • Application: Custom
  • Method: GET
  • Request URL: https://analyticsdata.googleapis.com/v1beta/properties/11111111/metadata (substitute in your own property ID)
  • OAuth: Google Analytics
  • Report style: Grid
    ga4-channelgrouping

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

49 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.

    • Sure, select dimension = landingPage, and then open the filter parameter and set landingPage exactly matches https://yourpage.com

      Reply

  8. Hi Ana,

    I'm trying to pull in Custom Channel groupings from the GA4 API however looks I need to do this through a custom request using Channel ID. According to the API docs I can make a meta data request through the below to get this info (swapping * for property ID). However I'm getting a 404 error when running. Would you be able to help at all?

    https://analyticsdata.googleapis.com/v1beta/{name=properties/*/metadata}

    Reply

    • Currently the preset integration supports only a single filter at a time. To include multiple filters you can set up a custom request (the example request there shows how to include 2 filters at once).

      Reply

  10. Hi,
    generally it works great! But is it possible to set the end date somehow that the API pulls data for actually today? If I schedule my request for every hour, I would like to see the updated data for today.

    Somehow I cannot figure out how 🙂

    Best,
    Nogi

    Reply

    • Hey Ngoc-Anh, please enter the word today under End date. That should allow you to fetch updated data for the current day (I suggest using merge mode to avoid duplicates). Let me know if that works or if you have any questions.

      Reply

      • Hi Ana,

        Thanks for sharing this. Can you share a sample of a request body for two segments and some metrics like sessions and transactions?

        Many thanks!
        Pablo

  12. Hi, I have a problem. I want to see pageviews, but values in GA4 and this API differ by 15%. In API I use screenPageViews

    Reply

    • Hey Zakh, this is likely because of sampling: https://support.google.com/analytics/answer/13331292?hl=en.
      In general, shortening the time period, reducing the number of distinct dimensions, and switching to sessions from users will all help to reduce the likelihood of sampling. If that doesn't seem to be the case here, can you please share your combination of metrics/dimensions so I can test it on this side?

      Reply

  13. Hi Ana,
    First of all, this is a great article.
    Is there a way to get the property name exported in the sheets instead of the property URL?

    Reply

    • Thanks, Prajakta! Unfortunately there's no way to get the property name, since the API doesn't provide that as an option. However, selecting the dimension "hostname" may help, since it will print out the hostname of the website you're tracking.

      Reply

  14. Hello,
    I am using Custom Google Analytics Data API (GA4) Request according to the instructions, it worked fine, until 24/7. From that date, no data are imported to GSheet.
    Date range is set-up like this:
    {"dateRanges":[{"startDate":"2023-05-01","endDate":"today"}],
    - but no data after 24/7
    I have not reached the limit of Gsheets, there is only about 10k rows.
    I tried to change it to "yesterday", specific date, but it wont work. Where could be the problem please?
    Thanks,
    Mike

    Reply

    • My immediate thought is that it could be related to latency, since GA4 can take 24+ hours to process data for very large accounts with millions of hits (info). Besides that, do you see any error messages when you run the request? Also, what happens if you reduce the date range, so the total date range is only a week or so? That could help if the issue is related to sampling.

      Reply

  15. It's unfortunate that you can't try this setup out without first upgrading your subscription to Business (as OAuth is only supported there).

    I understand that you need to make your living somehow but I'm not sure if this is the optimal way to handle this.

    Reply

    • Thanks for your feedback but everyone gets an automatic 14-day free trial of all features, including GA4, when they first install API Connector. If you’ve already used up your trial or are on a Pro account, please feel free to message support and we’ll give you a new trial (or install with a different email address for a new trial automatically).

      Reply

  16. Been using your chrome extension for a while now. However, im starting to find differences between the data exported and the real data in GA4.

    Could you guys help me to debug what im doing and tell me why im not seeing the exact same info as i see in GA4 reports (specifically users. According to their data, the total number of active users does not match in barely any case with the data downloaded for the same period of time and same project...

    Reply

    • I think the most likely reason for the discrepancy is sampling: https://support.google.com/analytics/answer/13331292?hl=en. The "active users" metric you mentioned is especially prone to sampling because it requires extra processing time on Google's servers in order to dedupe the data. For example, if a user visits your site on Monday and Tuesday, that's 2 sessions, but still only 1 user. Further, besides sampling, Google also withholds some data from reports because of thresholding: https://support.google.com/analytics/answer/9383630?hl=en

      Can you please create a simple report, e.g. sessions per day for a month? Let me know if the numbers match then. If so, then you will have further evidence that the issue is indeed because of sampling or thresholding, and you can then add in metrics to see which one is triggering the discrepancy. If the numbers still don't match, then we'll know the issue is something else (e.g. an incorrect property ID, a filter, etc). So please check and we can go from there.

      Reply

    • I can't say for sure why GA sent back a different number via API than through the interface, but one reasons could be sampling or data thresholds. Typically these can be avoided by reducing the date range or avoiding metrics like users (since users require de-duping, e.g. if someone views page A and page B, that's 2 views but only 1 user). Another possible cause is that the 2 reports aren't exactly the same -- can you please confirm that they contain all the same metrics, dates, and filters?

      Reply

  18. Quick question, im currently downloading and appending info from GA day by day (my settings are always yesterday - yesterday as start and end dates). Is there any way for me to run this on a monthly basis and update everything on every scheduled run? lets say today instead of downloading just the 14th, download everything from the 1st to the 14th??

    Reply

Leave a Comment

Jump To