Google 应用脚本 - 自动发送电子邮件(特定问题删除行和追加行)

Question

我正在尝试使用Google App Scripts为我发送我编写的HTML电子邮件。我的代码工作到我可以发送电子邮件的程度，但是正如您将在我的代码中看到的那样，我有 4 个不同的模板要发送。.如您所见，我有一些代码可以将模板更改 1 以找到正确的模板和 dateMath 以增加下一封电子邮件发送的日期。

我现在遇到的问题是删除行和附加行。我有 2 个用户正在尝试发送电子邮件，当我运行代码 deleteRow 和 appendRow 时，其中一个用户替换了另一个用户。我的屏幕截图会更有意义。这里有什么见解吗？脚本之前

脚本后

function sendEmails(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var today = Utilities.formatDate(new Date(), "MDT", "dd/MM/yyyy");
  for(var i = 1; i<data.length; i++){
    
    if(data[i][3] !== ""){ //skip if is empty
     
      if(isValidDate(data[i][3])){ //skip if isn't a valid date
        
        var formattedDate = Utilities.formatDate(data[i][3], "MDT", "dd/MM/yyyy"); 
        if(formattedDate == today){ //send email if checkin date is today
          
          if(data[i][4] == 1) {  
           var template = HtmlService.createTemplateFromFile('Template1');
          } else if(data[i][4] == 2){
            var template = HtmlService.createTemplateFromFile('Template2');
          } else if(data[i][4] == 3){
            var template = HtmlService.createTemplateFromFile('Template3');
          } else {
            var template = HtmlService.createTemplateFromFile('Template4');
          }                                                                                                      
          var email = data[i][2];
          var firstName = data[i][0];
          var lastName = data[i][1];
          template.firstName = firstName;
          var subject = "Your Next Steps ";
        
          var bcc = "[e-mail]";
  
          var message = template.evaluate();
        
          GmailApp.sendEmail(email,
           subject,
           message.getContent(), {
             htmlBody: message.getContent(),
             bcc: bcc
          });
          
          //change the template
          //delete the row
          sheet.deleteRow(i+1);
          //add the row again
          var newTemplate = data[i][4] + 1;
          var newSend = dateMath(data[i][3], 8);
          sheet.appendRow([firstName, lastName, email, newSend, newTemplate]);
          
        }
      } 
    }
  }
}
/**
 * Does math on dates
 * Triggered from functions
 * Input: date = the orginal date, d = +- number of days
 * Output: a new date
 */
function dateMath(date,d){
  var result = new Date(date.getTime()+d*(24*3600*1000));
  return result
}                  
/**
 * Figures out if is a data
 * Triggered from functions
 * Input: d: any
 * Output: boolean
 */
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" ){
    return false;
  } else {
    return true; 
  }
}

Answer 1

由于要删除行，因此i值不再对应于同一范围。例如，您有一个包含 5 个元素的数组：

[A, B, C, D, E]

如果删除元素0 ，将得到

[B, C, D, E]

现在，您将迭代器递增得i++; // i = 1，因此您将操作的下一个值不是"B"，而是"C"。

你可以通过运行这个来尝试自己

function test() {
  var letters = ["A", "B", "C", "D", "E"];
  for (var i=0; i<letters.length; i++) {
    Logger.log("i : " + i + " || Letter: " + letters[i]);
    letters.shift();
  }
}

若要修复代码，请添加一个独立于数组迭代器的 row 变量i 。

function sendEmails(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var today = Utilities.formatDate(new Date(), "MDT", "dd/MM/yyyy");
  var row = 2; // Starting at row 2
  for(var i = 1; i<data.length; i++){
    if(data[i][3] !== ""){ //skip if is empty
      if(isValidDate(data[i][3])){ //skip if isn't a valid date
        var formattedDate = Utilities.formatDate(data[i][3], "MDT", "dd/MM/yyyy"); 
        if(formattedDate == today){ //send email if checkin date is today
          if(data[i][4] == 1) {  
           var template = HtmlService.createTemplateFromFile('Template1');
          } else if(data[i][4] == 2){
            var template = HtmlService.createTemplateFromFile('Template2');
          } else if(data[i][4] == 3){
            var template = HtmlService.createTemplateFromFile('Template3');
          } else {
            var template = HtmlService.createTemplateFromFile('Template4');
          }                                                                                                      
          var email = data[i][2];
          var firstName = data[i][0];
          var lastName = data[i][1];
          template.firstName = firstName;
          var subject = "Your Next Steps ";
          var bcc = "[e-mail]";
          var message = template.evaluate();
          GmailApp.sendEmail(email,
           subject,
           message.getContent(), {
             htmlBody: message.getContent(),
             bcc: bcc
          });
          //change the template
          //delete the row
          sheet.deleteRow(row);
          row--; // Deleted a row
          //add the row again
          var newTemplate = data[i][4] + 1;
          var newSend = dateMath(data[i][3], 8);
          sheet.appendRow([firstName, lastName, email, newSend, newTemplate]);
        }
      } 
    }
    row++; // Go to the next row
  }
}