javascript - 如何让这个函数发送一封电子邮件一次并将实体更改为 "checked"

Question

您好，我有一个函数，如果实体包含“126d”，则会发送电子邮件。目前它会在每次更改时运行，导致每次出现带有“126d”的实体以及每次发生更改时它都会向我发送一封电子邮件。

显然我只想接收一次电子邮件，所以我的解决方案是有一个“状态”列，当实体包含“126d”并且电子邮件已发送一次时，它会更改中的“状态”同一行“检查”。不幸的是，到目前为止，我使其发挥作用的尝试都失败了。感谢您的帮助，谢谢!

function CheckMail() {
  var mailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List").getRange("A1:A500"); 
  var data = mailRange.getValues(); 
  var dataLength = data.length;
      for(var i=0;i<dataLength;i++){
        if(data[i][0] == "126d" || data[i][0] == "126D") {
          var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List").getRange("B1:B500"); 
          var name = range.getValues();
          var emailAddress = "email@gmail.com"
          var subject = "New parcel from " + name[i][0];
          var message = "Row " + (i+1) + " www.spreadsheet.com";
          MailApp.sendEmail(emailAddress, subject, message);
        }
      }
}

更新:我尝试了 @James Ds 解决方案，但奇怪的是，尽管将状态设置为“已检查”，但它仍然发送一封新电子邮件。

function CheckMail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List")
  var data = ss.getRange("A1:D500").getValues(); // Read all data in A1:D500

  for(var i=0;i<data.length;i++){ // Loop through all data
    if(data[i][0] == "126d" || data[i][0] == "126D" && data[i][3] != "checked") { // Check that A[i] is 126d and D[i] is not checked ([3] means col 3)    
      var emailAddress = "email@gmail.com" 
      var subject = "New parcel from " + data[i][1]; // get the subject from col B
      var message = "Row " + (i+1) + " www.spreadsheet.com"; // Message with row number
      MailApp.sendEmail(emailAddress, subject, message); // Send the mail
      ss.getRange(i + 1, 3).setValue("checked") // Set the 3rd column (D) to checked if the mail was sent
    }
  }
}

Answer 1

您没有尝试在任何地方设置“checked”值？

此代码应该可以满足您的需要，只需对范围和列进行一些小的修改

function CheckMail() {
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List")
 var data = ss.getRange("A1:D500").getValues(); // Read all data in A1:D500

 for(var i=0;i<data.length;i++){ // Loop through all data
     if((data[i][0] == "126d" || data[i][0] == "126D") && data[i][2] != "checked") { // Check that A[i] is 126d and D[i] is not checked ([C] means col C)    
  var emailAddress = "mail@gmail.com" 
  var subject = "New parcel from " + data[i][1]; // get the subject from col B
  var message = "Row " + (i+1) + " www.spreadsheet.com"; // Message with row number
  MailApp.sendEmail(emailAddress, subject, message); // Send the mail
  ss.getRange(i + 1, 3).setValue("checked") // Set the 3rd column (D) to checked if the mail was sent
  }
 }
}