Custom Emails from a Spreadsheet

Humanizing the Google Apps Script

Sebastian Fung
Mar 17, 2014 · 7 min read

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.


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:

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:


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:


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:


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(“")&"; 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(“")

This gives us:

Howdy Abigail!

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.

The first step is to change Line 10 from:

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


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”;


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


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.

=E2&”’s Slingbot Report Card”

Which gives us:

Abigail’s Slingbot Report Card

Final Product

The final product is an email script using Google Apps that allows us to customize the content and the subject line.

You can take a look at the final product here:

Example 5

Subscribe to follow my blog or say hi (@sebfung) on Twitter!

    Sebastian Fung

    Written by

    Find the right perfect card:

    Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
    Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
    Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade