[GA4] Combining infinite Reports in Google Sheets

A question I am often asked by users of GA4 Magic Reports (you can install it from here) is the following:

❓ Is there a way to add new data to the end of existing data when re-running a report?
This is because every time a report is run, the output is completely regenerated.
Anyway, the answer is simple: No.

But I don’t like simple things, and I especially don’t like not finding a solution to a problem that is brought to me…

✅ That’s why the answer becomes: Not directly, but here’s how you do it!

Google Sheets has the quality of being able to use its own data management functions: you can use native Formulas and you can use Scripts. And we will use them! 😄

ℹ Therefore, we will make sure that the tool automatically creates a report with a specific name every day and collects the results of all generated reports in one sheet.

Proceed as follows:

☑ Create and configure a new report from the GA4 Magic Reports add-on interface as you normally do (and save it)

☑ Replace the name of the report in the cell with this formula:
=”Report ” & YEAR(TODAY()) & MONTH(TODAY()) & DAY(TODAY())

This way the report name will be dynamic and will automatically update every day

☑ Schedule the report to run every day between 5 a.m. and 6 a.m.

☑ From the “Extensions” menu of Google Sheet, select “Apps Script“

🎁 Copy and paste the code I have created:

function combineDataFromDynamicSheets() {

  var sheetPrefix = "Report ";
  var report_range = "A16:C";
  var targetSheetName = "DataAggregator";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName(targetSheetName);

  if (!targetSheet) {
    targetSheet = ss.insertSheet(targetSheetName);
  } else {
    targetSheet.clear(); // Pulisce i dati esistenti nel foglio di destinazione
  }

  var combinedData = [];
  var sheets = ss.getSheets(); // Ottiene tutti i fogli nel file
  var regex = new RegExp("^" + sheetPrefix + "\\d+$"); // Crea la regex dinamica usando la variabile

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();

    if (regex.test(sheetName)) {
      var range = sheet.getRange(report_range);
      var values = range.getValues();
      

      for (var j = 0; j < values.length; j++) {
        if (values[j][0] !== "") { // Considera solo le celle non vuote
          combinedData.push([values[j][0],values[j][1],values[j][2]]);
        }
      }
    }
  }


  if (combinedData.length > 0) {
    targetSheet.getRange(1, 1, combinedData.length, 3).setValues(combinedData);
  }
}

☑ Click on “Triggers” section

☑ Add a “Time-driven” one that automatically runs between 6 a.m. and 7 a.m.

That’s it! Easier done than said 🙂

🙌 You now have an automatic reporting system for GA4 that centralises all daily data extractions in one place!

Let me know how it sounds!

🎯 Follow me, Michele Pisani, for the more GA4/PPC insight
💌 Subscribe to my Newsletter: I know I Know Nothing