GA4 Automated Email Reporting with Spreadsheets & Apps Script

You might have noticed losses in your Google Analytics data for various reasons. Sometimes, we realize these losses too late. For example, an event might suddenly stop showing up in Google Analytics, and we might not notice until much later. If you’re looking for a solution for these kinds of scenarios, this article is just right for you. In short, what I’m going to explain in this article is how to create automated email reporting logic to track the Google Analytics events and KPIs we set on a daily, weekly or monthly basis. Previously, i wrote an articel about how to extract GA4 data using Python, it can also be used for automated email reporting. Let’s get started.

GA4 add-on

We will use SynchWith’s GA4 add-on in this article since GA4 report builder add-on doesn’t have a scheduling feature yet. “GA4 addon by SyncWith” is an add-on that lets you transfer Google Analytics 4 data to Google Sheets. You can transfer all the reports you use in Google Analytics to Google Sheets using this add-on and schedule your reports to be updated at specific intervals. You just need to know the dimensions and metrics you use in your Analytics reports.

Steps to install the add-on:

1. Go to a Google Sheets file.
2. Click on the “Extensions” option in the menu.
3. Hover to “Add-ons” and click on “Get add-ons”
4. In the opened window, search for “GA4”
5. Finally, click on the SynchWith GA4 add-on that appears on the screen and install the add-on.

In the next steps, I will show you how to create reports using the add-on.

What is Google Apps Script

Apps Script is a platform where you can develop JavaScript-based applications for Google Workspace products. By using Apps Script, you can create applications that work in with various Google products like spreadsheets, docs, slides, and forms. This enables you to develop applications that help automate and expedite your tasks. We will cover how to create automated email reporting using Spreadsheets and Apps Script in this article.

For instance, if you want to create a script for Google Sheets, all you need to do is open a sheet file and click on ‘Extensions’ in the menu above. Then, choose the ‘Apps Script’ option. This will directly take you to the screen where you can start developer your applications. I will try to explain how you can use Apps Script to automate your emails reportings soon.

Create Scheduled Reports on Google Sheets

Firstly, select the ‘SynchWith’ add-on we installed earlier from the ‘Extensions’ menu, then click on the ‘Launch Sidebar’ option. (you should connect your GA4 account with the extension and grant access to read your Analytics data).

1. First, in the opened sidebar, choose the Google Analytics v4 connector.
2. Give a name to your report.
3. From the box labeled ‘Select Google Analytics 4 property,’ choose your property.
4. Select the date range you want data from in the ‘Date’ field.
5. Put the dimensions and metrics you want to use into the ‘Fields’ box.
6. Determine your sorting measure from the ‘Sort’ field.
7. Create filters and set data limits from the other boxes if desired.

Below, I am sharing a screenshot of my report settings.

When you click the ‘Next’ button, you will see an option to schedule the report. Since I want the data to be updated daily, I select the ‘Daily’ option, choose the time for data refresh, and then click the ‘Insert’ button. Once you click ‘Insert’, you will see that the selected data is added to your file as a new sheet.

At this point, my recommendation is to create multiple reports and later format them in another sheet tab to turn them into report formats. Then, you can send this report tab as a daily email in PDF format. As an example, I’ve created a tab as shown below and used the previously inserted analytics data.

Now, let’s learn how to use Apps Script for automated email reporting and daily exports.

Send Emails with Apps Script

We have reached the most critical part, as I mentioned earlier, Apps Script is a side tool that allows you to write scripts for Google Workspace products. The application we will develop with Apps Script in this article:

• It will periodically export our report daily or weekly, allowing us to attach the report’s link and PDF version.

• It will automatically send the report to the individuals that we have defined in a daily or weekly basis.

The first thing you need to do is go to your report and select “Apps Script” from the “Extensions” menu. This action will redirect you to the Apps Script panel. Name the application “dailyAnalyticsExport” at the top and paste the code I have written below into the gray code area.

I’m sharing all the code below, but I will explain later which line and which function is used for what purpose.

Code

function dailyAnalyticsExport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("report");
  var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetId = ss.getSheetId();

  var folderName = "daily_analytics_export";
  var folder = null;
  var folders = DriveApp.getFolders();

  while (folders.hasNext()) {
    var currentFolder = folders.next();
    if (currentFolder.getName() === folderName) {
      folder = currentFolder;
      break;
    }
  }

  if (!folder) {
    folder = DriveApp.createFolder(folderName);
  }

  var url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?exportFormat=pdf&format=pdf&gid=${sheetId}&size=letter&portrait=true&fitw=true`;
  var options = { headers: { Authorization: `Bearer ${ScriptApp.getOAuthToken()}` } };
  var response = UrlFetchApp.fetch(url, options);

  var blob = response.getBlob();
  var filename = `daily_report-${new Date().toISOString()}.pdf`;
  blob.setName(filename);

  var emailAddress = ["recipient@hotmail.com"];
  var subject = "Daily Google Analytics Export";
  var message = `Daily Google Analytics export : ${folder.createFile(blob).getDownloadUrl()} it is also attached to the mail. <br> Thanks.`;

  emailAddress.forEach(function (email) {
    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: message,
      attachments: [blob],
    });
  });
}

Explanation

I have a main function named “dailyAnalyticsExport()”.

• First, we specify which spreadsheet we will operate on and which tab we want to use for export. Please replace “getSheetByName(“report”)” with your own tab name.

• Next, we create a download link for the tab to be exported and instruct it to save the exported tab as a PDF in the “daily_analytics_export” folder with the name “daily_report-” + date + “.pdf”. If you don’t have a folder named “daily_analytics_export” on your Google Drive, it will automatically create one.

• Finally, we want it to send an email to the defined individuals with the link and PDF file attached. Add the email addresses of the people you want to send the automated emails (separated by commas) to the “emailAddress” variable. You can also edit the “subject” and “message” variables as desired.

If you’ve done everything correctly, you can click the “Save” button and then the “Run” button to test our application. It may ask you to grant access to the script when you click to the “Run” button. Grant access.

Our script ran successfully and sent the email as expected.

Scheduling

The last thing we need to do is add a timer to the script in order to send scheduled emails and automated email reportings. To do this, click on the “clock” icon on the left, and set your desired interval by clicking the big blue “Add Trigger” button.

Now, everything has been completed. You are ready to send automated analytics reports using Google Spreadsheets and Apps Script.

Conclusion

If you work in the digital marketing industry or at least want to improve your online business based on data, reporting and tracking are extremely important. It’s not always possible to go into Google Analytics and check everything one by one, especially if you want to see a lot of data at the same time. To do all of these, you will need automated reporting solutions. Automated email reporting will save your time and give you a general picture of how your business is doing. Cheers!