从Apps Script项目发送的重复电子邮件，具有每周一次的基于时间的触发器 [英] Duplicate emails sent from Apps Script project that has a weekly time-based trigger

问题描述

以下脚本正确地向符合脚本中指定条件的每个人每周发送一封电子邮件。触发器是基于时间的，设置为每周一早上运行。今天早上，脚本运行了4次，同一个人收到了4次相同的电子邮件。

The below script was correctly sending one weekly email to each person who met the conditions specified in the script. The trigger is time-based, set to run weekly on Monday mornings. This morning, the script ran 4 times and the same individuals received the same email 4 times.

我唯一能想到的就是上周我将工作表的快捷方式放到了共享文件夹中。该文件夹有5个人可以访问其中的任何内容-我和其他4个人。我100％肯定没有其他人打开工作表或脚本，更不用说授权它运行或创建另一个触发器了。

The only thing I can think of is that last week I put a shortcut to the Sheet into a shared folder. The folder has 5 individuals who can access anything in it - me and 4 other people. I am 100% certain none of the other people opened the Sheet or the script, let alone authorized it to run or created another trigger.

为什么会发生这种情况，我该如何解决？非常感谢您的协助！

Why would this happen and what can I do to fix it? Any assistance is very much appreciated!

每个Stackdriver日志的我的执行区域排名4次

仅配置了一个触发器

下面的完整脚本

function sendEmailLoop() {

  var sheets =   SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();

    if (sheet.getName() == "Summary") //Disregard tab named 'Summary' 
    {      
    }

    else {    
      var range = sheet.getDataRange(); //to set the range as array
      var values = range.getDisplayValues(); //to get the value in the array
      var lastRow = range.getLastRow();
      var ss = SpreadsheetApp.getActiveSpreadsheet();  //declare the spreadsheet
      var sheet = ss.getSheetByName("Sheet1");
      var message = "";
      var i;
      var logContent = '';


      for (i = 3; i < lastRow; i++) { 
        if (values[i][8] == 'TRUE') {

      var EmpName = values[i][0];        //[Name] cell A++
      var EmpEmail = values[i][1];       // [Email] cell B++
      var SupName = values[i][2];       //[Supervisor Name] cell C++
      var SupEmail = values[i][3];      //[Supervisor Email] cell D++
      var LastComplete = values[i][4];  //[Last Completed Date] cell E++
      var DueDate = values[i][5];       //[Due date] cell F++
      var Title = values[0][0];         //[Title] cell A1
      var URL = values[0][1];         //[URL] cell B1
      var CertTo = values[1][1];      // [Certificate goes to] cell B2
      var curDate = values[0][4];
      console.log(EmpEmail);

        Logger.log('to: ' + EmpEmail);
        Logger.log('subject: ' + EmpName + Title + 'Test');
        Logger.log('message: ' + 'This is a test message for the training that can be found at ' + URL);


        if (EmpEmail == "") {
          continue;
        };

        message = "Dear " + EmpName + ","+
            "<br/><br/>This is a reminder that you must complete the " + Title + " training by " + DueDate + " in order to comply with the annual training requirement. You last completed the course on " +
              LastComplete + ". " + 
                "<p>Please complete the course at <a href=\ " + URL + ">this link</a> prior to the due date. You will continue to receive email reminders until you complete it. Once completed, please email a PDF of your completion certificate to your supervisor and " + CertTo + ".</p>" +
                "<em><br/><br/>**This email is auto-generated. If you already completed this training, please let your supervisor know.**</em>";

        MailApp.sendEmail({
          to: EmpEmail,
          cc: SupEmail,
          subject: 'Annual ' + Title + ' Training Reminder - Due ' + DueDate,
          htmlBody: message});
      }
      }; //end for loop - email tab data
    };   // end 'else'
  }); // end function(sheet)       
} // end SendEmailLoop()  

推荐答案

这似乎是错误

作为解决方法，它已修复：

This seems to be a bug

Untl it's fixed, as a workaround:

• 使用脚本属性以节省上一次执行时间


• 实施在代码开头的 if 语句仅在上次执行时间（从脚本属性中检索）不少于一周前时才执行其余代码。

• Use Script Properties to save the last execution time
• Implement an if statement at the beginning of your code that executes the rest of the code only if the last execution time (retrieved from the script properties) is not less than one week ago

示例：

function sendEmailLoop() {
  if(!PropertiesService.getScriptProperties().getProperty("lastExecution")){
    PropertiesService.getScriptProperties().setProperty("lastExecution", new Date().getTime());
  }
  var lastExecution = PropertiesService.getScriptProperties().getProperty("lastExecution");
  var now = new Date().getTime();
  var oneWeek = 1000*3600*24*7;
  if(now-lastExecution >= oneWeek){
    // paste here the rest of your code
    PropertiesService.getScriptProperties().setProperty("lastExecution", now);
  }
}

这篇关于从Apps Script项目发送的重复电子邮件，具有每周一次的基于时间的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！