2

I set a time-driven trigger to run my script, but the only choices in the pull-down menu for this are 1 hour ranges, 1-2pm, 2-3pm, etc. I chose 1-2pm, and one day the macro ran at 1:38pm and another day it ran at 1:42pm. I would really like the script to run at exactly 1pm. Is there a way to do this?

2 Answers 2

2

Google Apps Script hasn't a built-in way to make a trigger to run exactly at certain time as setting a trigger to run a certain time has a +/- 15 minutes variation.

One workaround is to use a time-driven trigger to run every minute an include a condition to make some part of the code to execute if the current execution is running at the desired time.

Here is a starting base:

function runEveryMinute(){
  const date = new Date();
  const hours = date.getHours();
  const minutes = date.getMinutes();
  if(hours === 13 && minutes === 0){
   // do something
  } else {
   // do nothing
  }
}

You should have to refine the above i.e. instead of using minutes === 0 you might want to use another condition and a way to check if the task was not already don in order to handle possible variations on the start execution time, like using a spreadsheet or the Properties Service to log timestamp of the last execution of the code part.

Also you might have to implement some measures to prevent consuming the installable triggers execution time daily quota, like creating / deleting the trigger that will run every minute few minutes before / after the desired time.


A more "elegant solution" might be to have a combination of time-driven triggers, i.e one that run daily near the desired time. If it run too early, it should create a trigger to run time after certain milliseconds before the desired time and iterate using do or while loops until the desired time is reached.

This might require to keep the logs to make the necessary adjustments based on performance and reliability.

Related

7
  • 1
    Thanks for the suggestion and base code. Your suggestion got me thinking that I could schedule the function to run 12-1pm, query the time when it does run, calculate how long that is before 1pm, sleep for the necessary time, and then perform my action (which should happen at 1pm). Thanks again.
    – Mark Neal
    May 15 at 2:32
  • Actually I discovered my modification to your idea does not work because there is a time limit for functions to complete of 6 minutes. So if the trigger starts the function more than 6 minutes before 1pm, trying to sleep until 1pm will cause the function to time out.
    – Mark Neal
    May 15 at 23:45
  • @MarkNeal Thanks for keeping us posted. P.S. Google Workspace accounts have a 30 min execution time limit but I don't think that it's worthy to consume part of the daily quota keeping an execution sleeping for so many minutes. Take a look to this option developers.google.com/apps-script/reference/script/…
    – Rubén
    May 16 at 3:26
  • I think the way you originally suggested will work, but I really don't want to run the function every minute all the time just for it to actually do something only once per week. So I did the second part of what you suggested with the code in the next comment.
    – Mark Neal
    May 17 at 2:57
  • Sorry I don't know how to post code so it is readable. Anyway, I wrote a functionA to create a trigger for my action function to run every minute. I wrote another functionB to delete the trigger. My action function checks if the minute == 0. If it is it performs my action. If not, it does nothing. I run functionA and it creates the trigger and my action function starts running every minute. At the top of the hour it does perform my action. I then run functionB, which deletes the trigger. All this works fine. But when I try to do this by setting triggers for functionA and functionB, it fails.
    – Mark Neal
    May 17 at 3:20
0
function createTimeTriggerEveryNMinutes()
 ScriptApp.newTrigger("freezeSunday")
   .timeBased()
   .everyMinutes(1)
   .create();
}
function freezeSunday() {
  const date = new Date();
  const minute = date.getMinutes();
  if (minute == 0) {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('A37:Z69').activate();
    spreadsheet.getRange('A2:Z34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    return;
    }
  return
}
function triggerDeleteHandler() {
  var Triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i &lt Triggers.length; i++) {
    if (Triggers[i].getHandlerFunction() == "freezeSunday") {
      ScriptApp.deleteTrigger(Triggers[i])
    }
  }
}

If I run createTimeTriggerEveryNMinutes() it successfully creates the trigger for freezeSunday(). freezeSunday() starts running every minute, and at the top of the hour is successfully performs the range copy/paste. If I then run triggerDeleteHandler() it deletes the trigger and freezeSunday() stops running.

But if I set a trigger for createTimeTriggerEveryNMinutes() to run at 12-1pm and a trigger for triggerDeleteHandler() to run at 1-2pm, it does not work. After createTimeTriggerEveryNMinutes() completes its run at a random time between 12pm and 1pm, freezeSunday() never runs. And if I check my triggers, on the line for freezeSunday it says in red letters "Disabled." If I click on the info icon it says "Trigger disabled for unknown reason."
Are triggered functions not allowed to create triggers for other functions?

2
  • A follow up question should be posted as new question, not as an answer.
    – Rubén
    May 17 at 14:17
  • It's an answer to the original question, based on your suggestions, that should work but doesn't. I wasn't sure where it should go. I will pose it as a new question.
    – Mark Neal
    May 18 at 2:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.