scripts/

Overview

Google Apps Script is used by the ACM to extend the functionality of Google Spreadsheets for the ACM spreadsheet.

The files in the scripts/ directory are synced to the Google Apps Script project using the clasp command line tool.

The Google Apps Script project should have time-based triggers enabled in order to send e-mails, and must be published as a web app in order for the ACM website to be able to interact with spreadsheet data.

Design

The Javascript functions in scripts/ perform three main tasks based on the Google Spreadsheet data:

• send daily usage and weekly billing e-mails,
• provide access to Google Spreadsheet data to outside sources, such as the ACM website,
• extend the Google Spreadsheet UI with additional functionality.

Each of the functions responsible for the tasks above accesses the Google Spreadsheet data using the the Google Apps Script Spreadsheet API classes: Range, NamedRange, and Sheet.

Send Usage / Billing E-mails

The MailApp Google Apps Script class is used to send usage e-mails directly to Makers, CC'd to hello.nosp@m.@mak.nosp@m.erlab.nosp@m.s.co.nosp@m.m.

The e-mail sending process is triggered via time-based Google Apps Script triggers which scan all data rows in the "Activity, De-duped" sheet. Each row has a specific timestamp column (a separate column for each type of e-mail) which must be empty for the row to be processed in the e-mail summary.

There are two different types of e-mail which share the same e-mail template and processing logic:

• "usage" e-mails, processed daily at 5pm ~ 6pm, which are sent to the Maker's e-mail address and CC'd to hello@makerlabs.com. These summarize the usage for a single Maker, across all machines, in the last day.
• "billing" e-mails, processed weekly on Friday afternoons, which are sent to hello@makerlabs.com and summarize the monthly usage for multiple Makers, across all machines, for all Makers which have their Billing Date in that week.

Sending of e-mails is performed by the following two trigger functions in scripts/trigger:

• triggerActivitySendUsageEmails sends possibly multiple usage e-mails.
• triggerActivitySendBillingEmails sends the billing e-mail.

Both of these trigger functions rely on the same templates and rendering function sendActivitySummaryEmail.

The HTML content for the e-mail is configured in the HTML files in the scripts/templates/ directory, as described in the Branded E-mail Templates configuration section.

Web App Handlers To Access Spreadsheet Data From Website

In order to provide access to the Google Spreadsheet data from the ACM Website, the Web Apps Google Apps Script functionality is used. The doGet function is the entrypoint to all HTTP requests to the handler. Only a single URL path is allowed, so the ?href= query string argument is checked to determine which specific view to use.

The following two views are currently used:

• ?href=next-makerlabs-id: viewNextMakerLabsId returns the next available MakerLabs ID.
• ?href=users-fields: viewUsersFields returns the "Users" sheet section headers, column headers and validation/widget type in JSON format.

(Optional) Enforce Formatting In "Users" Sheet

The enforceFormattingInRange function checks the formatting for the first data row (non-header/frozen row) in the "Users" sheet, and ensures that formatting changes in other rows are reverted. If this function is configured with the onChange trigger function and/or the onEdit trigger function then changing the first data row is the only way to affect formatting and any changes will be applied to all other data rows.

Configuration

Branded E-mail Templates

The e-mail templates are located in scripts/templates/. They are written in HTML, using Google Apps Script HTML Service Templated HTML for loading dynamic content generated by the functions in scripts/views/.

The following three templates are used:

• branded-email.html, (top-level), E-mail template content wrapper/theme, using MUI (Material Design CSS Framework) panels.
• activity-usage-email.html, E-mail template to render activitySummary for possibly multiple MakerLabs IDs, showing details, summary and usage totals.
• thank-you-footer.html, E-mail template footer containing MakerLabs contact info & hours.

Google Apps Script Triggers

Google Apps Script triggers can be configured by:

1. Selecting Script Editor from the Tools menu of the Google Sheet.
   

   Screenshot of opening Google Apps Script 'Script Editor'
2. Selecting the stopwatch icon from the menubar of the Script Editor. ![Screenshot of selecting Triggers in 'Script Editor'](ScriptEditor_Triggers_cropped.png "`Script Editor` -> Triggers (Stopwatch icon)")
3. Configure the following triggers as shown in the screenshot below:
   • Time-based: triggerActivitySendUsageEmails (e.g. 5pm~6pm Daily)
   • Time-based: triggerActivitySendBillingEmails (e.g. Friday Afternoon)
     

     Screenshot of Google Apps Script Triggers Configuration Page

Google Apps Script Website Deployment

In order to allow the ACM website to access Google Spreadsheet data, the Google Apps Script project must be published as a web app. This enables the ?href=users-fields and ?href=next-makerlabs-id Web App handlers for public use.

To deploy the web app (one-time setup), perform the following steps:

1. Selecting Script Editor from the Tools menu of the Google Sheet.
   

   Screenshot of opening Google Apps Script 'Script Editor'
2. Choose Publish -> Deploy as web app from the menubar of the Script Editor.
   

   Screenshot of Selecting `Deploy as web app` in `Script Editor`

Hard-coded Spreadsheet Details

The Google Apps Script projectId is stored in the .clasp.json file.

Deployment

The clasp command line tool is used to sync files from the scripts/ directory to Google Apps Script.

Note

The *.gs files should NOT be modified from within the Script Editor UI as they are overwritten by the repo files when using the clasp push command.

Login (one-time setup)

clasp uses Google credentials to upload files to the Google Apps Script project, so a one-time login process must be performed to login with your Google Account and grant access to clasp.

To login, run the following:

yarn apps-login

Deploy

To sync the current script/ files to the Google Apps Script project, run the following:

yarn deploy

Note

clasp will upload to the Google Apps Script scriptID configured in the .clasp.json config file.

API Reference

scripts/ API Documentation