Loop not working while pulling data from a Google Analytics 4 API - App Script (overcome 10K of rows limit)

Question

I'm trying to retrieve data from a google analytics account. So far, the app script code is working.

function runReport(sheetName, dimensionArr, metricArr, startDate, endDate, filters) {
    let propertyId = "XXXXXXXXXXX";
    let propertyName = "Your Property Name";  // Set your desired property name here

    let spreadsheet = SpreadsheetApp.create('Google Analytics Report');
    let sheet = spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);

    // Loop over each date within the specified range
    let currentDate = new Date(startDate);
    let end = new Date(endDate);

    while (currentDate <= end) {
        let formattedDate = currentDate.toISOString().split("T")[0];  // Format to YYYY-MM-DD

        let nextPageToken;
        do {
            try {
                let request = AnalyticsData.newRunReportRequest();

                // Date
                let dateRange = AnalyticsData.newDateRange();
                dateRange.startDate = formattedDate;
                dateRange.endDate = formattedDate;
                request.dateRanges = dateRange;

                // Metric and Dimension setup
                let metrics = [];
                for (let x = 0; x < metricArr.length; x++) {
                    let metricx = AnalyticsData.newMetric();
                    metricx.name = metricArr[x];
                    metrics.push(metricx);
                }
                request.metrics = metrics;

                let dimensions = [];
                for (let x = 0; x < dimensionArr.length; x++) {
                    let dimensionx = AnalyticsData.newDimension();
                    dimensionx.name = dimensionArr[x];
                    dimensions.push(dimensionx);
                }
                request.dimensions = dimensions;

                // OrderBy = Date
                let dimensionOrderBy = AnalyticsData.newDimensionOrderBy();
                dimensionOrderBy.dimensionName = 'date';
                let orderby = AnalyticsData.newOrderBy();
                orderby.dimension = dimensionOrderBy;
                orderby.desc = false;
                request.orderBys = [orderby];

                // Filter
                if (filters) {
                    let dimensionfilter = AnalyticsData.newFilterExpression();
                    dimensionfilter.andGroup = AnalyticsData.newFilterExpressionList();
                    dimensionfilter.andGroup.expressions = [];

                    for (let x = 0; x < filters.length; x++) {
                        for (let j = 0; j < filters[x].conditions.length; j++) {
                            let filterExpression = AnalyticsData.newFilterExpression();
                            filterExpression.filter = AnalyticsData.newFilter();
                            filterExpression.filter.fieldName = filters[x].fieldName;
                            filterExpression.filter.stringFilter = AnalyticsData.newStringFilter();
                            filterExpression.filter.stringFilter.value = filters[x].conditions[j];
                            filterExpression.filter.stringFilter.matchType = filters[x].matchType;
                            dimensionfilter.andGroup.expressions.push(filterExpression);
                        }
                    }
                    request.dimensionFilter = dimensionfilter;
                }

                // Pagination
                if (nextPageToken) {
                    request.pageToken = nextPageToken;
                }

                let report = AnalyticsData.Properties.runReport(request, 'properties/' + propertyId);

                // Exit if no rows are found
                if (!report.rows || !report.rows.length) {
                    Logger.log(propertyName + '\tNo rows returned for date ' + formattedDate);
                    break;
                }

                // Append the headers only if they haven't been added before
                if (sheet.getLastRow() === 0) {
                    let dimensionHeaders = report.dimensionHeaders.map(dimensionHeader => dimensionHeader.name);
                    let metricHeaders = report.metricHeaders.map(metricHeader => metricHeader.name);
                    let headers = [...['datasource'], ...dimensionHeaders, ...metricHeaders];
                    sheet.appendRow(headers);
                }

                // Append the results.
                let rows = report.rows.map((row) => {
                    let dimensionValues = row.dimensionValues.map(dimensionValue => dimensionValue.value);
                    let metricValues = row.metricValues.map(metricValue => metricValue.value);
                    return [...[propertyName], ...dimensionValues, ...metricValues];
                });
                sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);

                // Update nextPageToken for next iteration
                nextPageToken = report.nextPageToken;

            } catch (e) {
                Logger.log('Failed with error: %s', e);
                break;
            }
        } while (nextPageToken);  // Continue fetching as long as there's a nextPageToken

        // Move to the next date
        currentDate.setDate(currentDate.getDate() + 1);
    }

    Logger.log('%s:\tReport spreadsheet created: %s', propertyName, spreadsheet.getUrl());
}

