Templating and Sending Emails From Google Sheets
A project that I was recently given at my job involved going through a Google spreadsheet containing a list of pending orders and sending emails to customers whose orders met certain criteria. There were about 3000 orders on the spreadsheet and maybe around 1500 of those orders met the criteria to have an email sent to them. The instructions were to manually go through and send each email, and working as part of a team of three I’m sure that we could have knocked out those emails within about a week or so, but a task like this is a great use case for Google Apps Script.
The company describes the program as follows: “Google Apps Script is a scripting platform developed by Google for light-weight application development in the Google Workspace platform.” Rather than having to go through and manually send each of these emails, we’re able to write up an email template, and then write a script and using Javascript that will go through our spreadsheet row by row to send out a templated email to each desired recipient. There are plenty of resources available on getting started with Google Apps Script so I’ll go ahead and jump straight into the code.
Actually before writing any code, there are two important things needed: a sheet with the data on the orders that I was going to parse through, and a template email message. I would recommend saving this templated message on a separate page in your spreadsheet in cell A1, but really this message could be saved anywhere on the spreadsheet. The example included later will assume the message is saved in cell A1 on a separate page of the spreadsheet. Below I’ve included template that is similar to the one that I ended up using. The words surrounded by percent signs are what I am going to dynamically populate based on each order (hint: I will be using the replace function). There are other ways to write templates, including writing HTML within the actual script, but for my purposes, the option of saving the template directly on the spreadsheet is sufficient.
Hello,
I am writing from ___ regarding your order %ORDERNUMBER% that you placed on
%DATE% for %ITEM%. At this time your order has not been fulfilled. Please
let me know if you still want for the order to be fulfilled, or if you
would prefer a refund.
Sincerely,
JacobNow that I had my template written, and my data ready to go, I can move into the code. To get this all done I wrote a total of three functions: sendEmail, prepareMessage, and sendEmailToList. The first two, sendEmail and prepareMessage, will both be called during the running of sendEmailToList. The former, sendEmail is the easiest.
function sendEmail(to, subject, body){
MailApp.sendEmail(to, subject,body)
}The MailApp.sendEmail function takes three (or more) arguments: the recipient of the message, the subject of the email, and the body. It is also possible to include a fourth argument, titled options, if it’s needed to CC/BCC anyone on the email, as well as include file attachments and a few other miscellaneous functions. I was interested in the bcc option as I thought that it might be helpful with error handling, and seemed like a good way to be able to make sure that emails were going to the correct people, however doing so would count against my daily quota limits for sending emails — more on that later.
The next function that I needed is titled prepareMessage. As shown in the template earlier, I need to have three unique values with each email, so this function will take each of those three values as arguments.
function prepareMessage(orderNumber, orderDate, requestedItem){
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("MessageTemplate")
const oldSheet = SpreadsheetApp.getActiveSheet()
SpreadsheetApp.setActiveSheet(sheet)
const dataRange = sheet.getRange(1,1,1,1)
const data = dataRange.getValues()
let message = data[0][0]
message = message.replace("%ORDERNUMBER%", orderNumber)
message = message.replace("%DATE%", orderDate)
message = message.replace("%ITEM%", requestedItem)
SpreadsheetApp.setActiveSheet(oldSheet)
return message
}
There’s a few things going on here. First, I am grabbing the specific sheet that has the message template on it. At the same time I am also saving to a variable “oldSheet” the sheet that contains the data — this will hopefully become more clear with the third and final function. Once I have the two sheets saved to variables, I set the message template sheet as the active sheet. From there, the next few lines are where I obtain that message string, where finally I get the actual message from the spreadsheet with the line “let message = data[0][0]”. I have a feeling there may be a better way to do this — I’d love to hear any suggestions. The next stage of the function is hopefully pretty clear, but this then is where I find each of the placeholders that were written into the template and replace them with the information that is specific to each order. It’s then important to reset the active sheet back to the original sheet and of course return the message. I’ve found the concept of switching between active sheets to be unintuitive as well as source of errors — take extra care with this.
The final, and largest part of the project is to write the function that will actually go through and look at each row/order, and send an email if necessary. I did not mention this earlier, but I also want to add a column to each relevant order with some sort of notation indicating that the email was indeed sent.
function sendEmailToList(){
//here is where I define what sheet I will be working with
//in this example the name of the page/sheet that I want is titled DummyData
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DummyData")
//I need to know how many rows are in this sheet in order to
//grab the correct number of rows
const rows = sheet.getLastRow()
//number of relevant columns for my use-case is 30
//getRange takes start row, start column, end row, end column
//this will be 0-indexed so subtract 1 from number of rows
const dataRange = sheet.getRange(2,1,rows-1,30)
const data = dataRange.getValues()
//data will be an array containing the information from each row
//each row will be stored as an array
//each column value will be indexed as an element of the row array
for (let i = 0; i < data.length; i++){
//I need to filter out empty lines as some empty rows are present
//in my sheet. I am primarily concerned with order type
//which is found at index 0 of the order array
//I will filter out corporate orders,
//filter out legal orders.
//Some orders have already had outreach. If there was outreach then
//column 28 gets populated, so I
//filter out orders for which contact was already made by making sure
//that column 28 is empty
if (
//data[i] represents a single row/order
//
data[i][0] &&
data[i][0] !== "corporate" &&
data[i][0] !== "legal" &&
data[i][28] === ''
)
{
//here is where I grab the values that I need for each email
//data[i][desiredColumnNumber] will get what I need
const emailAddress = data[i][18]
const orderNumber = data[i][1]
const orderDate = data[i][23].toLocaleDateString()
const requestedItems = data[i][26]
try {
sendEmail(
emailAddress,
//I also include the order number in the subject of each email
`Your Philadelphia School District Records Request - ${orderNumber}`,
//I call the prepareMessage function passing in the specific
//values for reach row as parameters
prepareMessage(orderNumber, orderDate, requestedItems)
)
} catch (e) {
//If I have any errors when sending the email I want to
//log the error message as well as the order number/email
//of the last processed row. I also want to stop executing
//this script if any errors do come up
Logger.log(e)
Logger.log(emailAddress)
Logger.log(orderNumber)
return
}
//Here is where I go to the end columns of each row and update the
//values of the last two columns. i+2 is important.
//+1 because i in the for-loop is going through an array that
//is 0-indexed, and +1 again since the data that I initially
//grabbed actually starts in the second column
sheet.getRange(i + 2, data[i].length - 1).setValue("No")
sheet.getRange(i + 2, data[i].length).setValue("Auto emailed student")
}
}
}