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
Commenti recenti