Send an email when a specific cell is changed

Question

I'm trying to build a project tracker that would

send an email to a list of emails
the title of the email is the text from cell A1
body of email is the text from cell D1
the trigger is when the value of the cell E1 changes to either "Complete" or "Attention"

So far I've created a script to send emails to the list of people created in a specific sheet. But I'm struggling with the function OnEdit and how to link the cells in single row when sending the email. The spreadsheet will have a number of rows referring to different projects.

function sendEmails(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  var lr = ss.getLastRow();
  for (var i = 2;i<=lr;i++){
    var currentEmail = ss.getRange(i, 1).getValue();
    var currentClassTitle = ss.getRange(i, 3).getValue();
    MailApp.sendEmail(currentEmail,currentClassTitle,"Project complete");
    }
  }

Any help and suggestions are greatly appreciated.

So you want to use an onEdit trigger to check if a cell in column E is changed to "Complete" or "Attention" to send an email with the data from the corresponding row? — ross
Welcome. This question has been asked and answered many, many times, and there's a reasonable expectation that you will do some initial research.. May I respectfully suggest that you Google "google sheets script email cell change site:.stackoverflow.com" to examine some precedents. After doing this, if you still have a question then raise it as a new question. — Tedinoz
Refer to stackoverflow.com/questions/57315867 for an example how to use onEdit(e) using Event Objects. — Tedinoz

AMolina AMolina · Accepted Answer · 2019-08-06T08:10:46

Try this:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSheet(); // The sheet.
  var data = ss.getDataRange().getValues(); // The data in all the cells
  var row = e.range.getA1Notation().substring(1);
  var email = ss.getRange("A"+row).getValue();
  var body = ss.getRange("D"+row).getValue();

  if (e.range.getA1Notation().substring(0,1) == "E"){
    if (data[row-1][4] == "Complete" || data[row-1][4] == "Attention"){
      MailApp.sendEmail(email,currentClassTitle,body);
    }
  }
}

EDIT

I fixed my code, it used to send unnecessary emails and not check properly which cell had been modified. The new code will check if the edited cell was in E column and whether the change was to set it "Complete" or "Attention". If the change was as required it will send a single email to the email on that row, with the content in the body row.

Send an email when a specific cell is changed

1 Answers