CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
user11849450
user11849450

Reputation:

Send an email when a specific cell is changed

I'm trying to build a project tracker that would

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.

Upvotes: 0

Views: 258

Answers (1)

AMolina
AMolina

Reputation: 1379

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.

Upvotes: 1

Related Questions