Google Apps Script

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: See Your First Spreadsheet Script Tutorial
  • Index

    This tutorial is divided into the following sections:


    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:
      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 (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.
    8. You may want to have a look at the documentation for the following methods used in the script above:

      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.

      
      // 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();
          }
        }
      }
      

      Summary

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

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.