Tutorial: Sending emails from a Spreadsheet

Hugo Fierro
Google Apps Script Team
May 2009

Goal

This tutorial shows how to use Spreadsheet data to send emails to different people.

Time to complete

Approximately 10 minutes

Prerequisites

Before you begin this tutorial, you must:

• Feel comfortable using the Script Editor and have experience using the most basic Spreadsheet functions.

Section 1: Running a simple example

1. Create a new empty Spreadsheet

2. Add a few rows of data. Every row should contain an email address in column A and the email message to be sent to that person in column B. For testing purposes, you may want to use your own email address in column A. Here's an example:

   

3. Open the Script Editor by clicking on the 'Tools' menu, then select 'Script editor...'.

4. Copy and paste the following script:

   gmail/sendingEmails/sendingEmails.gs

   View on GitHub

   /**
    * Sends emails with data from the current spreadsheet.
    */
   function sendEmails() {
     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, 2);
     // Fetch values for each row in the Range.
     var data = dataRange.getValues();
     for (var i in data) {
       var row = data[i];
       var emailAddress = row[0]; // First column
       var message = row[1]; // Second column
       var subject = 'Sending emails from a Spreadsheet';
       MailApp.sendEmail(emailAddress, subject, message);
     }
   }

5. Save the Script

6. Select the function sendEmails in the function combo box and click "Run"

7. Check out your email Inbox. Messages are usually immediately delivered, but sometimes it takes a few seconds.

You may want to have a look at the documentation for the following methods used in the script above:

• Sheet.getRange() (note that there are four versions of this method)
• Range.getValues()
• MailApp.sendEmail() (note that there are four versions of this method)

Section 2: Improvements

You may want to mark a cell in each row every time an email is sent. This way, if your script stops running (for instance, if there is a bug in your code or you reach the maximum number of emails you can send every minute or day) you will be able to re-run the script later on and avoid sending email duplicates.

Here's a simple extension of the code that sets the cells in column C to 'EMAIL_SENT' for each row after sendEmail is called.

gmail/sendingEmails/sendingEmails.gs

View on GitHub

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
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();
    }
  }
}

Summary

Congratulations, you've completed this tutorial. You should now be able to easily make your scripts send emails from a spreadsheet.