The code below works perfectly and sends the email I need it to send on an "OnChange" trigger. However, it sends an email for EVERY checked box in row 7 which is overload, it just needs to send an email for newly checked boxes.

Any advice on how to add a condition in the below code for this?

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Service');
  var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i >= 1; i--) {
    if (sheet.getRange(i,7).isChecked()){
   
    var name = sheet.getRange(i,1).getValue();
    var last = sheet.getRange(i,2).getValue();
    var body = name   " "   last
    var subject = 'New Service Item in Stock'
    MailApp.sendEmail('[email protected]', subject, body);
    }
  }
}

I haven't tried anything because there is nothing I could find online to get me in the right direction.

https://i.stack.imgur.com/cKfLv.png

CodePudding user response：

Try unchecking them after you send them:

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Service');
  var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i >= 1; i--) {
    if (sheet.getRange(i, 7).isChecked() && sheet.getRange(i,8).getValue() != "Sent") {
      var name = sheet.getRange(i, 1).getValue();
      var last = sheet.getRange(i, 2).getValue();
      var body = name   " "   last
      var subject = 'New Service Item in Stock'
      MailApp.sendEmail('[email protected]', subject, body);
      sheet.getRange(i, 8).setValue("Sent");
    }
  }
}

Performance improvement:

function sendEmails() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Service');
  var vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  vs.forEach((r, i) => {
    if (sh.getRange(i   2, 7).isChecked() && r[7] == "Sent") {
      let body = `${r[0]} ${r[1]}`;
      let subject = "New Service Item in Stock";
      MailApp.sendEmail('[email protected]', subject, body);
      sh.getRange(i, 8).setValue("Sent");
    }
  });
}