Pinterest Stumbleupon Whatsapp
Advertisement

Google Scripts is a powerful tool you can use to automate Google productivity tools How To Create An Automated Google Spreadsheet Report From Analytics Data How To Create An Automated Google Spreadsheet Report From Analytics Data Read More 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 Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More . 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 Create A Killer Website Analytics Dashboard With Google Core Reporting API Create A Killer Website Analytics Dashboard With Google Core Reporting API Would you like to just publish a site and run it blindly, or would you like a full-featured, functional dashboard that shows you what's going on with the site at all times? Read More . 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”.

Advertisement

send email google scripts

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.

send email google scripts

You can build an email of any size and structure by using columns to piece together both static and dynamic information.

send email google scripts

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.

send email google scripts

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:

send email google scripts

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.

send email google scripts

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.

send email google scripts

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\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\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;

The + character is the concatenation command 16 Excel Formulas that Will Help You Solve Real Life Problems 16 Excel Formulas that Will Help You Solve Real Life Problems The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them. Read More . 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.

send email google scripts

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 4 Google Scripts That Make Google Sheets Much More Powerful 4 Google Scripts That Make Google Sheets Much More Powerful Google Sheets is great out of the box, but it can be even more powerful if you use Google Scripts to enhance its capabilities. Read More that save you time. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. David Berman
    July 8, 2017 at 11:46 am

    Is there a way to have the script send an email triggered by an event, such as a cell containing a certain value, instead of having to choose from a menu? For example, I would like to automatically notify Jill if there is an error in a certain cell.

    • Ryan Dube
      July 8, 2017 at 1:30 pm

      Hey David,

      Yes, definitely. The way I would handle automation is to go into the Script Editor, Under the Edit menu, go to "Current Project's Triggers".

      Click on the "Add a new trigger" link. Under the "Run" column, select the function you've just created that you want to trigger, select "From spreadsheet", and choose "on change" or "on edit" (I think either would work fine).

      So long as in the start of your script, you have an if statement that references that cell and only sends an email if it contains an error (and does nothing else otherwise), it should work fine and shouldn't be too resource intensive. If you do find that it slows down the sheet at all though, you may want to experiment with changing "From spreadsheet" to "Time-Driven" and just having the script run every hour or so, checking whether that cell has an error.

      Hope that helps, and good luck!