在 e.range 上未定义范围，并计划指定持续时间和指定列值更改 AppScript 的 OnChange 触发器

问：

我正在尝试编写一个用于监控库存变化的脚本。然后我尝试仅在工作日上午 9 点至下午 4 点创建它。目的是将 onchange 触发器限制为仅在指定的时间段内工作，并且如果股价发生变化，则限制发送电子邮件。因此，它不会在指定的其他时间发送电子邮件。下面是我现有的没有调度的代码。

function Mon_Mine_Kerja() {
  
  var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
  Logger.log(" quota: " + emailQuotaRemaining);
  // Pulls data from the spreadsheet
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "Mon_Mine Kerja"
  );
  let source = sheet.getRange("A:AA");
  let data = source.getValues();

  //Prepares the email alert content
  let message =  "Perhatikan: <br><br>";

  let send_message = false;

  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[21] <= -0.005 || row[21]>0.025) && row[26]=="aktif"){
      message +=
        " <br>"+
        row[4] +
        ": " +
        " , Last :"+
        row[5]+
        " , G/L & % Change :"+
        row[22]+" "+row[21]*100+"%"+
        " , 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 protected]',
    subject: "Mon_Mine",
    htmlBody: message,
    
  });
  
}

上面的代码结果符合我的意图（没有计划） 然后我尝试将调度放入代码中。但是我被错误卡住了。

我正在尝试将调度放入以下代码中。它给了我错误，我一直在四处寻找参考资料并尝试将其投入工作，错误仍然存在。知道如何解决这个问题吗？

我期待什么？

• 我打算只在工作日上午 9 点至下午 4 点之间创建更改触发器
• 我对实现编程触发器感到困惑，所以我只会使用 onchange 简单触发器。如果电子表格中发生任何更改，触发器将触发，但我希望它仅在检测到指定列（当前价格）的更改时才发送电子邮件。

在我之前的问题中，我被建议把 MRE。但是我的例子在谷歌表格中，所以我的例子将在链接中。例如，如何将其放在其他平台中，这将是一个很好的 MRE，不会冒险开发人员犹豫单击链接？

这是我的示例和错误日志的屏幕截图

我的工作示例

function Mon_Mine_Kerja(e) {
  
  var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
  Logger.log(" quota: " + emailQuotaRemaining);
  // Pulls data from the spreadsheet
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(
    "Mon_Mine Kerja"
  );
  var sheetName = e.range.getSheet().getName(); 
  var range = e.range.getA1Notation(); 
  var values = e.range.getValues();
  // Check if the change occurred on the sheet “Mon_Mine Kerja” and in the fifth column
  if (sheetName == "Mon_Mine Kerja" && range.startsWith("E")) {
    // Get the old and new values of the changed cell
    var oldValue = e.oldValue;
    var newValue = e.value;
    // Check if the value has changed
    if (oldValue != newValue) {
      // Get the current date and time
      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();
      // Check if the day is a weekday and the hour is between 9 am and 4 pm
      if (day >= 1 && day <= 5 && hour >= 9 && hour <= 16) {
        
        let source = sheet.getRange("A:AA");
        let data = source.getValues();

        //Prepares the email alert content
        let message =  "Perhatikan: <br><br>";

        let send_message = false;

        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[21] <= -0.005 || row[21]>0.025) && row[26]=="aktif"){
            message +=
              " <br>"+
              row[4] +
              ": " +
              " , Last :"+
              row[5]+
              " , G/L & % Change :"+
              row[22]+" "+row[21]*100+"%"+
              " , 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 protected]',
          subject: "Mon_Mine",
          htmlBody: message,
          
        });
        
}
    }}}

更新：

• 这是有关如何应用更改的示例。由于新的电子表格，错误率仍然是 100%。但对于以前的电子表格，它可以正常工作。好吧，我会问另一个与冻结googlefinance有关的问题，以便该功能不会发送电子邮件。这就是应用更改的方式

• 我如何运行mon_mine_kerja（e）。我在AppScript上使用了“运行”按钮。好吧，似乎我必须在电子表格中使用 =Mon_mine Kerja（函数）。但是由于错误，我没有迈出那一步。