Following are the things upon which google scripts can be written:
1. Google forms
2. Spreadsheets
3. Documents
4. Integrating with third party APIs or any google API for that matter using OAuth
To start coding, one needs to know javascript as a pre-requisite. But apps script uses a specialized library which exposes classes and methods for different google suite products. Below are the ones which I got to explore:
- SpreadsheetApp for reading/writing from/to google spreadsheet
- FormApp for reading/writing from/to google forms. The only caveat of form is that on submit, we cannot preprocess the form before submit. One has to write an explicit trigger to trigger some code after submit. On initializing as well, one can write some code to set/ correct form data.
- One can use Session variable of the apps script which can be used to get the current running user and their basic information(like email, name and others).
- UrlFetchApp to fetch response generally from a website or an API. Got to work with custom APIs which had Auth Token, use Google Spreadsheet, Google secrets manager API using Url FetchApp
- Use custom libraries in your script like OAuth2. Use of things like OAuth ensures that we can do gsuite automation without the hassle of asking employees to authorize their access to google resources for the first time the script app runs.
- HTMLService when we want to expose Google script as HTML output.
Below is the sample code which does the same:
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
The function doGet() is the entry point for HTML output. Here the function looks for a file called Index.html and loads it. One can create CSS, javascript and reference them in the Index page. But the Stylesheet and Javascript file have to be added as .html in the project
References:
- Events which can be triggered in google https://developers.google.com/apps-script/guides/triggers/events#form-submit_1
- Scripting help/quickstart : https://script.google.com/home/start
- Calling response by API - https://developers.google.com/apps-script/guides/services/external
- Learning video to list dropdown from sheets: https://www.youtube.com/watch?v=o3AL7ASI_cA
- HTML page - https://developers.google.com/apps-script/guides/html
- OAuth and service account configuration for apps script https://medium.com/@daubejb/how-to-use-gcp-service-accounts-with-google-apps-script-projects-to-automate-actions-in-g-suite-7020a520bef7