1

I would like my function freezeSunday to run at exactly at 1pm on Sunday. Since it does not appear that you can trigger a function to run at a precise time (only at a randomly selected time within a range), I wrote the following code.

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);
    }
}
function triggerDeleteHandler() {
  var Triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < Triggers.length; i++) {
    if (Triggers[i].getHandlerFunction() == "freezeSunday") {
      ScriptApp.deleteTrigger(Triggers[i])
    }
  }
}

The first function sets a trigger for the freezeSunday function to run every minute. The freezeSunday function only performs its action when the minute is 0. Otherwise it does nothing. The last function deletes the "every minute" trigger. All of this works fine if I run the first function, createTimeTriggerEveryNMinues, manually. The freezeSunday function starts running every minute, and at 1pm it performs its action.

However, if I set a trigger to run createTimeTriggerEveryNMinutes sometime between 12-1pm instead of running it manually, things don't work. The freezeSunday function never runs, and the trigger for it in my list of triggers displays "Disabled" in red text. If I click on the info icon for that, it says "Trigger disabled for unknown reason."

Is it not permitted to set a trigger for a function from another triggered function?

2
  • The script has some syntax errors i.e. createTimeTriggerEveryNMinutes()has missing a {, the for statement has &lt instead of <. If this errors aren't present in you Apps Script project, please fix them here. (Google Apps Script should not allow you to save the project with syntax errors). P.S. Also, I suggest to avoid using title casing for naming regular variables, instead use camelCase, i.e. instead of Triggers use triggers. Use title case for naming classes. Ref. google.github.io/styleguide/jsguide.html
    – Rubén
    May 20 at 17:45
  • By the way, the triggerDeleteHandleris not called by freezeSunday.
    – Rubén
    May 20 at 17:56

2 Answers 2

0

Replace

spreadsheet.getRange('A37:Z69').activate();
spreadsheet.getRange('A2:Z34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

by

spreadsheet.getRange('A2:Z34').copyTo(spreadsheet.getRange('A37:Z69'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

This because time-driven triggers aren't able to activate ranges as this is something done in the Google Sheets user interface.

10
  • In both cases I describe above, the one that worked and the one that didn't, a trigger is set for the function freezeSunday. I'll try what you say, but I'm not optimistic.
    – Mark Neal
    May 20 at 14:54
  • After that change freezeSunday still never runs, and the trigger for it is still labeled with "Disabled."
    – Mark Neal
    May 20 at 17:25
  • @MarkNeal after fixing the errors in the code included in the question (mentioned in a question comment) and applying the change suggested here, the script is working correctly for me. I suggest you to start from scratch 1. Delete the Apps Script project, 2. Create a new bounded Apps Script project (Open the spreadsheet, click Extensions > Apps Script), 3. add the code, 4. Run createTimeTriggerEveryNMinutes
    – Rubén
    May 20 at 17:59
  • I think you might be misunderstanding what is not working for me. If I run createTimeTriggerEveryNMinutes directly, everything works fine. The trigger is set for freezeSunday and it runs. But if I set a trigger for createTimeTriggerEveryNMinutes to run, the trigger it sets for freezeSunday is disabled and freezeSunday never runs.
    – Mark Neal
    May 20 at 21:21
  • 1
    Yes. I actually created a new spreadsheet and then copy and pasted the code. Same result. The issue appears to be in using the Apps Script interface to create the trigger rather than doing it programatically. I'm posting an answer to show this.
    – Mark Neal
    May 22 at 0:10
0

I think I have discovered the issue. In the code below, if I use the createDoubleTrigger function to create the triggers for createTimeTriggerEveryNMinutes and triggerDeleteHandler, everything works correctly.

If instead I create triggers for createTimeTriggerEveryNMinutes and triggerDeleteHandler using the pull-down menus in the Apps Script interface, then freezeSunday never runs, and the trigger for it created by createTimeTriggerEveryNMinutes shows "Disabled."

I think the general solution is to always created triggers programatically.

function createDoubleTrigger() { ScriptApp.newTrigger("createTimeTriggerEveryNMinutes") .timeBased() .onWeekDay(ScriptApp.WeekDay.SUNDAY) .atHour(12) .nearMinute(40) .everyWeeks(1) .create(); ScriptApp.newTrigger("triggerDeleteHandler") .timeBased() .onWeekDay(ScriptApp.WeekDay.SUNDAY) .atHour(13) .nearMinute(20) .everyWeeks(1) .create(); } 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('A2:Z34').copyTo(spreadsheet.getRange('A37:Z69'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); } } function triggerDeleteHandler() { var Triggers = ScriptApp.getProjectTriggers(); for (var i = 0; i < Triggers.length; i++) { if (Triggers[i].getHandlerFunction() == "freezeSunday") { ScriptApp.deleteTrigger(Triggers[i]) } } }

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.