google sheets - Duplicate emails sent from Apps Script project that has a weekly time-based trigger

Question

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.

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!

Ran 4 times per the Stackdriver logs - 'My Executions' area

Only 1 trigger is configured

Full script below

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()  

Answer 1

This seems to be a bug

Untl it's fixed, as a workaround:

• 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

Sample:

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);
  }
}