Author: Matt Brigidi | Published: Nov. 25, 2024
You can automate emails using Google Sheets and its native programming language Google Apps Script.
Apps Script uses Google’s version of the JavaScript programming language. It allows users to augment the native functionality of Google Drive’s productivity applications, such as Documents, Slides, and Gmail.
This tutorial will focus on the fundamentals of sending an automated HTML email using data from a Google Sheet, which will populate a weekly email. You can use your own Google Sheet if you already have one; or you can follow along with the example by making a copy of my data sheet.
The completed project code files can be found at the bottom of the lesson; everything before then explains each step of the process.
We will be using a bound Apps Script project for this tutorial, which means it was created from the Google Sheet.
You can create the project by navigating to the ‘Extensions’ section in the Google Sheets menu and then selecting the Apps Script option.
You can also use an unbound Apps Script project — which means that it is a standalone project not directly associated with the Google Sheet.
You will have an “Untitled” Apps Script project.
Begin by naming the project something that will be recognizable. Google will send you diagnostic reports if anything ever breaks down with your application. By giving the project a distinct name, you will more easily reacclimate to the project in the event that you have to do additional development work or manage bugs.
Try to think of your future self anytime you are programming. Providing that version of you every bit of context will be a huge help if you have to revisit something you did days, weeks, months, or years prior!
New Google Apps Script projects will always open with a script file called “Code.gs”. It will have boilerplate code written for a function called “myFunction”. You can use that function if you would like to. I generally like to start from scratch because I like to maximize the amount of time I spend on my keyboard and minimize the amount of clicks I need with a mouse.
Apps Script requires a function to execute a script file, so you’ll need to create a function if you want to get output in your console and/or action from your code.
You can name your function whatever you like, however, you’ll want to give it a distinct name. If your project has more than one function and they share a name, then Apps Script won’t know which function you want to run.
For this tutorial, I decided to call my function “main” and stored it in the “Code.gs” file.
function main() {
}
Navigate to the “Files” portion of the navigation bar and select the “+” icon.
You will be prompted with two options:
We’ll want to create an HTML file, so that we can design the structure of our email, so that it has basic formatting, line breaks, and linked text.
Name your HTML file whatever you’d like; I’m following along with my YouTube tutorial , so I’ll be calling my HTML file “email”.
We’ll begin coding by using the SpreadsheetApp class, which will provide us with a range of methods and properties to interact with a Google Sheet.
You can access the spreadsheet in one of three ways:
We’ll be using the getActiveSpreadsheet method since we’re using a bound Apps Script project.
We’re going to create a variable, so that we can reference the spreadsheet connection throughout the script. We’ll use the acronym of “wb” to represent the “workbook”.
Your code will look like this:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
}
You will be able to access any sheet in the workbook using the native “getSheetByName” method.
This method is very common in tutorials but it is vulnerable to a significant flaw: if the name of the sheet changes, then the method will return an error.
The optimal way to reference something is by using an ID, because IDs do not change.
Historically speaking, Apps Script did not have a native method for accessing sheets by their ID, which meant that you would have to build your own function.
However, there was a post on Reddit by Justin Poehnelt, a Developer Relations Engineer at Google, that brought to my attention that getSheetById had been added to Apps Script. This is a wonderful development!
While it hasn’t been added to the official documentation yet, accessing sheets using their ID is the most stable method. That means you have two options:
I have not had a chance to use the Apps Script version of getSheetById yet; and given that it has not yet been added to the documentation, I don’t want this tutorial to include it only for it to be removed (for whatever reason).
So, for the sake of consistency with my original lesson, we will use getSheetByName because that’s what the original tutorial used. We will use the method with our “wb” variable and create a new variable called “sheet”.
Your code will now look like this:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
}
The getRange method will allow you to access the rows and columns within a Google Sheet tab. The method has four arguments; two mandatory arguments and two optional arguments.
All arguments must be formatted as a number.
The Mandatory arguments are:
The optional arguments are:
The getRange method has a numerical index that starts at 1, which means the first value is represented by the number 1.
Apps Script has four native methods that make using getRange really powerful:
While they sound similar, the most significant difference is whether null values are included or excluded.
These methods are crucial in automating the extraction of data from Google Sheets, because they provide dynamic calculations that can respond to the expansion and/or contraction of the dataset.
After you define your data range — which we will be doing shortly — you’ll need to use an additional method to get the values from the range (aka your data).
There are a few different methods to extract data values:
I recommend using either getValues or getDisplayValues, because both methods support single and multiple results; while getValue and getDisplayValue only return a single result.
It’s important to identify what you want to do with your data once you extract it:
I typically use getDisplayValues because I try to do as much of my data transformation before it gets to the Apps Script portion of my workflow. Therefore, I don’t need additional math operations before I can use my data (in this case, in an email).
If you do want to continue transforming your data, then you’ll need to use getValues. The reason is because getDisplayValues extracts all data as a string, which means numbers will become string objects and can no longer have math operations applied to them.
Essentially, the numbers appear as numbers to your eyes but the computer processes them as if they were a letter.
The getRange method will identify the data range and the getDisplayValues method will extract the values.
In this scenario, we want to extract the most recent line of data, meaning, the last row with data in the Google Sheets tab called “Summary”. Therefore, we need to dynamically identify the last row with data, which is why we’ll be using the getLastRow method.
We’ll resume coding by creating a variable, so that we can reference our data extraction later in the script. I chose to name my variable “data”; you can name your variable whatever you want.
The first argument of the getRange method is the row to start at, so, we will be using the sheet variable with the getLastRow method to identify the last row with data.
// first argument example
var data = sheet.getRange(sheet.getLastRow())
The second argument of the getRange method is the column to start at. This is a fixed value because we want to start at the first column in the data range. Therefore, we can hardcode a value of 1.
// second argument example
var data = sheet.getRange(sheet.getLastRow(), 1)
The third argument of the getRange method is the number of rows we want to include. Even though this is an optional argument, we need to enter a value here because we want to extract more than a single value of data. This is also a fixed value because we only want one row of data (the last row in the sheet with data, to be precise).
// third argument example
var data = sheet.getRange(sheet.getLastRow(), 1, 1)
The final argument of the getRange method is the number of columns to include. This could be a fixed value but my preference is to use a dynamic reference, so that the script is at least collecting the values (even if it doesn’t use them). Therefore, I will be using the getLastColumn method to identify the last column with data (you can hardcode the value if you so choose).
// fourth argument example
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn())
After completing the input values for getRange, you will use the getDisplayValues method to extract the values from the sheet.
Additionally, we will be adding a logging statement, so that we have some feedback in our console to ensure that we have successfully extracted the values we want. If you do not see the data range you want, then go back to the start of this section and make sure you've correctly followed the instructions.
You code should look something like this:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(data);
}
We need to establish a connection between the script file and the HTML file.
We’ll do this by declaring a variable called htmlTemplate, which will reference a method from the HtmlService class.
The HtmlService class has a method called createTemplateFromFile that allows us to reference an HTML file. The createTemplateFromFile method requires the input value to be structured as a sting, so we will pass the name of the HTML file — in my case, the file name is “email” — surrounded by quotation marks.
Your code will now look like this:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// create template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
}
We’ll use the “htmlTemplate” variable to define data points, so that we can access them within our HTML file.
The process for doing this is to reference the “htmlTemplate” variable then add a dot and a variable name. Then, we’ll reference the “data” variable and access each data point that we want to use in our email.
For example, the second value in our “Summary” tab is a person’s name. We could use the following to create a variable called “name” that can be used in our HTML file:
Additionally, we could also save the data extracts as variables. That’s what I did in my script, because I wanted to use those variables in other places in my script.
That would look like this:
htmlTemplate.name = data[0][1]
Since we used the getDisplayValues method to extract the values from the Google Sheet, the data variable is structured as a two-dimensional array. In order to get a value from a two-dimensional array, we need to define:
Arrays default to an index that starts at 0, which means the first value is represented by 0. Therefore, if we want to access the first array, we use 0. If we want to access the second element in the array, then we use the number 1.
For this project, I want to use four data points that exist in the Google Sheets tab at Columns 2, 4, 5, and 6. Remember: Google Sheets have an index that starts at 1. After we extract the data using the Apps Script getDisplayValues method, the values are structured as two-dimensional arrays, which have a 0 index.
That means we need to translate our Columns into the correct reference numbers:
| Letter | Number | Index: 0 | Index: 1 |
|---|---|---|---|
| A | 1 | 0 | 1 |
| B | 2 | 1 | 2 |
| C | 3 | 2 | 3 |
| D | 4 | 3 | 4 |
| E | 5 | 4 | 5 |
| F | 6 | 5 | 6 |
So, each of our “html variables” will need to have a name, as well as reference where they exist in the data variable just as we did with the “name” example. You can use any names you want but if you want to follow along with me, then you can reference my code below:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// create template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define data points
var name = data[0][1];
var inventory = data[0][3];
var grossRev = data[0][4];
var netRev = data[0][5];
// define html variables
htmlTemplate.name = name;
htmlTemplate.inventory = inventory;
htmlTemplate.grossRev = grossRev;
htmlTemplate.netRev = netRev;
}
Google Apps Script allows for up to 50 accounts to receive an email.
You need to structure the mailing list as an array with each address separated by a comma. You can either hardcode the list at the end of your script or you can create a variable with email addresses.
I prefer to create a mailing list variable, because I think it’s easier to maintain when hardcoding the addresses directly into the editor. You can find an example of my code below:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// create template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define data points
var name = data[0][1];
var inventory = data[0][3];
var grossRev = data[0][4];
var netRev = data[0][5];
// define html variables
htmlTemplate.name = name;
htmlTemplate.inventory = inventory;
htmlTemplate.grossRev = grossRev;
htmlTemplate.netRev = netRev;
// mailing list
var listPpl = ['your_email@email.com', 'another_email@email.com'];
}
Essentially, we need to create a connection from our HTML file back to our Script file, so that we can use the Script file to send the HTML as the body of the email.
We can accomplish this by using the htmlTemplate variable we created earlier.
We’ll create a new variable called “htmlForEmail” that is set equal to the htmlTemplate variable. We’ll use two additional methods — evaluate and getContent — in order to get the contents of the HTML file.
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// create template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define data points
var name = data[0][1];
var inventory = data[0][3];
var grossRev = data[0][4];
var netRev = data[0][5];
// define html variables
htmlTemplate.name = name;
htmlTemplate.inventory = inventory;
htmlTemplate.grossRev = grossRev;
htmlTemplate.netRev = netRev;
// mailing list
var listPpl = ['your_email@email.com', 'another_email@email.com'];
// evaluates the template and returns an htmploutput object
var htmlForEmail = htmlTemplate.evaluate().getContent();
}
The GmailApp is a class just like the SpreadsheetApp, which is what we used to extract data from the Google Sheet.
GmailApp has a method called “sendEmail”, which is the method I recommend when attempting to automate emails. Now, I use Gmail and typically send emails to other people who use Gmail (I work for a company that used Google’s productivity suite).
If you have issues with using the GmailApp, there is also a mailApp class that has much of the same functionality.
The sendEmail method has four inputs:
Since we are sending an HTML email using an HTML file, we’re going to need to use the options portion of the method. Your code will end up looking like this:
// send the email
GmailApp.sendEmail(
listPpl,
'Status Report for ' + name,
'This email contains html',
{htmlBody: htmlForEmail}
);
The first input is the variable that references our email array. You can also hardcode the email(s) if you would like.
The second input is the subject line. In my example, I am using the name variable with some boilerplate text. This way, the recipient has a personalized subject.
The third input is usually the body of the message. However, since we are using an HTML file, we will use this section to add a message if the HTML does not display for some reason.
The fourth input is where we can set additional options. In this case, we will use the htmlBody options to reference the “htmlForBody” variable, which is using the getContent method from the HtmlService to get our HTML file’s content.
The code for all that looks like this:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// create template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define data points
var name = data[0][1];
var inventory = data[0][3];
var grossRev = data[0][4];
var netRev = data[0][5];
// define html variables
htmlTemplate.name = name;
htmlTemplate.inventory = inventory;
htmlTemplate.grossRev = grossRev;
htmlTemplate.netRev = netRev;
// mailing list
var listPpl = ['your_email@email.com', 'another_email@email.com'];
// evaluates the template and returns an htmploutput object
var htmlForEmail = htmlTemplate.evaluate().getContent();
// send the email
GmailApp.sendEmail(
listPpl,
'Status Report for ' + name,
'This email contains html',
{htmlBody: htmlForEmail}
);
}
When you created your HTML file, Apps Script auto-populated boilerplate HTML code.
We’ll be focusing on writing a few paragraphs, so we’ll be working inside the body tags. You don’t need to know much HTML, but I think understanding the basics is a good idea. I will link my code after the next section, so that you can copy it into your project.
The concepts I’m using include:
HTML is pretty straightforward, so if you’re exploring Apps Script, then I’d recommend looking into it via YouTube tutorials or intro resources like those provided by W3 Schools.
You can reference your variables by using the following format:
/* this should be the name from your Code.gs file */
<?= variable_from_code_gs ?>
This will allow you to dynamically populate values into your HTML from your Google Sheet.
That’s it! You should be able to send an email using the most recently added data from your Google Sheets workbook tab!
Here is the full code:
function main() {
// access the Google Sheet
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the sheet with the data
var sheet = wb.getSheetByName('Summary');
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// create template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define data points
var name = data[0][1];
var inventory = data[0][3];
var grossRev = data[0][4];
var netRev = data[0][5];
// define html variables
htmlTemplate.name = name;
htmlTemplate.inventory = inventory;
htmlTemplate.grossRev = grossRev;
htmlTemplate.netRev = netRev;
// mailing list
var listPpl = ['your_email@email.com', 'another_email@email.com'];
// evaluates the template and returns an htmploutput object
var htmlForEmail = htmlTemplate.evaluate().getContent();
// send the email
GmailApp.sendEmail(
listPpl,
'Status Report for ' + name,
'This email contains html',
{htmlBody: htmlForEmail}
);
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<p>Hello, I hope this email finds you well — </p>
<p>Enclosed is a status report about <?= name ?>. The following is how the are currently progressing towards their month end targets:</p>
<ul>
<li>Inventory: <?= inventory ?></li>
<li>Gross Revenue: <?= grossRev ?></li>
<li>Net Revenue: <?= netRev ?></li>
</ul>
<p>Please reach out in the event of an error. This is an automated email, so please excuse any delays in response as I might not presently be at a computer.</p>
</body>
</html>
I hope you found this tutorial helpful. Please check out the Technology Tutorial section of this site for more lessons or check out my YouTube channel for all my videos.