Schedule Ga4 Reports Using Google Sheets API
Google Analytics 4 (GA4) does not provide a built-in option for scheduling reports. However, you can overcome this limitation by leveraging Google Sheets’ Apps Script and the Google Analytics Data API. This approach eliminates the need for third-party paid extensions and allows for a custom, automated reporting system.
Steps to Automate GA4 Reports in Google Sheets
1. Set Up Google Apps Script in Google Sheets
Open a new Google Sheet.
Navigate to Extensions → Apps Script.
Click the ‘+’ icon next to Services on the left pane.
Add Google Analytics Data API and Gmail API.
2. Configure Your GA4 Property
Copy your GA4 property ID.
Paste it into the script at the designated placeholder.
3. Define Metrics and Dimensions
To extract meaningful data, define metrics and dimensions as follows:
const metric1 = AnalyticsData.newMetric();
metric1.name = 'screenPageViews';
const metric2 = AnalyticsData.newMetric();
metric2.name = 'totalUsers';
const metric3 = AnalyticsData.newMetric();
metric3.name = 'userEngagementDuration';
const dimension = AnalyticsData.newDimension();
dimension.name = 'pagePath';
For the naming conventions of metrics and dimensions, refer to Google’s API Schema.
4. Apply Filters and Run the Report
Filters can be applied using:
AnalyticsData.newFilter();
Alternatively, you can process and clean data using native JavaScript methods.
To create a reporting request, use:
const request = AnalyticsData.newRunReportRequest();
request.dimensions = [dimension];
request.metrics = [metric1, metric2, metric3];
request.dateRanges = dateRange;
Execute the request using:
AnalyticsData.Properties.runReport(request, 'properties/' + propertyId);
5. Update Google Sheets and Send Notifications
Once the data is retrieved, update the sheet using the Google Sheets API and notify your team via email using the Gmail API:
GmailApp.sendEmail(
'support@suzhiumcorp.com',
'GA4 Scheduled Report',
'Hi Team,\nCheck out the latest analytics report: [Google Sheet Link]'
);
6. Automate Report Execution
In Apps Script, navigate to Triggers.
Set up a time-driven or calendar-driven trigger.
The script will run automatically at the scheduled time and email the report.
/**
* Runs a report of a Google Analytics 4 property ID. Creates a sheet with the report.
*/
function runReport() {
/**
* replace the below ID with your
* Google Analytics 4 property ID before running the code.
*/
const propertyId = '316981098';
try {
const metric1 = AnalyticsData.newMetric();
metric1.name = 'screenPageViews';
const metric2 = AnalyticsData.newMetric();
metric2.name = 'totalUsers';
const metric3 = AnalyticsData.newMetric();
metric3.name = 'userEngagementDuration';
const dimension = AnalyticsData.newDimension();
dimension.name = 'pagePath';
const dateRange = AnalyticsData.newDateRange();
dateRange.startDate = '2024-09-01';
dateRange.endDate = 'today';
const request = AnalyticsData.newRunReportRequest();
request.dimensions = [dimension];
request.metrics = [metric1,metric2,metric3];
request.dateRanges = dateRange;
const report = AnalyticsData.Properties.runReport(request,
'properties/' + propertyId);
if (!report.rows) {
Logger.log('No rows returned.');
return;
}
r = new RegExp("/*why-the-new-all-electric-mini-cooper-is-our-best-small-electric-car.*|.*new-all-electric-mini-cooper-6-big-reasons-to-love-it.*|.*new-all-electric-mini-cooper-its-high-tech-features-explained.*|.*new-all-electric-mini-cooper-find-your-perfect-spec.*|.*how-to-pick-your-perfect-all-electric-mini.*|.*new-all-electric-mini-cooper-mini-moments-of-magic.*|.*new-all-electric-mini-cooper-mini-reasons-to-go-electric.*/")
_r = report.rows.filter(function(i){
if(i["dimensionValues"][0].value.match(r) && i["dimensionValues"][0].value.match(r)[0]){
console.log(i["dimensionValues"][0].value)
console.log(i["dimensionValues"][0].value.match(r))
return i
}
})
console.log(_r[1])
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WC Beta");
console.log(sheet)
sheet.deleteRows(2, sheet.getLastRow());
console.log("sheet1 cleared")
// 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 = _r.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, _r.length, headers.length)
.setValues(rows);
GmailApp.sendEmail(
'support@suzhiumcorp.com',
'WC Weekly Stats',
`Hi Team,\n
Check out the latest stats here in this sheet:\n
https://docs.google.com/spreadsheets/d/1rlWg3bXsQF6N5IsBe-5VuUpl12ew/edit?gid=0#gid=0`
);
} catch (e) {
// TODO (Developer) - Handle exception
Logger.log(e);
}
}
Why This Method Works
This method offers several advantages:
Cost Savings: Avoids paid third-party extensions.
Customization: Fully tailored to your reporting needs.
Automation: Reports run and email notifications are sent on schedule.
Conclusion
By implementing this method, you can automate GA4 reports efficiently, ensuring timely insights without relying on native scheduling features. For customized analytics solutions, Suzhium Corp specializes in helping businesses maximize their data capabilities through automation and tailored tracking strategies.