Automating Email with Google Apps Scripts
Email automation is a way to create custom emails that reach the right people with the right message at the right moment—without doing the work every time.
When building this tool a primary objective was to keep maintenance as Lo-fi as possible, so non-engineers could update as they need. —
In this journal entry on Email Automation I’ll be covering:
- Google Gmail, Drive, & Spreadsheet Services
- Getting a list from a Google Drive Spreadsheet
- Use Script Editor to Create a HTML & Plain Text Email Message
- Add a custom send email menu button to the Spreadsheet menu
- Attach files from Google Drive
Google offers an amazing tool to work with along side their GSuite products and that’s Google Apps Scripts ‘GAS’.
Here’s our situation.
Every year your company attends the ‘big industry conference’. This year you are responsible for sending out a follow-up email to everyone who stops by and wants more information.
After the conference your team brings back a notepad with everyones info. Due to privacy concerns, the leadership team doesn’t want you to just CC everyone on the same email, and BCC can look like a scam or feel unprofessional. The day went great, but only 25 people signed up. So you sit down and send the same templated message with a pdf attached to everyone.
During lunch you’re asked how your morning has been? Everything went alright, you had to resend two emails because, yes, you forgot to attach the pdf. It took a lot longer than you wanted and it was prone to simple human error. You looked into third party options for sending email blasts, but it didn’t make business sense.
A couple months later the next event comes around and its a madhouse. Hundreds of people show up to the booth and sign up on the laptop you suggested the team bring this time. Now customizing pdfs and tailoring emails just got exponentially more time consuming and introduces additional risk! What are you to do? No fear, Automate it! Google Apps Scripts, JavaScript, and HTML to the rescue!
1. Getting a list from a spreadsheet
First, let’s look at how we get our event sign up list from a Spreadsheet into something we can use in our Apps Script.
Once you have contacts populated on a spreadsheet, navigate to the menu, click Tools>Script Editor.
# Example Spreadsheet Data
| Column A | Column B |
- - - | - - - - - - - - - - - | - - - - - |
Row 1 | Email Address | First Name |
- - - | - - - - - - - - - - - | - - - - - |
Row 2 | john.p.romano@gmail.com | Johnny |
From here we have to get a list of recipients and their first name and email Address.
function run(){
// SET sheet id
// Your spreadsheet ID is a unique string at the end of your URL
// https://docs.google.com/spreadsheets/d/ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789/
// var id = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
var id = 'REPLACE-WITH-YOUR-SPREADSHEET-ID';
// GET sheet by id
var ss = SpreadsheetApp.openById(id);
// GET sheet by name 'Sheet1'
var sheet = ss.getSheetByName('Sheet1')
// SET first row of data to process
// Row #1 is the title row
var startRow = 2;
// GET number of rows to process
// Row number offset because of title row
var numRows = ss.getLastRow()-1;
// GET the range of cells A2:B2
// getRange(row, column, numRows, numColumns)
var dataRange = sheet.getRange(startRow, 1, numRows, 2);
// GET values for each row in the Range.
var data = dataRange.getValues();
Log.logger(data)
}
Now we can test our work by inserting a logging statement as our last line and running the script. To show the logging console output, from the menu click View > Show Logs.
OUTPUT
[19-03-11 11:37:33:358 PDT] [[john.p.romano@gmail.com, Johnny]]
2. Creating a HTML & Plain Text Email Message
Gmail supports both plain-text and HTML email. When you compose an email in Gmail is creates these for you, but since we’re scripting we’ll have to handle both of those ourselves. Here is where it’s helpful to have a basic understanding of HTML, but I’ll try and break it down.
To start we’ll put together a hard coded email template. With any email correspondence we need to add common elements like subject, salutation, recipient, our main message, valediction, and sender name.
// Email Subject
var recipientAddress = 'john.p.romano@gmail.com'
var subject = 'It was great to meet you at our conference in Los Angeles!';
// Email Greeting
var salutation = "Dear";
var recipient = "Valued Customer";
// Email Body
var msg = 'Hello World, and thank you!';
// Email Signature
var valediction = 'Respectfully';
var senderName = 'Johnny Romano';
var senderTitle = 'CEO';
// Default Plain-text Message
var message = salutation + ' ' + recipient + ',\n';
message += msg + ' \n';
message += valediction + ', \n';
message += senderName + ' \n';
message += senderTitle;
// Send HTML Email
MailApp.sendEmail(recipientAddress, subject, message, {
name: senderName,
htmlBody: "<div>" + salutation + " " + recipient + ",</div><br>" +
"<div>" + msg + "</div><br>"+
"<div style='font-size:12px'>" + valediction + ",<br></div>" +
"<div style='font-size:12.8px'><b>" + senderName + "</b></div><br>" +
"<div style='font-size:12.8px'>" + senderTitle + "</div>",
});
Putting it all together
Perfect, so now we have all our components to sending an email. We have our email template, and a Google Sheet with two columns, one for first name, and one for email address. Now we need to build a loop that iterates through our list personalizing the recipient information.
function run(){
// Email Subject
var subject = 'Great Seeing You in our Virtual Los Angeles!';
// Email Greeting
var fontsize = "12.8px";
var salutation = "Hi";
var msg = "Thanks so much for stopping by our booth at CONFERENCE 2020!"
// Email Signature
var signatureColor = "#073763";
var valediction = "Best Regards"
var senderName = "Johnny Romano";
var senderTitle = "CEO";
// SET how many recipients
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = sheet.getLastRow()-1;
var dataRange = sheet.getRange(startRow, 1, numRows, 3);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
var num = 0;
// LOOP through all the contacts
for (i in data) {
var row = data[i];
var recipientAddress = row[0]; // First column
var recipientName = row[1]; // Second column
// Default Plain-text Message
var message = salutation + " " + recipientName + ',\n\n';
message += msg + '\n\n';
message += valediction + ', \n';
message += senderName + '\n';
message += senderTitle + '\n\n';
// SEND HTML EMAIL
MailApp.sendEmail(recipientAddress, subject, message, {
name: senderName,
htmlBody: "<div style=font-size:12.8px'>" + salutation + " " + recipientName +
",</div>"+
// HTML Email Body
"<br>" + "<div style=font-size:" + fontsize + "'>" + msg + "<br><br>" +
// Signature
"<div style='font-size:" + fontsize + "'>" +
"<font color='"+ signatureColor + "'>" + valediction + ",</font><br></div>" +
"<div style='font-size:" + fontsize + "'>" +
"<font color='" + signatureColor + "'> <b>" + senderName + "</b></font></div>" +
"<div style='font-size:" + fontsize + "'>" +
"<font color='" + signatureColor + "'>" + senderTitle + "</font></div><br>"
});
num++;
}
SpreadsheetApp.getActiveSpreadsheet().toast(num, "Emails sent");
}
A couple last things…
3. Adding a custom send email menu button
Google allows you to add custom menus to you Spreadsheets that will show up as a new menu item. It’s easy to add, simply create the following function. This will run our primary script named ‘run’.
// Create custom menu when Spreadsheet opens
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Send Emails')
.addItem('Send!', 'run')
.addToUi();
};
4. Attaching files from Google Drive
Sometimes you need to attach a file to your email. Here’s an example of a send mail template with two pdfs from Google Drive attached.
// Adding Google Drive Files as attachments
// i.e. https://drive.google.com/open?id=file1ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw
// PDF File 1
var file1 = DriveApp.getFileById('file1ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghjklmnopqrstuvw');
// PDF File 2
var file2 = DriveApp.getFileById('file2ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw');
// SEND HTML EMAIL as Active Sheet User
MailApp.sendEmail(recipientAddress, subject, message, {
name: senderName,
htmlBody: "<div style=font-size:12.8px'>" + salutation + " " + recipientName +
",</div>"+
// HTML Email Body
"<br>" + "<div style=font-size:" + fontsize + "'>" + msg + "<br><br>" +
// Signature
"<div style='font-size:" + fontsize + "'>" +
"<font color='"+ signatureColor + "'>" + valediction + ",</font><br></div>" +
"<div style='font-size:" + fontsize + "'>" +
"<font color='" + signatureColor + "'> <b>" + senderName + "</b></font></div>" +
"<div style='font-size:" + fontsize + "'>" +
"<font color='" + signatureColor + "'>" + senderTitle + "</font></div><br>"
attachments: [file1.getAs(MimeType.PDF), file2.getAs(MimeType.PDF)]
});
Thoughts on Backlog of updates
[ ] - Integrate with Google Forms
[ ] - Add GMail telemetry data in new spreadsheet tab
Google API Documentation:
Spreadsheet Service - This service allows scripts to create, acces, and modify Google Sheets Files.
Gmail Service - This Service lets you send email, compose drafts, manage labels, mark messages and threads, and conduct a variety of other Gmail account management tasks.
Drive Service - This service allows scripts to create, find, and modify files and folders in Google Drive.
Custom Menus in GSuite - onOpen()
Class DriveApp - getFileById()
Class SpreadsheetApp - getSheetByName()