How to schedule functions in Google AppScipts?
I was assigned a task of creating an application that could be scheduled periodically in Google AppScripts. This application should appear as a Modal Window in a spreadsheet whenever the user requests for it from the menu. After which, if the user selects, it triggers a function as per the scheduling of the user . This task can be looked at in the following modular way:
- Creating the HTML file for the modal window for the necessary components and functionalities could be performed.
- Creating the Google AppScripts function to handle the output from the Modal window when the user presses the submit Button.
The HTML part of this application isn’t really complicated because it contains nothing more than empty <div>’s as container’s for the rest of the elements to be added. Labels and select menus are added dynamically. Some inline styling has been added from as per the Google CSS standards. This makes the application and its components similar to elements in Google Add-ons.
<link rel=“stylesheet” href=“https://ssl.gstatic.com/docs/script/css/add-ons1.css”>
This part mainly controls everything on the Modal Window. Starting with the checkbox, a toggle functionality for scheduling and removing scheduled functions from the spreadsheet is added. Changing the state of the checkbox , adds the first <SELECT> menu to our Modal Window, dynamically. (Creating the select element using document.createElement() function, with it’s required functionalities and appending it to the corresponding <div> as a child node in the HTML ). Deselecting the checkbox removes all the present Triggers on the spreadsheet by calling a function in AppScripts.
One advantage that we have in this task is that the values or options in the select menus are constant and same for every user who selects some option, so the static arrays were enough. All I had to was to was add them to the options of the select menu as required.
Similarly, upon selecting the required period, corresponding select menu’s get added to the respective <div>’s. The submit button takes the data of the all the three <SELECT> menus and sends it to the AppScripts function . The submit and cancel button also close the modal window by calling google.script.host.close().
This part of the code contains mainly two types of functions. First type, responding to events in the Modal Window and the second type are the, scheduling functions to be triggered when the user submits his/her choice. For passing data, an array of the selected options is used as a parameter in the Google AppScripts function, which parses the Array and calls the necessary scheduled function to trigger the time based event /function as required. This is the link to the documentation of the time based events and how to trigger functions . This is a sample of how the scheduled function gets triggered:
ScriptApp is the Class for calling functions for manipulating various scripts and their data. The function NEWTRIGGER takes the parameter of the function name , which it is supposed to execute as per the scheduled period. It returns a Trigger for the script. The function TIMEBASED specifies what type of event is going to occur, and returns a CLOCK type TRIGGERBUILDER for the script. After which a variety of alternatives are present in the form of functions as per our requirements! GO through this link to know more about other schedules.
- Presently the app gets triggered every hour , no matter what we select on the select menus.
- The application doesn’t remember the user’s previous choice to add a trigger or not, it may keep on adding as many triggers as we want, each getting triggered on a different point in an hour.
Right now the function just adds the present time (either EDT or IST) to the first cell of the spreadsheet. We would want the scheduled function to analyze and comprehend data from Google Analytics.