Author: Matt Brigidi | Published: March 7, 2025
You can automate emails using Google’s 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 that comes from a Google Form. An email will be sent each time a new submission is made to the form.
Create a project folder in Google Drive.
Once inside the project folder, create a Google Form.
After you complete your form, go to the “Responses” menu item at the top of the page. There is an option to “Link to Sheets”, where you can create a Google Sheet that is linked to the form and will automatically log the responses.
Alternatively, you can use an existing Google Sheet instead of creating a new one.
We will be using a bound Apps Script project for this tutorial, which means that we will be creating a scripting project 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 name 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.
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 workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
}
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. If you’ve seen the related video to this post, then you will notice that I created a custom function to get the sheet ID.
Fortunately, the development team that maintains Apps Script built in a native function, so that we no longer have to. Your code will now look like this:
function main() {
// access the workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
}
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 with the form responses (aka Sheet ID zero). 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 “formResponses”; 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 formResponses = 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 formResponses = 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 formResponses = 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 formResponses = 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.
You code should look something like this:
function main() {
// access the workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
}
We’ll also want to programmatically extract the questions being asked in the form. Those values are available in the first row of the sheet.
We’re going to use a nearly identical method as the formResponses variable. The only difference is we want to get the first row instead of the last row:
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
Your code should now look like this:
function main() {
// access the workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
}
Google Forms has an option to include the email of the person who submitted the response, which is populating the last column.
We’re going to declare a variable called “emailRecipient” that will reference the array that is created by the formResponses variable:
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
Your full code will now look like this:
function main() {
// access the workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
}
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 workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
// create a 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 “Responses” tab is the question How many videos do you watch in a week?. We could use the following to create a variable called “questionTwo” that can be used in our HTML file:
htmlTemplate.answerTwo = formResponses[0][1];
We could also save the data extracts as variables. I use this method when I want to use variables in other places in my script.
That would look like this:
// email recipient
var emailRecipient = formResponses[0][4];
// connect to html template
htmlTemplate.email = emailRecipient;
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, 3, 4, and 5. 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 “response” example. You can use any names you want but if you want to follow along with me, then you can reference my code below (remember we need to do this for the questions and the answers):
function main() {
// access the workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
// create a template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define html variables for questions
htmlTemplate.questionOne = formQuestions[0][1];
htmlTemplate.questionTwo = formQuestions[0][2];
htmlTemplate.questionThree = formQuestions[0][3];
// define html variables for answers
htmlTemplate.answerOne = emailRecipient[0][1];
htmlTemplate.answerTwo = emailRecipient[0][2];
htmlTemplate.answerThree = emailRecipient[0][3];
}
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 workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
// create a template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define html variables for questions
htmlTemplate.questionOne = formQuestions[0][1];
htmlTemplate.questionTwo = formQuestions[0][2];
htmlTemplate.questionThree = formQuestions[0][3];
// define html variables for answers
htmlTemplate.answerOne = emailRecipient[0][1];
htmlTemplate.answerTwo = emailRecipient[0][2];
htmlTemplate.answerThree = emailRecipient[0][3];
// evaluates the template and return html output
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 uses 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(emailRecipient,
'Feedback from your YouTube Channel',
'',
{htmlBody: htmlForEmail})
The first input is the variable that references our email variable. 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 workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
// create a template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define html variables for questions
htmlTemplate.questionOne = formQuestions[0][1];
htmlTemplate.questionTwo = formQuestions[0][2];
htmlTemplate.questionThree = formQuestions[0][3];
// define html variables for answers
htmlTemplate.answerOne = emailRecipient[0][1];
htmlTemplate.answerTwo = emailRecipient[0][2];
htmlTemplate.answerThree = emailRecipient[0][3];
// evaluates the template and return html output
var htmlForEmail = htmlTemplate.evaluate().getContent();
// send the email
GmailApp.sendEmail(emailRecipient,
'Feedback from your YouTube Channel',
'',
{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 workbook
var wb = SpreadsheetApp.getActiveSpreadsheet();
// access the responses tab
var sheet = wb.getSheetById(SHEET_ID_GOES_HERE);
// collect the form responses
var formResponses = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formResponses);
// collect the form questions
var formQuestions = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
// logging for transparency
Logger.log(formQuestions);
// email recipient
var emailRecipient = formResponses[0][4];
// logging for transparency
Logger.log(emailRecipient);
// create a template object for dynamically constructing html
var htmlTemplate = HtmlService.createTemplateFromFile('email');
// define html variables for questions
htmlTemplate.questionOne = formQuestions[0][1];
htmlTemplate.questionTwo = formQuestions[0][2];
htmlTemplate.questionThree = formQuestions[0][3];
// define html variables for answers
htmlTemplate.answerOne = emailRecipient[0][1];
htmlTemplate.answerTwo = emailRecipient[0][2];
htmlTemplate.answerThree = emailRecipient[0][3];
// evaluates the template and return html output
var htmlForEmail = htmlTemplate.evaluate().getContent();
// send the email
GmailApp.sendEmail(emailRecipient,
'Feedback from your YouTube Channel',
'',
{htmlBody: htmlForEmail})
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<p>Hello, I hope this email finds you well — </p>
<p>Enclosed is feedback from a recent survery you took. You can find your answers to the questions below.</p>
<p><?= questionOne?></p>
<p><?= answerOne ?></p>
<p><?= questionTwo?></p>
<p><?= answerTwo ?></p>
<p><?= questionThree?></p>
<p><?= answerThree ?></p>
<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.