CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Andrew
Andrew

Reputation: 113

time based trigger to function that sets triggers fails intermittently

I have a function (see below) that deletes and then resets 3 triggers programmatically based on times contained in a sheet that are calculated based on the GoogleClock() function, and that auto-increments each day to the next workday (using the WORKDAY() ) function and a range containing all holidays. One of the three triggers it sets is a trigger to run itself again at the end of following workday, wherein the process restarts.

This is working about 60% of the time, and failing the rest of the time. No error email message is being generated, as far as I have seen.

The function is below. The while loop is intended to ensure that the spreadsheet values are not in the midst of GoogleClock() recalculation when the script runs, which was my original suspicion for why it was failing. Any suggestions?

function setCustomRunTriggers() {
  Utilities.sleep(5000);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PushedSchoolHolidays");
  var nextMidnight = sheet.getRange('D3').getValue();
  var nextSeven = sheet.getRange('D4').getValue();
  var nextSevenOFive = sheet.getRange('D5').getValue();
  var type =  getType(nextMidnight);
  while (type!="Object") {
    nextMidnight = sheet.getRange('D3').getValue();
    nextSeven = sheet.getRange('D4').getValue();
    nextSevenOFive = sheet.getRange('D5').getValue();
    type =  getType(nextMidnight);
  }
  var triggers = ScriptApp.getScriptTriggers();
  for (var i = 0; i<triggers.length; i++) {
    var eventType = triggers[i].getEventType();
    var triggerSource = triggers[i].getTriggerSource();
    var handlerFunction = triggers[i].getHandlerFunction();
    if ((handlerFunction=='functionA')&&(eventType=="CLOCK")&&(triggerSource=="CLOCK")) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
    if ((handlerFunction=='functionB')&&(eventType=="CLOCK")&&(triggerSource=="CLOCK")) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
     if ((handlerFunction=='setCustomRunTriggers')&&(eventType=="CLOCK")&&(triggerSource=="CLOCK")) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  ScriptApp.newTrigger('functionA').timeBased().at(nextMidnight).create();
  ScriptApp.newTrigger('functionB').timeBased().at(nextSeven).create();
  ScriptApp.newTrigger('setCustomRunTriggers').timeBased().at(nextSevenOFive).create();
}

Upvotes: 2

Views: 2776

Answers (2)

megabyte1024
megabyte1024

Reputation: 8660

I see the following problems in the provided code

  1. Using the SpreadsheetApp.getActiveSpreadsheet method in a time-based trigger code. If the user to whom belongs the trigger has no any opened spreadsheet or the active spreadsheet has no PushedSchoolHolidays sheet, then the code throws an "Cannot call method "getSheetByName" of null" exception or something like that. It is necessary to use the SpreadsheetApp.openById method to get rid of this problem.
  2. the getType methods returns a string differing of "Object" during more than 5 mins. The script hosting code throws the "Exceeded maximum execution time" exception.
  3. All triggers execution time exceeds the Day Trigger Aggregate Execution Time limit.

No error email message is being generated, as far as I have seen.

Programatically created triggers have no Execution failure notification emails. It can be a cause of why you do not receive error emails.

Upvotes: 4

Jpsy
Jpsy

Reputation: 20882

I have learned to place all (!) of my newTrigger()s into try/catch blocks with an email notification in the catch block. This allows me to receive some feedback when the newTrigger fails, e.g. because you ran into a daily trigger limit.

  try{
    ScriptApp.newTrigger('myFunction').timeBased().after(10000).create();
  } catch(error){
    MailApp.sendEmail("[email protected]", "NEWTRIGGER FAILED!", "Creating new time based trigger failed: \r\n" + error);
  }

Upvotes: 0

Related Questions