Google Apps Script: Create Your Own Mail Merge

Google Apps Script: Create Your Own Mail Merge

Mail Merge

I use peer evaluation quite a bit in my class. My data is collected via a Google Form and are in a spreadsheet.  I use mail merge to send students back the comments of the peer evaluations. While there are pre-made Add-On’s to send out emails, there can be an email limit. This tutorial will show you how to make a basic mail merge from Google Sheets. You will have wanted to do some other Google Apps Script tutorials before this one.

Google Form

In order to use a mail merge, you will need to have the email address of who you want to send the responses to. One of the columns in the response spreadsheet needs to be email address. Each row of data will need an email address to send the information to. You can run a mail merge from data not collected from a Google Form.

Count the Columns

The script will be written in the spreadsheet with the Form responses. In the Apps Script you will use getRange(row, column) for the data. The column is not the letter, but rather the count of the column. Column C is the 3rd column. Take note of what column number each column header represents. Note that counting starts at 1. The first column has a reference number of 1.

Script Editor

In the spreadsheet, use the Tools menu to choose “Script editor.” Within the myFunction function, write the script.

var ss

When writing a script that is attached to a spreadsheet your first line of code is almost always

var ss = SpreadsheetApp.getActiveSpreadsheet();

This allows you to use the variable ss to signal that your code is applying to the active spreadsheet.

var sheet

When writing Google Apps Script for spreadsheets you want to be aware of the different levels in the spreadsheet. There are global changes to the spreadsheet, changes to a particular sheet, and changes to a particular range. The variable ss refers to the spreadsheet as a whole. You will want to define the specific sheet within the spreadsheet you are working on.

Options

There are 3 ways that I typically define a particular spreadsheet. I either write the code to execute on the sheet I am looking at. This is helpful when I want to run a repetitive task multiple times within the same spreadsheet. The method getSheets() is an array of all the sheets in the spreadsheet. Placing a bracket with a zero next to the method defines the first sheet. Unlike the columns, sheets start counting at zero. The method getSheetByName allows you to reference a sheet by its name. This is helpful since it references a particular sheet rather than the location of a sheet.

Choose one of these options to define the sheet level.

var sheet = ss.getActiveSheet();
var sheet = ss.getSheets()[0];
var sheet = ss.getSheetByName(‘Form Responses 1’);

Last Row

If you are doing a mail merge off of a spreadsheet you will want to start at row 2 (skip the header row) and go until the last row that has data. Use the variable sheet and the method getLastRow() to find the last row of data on that sheet.

var last = sheet.getLastRow();

For Loop

A for loop repeats a set of code until the criteria is complete. For a mail merge, you want to repeat a set of code for each line in the spreadsheet.

for(var i=2;i<last+1;i++){
}

In the for loop, define a variable. In this case, I used the variable i. Since my first row of data is in row 2, I started my variable value at 2. A for loop has 3 inputs. Separate the inputs with a semicolon. The second value in the for loop tells the loop when to stop looping. When the variable i has a value less than one more than the last row, stop running the loop. The third value tells the loop to increment the variable i by one each time. i++ is a shortcut for i = i + 1.

Define Variables

For each column that you wish to include in your mail merge, define a variable for that range. You will want to define these variables inside the curly braces of the for loop. Type var to define a variable. Choose a single word to describe the value in the column you are referencing. Start with the variable sheet and the method getRange(row, column). In the range, you list the row number and the column number. For a mail merge, the row changes each time. You start with row 2 and then go to row 3, etc.. When defining the row number use the variable i. Couple this with the method getValue() to obtain the value of what is in the cell defined by the range.

These are some of the variables I used on my peer evaluation. Use your own variable names and make sure the column number matches the value you are defining.

var group = sheet.getRange(i,1).getValue();
var email= sheet.getRange(i,2).getValue();
var members = sheet.getRange(i,3).getValue();
var blank = sheet.getRange(i,4).getValue();
var one = sheet.getRange(i,5).getValue();
var two = sheet.getRange(i,6).getValue();
var three = sheet.getRange(i,7).getValue();

Message

For the mail merge, you want to send a message that incorporates the values from the spreadsheet rows. Define a variable for the message. Define this message within the for loop.

var message =

Text Strings

All text strings need to be within single quotations.

Plus

Concatenate (put together) text strings with the variables you defined with a plus sign. Be aware of spaces in your text strings. ‘Dear’+group will result in DearGroup 1 instead of Dear Group 1. Include a space after the word dear or include the text string +’ ‘+ which adds in a space. (It may not be obvious, there is a space between the single quotes.)

