Custom Emails from a Spreadsheet

Humanizing the Google Apps Script

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.

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:

Image for post
Image for post
Image for post
Image for post

Customized Content

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

This is a second test
=E2&”This is a second test”
AbigailThis is a test
=E2&” This is a second test”
Abigail This is a test
=E2&CHAR(10)&” This is a second test”
Abigail 
This is a test
=E2&CHAR(10)&CHAR(10)&” This is a second test”
Abigail This is a test
=E2&CHAR(10)&CHAR(10)&” This is a second test”&CHAR(10)&CHAR(10)&”You made “&I2&” favorites which turned into “&J2&” conversions/followers!”
AbigailThis is a second testYou made 775 favorites which turned into 50 conversions/followers!
Image for post
Image for post
=HYPERLINK(“http://slingbot.co")
=”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")
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 followers
The 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 Slingbot
Fact of the Day: Tennessee banned the use of a lasso to catch fish.
[http://slingbot.co](https://www.slingbot.co)
Image for post
Image for post

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();
}
}
}
var dataRange = sheet.getRange(startRow, 1, numRows, 3)
var dataRange = sheet.getRange(startRow, 1, numRows, 4)
var subject = “Sending emails from a Spreadsheet”;
var subject = row[3]; // Fourth column
var numRows = 2; // Number of rows to process
var numRows = 100; // Number of rows to process
=E2&”’s Slingbot Report Card”
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.

Image for post
Image for post

Find the right perfect card: http://messenger.com/t/asksebby

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store