Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen

Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue?

I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task.

Reminding staff to complete a task can be time consuming and irritating. What if you could send an email to all those staff members who haven’t completed the task with a simple click of the button?

In this tutorial, we will learn how to create a Google Apps Script HTML email mail merge service that will send an email to a staff member who has not completed their task, indicated by a missing tick on a tickbox.

We will use the sample sheet below:

The Google Sheets reminder is triggered by a custom menu item.  You can see it at the top right of the image.

When the reminder is sent, it collects the rows of staff that have not indicated that they have completed the task.  It then sends an email to those staff members  with the reminder below:

As you can see, I’ve added some custom HTML to highlight the header and modify the width of the image. The email contains a number of unique pieces of data gathered from the Google Sheet Row:

• The name of the user.
• The deadline.
• The name of the sheet (In this case, Tester).
• The number of days overdue.

It also contains a link to the check sheet for the user to update once they have completed their task.

Back at The Badger Google Sheet, an alert window appears once the code is completed and also indicates any emails that could not be sent.

To get started you can Make a Copy of the Google Sheet by following this link:

The Badger

If you would prefer a walkthrough for creating The Badger Google Sheet, you can check out my previous tutorial:

Google Sheets: How to create a task completion check sheet.

Let’s take a look at the code:

The Code

The code is divided into two parts. The Code.gs, the Google Apps Script code, and the email.html, which contains the HTML and email message.

Code.gs

The email HTML:

email.html

Quick Use

To quickly apply this code to your own project, there are a number of locations that you will need to customise. I’ve tried to maintain notes in the code to help you know what you need to edit. If you find anything confusing you can always find the relevant part in The Walkthrough below for more information. The Quick Use portion of the tutorial is designed for users with limited coding experience.

There are three main areas of the code that you will need to edit for your own project:

1. The GLOBAL variables.
2. The email.html.
3. Some parts of the sendEmail() function.

The global variables

There are a number of global variables that you will have to change at the top of the Code.gs sheet.

ss_id

This is the spreadsheet id. You can find this in the URL bar at the top of your Chrome browser.

sheetName

This can be found at the bottom of your sheet as one of the sheet tabs.

dueDateCell

Most check sheets have a due date somewhere on the sheet as a reminder of the deadline for the user. For us, it is “C2”.

startRow

This is the first row after the head and the first row of data we will be collecting.

reminderColStart

This is the column that our reminder column details start on. These detail the number of reminders sent in column F (The 6th column) and the date the last reminder was sent, column G.

This is a good psychological incentive for users and team managers to get the task done.

Next, we have some Email Data. 

Subject

This is the subject of the email. It is put here for ease of access.

imgID

For the purpose of highlighting how to embed images into HTML emails in Google Apps Script, I have added a friendly badger. You could add a logo or something else, like… oh, I don’t know… a goat.

The imgID references the Google Drive ID of the image. You can get the image ID by right-clicking the image in your Google Drive file and selecting Share. You will see a share link url. You will probably have to copy it all and paste it somewhere before selecting out the image id.

Email.html

You can modify the email.html to anything you want. However, it is important to keep in mind that most email services require you to put your CSS inline in your HTML. That is inside the element tags like this:

You will also notice a couple of other unusual elements in the email.html

The <img src='cid:badger'>  on line 15 is the location of the image we have embedded in the email. As you can see the image tag is a little different to what you may be used to when creating an HTML page. Just know that you can insert images in this format into HTML emails with Google Apps Script. The cid references to badger which is the name of a variable in your sendEmail() function back in our Code.gs file. The code fetches the ID of the image from our Google Drive folder.

We will go into more detail on this in the walkthrough below.

You will also have noticed tags like the following:

<?= name_of_something ?>

For this email, these are place-markers for the unique data you will add from your Google Sheet rows into the HTML email. For example, they may represent the user’s name and how many days overdue they might be. These reference variables in the sendEmail() function.

If you add a new place-marker you will need to update the sendEmail() function accordingly.

sendEmail()

 

In the sendEmail() function you will need to modify the staff variable objects ( lines 15-18). These generally reference the row data of the user whose task is overdue. These are displayed in an array starting a zero. In our example, the “name” on line 15 references the name column of our sheet.