function main() {
    let dimensions = [], metrics = [], filters = [];
    let startDate = "2022-09-02";
    let endDate = "2022-09-03";

    dimensions = ['date','landingPage', 'pagePath', 'sessionDefaultChannelGroup','sessionSourceMedium'];
    metrics = ['sessions', 'totalRevenue'];
    runReport("Sheet1", dimensions, metrics, startDate, endDate);
}

However, the data received are limited by 10K of row per day requested. I'd like to overcome this 10K of rows per day (date) limit and pull more data from the API.

When I'm trying to retrieve more than 10K of rows for a single day with a PageToken, the limitation are still there. Even more, when I'm adding PageSize, I'm getting an error type :

GoogleJsonResponseException: API call to analyticsdata.properties.runReport failed with error: Invalid JSON payload received. Unknown name "pageSize": Cannot find field.

Any idea how to correct the current code ?

The idea is to have the loop to work by getting all data (10K per 10K, until all data are pulled from a day/date), then move to the next day based on the date set on the script.

Thanks

Answer 1

I think you are a little confused. There is no limit to the number of rows you can request.

A report request has a parameter called limit. this is by default set to 10k

If unspecified, 10,000 rows are returned. The API returns a maximum of 250,000 rows per request, no matter how many you ask for.

Each page of the report will return a max of Limit rows. If there are more rows in the report.

You then need to paginate over the additional pages using the limit and offset parameters

For example "limit": 100000, "offset": 100000 will return the next 100k rows starting at row number 100k

So your solution would be to paginate to get more data from the api.

Note: There is a 10k limit to the number of REQUESTS that you can make to a property, but this is requests against the api not the amount of data returned.

Example for recursive pagination with the google analytics data api and app script

function new_metric(name){

  var item = AnalyticsData.newMetric() ;
  item.name = name;
  return item;
}

function new_dimension(name){

  var item = AnalyticsData.newDimension() ;
  item.name = name;
  return item;
}


function run_a_request_with_pagination(){


  // building the report
  const propertyId = "250796939";
  const request = AnalyticsData.newRunReportRequest();
  request.dimensions = [];
  request.metrics = [];
  // Parse metrics      

  request.metrics.push(new_metric("screenPageViews")) 

  request.dimensions.push(new_dimension("city")) 
  request.dimensions.push(new_dimension("pageTitle")) 


  // Parse date range
  const dateRange = AnalyticsData.newDateRange();
  dateRange.startDate = "2022-01-01";
  dateRange.endDate = "today";      
  request.dateRanges = dateRange;

  // make a request that will continue to paginate over the results.
  paginate(propertyId, request, 10000, 0) 
}


function paginate(propertyId, request, limit, offset = 0){

  console.log("Limit is: " + limit)
  console.log("Offset is." + offset)    

  try {

    // Set the limit
    request.limit = limit
    // Set the offset for this page.
    request.offset = offset       

    const report = AnalyticsData.Properties.runReport(request, 'properties/' + propertyId);
    if (!report.rows) {
      console.log('No rows returned.');
      return;
    }
    console.log("Fetching: " + limit + " rows. Starting at: " + offset )
    console.log("There are a total of: " + report.rowCount + " rows." )
    console.log("There are in this request." + report.rows.length + " rows." )

    // If there are more rows to grab it will run this method recersively 
    if(report.rowCount > limit + offset){
      paginate(propertyId, request, limit, limit + offset) 
    }
  } catch (e) {
    console.log('Failed with error: %s', e);
  }
}