Using Google Apps Script and Time-based Triggers to Schedule Automated Emails of Google Sheets
In a previous post, I detailed how to use Google Forms and Google Sheets to log employees' time sheets. In this post, I'll demonstrate how to schedule an automated email to deliver your Google Sheets reports on a daily/weekly basis.
Google Apps Script is a powerful tool that can be used to integrate Google apps - like Docs, Sheets, Forms, and Gmail. For this application, we'll use the script editor within Google Sheets to schedule a trigger that runs daily/weekly/monthly. This trigger will run a function that we'll name 'emailReport'. This function will call the MailApp service which allows the user to send an email. Within this function, we'll create a PDF attachment based on a specific sheet that contains the report we want to email.
We'll start by creating the 'emailReport' function and then setup a trigger. Let's take a look at the MailApp.sendEmail method we'll be using:
MailApp.sendEmail(recipient, subject, body, options)
The recipient, subject, and body parameters are self-explanatory. We'll declare and set these parameters using variables. Make sure to update them to your specifications.
The options parameter will allow us to include an attachment within the email. As you can see below, the attachment parameter accepts a blob data type object.
I'll explain how to convert the attachment to this data object, but first let's create our attachment.
Google allows documents to be downloaded via a HTTP request. Typically these are done by visiting a specific URL. For example, clicking on this link will download the Google Sheet we used to save our Timesheet Google Forms' response: https://docs.google.com/spreadsheets/d/13X162GFQCnZMzpneAzK3_etv46zVuW4nWn9713-I-rw/export
The 'export' in our URL, tells Google to download the Google Sheet. By default, it will download as an XLSX doc, but we can customize the format by adding additional parameters to the URL like this.
In this example, we set a number or parameters including the format as PDF and indicated that we wanted to download the 'Weekly Timesheet Totals' sheet by referencing its ID.
However, instead of visiting this URL to download the attachment, we'll access the attachment via the URL Fetch Service. This service allows us to make a HTTP request and fetch the response (the attachment) as a blob which is exactly what we need for the 'sendEmail' function.
Here we combined the Base URL (url) with the URL Parameters (url_ext) and the Sheet ID (sheetID). This is the full URL we want to request data from.
We'll pass this URL along with the appropriate authorization using the UrlFetchApp.fetch method and receive a response. By using the getBlob() method, the response is returned as a blob.
We'll set the name of the blob and include the '.pdf' file extension. Everything is now ready to attach a PDF of the sheet to the email.
Here is the complete 'emailReport' function:
Make sure to update the variables to your specifications. Then you can run the function to test if it works. You'll have to authorize the function the first time you run it.
Now let's automate the delivery of this report.
To do so, we'll create a trigger. Click on Edit, and then 'Current project's triggers'.
You shouldn't see any results, since we haven't created any triggers yet.
Click on + Add Trigger and you should see a popup like this.
The 'emailFunction' should already be selected. Since we want to run this trigger on a schedule, we need to change the event source from 'From spreadsheet' to 'Time-driven'. Select the frequency you want the email to be sent. In our case, we'll select 'Week timer' and have it run on Monday between Midnight and 1am.
Click Save and the trigger is ready to run. The trigger will run the 'emailReport' function sometime between 12-1am every Monday and then you should see the email in your inbox.