Useful Google Spreadsheet Script Triggers

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

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

• Intro to Google Application Scripts (GAS)
• Copying with custom conditions in Google Application Scripts