{"id":5185,"date":"2013-09-16T14:44:38","date_gmt":"2013-09-16T11:44:38","guid":{"rendered":"http:\/\/railsware.com\/blog\/?p=5185"},"modified":"2021-08-11T18:45:58","modified_gmt":"2021-08-11T15:45:58","slug":"useful-google-spreadsheet-script-triggers","status":"publish","type":"post","link":"https:\/\/railsware.com\/blog\/useful-google-spreadsheet-script-triggers\/","title":{"rendered":"Useful Google Spreadsheet Script Triggers"},"content":{"rendered":"In my <a title=\"Intro to Google Scripts\" href=\"https:\/\/railsware.com\/blog\/intro-to-google-scripts\/\" target=\"_blank\" rel=\"noopener noreferrer\">previous<\/a> blog post, I&#8217;ve provided a brief intro to Google Scripts and with this article, I&#8217;d like to share several useful code examples that might come handy.\n<h2>Problem<\/h2>\n&#8216;Exceeded maximum execution time&#8217; error message appears while executing a Google Spreadsheet scripts.\nIt usually pops up when a 6 min time limit per script execution is exceeded. Let&#8217;s see how we can deal with this if our execution requires more time.\n<h2>Approach\/Solution<\/h2>\nHere&#8217;re the key point of the approach I suggest:\n<ul>\n \t<li>refactoring your scripts if required (optional)<\/li>\n \t<li>info sheet<\/li>\n \t<li>triggers queue<\/li>\n<\/ul>\nand useful:\n<ul>\n \t<li>error handling<\/li>\n \t<li>menus<\/li>\n<\/ul>\n<h3>Refactoring your scripts if required (optional)<\/h3>\nThe 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.\nExample: 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.\nIn this case, we end up having 1 function with 3 parameters &#8211; startrownum, endrownum, targetspreadsheet &#8211; 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&#8217;re are not able to proceed further. So, now when all DOWD functions are fixed, we are ready to move to the info sheet.\n<h3>&#8216;Info&#8217; sheet with spreadsheet keys column<\/h3>\nInfo 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:\n<a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1546.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5448 size-full\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1546.png\" alt=\"2013-09-05_1546\" width=\"1082\" height=\"442\" \/><\/a>\nTip: you can use info sheet to keep the data that shows whether a specific target spreadsheet should be processed.\nAfter we complete setup of the info sheet, we can move on to triggers.\n<h3>Google automatic and own triggers queue<\/h3>\nWe will use <a href=\"https:\/\/developers.google.com\/apps-script\/execution_time_triggers\" target=\"_blank\" rel=\"noopener noreferrer\">Google time-driven triggers<\/a>. Here&#8217;re a few words about their specifics:\n&#8211; Google does not provide trigger start time programmatically;\n&#8211; 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;\n&#8211; triggers are no longer visible to the other team members; it was a bad surprise.\nTherefore, it\u2019d 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).\n<h3>Error handling<\/h3>\nThere are only a few options &#8211; <a href=\"https:\/\/developers.google.com\/apps-script\/understanding_triggers?hl=en#TriggerFail\" target=\"_blank\" rel=\"noopener noreferrer\">Google built-in<\/a> and your own. Unfortunately, there&#8217;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.\n<h3>Menus<\/h3>\nIt\u2019s better to set up <a href=\"https:\/\/developers.google.com\/apps-script\/understanding_triggers?hl=en#Simple\" target=\"_blank\" rel=\"noopener noreferrer\">Google event (on open) trigger<\/a> which draws menus to allow users call functions manually in usual way.\n<a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1614.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5449 size-full\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1614.png\" alt=\"2013-09-05_1614\" width=\"1027\" height=\"392\" \/><\/a>\n<h2>Code examples<\/h2>\n<h3>A few words about triggers <a href=\"https:\/\/docs.google.com\/spreadsheet\/ccc?key=0AknwBxzafq1HdF9pS2tNZk9EMm96R0xxTEtyay1sM1E#gid=25\" target=\"_blank\" rel=\"noopener noreferrer\">example spreadsheet<\/a><\/h3>\nThere is a Triggers example spreadsheet that&#8217;ll demonstrate how everything described above works on the fly.\n<a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/googlescripts.png\">\n<\/a><a href=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1726.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5450 size-full\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1726.png\" alt=\"2013-09-05_1726\" width=\"890\" height=\"440\" \/><\/a>\nTo show key points of the described approach, we use <em>LongExecutionFunction<\/em> that crashes because of timeout. It should fill in 2 rows &#8211; 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.\nThen we refactor this long execution function to <em>YourGeneralIdeaFunction<\/em> and <em>YourGeneralIdeaFunctionDOWD<\/em>. 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.\nAfter that we have loop parameters &#8211; Monday and Sunday which we place as last date&amp;time columns to our sync_info sheet.\nAnd, finally, we create our triggers to run it manually and automatically every hour &#8211; <em>RootTrigger_ManualStartYourFunctionGeneralIdea<\/em> and <em>RootTrigger_YourFunctionGeneralIdea<\/em>. Then we update <em>SetupInitialScheduledTriggers<\/em> 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).\n<h2>Some key functions<\/h2>\n<h3>RootTrigger<\/h3>\n<em>RootTrigger_<\/em> &#8211; 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 <em>ManualStart<\/em> triggers, but here it means that person calls it manually from menu.\n<pre>function RootTrigger_YourFunctionGeneralIdea () {  \n  \/\/ put trigger to queue as last element\n  setTriggerToQueue ( \"RuntimeRootTrigger_YourFunctionGeneralIdea\" );\n};\n\n\nfunction RootTrigger_ManualStartYourFunctionGeneralIdea () {\n  \/\/ put trigger to queue as first element\n  setTriggerToQueue ( \"RuntimeRootTrigger_ManualStartYourFunctionGeneralIdea\", 0);    \n};<\/pre>\n<h3>setTriggerToQueue<\/h3>\n<em>setTriggerToQueue<\/em> function places 1d array = [trigger name, created user email, created time] to triggers queue in <em>positioninqueue<\/em> position. Trigger queue is saved to script property whose name can be found in <em>triggerqueueproperty<\/em> global variable.\nScript properties are available to all users and it allows to get a list of triggers for them.\n<h3>SetupInitialScheduledTriggers<\/h3>\nHere 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)\n<h3>RuntimeRootTrigger_YourFunctionGeneralIdea<\/h3>\nThis 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 <i>notifyingpeople<\/i>.\n<pre><code>function RuntimeRootTrigger_YourFunctionGeneralIdea () {  \n\n  try {\n  var thistrigger = \"RuntimeRootTrigger_YourFunctionGeneralIdea\";\n \n    \/\/ delete previous runtime version of this trigger, it means that it fails for some reason - timeout or smth else\n    DeleteRuntimeClockTriggerByHandlerFunction ( thistrigger );\n \n    \/\/ here we check there are no scheduled|running runtime triggers and do not check queue because root calls sets to queue\n    if ( !isRuntimeTriggersWithIgnore  ( thistrigger ) ) {     \n     \n      \/\/ we created duplicate trigger of this to workaround script crash because of timeout on main function call\n      var delayminutes = minutesdelays * 60 * 1000; \/\/ 7 mins as for script execution are 5 mins only\n    \n      \/\/ we create trigger one by one untill execute it successfully\n      var nexttryTrigger = ScriptApp.newTrigger( thistrigger ).timeBased()    \n        .after(delayminutes)\n        .create();\n        \n      \/\/ setting running trigger to property to have ability to check - is it broken\n      outputStartingTriggerAsProperty (thistrigger);\n      \n      \/\/MAIN FUNCTION CALL is here, place it under this comment    \n      YourGeneralIdeaFunction ();\n    \n      \/\/ deleting next duplicate trigger, it means that main function is executed wo troubles\n      ScriptApp.deleteTrigger(nexttryTrigger);\n    \n      \/\/ starting 0 element from triggers queue in 20 sec, to allow this trigger finishes\n      if ( !isTriggerQueueEmptyWithIgnore () ) {\n        ScriptApp.newTrigger( getTriggerQueueItem (0) [0] ).timeBased()    \n          .after(20*1000)\n          .create();\n      };\n    \n      \/\/ deleting 0 element in triggers queue\n      deleteTriggerQueueItem (0);\n    \n    } else {\n      throw (Error(\"nice, some error happens\"));\n    };\n    \n  } catch (e) {\n    var body = getEmailBodyForError (e);\n\n    for (var i=0;i&lt;notifyingpeople.length;i++)\n      MailApp.sendEmail(notifyingpeople[i], \"Google scripts error report\", body);\n  };\n    \n};<\/code><\/pre>\n<h3>Trigger_ToRestartQueue<\/h3>\nAnd additional trigger to be executed each 10 mins to check triggers execution and restart queue, if there are some issues which prevent execution.\n<h2>Summary<\/h2>\nNow 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\u2019d like to mention that by using such an approach, we&#8217;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).\nHope this post will help you get some firm ground where you can stand and start developing useful things.\n<h2>Related Posts<\/h2>\n<ul>\n \t<li><a title=\"Intro to Google Scripts\" href=\"https:\/\/railsware.com\/blog\/intro-to-google-scripts\/\" target=\"_blank\" rel=\"noopener noreferrer\">Intro to Google Application Scripts (GAS)<\/a><\/li>\n \t<li><a title=\"Copy function in Google Scripts\" href=\"https:\/\/railsware.com\/blog\/google-spreadsheet-scripts-useful-functions-copying-with-custom-conditions\/\" target=\"_blank\" rel=\"noopener noreferrer\">Copying with custom conditions in Google Application Scripts<\/a><\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"<p>In my previous blog post, I&#8217;ve provided a brief intro to Google Scripts and with this article, I&#8217;d like to share several useful code examples that might come handy. Problem &#8216;Exceeded maximum execution time&#8217; error message appears while executing a Google Spreadsheet scripts. It usually pops up when a 6 min time limit per script&#8230;<\/p>\n","protected":false},"author":54,"featured_media":9470,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[],"coauthors":["Oleg Shaydenko"],"class_list":["post-5185","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development"],"acf":[],"aioseo_notices":[],"categories_data":[{"name":"Engineering","link":"https:\/\/railsware.com\/blog?category=development"}],"post_thumbnails":"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2013\/09\/2013-09-05_1546.png","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/5185","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/users\/54"}],"replies":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/comments?post=5185"}],"version-history":[{"count":102,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/5185\/revisions"}],"predecessor-version":[{"id":13940,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/5185\/revisions\/13940"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media\/9470"}],"wp:attachment":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media?parent=5185"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/categories?post=5185"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/tags?post=5185"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/coauthors?post=5185"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}