Problem

The system had to prevent double-booking, stay usable on mobile, work within Apps Script limits, and still provide staff with an operational view.

Context / users

This is a low-cost operational workflow tool built around real constraints: limited budget, stressed users on phones, no paid infrastructure, and staff who should not have to manage custom software.

Solution

I built a web app backed by a Google Sheet with configuration and slot data. The public flow uses phone-number lookup and a single-page state machine. The staff dashboard handles visibility, check-in, cancellation, and open/close control.

• Same-day slot reservation flow with phone-number lookup instead of user accounts
• Config-driven operating hours, slot duration, grace period, and concurrency via Google Sheets
• Public booking and reservation-status experience combined into one mobile-first interface
• Guest-side countdowns, check-in window logic, cancellation, and reservation recovery flows
• Staff dashboard with masked phone display, manual check-in, cancellation, and booking open/close controls
• PWA-oriented manifest/install flow plus basic service-worker caching
• Automatic slot expiration and daily cleanup via time-driven triggers
• Setup guide, user guide, and signage asset for handoff and deployment

Architecture

The backend is function-oriented. Caching, locking, validation, scheduling, and admin actions are separated by responsibility. Google Sheets serves as both datastore and config layer.

Engineering Details

• • CacheService is used for config, slot availability, and rate-limit counters to reduce repeated spreadsheet reads and cold-start pain
• • LockService protects the booking write path so concurrent requests do not double-book the same slot
• • The backend normalizes Google Sheets time values from strings, numbers, or Date objects before business-logic checks
• • Combined bootstrap APIs reduce front-end round trips on initial load
• • Validation exists on both sides: the UI gates obvious bad input, while the server re-validates phone numbers, times, and admin setting changes
• • The public UI uses explicit screen transitions and 30-second status refreshes instead of a framework state library
• • Admin authentication is intentionally lightweight: a shared key from the Config sheet is checked with constant-time comparison, with added delay to reduce brute-force probing
• • Tradeoff: the admin key is passed in the URL, which keeps deployment simple but is weaker than a real session model
• • The `LOOKUP_PER_IP` constant name no longer matches the current implementation, because lookup limiting appears to be keyed by normalized phone number

Outcome

• Produced a working prototype that demonstrates a credible low-cost scheduling workflow without paid hosting, SMS, staff tablets, or a custom database
• Turned the project into something another organization could realistically deploy by shipping setup, staff, and user documentation alongside the code
• Created a stronger portfolio example than a generic CRUD demo because the build is tied to a real operational constraint set: concurrency, privacy, latency, and handoff
• If rolled out as designed, the flow should reduce waiting-on-site and make shower availability more predictable for both guests and staff

Tradeoffs / Limits

• • The repo explicitly presents this as a concept / working prototype, not a live production deployment with measured usage outcomes
• • There is no automated test suite; correctness is enforced through code structure, guardrails, and manual flow testing
• • Admin auth is a shared secret in the URL rather than a proper user/session system
• • PWA support is best described as installable / offline-friendly rather than fully offline-first
• • Some repo documentation is stale: it still references check-in codes, extra admin actions, and integration behaviors that do not match the current implementation exactly

Why It Matters

It shows practical systems work under real budget and deployment constraints.