Import Google Analytics 4 (GA4) Data to Google Sheets for free

We already know when Google Analytics Universal will stop working, but Google still didn't share free add-on like for Google Analytics Universal which enables downloading data directly in Google Sheets and most important to schedule it. Maybe my solution isn't perfect but it's for free and you don't need to use any servers etc.

What did I do?

1) Under Extension I selected  Apps Script

2) I added new Service AnalyticsData (v1beta version)

3) I clicked See documentation to find good example how to apply it in Google Apps Script

https://developers.google.com/apps-script/advanced/analyticsdata?authuser=0 

4) I copied code from the documentation into my sheet and launched some changes - instead of creating a new sheet every time I seleced a sheet name where generated data should be pasted (marked by pink)

5) Last step was running the code - before it is run for the first time you will be asked about permission 

This is example of very simple report in GA4, without filters, this topic is covered in 2 separate posts:

[link] - dimensionFilters

[link] metricFilter

  /**

 * Runs a report of a Google Analytics 4 property ID. Creates a sheet with the

 * report.

 */

function runReport() {

  /**

   * TODO(developer): Uncomment this variable and replace with your

   *   Google Analytics 4 property ID before running the sample.

   */

  const propertyId = '312668969';

  try {

    const metric = AnalyticsData.newMetric();

    metric.name = 'activeUsers';

    const dimension = AnalyticsData.newDimension();

    dimension.name = 'city';

    const dateRange = AnalyticsData.newDateRange();

    dateRange.startDate = '2022-05-18';

    dateRange.endDate = '2022-05-18';

    const request = AnalyticsData.newRunReportRequest();

    request.dimensions = [dimension];

    request.metrics = [metric];

    request.dateRanges = dateRange;

    const report = AnalyticsData.Properties.runReport(request,

        'properties/' + propertyId);

    if (!report.rows) {

      Logger.log('No rows returned.');

      return;

    }

    const sheet =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")

    // Append the headers.

    const dimensionHeaders = report.dimensionHeaders.map(

        (dimensionHeader) => {

          return dimensionHeader.name;

        });

    const metricHeaders = report.metricHeaders.map(

        (metricHeader) => {

          return metricHeader.name;

        });

    const headers = [...dimensionHeaders, ...metricHeaders];

    sheet.appendRow(headers);

    // Append the results.

    const rows = report.rows.map((row) => {

      const dimensionValues = row.dimensionValues.map(

          (dimensionValue) => {

            return dimensionValue.value;

          });

      const metricValues = row.metricValues.map(

          (metricValues) => {

            return metricValues.value;

          });

      return [...dimensionValues, ...metricValues];

    });

    sheet.getRange(2, 1, report.rows.length, headers.length)

        .setValues(rows);

  

  } catch (e) {

    // TODO (Developer) - Handle exception

    Logger.log('Failed with error: %s', e.error);

  }

}