Google Scripts is a powerful tool you can use to automate Google productivity tools like Sheets and Docs. The ability to automatically send emails makes it an extra powerful tool.
Maybe you're an employee hoping to send a monthly email out to your boss with automatically-calculated formulas in Google Sheets. Or maybe you're a manager who spends far too much time emailing individual members of your team with things like performance data or status updates. The Google Scripts function you'll learn in this article will help you accomplish these tasks and much more.
With just a little effort one time, you'll never have to manually send out data again. Let Google Scripts act as your own personal assistant, doing all of the work for you.
Setting Up Your Sheet to Send Email
The first step in getting your Google Script to send mail via Google Sheets is properly setting up a sheet that has all of the names, email addresses and messages to all of the people you want the script to email.
In my example, I'm starting with a spreadsheet that I've set up to automatically import all of the published articles for the month from Google Analytics. I've created an "All Authors" sheet that tallies up the published articles for each author, and now I want to send each individual author a email with their tally.
To get started, create your special email sheet by adding a new sheet in your current spreadsheet and call it something like "Send-Emails".
In this new Send-Emails sheet, you'll want to create a header. Each row in this spreadsheet will represent an individual email that will get sent out. So in this example I've created a sheet for the recipient name, their email address, and pieces of the message that I'm going to piece together inside of the script.
You can build an email of any size and structure by using columns to piece together both static and dynamic information.
The static information is just text that I've typed into the cell. This won't change from month to month. It's just a part of the email message that always stays the same. However, for the data that you want to change every month, you can insert functions that will import the relevant data from any other sheet in your spreadsheet.
The idea here is that whenever you open the sheet to review your automated report, you will have a menu item you can click to run the send email Google Script and distribute the results to everyone. You'll see how to customize this new menu item at the bottom of this article.
Once you're finished creating your sheet, it'll look something like this:
Now that your sheet for all of the individual emails is ready, it's finally time to write the script!
Writing the Automated Email Script
To write your script, you need to use the script editor. You'll find the script editor under the Tools menu by selecting Script editor.
The script editor will look something like below. You'll need to crate a new function at the top of the pane on the right.
Just copy and paste the script below into the script area.
function sendArticleCountEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("Send-Emails"));
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange("A2:F4");
var data = dataRange.getValues();
for (i in data) {
var rowData = data[i];
var emailAddress = rowData[1];
var recipient = rowData[0];
var message1 = rowData[2];
var message2 = rowData[3];
var parameter2 = rowData[4];
var message3 = rowData[5];
var message = 'Dear ' + recipient + ',\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;
var subject = 'Your article count for this month';
MailApp.sendEmail(emailAddress, subject, message);
}
}
This script may look somewhat complicated, but it actually isn't at all. Let's break it down so you know exactly what each line is doing.
Breaking Down the Code
In order for this function to work right, you need to ensure that the sheet where all of your email information is stored is actually the active sheet. Otherwise everything that comes after won't work. That's what these two lines are for:
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("Send-Emails"));
Next, we need to extract all of the data from that sheet. The sheet.GetRange() method will extract information from whatever range of cells you provide within quotes. Next, the dataRange.getValues() method actually extracts the values and stores them into a two-dimensional array called data.
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange("A2:F4");
var data = dataRange.getValues();
Now that we have all of the data stored in an array, we can loop through the array using a for loop. Each element of the array is a row that contains a one dimensional array of column elements. You can reference each column using a number.
var rowData = data[i];
var emailAddress = rowData[1];
var recipient = rowData[0];
var message1 = rowData[2];
var message2 = rowData[3];
var parameter2 = rowData[4];
var message3 = rowData[5];
As you can see above, I pulled the column elements into a one-dimensional array called rowData. Then I reference the second column (to obtain the email address) by referencing rowData[1], the second element of the array (the first element of an array is always zero).
The next step in this function is to piece together all of the segments of the messages that make up the body of the email. The nice thing here is that you can actually give the email content a pretty good format by using the \n character, which is a carriage return. Here is what the concatenation of the email body looks like:
var message = 'Dear ' + recipient + ',\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;
The + character is the concatenation command. You put actual static text inside of single quotes. So this message is pieced together by putting the word "Dear " in front of the recipient variable (notice "Dear " has a space).
Next, you add two carriage returns onto the end. This will start the actual message inside the body two lines down. Next you add the first message segment, a space, the second message segment, a space, the variable that will get inserted into the message each month, and finally the last part of the message.
You can have as many pieces of the message as you want, you simply have to use more columns to create the entire message in pieces.
The last couple lines of the code just set the subject line for the email (this could also include data from the spreadsheet if you wanted), and finally the sendEmail() method.
How to Trigger Your Script
Back in the Script Editor window, just search for the "onOpen" function. If the function is there, you'll likely see a bunch of code inside of the brackets. Go to the bottom of that code and insert the following lines:
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Send Emails', functionName: 'sendArticleCountEmails'}
];
spreadsheet.addMenu('Send Emails', menuItems);
Just make sure to type the exact name of your function in single quotes for the functionName parameter. This will load a custom menu item into your sheet every time your sheet opens.
Now, whenever you open up your spreadsheet on a monthly basis to review all of your data, all you have to do is click on the menu item to send out the emails. This will run your new function and send all of those emails for you. A job that used to take you several hours will now only require a single click of the mouse!
Google Scripts Can Help You Save Time
This is just one example of the many ways you can use Google Scripts to automate all sorts of things that save you time. For example, you can use Google Scripts to automatically generate a Google Analytics report. The difficult part is only that it does take a bit of work up front, but every minute you spend setting up automation like this using scripting is many hours saved. Just think of all of the other wonderful things you could do with those hours!
Have you ever used clever Google Scripts to automate your productivity? Share your own examples in the comments section below!