Apps Script Systems

Campus Tour Operations

Live coordination dashboard for tour groups moving between stations

Campus tour days were coordinated by walkie-talkie and clipboard. Nobody had a single view of where each group was, which station was backed up, or which group had stopped moving — problems surfaced only after a group was already late.

How it works

Every tour group is a row. It carries a route, a section, a head count, the station it is currently at, and a status — moving, arrived, or completed. Staff at each station mark a group in and out from their phone; the dashboard adds it up live: total students on site, how many groups are between stations right now, how many have finished, and the state of every station.

The sheets behind it are deliberately boring — Settings, Groups, Students, EventLog, StationStatus. Anyone on the committee can open the spreadsheet and read it without me there to explain, which matters more than elegance for a tool that has to survive me graduating.

The part I am most pleased with

Groups do not report that they are stuck. They just stop moving, and nobody notices until a downstream station is standing idle.

So the system computes how long it has been since each group was last updated and surfaces anything that has gone quiet. It is a small piece of arithmetic that turns the dashboard from a record of what happened into a warning about what is about to.

Measuring before optimising

The first version rewrote the station-status sheet and read it back on every single interaction — three or more Sheets API calls per click, for a value only the client ever consumed and the sheet was never read back for. On a quiet afternoon that was invisible. During an actual event, with staff at every station tapping at once, it was the difference between instant and sluggish.

Computing it in memory and returning it directly removed those calls. That change came from watching the tool be slow while people were using it, not from guessing in advance — and I would not have predicted which call was the expensive one.

There is a second, subtler fix in the same place: dates have to be serialised before they cross back to the client, because a raw Date in an Apps Script return value breaks the JSON encoding for the entire response, not just that field. One unserialised timestamp takes the whole dashboard down.

Nightly Inventory

Barcode-driven stock control for shared committee equipment

Equipment was counted by hand against a paper list at the end of each night. Items went missing without anyone knowing who had borrowed them, and disputes were unresolvable because there was no record.

How it works

Every item, box, and shelf has a barcode. The system generates the labels, prints them as a sheet, and reads them back through a scanner page that runs in a phone browser — no app to install, which matters when the people scanning change every semester.

Three registries sit behind it: Shelf Registry, Box Registry, and a Transaction Log that records every movement. Borrowing is a request, not a handshake: a member picks items, a pickup date and a time slot, and a return date, and gets back a request ID like BR-20260214-003. Confirmation, approval, and overdue notices all go out by email automatically.

The point was never the barcodes. It was that "who has the projector" stopped being a question anyone had to remember the answer to.

A schema decision worth explaining

When I added box locations, the obvious place for the column was next to the quantity fields, where it reads naturally.

I put it after Status instead, at the end. The Available column is formula-based, and several other parts of the system address columns by index — inserting in the middle would have shifted Available and Status and silently broken every one of them. The natural-reading position would have cost an afternoon of debugging something that looked unrelated.

That trade — a slightly worse-looking sheet for a schema that does not break its own dependents — is the kind of judgement I did not have when I started this project and did by reflex by the end of it.

What it replaced

An end-of-night count against a printed list, done by whoever was still around. Items went missing without anyone knowing who had taken them, and there was no record to settle a disagreement with — so disagreements were settled by whoever remembered more confidently.

Committee Registry

OTP-verified member registry with an AI automation suite

Committee membership lived in a Google Form response sheet. Duplicate and mistyped entries accumulated, nobody chased inactive members, and answering a question about the roster meant reading the sheet by eye.

How it works

The registry holds the committee's whole membership lifecycle: intake form, OTP-verified registration, password provisioning, Discord account linking, the org chart, attendance, memberships across more than one organisation, and archival when someone leaves. Two kinds of user — admins who run registration drives, and members who log in occasionally to check their profile or see where they sit in the org chart.

It replaced a Google Form response sheet. Duplicate and mistyped entries accumulated there faster than anyone cleaned them, nobody chased inactive members because nobody knew who they were, and answering a question about the roster meant reading the sheet by eye.

The AI automation suite

Eight automations run in production here, all on Groq:

• Duplicate detection during bulk member import
• Auto-approval scoring for equipment borrow requests
• Automated post-event reports
• Natural-language search across all three Apps Script systems
• Personalised absence follow-up emails
• Predictive inventory demand forecasting
• A chatbot with function calling, on an /ask command
• Inactivity detection that sends a soft warning rather than a removal

Most of these are decision support, not chat. Forecasting demand and scoring a borrow request are the two that actually removed recurring work — a human still decides, but they start from an answer instead of a blank sheet.

The chatbot is the one people demo and the one I would cut first if I had to choose. Duplicate detection is unglamorous and has probably saved more hours than the rest combined.

Running it for nothing

The whole suite runs on a free tier, with no credit card attached. That was a hard constraint — a student committee has no budget line for API calls, and a system that stops working when a trial expires is worse than no system.

It shapes the design: batch where possible, use the small fast model unless the task genuinely needs the large one, and never call a model for something a if statement can answer.

Event Registration

QR check-in, waitlists, and a live attendance display

Event sign-in was a printed name list at a door. Queues formed while staff searched pages by hand, walk-ups had no waitlist, and the final attendance figure was transcribed from paper the following day.

How it works

A participant registers through a bilingual form, gets a QR code by email, and is scanned in at the door. Staff run a scanner page on their own phones; a separate live display shows the room filling up in real time. Check-out is scanned too, so the attendance figure is a fact rather than an estimate, and the whole thing exports to CSV when the event is over.

Events open and close, so registration closes itself rather than depending on someone remembering to take the form down.

Details that only show up in a real event

The email rule has an exception. Students must register with an @bumail.net address — that is how the system knows they are who they say they are. But alumni no longer have one, and the first version locked them out of their own department's events. Alumni are now exempt from the domain check while every other validation still applies.

Every message is bilingual. Errors come back in Thai and English together, in one string, because a registration form that fails in a language you are less comfortable reading is a form you abandon.

Check-in runs inside a token window. A QR code that works forever is a QR code that gets screenshotted and forwarded. Tying validity to a window means a scan proves someone is at the door now.

Dates are normalised to Bangkok time explicitly. Apps Script will happily hand you a Date in whatever timezone the script property says, and an event on the 14th quietly becomes the 13th for anyone reading the export. It is a one-line conversion and it is the kind of bug that is invisible until it is embarrassing.

What it replaced

A printed name list at a door. Queues formed while staff searched pages by hand, walk-ups had nowhere to go, and the final attendance number was transcribed from paper the following day — by which point the paper had usually been folded into someone's bag.