Sending emails from Google Sheets

Why send emails from Google Sheets in the first place? A case study.

First of all, let’s clarify why you would even want to use Google Sheets to send emails in the first place. The idea itself may sound crazy at first, but there are tons of use cases behind it. Let’s illustrate it in an example.

As you may know, except for all our regular team members, we have a bunch of minions working for us day and night*. Our minions help us develop and maintain Mailtrap as the best platform for pre-production email testing. As everyone knows, minions like bananas. A lot.

To motivate minions, we pay them in bananas – the exact amount depending on the complexity of the task and their performance. Since feeding an army of minions is a rather complex task, we’ve decided to ease the pain by implementing some automation. Each of our minions is added to the spreadsheet, listing its name, email address (even minions have those), and the accumulated amount of bananas.

By the way, if you use an accounting app such as Xero to monitor payments to your minions (ekhm, contractors), you can automate the data import using Coupler.io. Read How to Integrate Xero and Google Sheets to learn more.

Since we have more important things to do than handing out individual bananas to minions, we’ve set up a rule – each minion needs to accumulate 20 or more bananas before they can pick them up. Of course, they can wait longer, until they reach 50 for example, but, really, who would do that?

Since minions are not very good with spreadsheets, we need to email them when their banana count reaches 20 or more. For that, we’re going to need to build a script to send emails from Google Spreadsheets.

Sending emails from Google Sheets step by step

To get started, let’s open the Script Editor. Pick Tools from the menu at the top and then Script Editor. A new page will load.

Name it what you wish. Clear the window and insert the following JavaScript code:

Save the script you’ve just created. If you’re ready to run it, pick the name from the list at the top (it will contain just one position in the beginning anyway) and hit the triangular ‘Run’ button.

Before you send a campaign, you’ll need to authorize Gmail to send emails on your behalf.

There’s a chance that the script you’ve just created will be considered as unverified by Google at first. If that happens, open ‘Advanced’ options and choose to proceed anyway.

If your script is correct, it will be executed immediately. Double-check that you added the correct email addresses to the spreadsheet (we haven’t ;-)). Otherwise, your emails will simply bounce.

Your email will be sent right away and, before you finish this sentence, it will properly land in the respective minion’s inboxes. Each will look something like this:

Let’s break down the code

To make it very clear what just happened, let’s explain the code in a bit more detail.

function sendBananaCountEmails() {
const sheet = SpreadsheetApp.getActiveSheet();
const dataRange = sheet.getRange("A2:C6");
const data = dataRange.getValues();

In the first part of the code, we’re making sure that our data source is an active spreadsheet. If it’s not, we won’t be able to fetch data from it.

We also declare the range of data to be used with the script – in our case rows A-3 and columns 2 to 6.

data.forEach(function (rowData) {
const recipient = rowData[0];
const emailAddress = rowData[1];
const bananasCount = rowData[2];

Then, we declare three variables, each corresponding to a different column of our spreadsheet.

if (bananasCount < 20) {
return
}

As you remember, we only wanted to send emails when a minion has accumulated 20 or more bananas. We specified this condition here.

const greeting = 'Dear ' + recipient + ',\n'
const bananasCountMessage = 'You have ' + bananasCount + ' bananas accumulated.';
const greatJobMessage = 'Great job! Come pick them up :)';

const message = [greeting, bananasCountMessage, greatJobMessage].join('\n');
const subject = 'Good news, you have some bananas waiting for you!';

Now, all we have to do is put the pieces together to form a message with a subject.

MailApp.sendEmail(emailAddress, subject, message);
})
}

Finally, we tell Gmail to send emails on our behalf to all the records that meet the criteria.

Automating the process

The script we explained above won’t work without human help. Every time you want to launch a check, you’ll need to run the script manually. For some, it will be perfectly fine. For most, a bit more automation will be required.

We need to create a script for sending automatic emails from Google Spreadsheets. We’ll run it at regular intervals according to our needs.

If you want the minions to know right away when they reach or cross the threshold, daily checks will probably do the job. You can also opt for weekly, monthly or any other. Of course, you can just send emails to everyone in the spreadsheet regardless of their banana count. Or you could stay true to the initial criteria of >=20 bananas. Let’s see how both are done.

We’re going to implement two types of reports:

• A daily report that’s only sent to minions that have accumulated 20 or more bananas.
• A monthly report that’s sent to everyone involved.

Because of that, let’s follow theDRY principle and extract a common behaviour into a common function. Let’s create a new script…

… and call it “forEachMinion“. It will look like this:

function forEachMinion(callback) {
const sheet = SpreadsheetApp.getActiveSheet();
const dataRange = sheet.getRange("A2:C6");
const data = dataRange.getValues();
​
data.forEach(function (rowData) {
const recipient = rowData[0];
const emailAddress = rowData[1];
const bananasCount = rowData[2];

callback(recipient, emailAddress, bananasCount);
})
}

We’ll use the function above for both reports – daily and monthly.

Now let’s create separate scripts for each report.

bananaDailyReport:

function bananaDailyReport() {
forEachMinion(function(recipient, emailAddress, bananasCount) {
if (bananasCount < 20) {
return
}
​
const greeting = 'Dear ' + recipient + ',\n'
const bananasCountMessage = 'You have ' + bananasCount + ' bananas accumulated.';
const greatJobMessage = 'Great job! Come pick them up :)';
​
const message = [greeting, bananasCountMessage, greatJobMessage].join('\n');
const subject = 'Good news, you have some bananas waiting for you!';
​
MailApp.sendEmail(emailAddress, subject, message);
})
}

bananaMonthlyReport:

function bananaMonthlyReport() {
forEachMinion(function(recipient, emailAddress, bananasCount) {
const greeting = 'Dear ' + recipient + ',\n'
const bananasCountMessage = 'You have ' + bananasCount + ' bananas on your account. Keep it going!Remember you can come pick them up once you reach 20 or more.';
const message = [greeting, bananasCountMessage].join('\n');
const subject = 'Here's your weekly banana summary!';
MailApp.sendEmail(emailAddress, subject, message);
})
}

You can run each of these scripts manually as we did before but that’s not the point, right?

Luckily, Google already implemented a simple tool for triggering reports. It’s aptly namedTime-driven triggers, and we’ll use it today.

Let’s create another script, which will set up triggers responsible for calling our mailing functions. We’ll name it setupBananaCountEmailsSchedule.

function setupBananaCountEmailsSchedule() {
ScriptApp.newTrigger('bananaDailyReport')
.timeBased()
.everyDays(1)
.atHour(23)
.create();

ScriptApp.newTrigger('bananaMonthlyReport')
.timeBased()
.onMonthDay(30)
.atHour(23)
.create();
}

That would be all. But if at some point you decide to delete these triggers, you can use the following code:

function deleteBananaCountEmailSchedule() {
const allTriggers = ScriptApp.getProjectTriggers();

allTriggers.forEach(function(trigger) {
if (['bananaDailyReport', 'bananaMonthlyReport'].indexOf(trigger.getHandlerFunction()) > -1) {
ScriptApp.deleteTrigger(trigger);
}
})
}

You’ll still be able to trigger daily and monthly reports manually but nothing will be sent out automatically. Hopefully, the minions will continue working as usual. 😉

Other considerations

To make things simpler, an automated email script can fetch all the necessary details from a spreadsheet. It doesn’t look pretty but can be useful in some situations. For a specific example with a code, check outGoogle Developer pages.

We hope you found our guide useful. Until next time!

I hope our google apps script to send emails was useful for you. It was initially published in the article "Sending emails from Google Sheets"in the Mailtrap Blog.