Pull Google Analytics 4 metrics into Sheets

Northwind’s marketing team checks the Google Analytics 4 dashboard for the same handful of numbers every week — sessions and users, day by day. The figures are all there, but exporting them into a Sheet for a chart or a shared report means clicking through the GA4 interface by hand, and that chore quietly gets skipped.

This script removes the chore. It calls the Analytics Data API for the last seven days of daily sessions and users, then rewrites a Sheet so it always holds the current week. Point a chart at that Sheet once and the chart updates itself — the traffic report becomes self-maintaining.

What you’ll need

• Enable the Analytics Data API in Advanced Services. In the Apps Script editor, open Services, add Google Analytics Data API, and keep the identifier AnalyticsData.
• Your GA4 property ID. Find it in GA4 under Admin → Property Settings; the script needs it in the form properties/123456789.
• Edit access to that GA4 property under the Google account running the script.
• A Google Sheet to write the report into, with its ID copied into the config below. The script rewrites the first tab on every run.

The script

// The GA4 property to report on, in "properties/NNNNNNNNN" form.
const GA4_PROPERTY = 'properties/123456789';

// The Sheet that holds the traffic report.
const REPORT_SHEET_ID = '1abcGaId';

// How far back to report. GA4 accepts relative dates like this.
const DATE_RANGE = { startDate: '7daysAgo', endDate: 'today' };

/**
 * Pulls daily sessions and users from GA4 and rewrites the report
 * Sheet so it always shows the latest week. Runs on a trigger.
 */
function pullGa4Sessions() {
  // 1. Ask the Analytics Data API for sessions and users, broken
  //    down by date, over the configured range.
  const res = AnalyticsData.Properties.runReport({
    dateRanges: [DATE_RANGE],
    dimensions: [{ name: 'date' }],
    metrics: [{ name: 'sessions' }, { name: 'totalUsers' }],
  }, GA4_PROPERTY);

  // 2. Guard: a property with no traffic returns no rows.
  if (!res.rows || !res.rows.length) {
    Logger.log('GA4 returned no rows — nothing to write.');
    return;
  }

  // 3. Flatten each GA4 row into [date, sessions, users]. Metric
  //    values arrive as strings, so parseInt makes them real numbers.
  const rows = res.rows.map((r) => [
    formatGaDate(r.dimensionValues[0].value),
    parseInt(r.metricValues[0].value, 10),
    parseInt(r.metricValues[1].value, 10),
  ]);

  // 4. Rebuild the report tab from scratch so it never holds stale days.
  const sheet = SpreadsheetApp.openById(REPORT_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 3)
    .setValues([['Date', 'Sessions', 'Users']]);
  sheet.getRange(2, 1, rows.length, 3).setValues(rows);
  Logger.log('Wrote ' + rows.length + ' days of traffic.');
}

/**
 * GA4 returns dates as "YYYYMMDD" with no separators. Reformat them
 * to "YYYY-MM-DD" so Sheets recognises them as real dates.
 */
function formatGaDate(raw) {
  return raw.slice(0, 4) + '-' + raw.slice(4, 6) + '-' + raw.slice(6, 8);
}

How it works

1. pullGa4Sessions calls AnalyticsData.Properties.runReport, asking for two metrics — sessions and totalUsers — broken down by the date dimension, over the date range in DATE_RANGE. GA4 accepts relative dates like 7daysAgo, so the window slides forward automatically.
2. It checks that the report actually contains rows. A property with no traffic in the window returns none, so the script logs a message and stops rather than writing an empty table.
3. It maps each GA4 row into a flat [date, sessions, users] array. Metric values come back as strings, so parseInt converts them to numbers Sheets can chart and sum.
4. formatGaDate turns GA4’s compact YYYYMMDD date into YYYY-MM-DD so Sheets stores it as a genuine date rather than an eight-digit number.
5. It clears the first tab, writes a header row, and writes the data — so the Sheet always reflects the latest seven days, never a mix of old and new.

Example run

The Analytics Data API returns rows like this:

{
  "rows": [
    {
      "dimensionValues": [{ "value": "20250818" }],
      "metricValues": [{ "value": "412" }, { "value": "388" }]
    }
  ]
}

After a run, the report tab holds the current week:

Date	Sessions	Users
2025-08-18	412	388
2025-08-19	467	431
2025-08-20	503	469
2025-08-21	489	452

A chart anchored to this range now refreshes itself every time the script runs.

Trigger it

To keep the report current without anyone opening GA4, run it on a schedule:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose pullGa4Sessions, event source Time-driven, type Day timer, and pick an early hour such as 6am to 7am.
4. Save and approve the authorisation prompt.

The Sheet then refreshes every morning, and any chart built on it follows along.

Watch out for

• The script rewrites the first tab on every run. Keep charts and notes on a separate tab — anything in the data tab is wiped each time sheet.clear() runs.
• GA4 data is not final immediately. The most recent day or two can still change as Google finishes processing, so today’s figures may drift slightly on tomorrow’s run.
• runReport returns at most 10,000 rows per call. Seven days of daily data is tiny, but if you widen the range or add a high-cardinality dimension like page path, page through the results with limit and offset.
• The account running the script needs access to the GA4 property. A permissions error here is an access problem, not a code bug.
• To extend the report — top pages, conversions, traffic source — add dimensions and metrics to the runReport request, then widen the Sheet write to match the new column count.
• GA4 reporting has API quotas per property. A once-daily run is negligible; watch quota only if you schedule frequent runs across many properties.