Problem

Approach/Solution

refactoring your scripts if required (optional)

info sheet

triggers queue

error handling

menus

Refactoring your scripts if required (optional)

‘Info’ sheet with spreadsheet keys column

Google automatic and own triggers queue

Error handling

Menus

CODE EXAMPLES

A few words about triggers example spreadsheet

Some key functions

RootTrigger

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

SetupInitialScheduledTriggers

RuntimeRootTrigger_YourFunctionGeneralIdea

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); }; };

Trigger_ToRestartQueue

Summary

Related Posts

In my previous blog post, I’ve provided a brief intro to Google Scripts and with this article, I’d like to share several useful code examples that might come handy.‘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.Here’re the key point of the approach I suggest:and useful: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 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.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).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.It’s better to set up Google event (on open) trigger which draws menus to allow users call functions manually in usual way. 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). 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.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.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)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 inAnd additional trigger to be executed each 10 mins to check triggers execution and restart queue, if there are some issues which prevent execution.Now you have a quite good solution on how to deal with long execution scripts. It allows every team member to get info about running trigger, triggers queue; you get error emails with detailed info right after it happens. I’d like to mention that by using such an approach, we’ve built a system where master sheet gets all data from 15 source spreadsheets and then passes back some info and updates additional spreadsheet (different data syncs every 1 hr, 4hrs and once a day). Hope this post will help you get some firm ground where you can stand and start developing useful things.