Home > database >  How to run google app script at exact time?
How to run google app script at exact time?

Time:05-14

Currently, I want to run my google app script at exact time. I have already do some reading about this problem and I decide to follow the solution in this post It's possible run Google Sheets script Exact Time Everyday?.

However, every time the time trigger only work only once. It will display error for the second time trigger. I google about this problem and most of them state that is google v8 bug.( https://issuetracker.google.com/issues/150756612).

So, I'm decide to downgrade my code to ES5 since that is the only solution from the post( https://issuetracker.google.com/issues/150756612). But, I'm facing a problem about changing the spread operator(...) to ES5

Here is my code:

function runTrigger(hours,minutes) {
  
  var today_D=new Date();
  var year=today_D.getFullYear();
  var month=today_D.getMonth();
  var day=today_D.getDate();
  
  day=day 1; 
  
  var pars=[year,month,day,hours,minutes];
  
  var schedule_date=new Date(...pars);
  var hours_reamain=Math.abs(schedule_date - today_D)/36e5;
  
  ScriptApp.newTrigger("testFunction")
   .timeBased()
   .after(hours_reamain*60*60*1000)
   .create()  
}

function deleteTrigger() {
  var triggers=ScriptApp.getProjectTriggers();
  
  var ssId="1d5P4ohgSLtOzuInxq1IYQUsB4GybeYhth5Gj21RI3rA";
  var ss=SpreadsheetApp.openById(ssId);
  
  for(var i=0 ; i< triggers.length ; i  ) {
    ss.appendRow([new Date() ,JSON.stringify(triggers[i]), JSON.stringify(triggers[i].getHandlerFunction()) , JSON.stringify(triggers[i].getEventType()) , JSON.stringify(triggers[i].getTriggerSource())])
    if(triggers[i].getHandlerFunction() === "testFunction") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

function setTrigger() { 
    deleteTrigger();
    runTrigger(1,35);
}

function testFunction() {
  //some code at here
  setTrigger();
}

Can anybody teach me how can I change the ...(spread operator) to ES5

CodePudding user response:

What I'm doing so far is throwing a function that will set the trigger to the exact time based on the current time. I make sure the previous trigger is deleted and create it again.

function actionAtExactHour() {
  var f = 'myFunction'
  ScriptApp.getProjectTriggers().filter(trigger => trigger.getHandlerFunction() == f).forEach(t => ScriptApp.deleteTrigger(t))
  var d = new Date();
  ScriptApp.newTrigger(f)
    .timeBased()
    .after((60 - d.getMinutes()) * 60 * 1000)
    .create()
}
function myFunction(){
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue(new Date())
}

However, I can't put a trigger on actionAtExactHour to e.g. fire the trigger daily and have myFunction run every day at the exact time...google will disable that trigger. To do this, I need to run actionAtExactHour myself a few minutes before the exact time I want

CodePudding user response:

It's a javascript Date() constructor

 var schedule_date=new Date(year,month,day,hours,minutes);

Date() constructor

  • Related