From: Tomás Herrera, operations manager, Orchard Lane Schools (fictional)
Staff report building problems through a Google Form. The answers land in Form Responses 1 and someone copies them into the Queue by hand, gives each an ID and phones the site manager when it's urgent. Jobs get missed.
The data: Form Responses 1 (Timestamp, Email, Building, Issue, an optional Priority), the Queue tab, and Buildings, which lists each building's site manager email.
Done looks like: one Apps Script project where
- setup installs an On form submit trigger for onRequest, without duplicating it when run twice,
- each request joins the Queue as REQ-n with priority Normal when blank and status New,
- an Urgent request emails that building's site manager,
- setting a Status to Done stamps the date in the Closed column,
- sendDigest emails [email protected] the open requests, one per line, and nothing when all are done,
- setup also schedules sendDigest every day at 7,
- whoever sent the form gets an email confirming their request ID.
Build it one step at a time; each step adds to your script. All names and addresses are fictional practice data.
You'll grow one Apps Script project over six steps. Each step is checked by running setup, then submitting four test requests through your trigger, and, from step 3, setting a status to Done and running the timer. The checker compares the Queue, the recorded emails and the installed triggers with the reference project.
Keep setup safe to run twice: it checks ScriptApp.getProjectTriggers() before creating a trigger. The request ID uses the Queue's last row before the new row is added, so the first request is REQ-1.
Install each trigger once, however often setup runs.