Sunday, May 31, 2020

Google Apps scripts, features and gotchas

At work, we had an interesting problem to solve. There were surveys being sent out in google forms and we had to process the response before making the employees follow the next steps of action. When I started researching, I came across google apps scripts( script.google.com ) where one can code actions on scripts, sending emails and other such interesting things.

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
To deploy the project, one can just go to Publish and find the various options to publish the project.
One can deploy the project as 'Web app' which will create a script executable. This script executable can be referenced in a web page or a google site. The project will appear as a web frame.
For deployment as a forms addon, one can start coding by going to a google form. In the editable mode of the form, one can click on script editor and start coding for the form actions. 

Few things which can be taken advantage of:
The variables defined in the project will be accessible throughout the project in any file of the project. This brings me to the next best practice which is segregating functions in a different file so that it is easier to maintain.
Depending on which class is used, the scopes of the project gets updated automatically. If SpreadsheetApp is used, then the scope of https://www.googleapis.com/auth/spreadsheets is added.
This is later used by the application to challenge for authorization for the user to get required permissions for the script to run.


References:

Posted by at No comments:
Subscribe to: Posts (Atom)