javascript - 如何避免在单元格中的值更新后发送重复电子邮件 : Javascript

Question

我正在尝试从发送到电子邮件的 Google 工作表中获取值，并更新单元格中的值。这有效。

但是，当我再次运行代码时，它会从值已更新为 3 的初始行中进行选择(以避免电子邮件重复)。

我希望它忽略之前发送的行，只选择第 41 列未设置为 3 的行。我在这里可能做错了什么？

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); 
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  for (var i = 1; i < data.length; ++i) {
    var rowData = data[i];
    var sender = rowData[1];
    var name = rowData[2];
    var client = rowData[3];
    var sdate = rowData[4];
    var edate = rowData[5];
    var loe = rowData[7];
    var emailSent = rowData[41];
    var emailAddress = 'test@example.com';
    var message = 'Email:' + sender + ',\n\n' + name + ' ' + client + ',\n\n' + sdate + ',\n\n' + edate + ',\n\n' + loe;
    var subject = 'Alert- New Project';
    if (emailSent !== '3' && MailApp.getRemainingDailyQuota()>0 && emailAddress && subject && message) {       
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(i+1, 41).setValue('3');
    }
  }
}

Answer 1

试试这个:

行[41]是第42列；

列索引从 1 开始

数组索引从零开始

   function sendEmails() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet(); 
      var dataRange = sheet.getDataRange();
      var data = dataRange.getValues();
      for (var i = 1; i < data.length; ++i) {//this skips the first row in the data array which is often a header.
        var rowData = data[i];
        var sender = rowData[1];//column2
        var name = rowData[2];//column3
        var client = rowData[3];//column4
        var sdate = rowData[4];//column5
        var edate = rowData[5];//column6
        var loe = rowData[7];//column8
        var emailSent = rowData[40];//Changed this from 41 to 40.Array index 41 is column 42
        var emailAddress = 'test@example.com';
        var message = 'Email:' + sender + ',\n\n' + name + ' ' + client + ',\n\n' + sdate + ',\n\n' + edate + ',\n\n' + loe;
        var subject = 'Alert- New Project';
        if (emailSent !== '3' && MailApp.getRemainingDailyQuota()>0 && emailAddress && subject && message) {       
          MailApp.sendEmail(emailAddress, subject, message);
          sheet.getRange(i+1, 41).setValue('3');//the 41 is a column index not an array index
        }
      }
    }