Hugo Fierro, Google Apps Script team
Ikai Lan, Google Apps Script team
Originally published March 2009, updated December 2012
Goal
This tutorial shows an easy way to collect information from different users in a spreadsheet using Google Forms, then leverage it to generate and distribute personalized emails.
Time to Complete
Approximately 15 minutes
Prerequisites
Before you begin this tutorial, you should already be familiar with:
Sections
This tutorial is divided into the following sections:
- Section 1: Preparing a spreadsheet and running the example
- Section 2: Understanding the code
- Section 3: Exercise: modifying the form and the personalized email
- Section 4: Full code
- Summary
Section 1: Preparing a spreadsheet and running the example
New editor
- Open our Spreadsheet template for this tutorial.
Create a personal copy of the spreadsheet by clicking on 'Make a Copy'. You can now edit the spreadsheet and start writing scripts.
Submit data to the spreadsheet by clicking on the 'Form' menu, then 'Go to live form'. Make sure you enter your own email address.
If you go back to the spreadsheet, you should see a new row with the data you entered in the form
Select the menu item Tools > Script editor.
In the script editor, from the function dropdown list, select sendEmails and click Run. After you authorize the script, as many emails as rows in the spreadsheet are sent.
Finally, check your email. It may take a few seconds for the message to be delivered, so you may want to refresh.
Legacy editor
- Open our Spreadsheet template for this tutorial.
Create a personal copy of the spreadsheet by clicking on 'Make a Copy'. You can now edit the spreadsheet and start writing scripts.
Submit data to the spreadsheet by clicking on the 'Form' menu, then 'Go to live form'. Make sure you enter your own email address.
If you go back to the spreadsheet, you should see a new row with the data you entered in the form
Select the menu item Tools > Script editor.
In the script editor, select the menu item Run > sendEmails. After you authorize the script, as many emails as rows in the spreadsheet will be sent.
Finally, check your email. It may take a few seconds for the message to be delivered, so you may want to refresh.
Continue reading Section 2 to learn more about how this example's script works.
Section 2: Understanding the code
In this section, we will explain relevant parts of the script. The full code is available in Section 5.
Function
sendEmails
The code above gets a range that contains all the data in the spreadsheet. Note that unbounded ranges are not supported so we compute the range using the actual total number of rows in the Sheet.
The code above retrieves the string template that will be used to generate personalized emails. It is defined in the "A1" cell in the second sheet called 'Email Template'.
Note the four template markers, like
${"First Name"}
. They correspond to column names in the data Sheet and specify where the data from a given row should be displayed.Reads all the data in a given range of spreadsheet cells.
Given the template string described above, prepares the text of an email that contains the information submitted by a user using a form.
Sends the actual email.
Function
fillInTemplateFromObject
The JavaScript function
match
find instances of a given pattern of text (See Regular Expressions).Tries to retrieve the value in a data object corresponding to a marker. This is done by normalizing the marker name and checking if the data object has a value associated with the normalized name.
Replaces a marker in the email template string with a value or simply remove the marker if no value for it has been found.
Section 3: Exercise: modifying the form and the personalized email
At this point you should be able to modify the form and personalized emails.
- Click on the 'Form' menu, then 'Edit Form' and add a new question to retrieve the name of the department.
- Open the live form and submit some data (once again, make sure you put your own email address to be able to see the results).
- Notice how a new column has been created in the Spreadsheet that corresponds to the new question in the Form.
- Now, modify the template email (cell "A1" in the second sheet) and add a marker to display the department name somewhere in the email. Remember that the marker name has to match the column name in the data Sheet.
Open the Script Editor. Update the variable
dataRange
in thesendEmails
function to read the new department column:var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 5);
Run the function
sendEmails
and check your email.Check that the department names appear in the email. If it does not, verify the marker name in the template string.
Section 4: Full code
Summary
Congratulations, you have completed this tutorial and have created your own simple Mail Merge application leveraging Google Forms. It should allow you to create new Forms and send personalized emails without having to modify any code. Feel free to reuse the script and adjust it to your needs!