I recommend that you keep name, email and dueDate the same but adjust their location if it is on different columns in your Google Sheet. You can add extra staff items underneath separated by a comma if you wish to add them into your email.html. Remember, you will need to update the HTML file. You will also need to update the body variable.

The body variable on lines 32-35 is referenced in email.html inside those question mark tags:

<?= name_of_something ?>

For example, body.name references our staff.name object which is the name of the staff member who is being sent a reminder. body.name is referenced in the email.html as:

<?= name ?>

Again, for more information on this, please review the relevant part of the walkthrough below.

This should be everything you need to get started on your own project.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

The Walkthrough

We’ve already discussed the Global variables in the Quick Start guide above. Before we move on to the main() function, we need to take a quick look at how the custom menu works.

onOpen()

The onOpen() function creates a custom menu item when The Badger Google Sheets file is opened each time.

onOpen() is a simple trigger created by Google Apps Script. It is run when the user opens a file. It is generally used to create menus or checks users credentials.

Line 10, gets the user interface instance class for the SpreadsheetApp parent class. the UI class has the ability to create menus and submenus with the createMenu builder. First, we name our menu, “Reminders” (Line 11). Then we add an item to the Reminder title. We will call the “Send reminder”. When this is clicked, it will run the main() function. Once we have set everything up, we add our menu data to the UI on line 13.

main()

Note: you can pop out the code and reference it as you read through the explanation. There is a little button in the top right of the code block.

As the name suggests, the main() function is the core function for the Badger script. Lines 9-10 call the Google Spreadsheet by the selected id from the Globals at the top of the page and then get the indicated sheet tab, in our case “Quarterly Report”.

Next, we find the last column and row data (lines 12-13). We’ll use these to get our data range a little later.

On line 15, we get the date values of the dueDateCell which for us is cell  “C2”. This will most likely return a Unix Time Stamp if Google Sheets has read the cell correctly. That timestamp is a little too verbose for our users to quickly read so we will use the Utilities Service to format the date into something more simple.

Utilities.formatDate takes 3 parameters:

1. The date as a Unix Time Stamp.
2. The Timezone.
3. The format, known as SimpleDataFormat.

For our example, our first parameter is the dueDate. I’m currently on summer vacation in Thailand (they treat goats well here!) so the timezone for me is GMT+7. Finally, the simple data format that I want to display is the day of the week, the day’s date, the month and the year. This would read as:

"EEE, dd MMM yyyy"

Which would look like:

"Sun, 19 May 2019"

There are a lot of options for you to adapt this to your own preference or region.

On lines 19-20, we grab all the data after the header in the spreadsheet. We use the range class to access this. This class takes up to 4 parameters:

1. Start row: Indicated by our startRow global variable.
2. Start column: 1 or the first column.
3. Row depth: Subtract the start row from the last row and add 1.
4. Column width: the lastColumn variable.

We then set up 2 empty arrays. badEmailList collects an array of any email that could not be sent.

reminder will store all the values including the updated values of the two reminder columns F and G.

Line 24 starts the main loop. This loops through each row looking to see if tick box in column C  (or array item 2) has not been checked. If it hasn’t, then it run’s the sendEmail() function.

The sendEmail() function takes 3 parameters:

1. all the column values for the currently looped row as an array.
2. The URL of the current spreadsheet as a string.
3. The due date as a string.

The sendEmai() function returns any “bad” email addresses that the email could not send to into the badEmail variable.

Then on line 32, we first check to see if sendEmail() did not return a bad email ( a falsy, undefined). If it returns undefined (or empty), we want to add one to the current row’s Reminders Sent column F (rangeVals[row][5] += 1;) to increase the number of reminders and, update to the current date for the current row’s Last Reminder Date column G (rangeVals[row][6] = new Date();). Otherwise, if there is a bad email we want to record it and push it to the badEmailList.

Line 42, pushes each row’s reminder data to the reminder array. Then on line 45, we get the reminder range for columns F and G and update the values in the Google Sheet.

