Contents hide
Problem
‘Exceeded maximum execution time’ error message appears while executing a Google Spreadsheet scripts. It usually pops up when a 6 min time limit per script execution is exceeded. Let’s see how we can deal with this if our execution requires more time.Approach/Solution
Here’re the key point of the approach I suggest:- refactoring your scripts if required (optional)
- info sheet
- triggers queue
- error handling
- menus
Refactoring your scripts if required (optional)
The idea is to decompose the big functions related to your data to a smaller size which will help reduce time execution and avoid situations when function that directly operates data is being executed for more than 6 min. Example: if you need to copy some huge amount of cells to 10 other spreadsheets, you can, first of all, create a function that gets start row and end row numbers as parameters to limit amount of data per function call and organise a loop to cover all rows. And the second improvement is to specify target spreadsheet as a parameter and a loop to cover all spreadsheets. In this case, we end up having 1 function with 3 parameters – startrownum, endrownum, targetspreadsheet – which directly operates data and 1 common function which contains 2 loops. As we know, while any directly operating with data (DOWD) function execution time exceeds 6 mins, we’re are not able to proceed further. So, now when all DOWD functions are fixed, we are ready to move to the info sheet.‘Info’ sheet with spreadsheet keys column
Info sheet holds information about parameters that are selected to organise loops to reduce execution time for DOWD functions. Columns show last datetime of rows ranges sync and rows show target spreadsheet. For example:
Tip: you can use info sheet to keep the data that shows whether a specific target spreadsheet should be processed. After we complete setup of the info sheet, we can move on to triggers.Google automatic and own triggers queue
We will use Google time-driven triggers. Here’re a few words about their specifics: – Google does not provide trigger start time programmatically; – Google manages exact start time regardless of whether you specify it or not. This means that if you create 3 triggers with +7 min start time for each, they can be executed in another order; – triggers are no longer visible to the other team members; it was a bad surprise. Therefore, it’d be better to build own triggers queue and manage it, but we still may need Google managed triggers to run different groups of functions every (1hr, 4hr, 24hr).Error handling
There are only a few options – Google built-in and your own. Unfortunately, there’re no possibility to access trigger notification properties which can be changed via UI. And this makes us update each trigger settings manually and use standard Google notification email or setup our own function with required data like triggers queue.Menus
It’s better to set up Google event (on open) trigger which draws menus to allow users call functions manually in usual way.
Code examples
A few words about triggers example spreadsheet
There is a Triggers example spreadsheet that’ll demonstrate how everything described above works on the fly.
To show key points of the described approach, we use LongExecutionFunction that crashes because of timeout. It should fill in 2 rows – 1st is dates of Mondays of 2013 and 2d is dates of Sundays. To avoid useless complexity in our example, we simply set delay after filling Mondays. Then we refactor this long execution function to YourGeneralIdeaFunction and YourGeneralIdeaFunctionDOWD. Each execution of DOWD function is less than Google timeout, but general function where these calls are located still can not be run at a time without timeout exception. After that we have loop parameters – Monday and Sunday which we place as last date&time columns to our sync_info sheet. And, finally, we create our triggers to run it manually and automatically every hour – RootTrigger_ManualStartYourFunctionGeneralIdea and RootTrigger_YourFunctionGeneralIdea. Then we update SetupInitialScheduledTriggers and run to avoid manual creating of every-hour triggers (it saves some time even if you set up scheduled triggers only once, but you will not be able to set Google error sending immediately for each trigger as you may do with UI).Some key functions
RootTrigger
RootTrigger_ – prefix in function name means that it will be called by Google managed schedule (for example we have several different functions to be called every 1hr, 4hr, 24hr). Also, we use this naming convention in ManualStart triggers, but here it means that person calls it manually from menu.function RootTrigger_YourFunctionGeneralIdea () {
// put trigger to queue as last element
setTriggerToQueue ( "RuntimeRootTrigger_YourFunctionGeneralIdea" );
};
function RootTrigger_ManualStartYourFunctionGeneralIdea () {
// put trigger to queue as first element
setTriggerToQueue ( "RuntimeRootTrigger_ManualStartYourFunctionGeneralIdea", 0);
};setTriggerToQueue
setTriggerToQueue function places 1d array = [trigger name, created user email, created time] to triggers queue in positioninqueue position. Trigger queue is saved to script property whose name can be found in triggerqueueproperty global variable. Script properties are available to all users and it allows to get a list of triggers for them.SetupInitialScheduledTriggers
Here is a setup function that will help avoid manual creation of the initial set of triggers (note: you can hold this info too in another script property)RuntimeRootTrigger_YourFunctionGeneralIdea
This is the body of typical trigger function to run refactored long execution function. In case of errors during execution there is try catch clause to email to people specified in notifyingpeople.function RuntimeRootTrigger_YourFunctionGeneralIdea () {
try {
var thistrigger = "RuntimeRootTrigger_YourFunctionGeneralIdea";
// delete previous runtime version of this trigger, it means that it fails for some reason - timeout or smth else
DeleteRuntimeClockTriggerByHandlerFunction ( thistrigger );
// here we check there are no scheduled|running runtime triggers and do not check queue because root calls sets to queue
if ( !isRuntimeTriggersWithIgnore ( thistrigger ) ) {
// we created duplicate trigger of this to workaround script crash because of timeout on main function call
var delayminutes = minutesdelays * 60 * 1000; // 7 mins as for script execution are 5 mins only
// we create trigger one by one untill execute it successfully
var nexttryTrigger = ScriptApp.newTrigger( thistrigger ).timeBased()
.after(delayminutes)
.create();
// setting running trigger to property to have ability to check - is it broken
outputStartingTriggerAsProperty (thistrigger);
//MAIN FUNCTION CALL is here, place it under this comment
YourGeneralIdeaFunction ();
// deleting next duplicate trigger, it means that main function is executed wo troubles
ScriptApp.deleteTrigger(nexttryTrigger);
// starting 0 element from triggers queue in 20 sec, to allow this trigger finishes
if ( !isTriggerQueueEmptyWithIgnore () ) {
ScriptApp.newTrigger( getTriggerQueueItem (0) [0] ).timeBased()
.after(20*1000)
.create();
};
// deleting 0 element in triggers queue
deleteTriggerQueueItem (0);
} else {
throw (Error("nice, some error happens"));
};
} catch (e) {
var body = getEmailBodyForError (e);
for (var i=0;i<notifyingpeople.length;i++)
MailApp.sendEmail(notifyingpeople[i], "Google scripts error report", body);
};
};