Custom Emails from a Spreadsheet

Humanizing the Google Apps Script

7 min readMar 17, 2014

For non-technical people venturing into the startup space, tools like Excel might be more familiar than JavaScript. The Google Apps Script is a JavaScript-based language that allows you to easily do amazing things with Google Docs, Sheets, and Forms. Google Developers has a very good tutorial on sending emails from a spreadsheet.

This guide expands on the script, allowing you to customize the emails to be more personal. Remember, there is no more B2B or B2C. It’s H2H: Human to Human. The problem with static content is that it looks like it was copy-pasted, or worse, that it’s spam. Customization is powerful — email with subject lines that include the receipients first name have a high impact on open rate, especially in the government, creative, or political space. You want to engage these people and give the impression that they’re worth your time.

By the end of the guide, you learn how to send emails with customized (variable-based) content and subject lines.

Starting

If you’re looking at the Google Apps Script to send emails, you probably have a spreadsheet with emails, names, and key information that you want to include. Let’s take a fictional example from my current startup, Slingbot:

Example 1

For the purpose of this tutorial, let’s assume our goal is to email this list of trial users to convert them to paying customers.

We can copy the details we need — such as email, name, and metrics — to another tab and arrange it in a similar way to the Google Developer tutorial.

Example 2

If the data set was significantly larger, you would want to avoid manipulating the database itself, and instead use functions like VLOOKUP or IF statements to port that information over.

Now, our data is set up in the same way as the tutorial on Google Developers.

Customized Content

We’ll start off where the tutorial on sending emails from a spreadsheet left off:

This is a second test

One of the first things you probably want to do is to include their name. From Example 2, we can see that the first names are in Column E. To incorporate this into the content, we need to turn Column B into an Excel formula. We do this by starting the cell off with an equal sign.

=E2&”This is a second test”

This outputs:

AbigailThis is a test

The ampersand (&) symbol is used to combine different strings. However, since it doesn’t account for normal grammatic rules, we have to add a extra space within the second string:

=E2&” This is a second test”

Which outputs:

Abigail This is a test

In emails, like letter form, it’s normal to put someone’s name above the content. We do this by using line breaks. In Excel, this is done by using CHAR(10):

=E2&CHAR(10)&” This is a second test”

Which outputs:

Abigail 
This is a test

To use two line breaks (or more), you connect them like you do other strings:

=E2&CHAR(10)&CHAR(10)&” This is a second test”

Which results in:

Abigail This is a test

To incorporate other details, you just combine additional strings:

=E2&CHAR(10)&CHAR(10)&” This is a second test”&CHAR(10)&CHAR(10)&”You made “&I2&” favorites which turned into “&J2&” conversions/followers!”

Which gives us:

AbigailThis is a second testYou made 775 favorites which turned into 50 conversions/followers!

The benefit of using formulas is that you can copy-and-paste them down.

What you end up with is this:

Example 3

You can also include clickable hyperlinks using the HYPERLINK function:

=HYPERLINK(“http://slingbot.co")

If we combine all of these elements together and add in better copy, we get Slingbot’scurrent conversion email.

=”Howdy “&E2&”!”&CHAR(10)&CHAR(10)&”Your free trial of our Sling plan just ended and we’re pleased to announce that your “&G2&” campaigns made “&I2&” impressions, which led to “&J2&” conversions. That’s a “&K2&”% conversion rate — nice shooting!”&CHAR(10)&CHAR(10)&”Right now, you have an important decision to make, do you want to be good or do you want to be great? Your account has automatically been downgraded to our free plan (which is still good!) but we know you can be better. Let’s break down what you can expect:”&CHAR(10)&CHAR(10)&”Free — 2,000 impressions — about “&L2&” followers”&CHAR(10)&”Shortbow ($9.99) — 4,000 impressions — about “&M2&” followers”&CHAR(10)&”Crossbow ($29.99) — 8,000 impressions — about “&N2&” followers”&CHAR(10)&”Catapult ($49.99) — 14,000 impressions/month — about “&O2&” followers”&CHAR(10)&CHAR(10)&”The premium plans have additional features exclusive to “&HYPERLINK(“http://slingbot.co")&"; features like geo-location, happiness barometer, and language filter, will be at your fingertips!”&CHAR(10)&CHAR(10)&”If you need help or have any questions (about our different plans, or about how to lasso a fish), shoot us an email ☺”&CHAR(10)&CHAR(10)&”Sling on!”&CHAR(10)&”Sebastian from Slingbot”&CHAR(10)&CHAR(10)&”Fact of the Day: Tennessee banned the use of a lasso to catch fish.”&CHAR(10)&HYPERLINK(“http://slingbot.co")

This gives us:

Howdy Abigail!Your free trial of our Sling plan just ended and we’re pleased to announce that your 3 campaigns made 775 impressions, which led to 50 conversions. That’s a 6.45% conversion rate — nice shooting!Right now, you have an important decision to make, do you want to be good or do you want to be great? Your account has automatically been downgraded to our free plan (which is still good!) but we know you can be better. Let’s break down what you can expect:Free — 2,000 impressions — about 129 followers
Shortbow ($9.99) — 4,000 impressions — about 258 followers
Crossbow ($29.99) — 8,000 impressions — about 516 followers
Catapult ($49.99) — 14,000 impressions/month — about 903 followersThe premium plans have additional features exclusive to [http://slingbot.co](https://www.slingbot.co); features like geo-location, happiness barometer, and language filter, will be at your fingertips!If you need help or have any questions (about our different plans, or about how to lasso a fish), shoot us an email ☺Sling on!
Sebastian from SlingbotFact of the Day: Tennessee banned the use of a lasso to catch fish.
[http://slingbot.co](https://www.slingbot.co)

A live copy of this is available here:

Example 4

Customized Subject Line

To customize the subject line, we’ll start where the tutorial left off:

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = “EMAIL_SENT”;function sendEmails2() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 2; // First row of data to process
 var numRows = 2; // Number of rows to process
 // Fetch the range of cells A2:B3
 var dataRange = sheet.getRange(startRow, 1, numRows, 3)
 // Fetch values for each row in the Range.
 var data = dataRange.getValues();
 for (var i = 0; i < data.length; ++i) {
 var row = data[i];
 var emailAddress = row[0]; // First column
 var message = row[1]; // Second column
 var emailSent = row[2]; // Third column
 if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
 var subject = “Sending emails from a Spreadsheet”;
 MailApp.sendEmail(emailAddress, subject, message);
 sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
 // Make sure the cell is updated right away in case the script is interrupted
 SpreadsheetApp.flush();
 }
 }
}

The first step is to change Line 10 from:

var dataRange = sheet.getRange(startRow, 1, numRows, 3)

To:

var dataRange = sheet.getRange(startRow, 1, numRows, 4)

This expands the range of variables for the script and allows us to use Column D (the fourth column!) for another script variable.

The next step is to change Line 19 from:

var subject = “Sending emails from a Spreadsheet”;

To:

var subject = row[3]; // Fourth column

Remember row[0] is Column A, so row[3] is Column D.

Next, we change Line 8 from:

var numRows = 2; // Number of rows to process

To:

var numRows = 100; // Number of rows to process

This runs the script through 100 rows, which is how many emails we have in our example. Although you can set this to 999999 (so it always runs through), I find it’s best practice to run it in batches to minimize the impact of human errors.

Finally, we update Column D by combining strings like we did for the customized content.