Send email 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:

    Sending Emails

  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
    /**
     * Sends emails with data from the current spreadsheet.
     */
    function sendEmails() {
      try{
        // Get the active sheet in spreadsheet
        const sheet = SpreadsheetApp.getActiveSheet();
        let startRow = 2; // First row of data to process
        let numRows = 2; // Number of rows to process
        // Fetch the range of cells A2:B3
        const dataRange = sheet.getRange(startRow, 1, numRows, 2);
        // Fetch values for each row in the Range.
        const data = dataRange.getValues();
        for (let row of data) {
          const emailAddress = row[0]; // First column
          const message = row[1]; // Second column
          let subject = 'Sending emails from a Spreadsheet';
          //Send emails to emailAddresses which are presents in First column
          MailApp.sendEmail(emailAddress, subject, message);
        }
      }
      catch(err){
        Logger.log(err)
      }
    }
  5. Save the Script.

  6. Select the function sendEmails in the function dropdown list and click Run.

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

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

Section 2: Improvements

You might 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
// This constant is written in column C for rows for which an email
// has been sent successfully.
let EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendNonDuplicateEmails() {
  try{
    // Get the active sheet in spreadsheet
    const sheet = SpreadsheetApp.getActiveSheet();
    let startRow = 2; // First row of data to process
    let numRows = 2; // Number of rows to process
    // Fetch the range of cells A2:B3
    const dataRange = sheet.getRange(startRow, 1, numRows, 3);
    // Fetch values for each row in the Range.
    const data = dataRange.getValues();
    for (let i = 0; i < data.length; ++i) {
      const row = data[i];
      const emailAddress = row[0]; // First column
      const message = row[1]; // Second column
      const emailSent = row[2]; // Third column
      if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
        let subject = 'Sending emails from a Spreadsheet';
        // Send emails to emailAddresses which are presents in First column
        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();
      }
    }
  }
  catch(err){
    Logger.log(err)
  }
}

Summary

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