Google Sheets - Send email based on another cell's text

Question

I have a script that sends an email for every cell within a range of cells. To prevent sending duplicate emails whenever the script is re-run, it marks a cell (column C) in each row every time an email is sent. Please see the example in ‘Sheet2’.

https://docs.google.com/spreadsheets/d/1_oMbZZjOhfdKuYNWOuyF3fao8yO6W3juFjUsFbFBJaU/edit?usp=sharing

However, I want to convert this into a script for 'Sheet1' that sends an email whenever a cell within its corresponding row contains specific text. In this example, whenever column I contains a checkmark (✓), I would like for a message (column E) to be sent with its corresponding subject line (column D). I also want to prevent duplicate emails, much like the example in ’Sheet2’. In other words, once column I contains a checkmark, the message is sent, completed, and will not re-send unless the checkmark is deleted and re-entered.

// 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 ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet2');
  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 = s.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 = "ta.ri...@gmail.com"; 
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = row[0];   // First column
      MailApp.sendEmail(emailAddress, subject, message);
      s.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Any help would be greatly appreciated.

Thank you!

Cooper Cooper · Accepted Answer · 2017-10-01T17:05:51

Try this:

function sendEmails2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1');
  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 = s.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 = "ta.ri...@gmail.com"; 
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (row[8]) {  // Prevents sending duplicates
      var subject = row[0];   // First column
      MailApp.sendEmail(emailAddress, subject, message);
      s.getRange(startRow + i, 9).setValue("");
      SpreadsheetApp.flush();
    }
  }
}

Google Sheets - Send email based on another cell's text

1 Answers