Using the Google Analytics Data API to Pull GA4 Data into Google Sheets

The Google Analytics Data API is a powerful tool that enables you to access, process, and analyze your Google Analytics 4 data from anywhere, including Google Sheets. By using the API, you can pull data from Google Analytics 4 into a Google Sheets spreadsheet, where you can manipulate and visualize it in any way you need.

Access Google Analytics 4 Data from Google Sheets

The method outlined below is for advanced solutions that require more control or customization of the data transfer process, custom menu options, and user functionality in Google Sheets. For simple reports and data transfer needs, you can export data files from GA4 and import them into Google Sheets using the menu options in those tools. You can also use a Google Sheets Add-on such as the official GA4 Reports Builder for Google Analytics from Google, Michele Pisani’s GA4 Magic Reports Add-On, the SyncWith Google Sheets Add-on, or the API Connector for Google Sheets from Mixed Analytics, which all enable users to create and run reports on GA4 data from within Google Sheets.

Any of those may serve as a quick and relatively easy method to perform most common tasks, but if you need more advanced functionality, read on…

Basic Steps and Sample Code

  1. Enable the Google Analytics API. To do this, go to the Google Developers Console and select your project. Then, click the APIs & Services tab and search for Google Analytics Data API. Click the Enable button next to the API.
  2. Create a Google Sheets spreadsheet.
  3. Create a new Apps Script. In the spreadsheet, go to Extensions->Apps Script.
  4. Add the Google Analytics Data API client library to your script. In the Apps Script editor, click on the plus (+) sign next to the Services Headers to Add a Service. Scroll to find the Google Analytics Data API. Select the service and click the Add button.
  5. Write a script to pull data from Google Analytics 4. The following is a simple script that you can use to pull data from Google Analytics 4 into a Google Sheets spreadsheet. Feel free to start with this script and modify it as needed to suit your specific requirements.
/* Copyright 2023 Optimization, LLC (o10n)
 * version 1 - Modified Dimensions & Metrics for this example and Added OrderBy functionality
 *
 * Derived from Google LLC, Copyright 2021
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
function pullDataFromGA4() {
  // Set the Google Analytics 4 property ID.
  const propertyId = 'YOUR_GA4_PROPERTY_ID';
  try {
    // Construct Dimensions Array
    const dimension = AnalyticsData.newDimension();
    dimension.name = 'date';
    // Construct Metrics Array
    const metricsArray = [];
    const totalUsers = AnalyticsData.newMetric();
    totalUsers.name = 'totalUsers';
    metricsArray.push(totalUsers);
    // Construct Date Range
    const dateRange = AnalyticsData.newDateRange();
    dateRange.startDate = '7daysAgo';
    dateRange.endDate = 'yesterday';
    // Construct Order By
    const orderBy = AnalyticsData.newOrderBy();
    orderBy.desc = false;
    const dimOrderBy = AnalyticsData.newDimensionOrderBy();
    dimOrderBy.dimensionName = dimension.name;
    dimOrderBy.orderType = 'NUMERIC';
    orderBy.dimension = dimOrderBy;
     
    // Construct Report Request
    const request = AnalyticsData.newRunReportRequest();
    request.dimensions = [dimension];
    request.metrics = [metricsArray];
    request.dateRanges = dateRange;
    request.orderBys = orderBy;
    // Run the report.
    const response = AnalyticsData.Properties.runReport(request, 'properties/' + propertyId);
    // Get Active Sheet and Clear All Data
    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
   
    // Construct Header
    const dimensionHeaders = response.dimensionHeaders.map(
        (dimensionHeader) => {
          return dimensionHeader.name;
        });
    const metricHeaders = response.metricHeaders.map(
        (metricHeader) => {
          return metricHeader.name;
        });
    const headers = [...dimensionHeaders, ...metricHeaders];
    // Write Header to Sheet
    sheet.appendRow(headers);
   
      // Construct Row Data
      const rows = response.rows.map((row) => {
        const dimensionValues = row.dimensionValues.map(
            (dimensionValue) => {
              return dimensionValue.value;
            });
        const metricValues = row.metricValues.map(
            (metricValues) => {
              return metricValues.value;
            });
        return [...dimensionValues, ...metricValues];
      });
      // Write Row Data to Sheet
      sheet.getRange(sheet.getLastRow(), 1, response.rows.length, headers.length)
          .setValues(rows);
  } catch (e) {
    console.log('Failed with error: %s', e.error);
  }
}

To use the script, replace YOUR_GA4_PROPERTY_ID with the ID of your Google Analytics 4 property. You can find the property ID in the Google Analytics 4 interface (it’s the ID listed in Admin->Property Settings).

Once you have replaced the property ID, you can run the script by clicking the Run button in the script editor. The script will pull data from Google Analytics 4 and write it to the spreadsheet.

You can modify the script to pull different dimensions and metrics, or to filter the data. You can also use the script to pull data from multiple Google Analytics 4 properties or to pull data from different date ranges. Visit the Google Apps Script Analytics Data Service API Reference, for more information.

Examples of using Apps Script with the Google Analytics Data API

There are many benefits to using the Google Analytics Data API to pull data from GA4 into a Google Sheets spreadsheet:

  • Automated Data Retrieval: Automate the data pull process, so that you don’t have to manually export data from Google Analytics 4 into Google Sheets.
  • Data Aggregation: Easily combine data from multiple sources like Google Analytics 4, CRM, or ERP into one spreadsheet.
  • Data Flexibility: Re-Organize, manipulate and filter data in any way you need to.
  • Tailored Reporting: Create custom reports and visualizations that are tailored to your specific needs.
    BONUS ECommerce SEO Tip – Identify products with the most organic search traffic and no sales (zero-converting organic search traffic) by importing visitor source, landing page, and order data. Filter to product pages from organic search, with no sales orders, and sort by page views in descending order. Analyze these pages to determine why they aren’t converting!
  • Custom Menus and Functions: With Google Apps Script, you can create custom functions and menus in Google Sheets (and other Google Workspace apps) that extend your data processing capabilities and provide users with more control.

Google Sheets and the Google Analytics Data API are both powerful tools for marketers, analysts, and website owners. Combining them will help you unlock the full potential of your GA4 data.


Can We Assist?
Expert Advice in Just a Few Clicks
Please enable JavaScript in your browser to complete this form.
Name
How Can We Help?