Skip to content

Apps Script based Workflow and Process Automation

License

Notifications You must be signed in to change notification settings

giantswarm/gapps-automation

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Library Test

Apps Script Automation Projects

Apps Script based automation for a company that uses GApps with Personio.

Uses Google's clasp tool for deployment.

Dependencies

Local Dependencies

Cloud Services for Deployment

  • Advanced Sheets Service
    Enable via Google Cloud Console or clasp apis enable sheets
  • Google Calendar API
    Enable via Google Cloud Console or clasp apis enable calendar
  • Personio API v1
    Create an API token with the necessary fields on Personio

Deployment

  1. Enable Google Apps Script API
    https://script.google.com/u/1/home/usersettings
    • Some scripts need additional configuration (service accounts, scopes, ...). Refer to the function comments for that.
  2. Login globally using clasp
    clasp login
    The login info is stored in ~/.clasprc.json.
  3. Change into the relevant project sub-directory
    cd personio-to-sheets
  4. Login "locally" inside the script project directory:
    clasp login --creds <OAUTH2_GOOGLE_CLOUD_PROJECT_CLIENT_SECRET_FILE>
  5. Upload from working copy to a new or existing Apps Script project
    The personio-to-sheets sub project is used as an example.
    • Link and push to existing Apps Script project:
      clasp list | sed 's/https\:\/\/script\.google\.com\/d\///g'
      SCRIPT_ID={SCRIPT_ID_COPIED_FROM_ABOVE} make personio-to-sheets/
      
    • Link and push to new project (with or without parent sheets/docs document):
      cd personio-to-sheets/
      clasp create --help  # see documentation for parameter '--type'
      clasp create --title "$(basename "$(pwd)")" --type standalone --parentId OPTIONAL_PARENT_DOCUMENT_ID
      cd ..
      SCRIPT_ID={SCRIPT_ID_COPIED_FROM_ABOVE} make personio-to-sheets/
      
  6. Configure properties for the scripts using the builtin helper function:
    clasp run 'setProperties' --params '[{"KEY": "VALUE"}, false]'
  7. Install automated script functions:
    # install a script (argument "5" could be the delay in minutes)
    clasp run 'install' --params '[5]'  
  8. Uninstall automated script function:
    clasp run 'uninstall' # remove trigger(s) for the relevant project

Using Makefile

A Makefile is provided to ease development and deployment on CI.

Examples

  • Build node library in lib-output and run local tests:

    Running the tests requires the node binary to be installed. The installed node must implement fetch() (>= v16).

    make clean && make test

  • Assemble and push all projects (must be locally logged in, see above):

    make

  • Assemble and push the logged in project options-sheets:

    make options-sheets/

  • Clean all generated files (for example $project/lib or lib-output):

    make clean

Usage

Apps Scripts projects have differing forms of deployments.

Many are deployed as API Endpoint to be called via REST API or triggers.

Other possibilities include SheetUtil Macros and GApps Addons.

GCloud

Optionally install gcloud (CLI) to manage execution, settings and logging of the automation:

https://cloud.google.com/sdk/docs/cheatsheet

Library

According to Google docs Apps Script Libraries should be used sparingly to avoid decreasing performance.

We maintain a shared directory based library, called lib, which includes all our shared code for in-house App Script work.

This directory is copied into each dependent project via Makefile.

Hints

Attaching to a Sheets/Docs/Forms Container

The Google Sheets/Forms UI makes it hard to attach a script project when using multiple Google accounts.

To attach a project, get the document ID and use the following clasp command line in the project subdirectory:
clasp create --parentId {SHEET_ID} --rootDir .

The logged in clasp user account must have Editor access to the document.

Building just the Lib

For testing or use in other projects (possibly targetting another runtime) building just the library part of this project may be required.

To build just lib-output/lib.js run the following:

# the directory lib-output will be automatically created if it doesn't exist
make lib