Automate Email Sending from Google Sheets Using App Script

Basic Setup

1. Create a New Spreadsheet

   Start by creating a blank Google Spreadsheet.

2. Add Data

   Populate the spreadsheet with data. Each row should have an email address in column A and the corresponding message in column B. Use your own email for testing. Example:

   • Column A: test@example.com
   • Column B: Hello, this is a test email!

3. Open the Script Editor

   Navigate to the Tools menu and select Script editor.

4. Copy and Paste the Script

   /**
    * 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. Run the Script

   Select the sendEmails function from the dropdown and click Run.

7. Check Your Inbox

   Check your inbox to see if the emails have been delivered. Delivery is usually immediate.

8. Review the Documentation

   Refer to the following methods in the script:

   • Sheet.getRange()
   • Range.getValues()
   • MailApp.sendEmail()

Improvements

To avoid sending duplicate emails, track sent emails by marking them.

Here's an enhanced version of the code:

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

This code marks each row as 'EMAIL_SENT' in column C to prevent duplicate emails.