Search code examples
google-apps-scriptgoogle-sheetstriggersmenuexecution

Google-Apps-Script Trigger for Google-Sheets provides incorrect answer compared to menu or manual executed script


Does anyone know why Google Apps Script Triggers would provide different answers from the same script executed from a menu? The code should only execute doSomething() on weekdays, between 12:50 & 1:00 PM, on non-holiday days when run = true.

My function...

function functionTester(test) {
  let now = new Date();
  let timeNow = (now.getHours() * 100) + now.getMinutes();
  let run = true;
  test = test || false;
  run = !test ? 
    ((now.getDay() == 6 || now.getDay() == 0 || timeNow < 1250 || timeNow > 1300 || Holiday(now)) ?
    false : true) : true;   // After hours, Weekend, Holiday

Logger.log("Run = "+run+"\r\n1 : "+(now.getDay() == 6)+"\r\n2 : "+(now.getDay() == 0)+"\r\n3 : "+(timeNow < 1250)+"\r\n4 : "+(timeNow > 1300)+"\r\n5 : "+Holiday(now));

  if (!run) { return };
  doSomething();
};

As expected, run = false because condition 3 = true when manually executed, which is the intended output...

Run = false
1 : false
2 : false
3 : true
4 : false
5 : false

But when I run this with a trigger i get the following...

Run = true
1 : false
2 : false
3 : true
4 : false
5 : false

Run is now true even though condition 3 is true.

Any ideas?


Solution

  • Thank you Iamblichus! The key was...

    time-triggered functions don't receive parameters.

    I deleted test from line 1, and forced test = false on line 5. Otherwise, with code test = test || false on line 5, test is set to [object Object] ,which apparently makes test becomes truthy, and the other conditions are irrelevant. My updates always run when executed by a time trigger independent of the other conditions.

    My newest code looks like this...

    function functionTesterToo() {
      const now = new Date();
      const currentTime = (now.getHours() * 100) + now.getMinutes();
      const workDayStart = 1259, workDayEnd = 1300;
      const test = false;
    
      (test || (!(now.getDay() == 6 || now.getDay() == 0) && (currentTime > workDayStart && currentTime < workDayEnd) && !Holiday(now))) ?  Logger.log("Getting Updates!") : Logger.log("Skipped Updates!");
    
      Logger.log("test = "+test);
      Logger.log("Not a weekend? : "+!(now.getDay() == 6 || now.getDay() == 0));
      Logger.log("During work hours? : "+(currentTime > workDayStart && currentTime < workDayEnd));
      Logger.log("Not a holiday? : "+!Holiday(now));
    };
    

    After being executed from a time trigger the Logs now show this...

    Mar 4, 2021, 9:50:08 AM   Info   Skipped Updates!
    Mar 4, 2021, 9:50:08 AM   Info   test = false
    Mar 4, 2021, 9:50:08 AM   Info   Not a weekend? : true
    Mar 4, 2021, 9:50:08 AM   Info   During work hours? : false
    Mar 4, 2021, 9:50:08 AM   Info   Not a holiday? : true