var message = ‘Dear ‘ + group + ‘, here is your peer evaluation information.’

Use \n to Make a Line Break

Include forward slash n, \n, in your text strings to insert a line break. You do not need to have a space after the \n and the next letter in the text string. Do not use the enter key, your message  will continue to the right on the script editor page. You can use \n\n to have two returns to create space between the lines.

var message = ‘Dear ‘ + group + ‘, here is your peer evaluation information. \nYour group project was evaluated at a level 1 ‘ + one + ‘ times. \nYour group project was evaluated at a level 2 ‘+ two + ‘times.

Send Email

Within the for loop use the method sendEmail(email, subject, body) to send an email for each row. For each of the 3 inputs for the sendEmail method, you should have a defined variable to reference.

GmailApp

Sending an email calls the GmailApp not the SpreadsheetApp. Use the sendEmail() method with GmailApp. The column that contains the email address, type that variable into the first spot of the sendEmail method. For the second input of subject line, type a text string in single quotations. You can concatenate a text string with some of your defined variables. I like to refer specifically to the person or group I am sending the email to. This makes the mail merge seem more personal. The third input should be your message variable.

 GmailApp.sendEmail(email, group+’ Summary of VIDEO PROJECT peer evaluation’, message);

Save and Run

Click on the save disk in the toolbar. Name the script whatever you like. Click on the run icon to send the mail merge. Tip: Double check the email addresses in the spreadsheet before sending the emails. The script will stop if it encounters a bad email address.
Save and Run

Sample Script

Here is the code I used to email back feedback. I created a Pivot Table to summarize the peer evaluation data. I used that information to create the mail merge instead of using the form responses sheet.

function mailSummary() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(‘groupsummary’);
var last = sheet.getLastRow();
for(var i=2;i<last+1;i++){
var group = sheet.getRange(i,1).getValue();
var email= sheet.getRange(i,2).getValue();
var members = sheet.getRange(i,3).getValue();
var blank = sheet.getRange(i,4).getValue();
var one = sheet.getRange(i,5).getValue();
var two = sheet.getRange(i,6).getValue();
var three = sheet.getRange(i,7).getValue();
var four= sheet.getRange(i,8).getValue();
var five = sheet.getRange(i,9).getValue();
var total = sheet.getRange(i,10).getValue();

var message = ‘Dear ‘+members+’ ‘+’\nYour peers evaluated your video project.\nPlease check that all of the group members received this email. If not, please forward.\n \nEach student was asked to provide peer evaluation on each topic area.\nYou received feedback from ‘+total+’ people. \nYou received ‘+one+’ ratings of 1 for quality of your project.\nYou received ‘+two+’ ratings of 2 for quality of your project.\nYou received ‘+three+’ ratings of 3 for quality of your project.\nYou received ‘+four+’ ratings of 4 for quality of your project.\nYou received ‘+five+’ ratings of 5 for quality of your project.\n’;

GmailApp.sendEmail(email, group+’ Summary of VIDEO PROJECT peer evaluation’, message);
}
}

More Sample Script

Separate from the summary of peer evaluation data, I sent the groups each of the free response comments made on their project. This generates an email to each group for each peer evaluation.

function mailFeedback() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(‘comments’);
var last = sheet.getLastRow();
for(var i=2;i<last+1;i++){
var group = sheet.getRange(i,1).getValue();
var members= sheet.getRange(i,2).getValue();
var email = sheet.getRange(i,3).getValue();
var quality = sheet.getRange(i,4).getValue();
var well = sheet.getRange(i,7).getValue();
var improve = sheet.getRange(i,8).getValue();
var learned= sheet.getRange(i,9).getValue();
var additional = sheet.getRange(i,11).getValue();

var message = ‘Dear ‘+members+’ \nYour video group project was: ‘+group+’ \nYou will receive several emails with the feedback from your peers.\nPlease check that this email was sent to all of your group members. If not, please forward to group members.\n\n \nWhat you did well: ‘+well+’\n \nWhat could have been improved: ‘+improve+’ \nWhat the peer reviewer learned: ‘+learned+’ \nThe peer reviewer may have provided additional feedback: ‘+additional;

GmailApp.sendEmail(email, group+’ Your Peer Eval Comments ‘, message);
}
}

© 2024 All Rights Reserved.