1 - INTRODUCTION
Dear valuable members of the community,
This post is my personal long-time promise to @praveen that I’ll write about nearly a year ago but couldn’t be able to keep my promise and spare some time because of our work load as an AppSheet Partner and Developer. Now I have found some time, I decided to write it down. Hope you can find a benefit of this post for your apps and/or projects.
The best resources - amongst many others - can be listed as:
This will be a bit multiple post items linked to each other under this thread, so thank you for your patience.
2 - THE DEVELOPMENT PLATFORM
Any Google Apps Script project, can be developed in 2 platforms or editors:
- In a Google Document container (gDoc, gSheet etc.)
- In a Standalone Script File (needs Google Apps Script add-on attached to your gDrive)
To access the Script Editor from any Google Document, you can simply choose Tools > Script Editor. To attach Google Apps Script add-on to your gDrive, please refer to my older post here:
3 - GOOGLE APPS SCRIPT TRIGGERS
There are 2 kinds of triggering (or you can simply call it as execution) with Google Apps Script:
- Simple Triggers: Triggers let Apps Script run a function automatically when a certain event, like opening a document, occurs. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which executes when a user opens a Google Docs, Sheets, Slides, or Forms file.
- Installable Triggers: Like simple triggers, installable triggers let Apps Script run a function automatically when a certain event, such as opening a document, occurs. Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.
For both simple and installable triggers, Apps Script passes the triggered function an event object that contains information about the context in which the event occurred. Installable triggers offer more capabilities than simple triggers but must be activated before use.
To use a simple trigger, simply create a function that uses one of these reserved function names:
onOpen(e)runs when a user opens a spreadsheet, document, presentation, or form that the user has permission to edit.
onEdit(e)runs when a user changes a value in a spreadsheet.
onInstall(e)runs when a user installs an add-on.
doGet(e)runs when a user visits a web app or a program sends an HTTP GET request to a web app.
doPost(e)runs when a program sends an HTTP POST request to a web app.
e parameter in the function names above is an event object that is passed to the function. The object contains information about the context that caused the trigger to fire, but using it is optional.
There are several installable triggers for G Suite applications:
- An installable open trigger runs when a user opens a spreadsheet, document, or form that they have permission to edit.
- An installable edit trigger runs when a user modifies a value in a spreadsheet.
- An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.
- An installable form submit trigger runs when a user responds to a form. There are two versions of the form-submit trigger, one for Google Forms itself and one for Sheets if the form submits to a spreadsheet.
- An installable calendar event trigger runs when a user’s calendar events are updated—created, edited, or deleted.
You can use installable triggers in standalone and bound scripts.
In order to execute a Google Apps Script, with any data change in the back-end which is manipulated by an AppSheet app, you need to use an installable change trigger which uses a Google Document as a container.
My partner @Bellave_Jayaram is also a coding expert. Jay, no need to say but your contributions are always welcome bud.