Finally, on line 48, we call the openCompleteWindow() function, which takes the list of bad emails. This will send an alert to the user indicating that the code is complete and display any emails that could not be sent.

sendEmail(all vals in row, spreadsheet URL, due date)

The sendEmail() function creates a custom email based on an HTML template and sends it to each email address as the main() function loops through each staff member who is overdue.

Lines 14-19, create a staff object containing the name of the overdue staffer member, their email, the due date and the number of days overdue. These will be used later when preparing the email for data unique to the overdue staff member.

First, on line 20 we update the staff.daysOverdue object. If it is a single day, we want to display 1 day, but if it more that one day we want to use “days” with an “s”.

Next, we need to set up the image to be embedded into the email (Line 24). To grab the image URL we add the standard URL prefix for a Google Drive file to the image ID we added to our Globals at the top of the code.

We then need to grab the image using the UrlFetchApp. The URL Fetch service is not just for grabbing Google Drive data it can also attempt to fetch data and files from any HTTP or HTTPS URL. We use fetch(url) to grab our image. Next, we need to set that image as a Blob (binary large object). The getBlob method is used to do this. Then we set its name. In our case, we will set the name to “badgerBlob”, just like the variable we put it in (line 25).

The next task is to set up the HTML email (Lines 31-25). We could also put the HTML for the email inside the MailApp.sendEmail parameters but I think it looks a little messy and is infinitely more difficult to test.

We are going to create an email template from a file using:

HtmlService.createTemplateFromFile("email");

Creating a HTML template allows us to add custom values from our Code.gs sheet. We can send this data to the HTML template by creating objects.

We’ve called our HTML template we drew from our email.html, body. As you can see on lines 32 to 35, we have set these objects to display name, due date, overdue days and the report name.

To display these values in the HTML we use scriptlet tags <?= ... ?> . So for example, if we wanted to put the users’ name after “Hello, ” in our HTML body we would write:

<?= name ?>

Have a look a the image below to see the matches between these body objects and the HTML.

Finally, this is where all the magic occurs. It’s time to send the email. Well, attempt to at least. We will chuck in some try and catch error handling just in case our emails are faulty.

Line 39 calls the Google Apps Script MailApp service. To send an email we are going to use the sendEmail method. There are a number of ways to send an email using this method, but we will use the advanced parameters. Why? Well, when you use the advanced parameters you can set object parameters like “to”, “subject”, “htmlbody”. These are a lot more clear for the coder to read than a list of parameters. Take a look at the first three parameters for our code:

1. to: the email address we will send our message to. In our case the staff.email.
2. subject: the subject of the email. We created this up top in the Globals.
3. htmlbody: the HTML you will send in your email. As mentioned earlier, you could put all the HTML in here inside quotation marks, but it is unruly and messy. For our example, we grab our HTML template with all it’s custom data parameters drawn from the current sheet row. evaluate it and then display the content.

Inside our sendEmail advanced parameters, we next display our inlineImages. This parameter allows us to insert our little badger gif inside the HTML instead of as a boring attachment.

inlineImages takes a list of objects like this:

These are then used inside the HTML and can be displayed like this:

For our purposes, we used our badgerBlob.

That’s the email sent.

On our catch(error) on line 49, we return a faulty email if the email is not sent. Otherwise, badEmail back in main() will return a falsy statement of undefined.

openCompleteWindow(badEmailList)

This final function, openCompleteWindow(), takes the badEmailList parameter. On line 9, it called the UI class in order to use the alert popup window.

The alert method can take a number of parameters, but we will only use the message parameter. We will add our text and make it clear in the code separated it by a “+” for each line. We will add a "\n" to each bad email in the bad email list so that forms a string of bad emails that return each line.  To do this we will use the join  method and join by “\n” which is plain text carriage return.

Done!

Conclusion

The focus of this tutorial was to look at sending HTML emails via Google Apps Script. I hope that by being able to demonstrate it in a deployable example that you can get a better feel of how to use, MailApp, UrlFetchApp and HtmlService.

In our next tutorial, we will use our current project and adapt it to run daily on time triggers so that we don’t have to check in and click Send reminders menu button each day.

Stay tuned for the next tutorial. I will add a link to this page once it is up.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi