googlescripts

Useful Google Spreadsheet Script Triggers

| 8 Comments

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.

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

and useful:

  • 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: 2013-09-05_1546
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. 2013-09-05_1614

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.
2013-09-05_1726
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.

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.

Trigger_ToRestartQueue

And additional trigger to be executed each 10 mins to check triggers execution and restart queue, if there are some issues which prevent execution.

Summary

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.

Related Posts

Share
* Railsware is a premium software development consulting company, focused on delivering great web and mobile applications. Learn more about us.
  • Pingback: Useful Google Spreadsheet Script Triggers | Rai...

  • Pasi Illikainen

    Hi, i got an error when trying execute menu creation script at your example. It says “You do not have access to library RoadmapScripts, used by your script, or it has been deleted.”

    • Oleg Shaydenko

      Hi Pasi,

      Thanks a lot for your feedback! I guess that you did
      local copy and trying to execute it. If so you should remove dependency
      on RoadmapScripts with the following steps (now it’s removed in
      published spreadsheet).

      0. Open ScriptEditor
      1. click Resources-> Manage libraries
      2. remove RoadmapScripts line
      3. click Save

      Also, do not forget to update spreadsheetid in StartHere.gs with your new spreadsheetid.

      Hope it helps

      • Pasi Illikainen

        Hi Oleg,

        Yes it worked now. Thank you! And thank you for your great articles! Im looking different solutions to improve coming information handling and google spreadsheet with scripts will be the key i think.

        • Oleg Shaydenko

          Google scripts allow libraries (More details on google help) and RoadmapScripts is our internal library with biz specific logic inside.

  • Ganesh

    Hi, I am after a devloper who can code me to manually control trigger inside a spreadsheet.
    can u guide me if you can help or direct to a person who can help, i am also finding it difficult converting old sheets to new sheets since google right now don’t have a solution automated.

    Thanks & regards
    Ganesh

    • Yaroslav Lazor

      Just copy sheets one by one. From most independent (one that has no formulas linking to other sheets) to most dependent.
      After copying lets same “my sheet” from old to new – it will be names “copy of my sheet” just rename it back to “my sheet”.
      Did it many times – works great.

  • Lionel COSTE

    Hello and thanks for this useful topic. I actually use Andrew Stillamn’s Autocrat script that is so amazing and well thinked. Unfortunatly, when i’m running it on large amount of cells in a big data sheet, i meet the issue Timeout, regarding the more than 6 minutes running elapsed… So I try to add some Spreadsheet flush and other tips to avoid this kind of issue. But as a newbie, I often do headscratching and brainstrorm. Maybe your both knowledges should lead to a genialissim script available even for big amounts of data spreadsheets.

    Lionel COSTE

    absalon974@gmail.com

Want to get more of Railsware blog?

RSS FEED

We're always ready to help!

CONTACT US