从 Appscript Google 表格的列值更改发送电子邮件

问：

我编写了一个脚本，如果我的电子表格基于几个条件（单元格值 > 0 和单元格值 ！= “”）发生更改，我会向我发送电子邮件通知 此脚本中存在一个缺陷，因为我使用 importrange 并且我在更改时使用触发器。因此，importrange 似乎每 2 分钟更新一次其值。然后触发器将在邮件中触发相同的消息。

我想做的是根据列值的变化添加if条件。如果列更改为其他值，则让脚本触发发送电子邮件。但是，如果列没有更改，则不会触发脚本发送电子邮件。尽管满足此条件（（单元格值 > 0 且单元格值 ！= “”））。

我很乐意得到任何线索或更好的剧本。 这是我的剧本

    function Alert_Pos_Kerja() {
 
  var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
  Logger.log(" quota: " + emailQuotaRemaining);
  // Pulls data from the spreadsheet
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "Alert_Pos Kerja"
  );
  var date = new Date();
  // Get the day of the week (0 for Sunday, 1 for Monday, ..., 6 for Saturday)
  var day = date.getDay();
  // Get the hour of the day (0 for midnight, 1 for 1 am, ..., 23 for 11 pm)
  var hour = date.getHours();

  //Prepares the email alert content
  let message =  "Alert Trade, Services & Investment-Retail Trade: <br><br>";
  let send_message = false;
  // Check if the day is a weekday and the hour is between 9 am and 4 pm
  if (day >= 1 && day <= 5 && hour >= 8 && hour <= 16) {
    let source = sheet.getRange("A:t");
    let data = source.getValues();





console.log("starting loop");

//Loops through the cells in the spreadsheet to find cells where the stock fell below purchase price
let n = 0;
for (let i in data) {
  //Skips the first row
  if (n++ <= 3) continue;

  //Loads the current row
  let row = data[i];

  console.log(row[1]);
  console.log(row[4]);
  console.log(row[5]);

  //Once at the end of the list, exits the loop
  if (row[4] == "") break;

  //If value is below purchase price, adds stock ticker and difference to list of tax loss opportunities
  // if(row[5]>0&&(row[6]!="" || row[6]!="Cek SOTP")){
  if(row[5]>0&& row[6]!="" ){
    message +=
      " <br>"+
      row[4] +
      ": " +
      " , Last : "+
      row[5]+
      " , Action : "+
      row[6]+
      " , Info: "+
      row[3]+
      " , Tanggal Info : " +
      row[1]+
      " , Technical : " +
      row[7]+
      " , Target Price : " +
      row[8]+
      " , Remark : " +
      row[9]+
      " , Sektor : " +
      row[10]+
      " , Risk Close : " +
      row[11]+
      " , Price & % to Upper R : " +
      row[12]+" "+row[14]*100+"%"+
      " , Price & % to Lower S : " +
      row[13]+" "+row[15]*100+"%"+
      " ,  quota: " + emailQuotaRemaining +
      " <br>";
    send_message = true;
  }
   }

  if (!send_message) return;
    MailApp.sendEmail({
    // to: SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail(),
    to: '<<email>>',
    subject: "Alert Trade, Services & Investment-Retail Trade",
    htmlBody: message,
   
  });
  }
 
}

这是我的电子表格的示例。

https://docs.google.com/spreadsheets/d/1LcJLqQLNXJp-B1V9Knf4l1gGZIaelZkh9VEkJ8rivTI/edit?usp=sharing