Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Business Operations: Building a Unified Task Dispatch System with Lambda, Google Apps Script, and Python

This session involved consolidating scattered business operations—event scheduling, service coordination, and customer communications—into a unified dispatch system. The challenge: multiple platforms (Google Calendar, email, web dashboards) operating in isolation. The solution: Lambda functions as orchestration hubs, with Google Apps Script for calendar sync and Python CLI tools for batch operations.

What Was Done

We built three interconnected systems to replace manual, error-prone workflows:

  • Calendar API via Lambda: Unified interface for reading/writing Google Calendar events across multiple service calendars
  • Apps Script Deployment Pipeline: Automated syncing of calendar data from Google Calendar back to external platforms (GetMyBoat, Boatsetter iCal feeds)
  • Python Dispatch Tools: CLI-based task dispatch for service coordination (boat cleaning scheduling, email batch processing)

The motivation: Previously, calendar holds, service bookings, and task status updates existed in separate systems with manual synchronization points. This created race conditions, missed bookings, and duplicated work.

Technical Architecture

Lambda Calendar API

The core orchestration layer is a Lambda function that exposes HTTP endpoints via API Gateway (v2 format). The function handles multiple actions via route parameters:

GET /calendar/list-events
GET /calendar/add-event
POST /calendar/add-event
GET /calendar/get-credentials

The Lambda function code checks action names by parsing the request path. We discovered the correct action signatures by downloading the function code and grepping for calendar-related patterns:

Find all action names in Lambda function:
  - Match pattern: function handlers for `add-calendar-event`, `list-calendar-events`
  - Verify against API Gateway routes to ensure mapping matches
  - Test endpoint with auth token from repos.env

Why Lambda? Google Calendar API credentials are sensitive and shouldn't be embedded in client code. Lambda acts as a credential-holding proxy. The function reads credentials from environment variables (stored in Lambda config, sourced from a secure vault during deployment), and all requests must include a bearer token that's validated before any calendar operation executes.

Authentication: The dashboard and external tools call the Lambda endpoint with an auth token. This token is verified against a known value in the Lambda environment before any operation proceeds. This prevents unauthorized calendar modifications while keeping the API accessible for legitimate operations.

Google Apps Script Calendar Sync

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs

The Apps Script project (identified via .clasp.json mapping) pulls events from Google Calendar and pushes them to external booking platforms. Specifically:

  • Input: Read from a master "Sea Scout Holds" calendar (specific calendar ID stored in GAS environment)
  • Output: Push iCal feed to GetMyBoat and Boatsetter platforms (iCal URLs stored in GAS script properties)
  • Scheduling: Apps Script time-based trigger runs every 15 minutes (configurable via ScriptApp.newTrigger())

The script iterates through calendar events, filters by date range and event properties, and generates an iCal-format feed that external platforms poll for availability blocks. This prevents double-booking: when a Sea Scout event is added to the master calendar, it automatically becomes unavailable on the boat rental platforms within 15 minutes.

Key implementation detail: The script uses sendEmail() calls for error logging and status updates. We updated the polling interval and email recipients during this session, then pushed changes back to GAS via clasp push. This required:

1. Local clone of GAS project via clasp
2. Edit CalendarSync.gs locally
3. Push changes: clasp push
4. Verify deployment in GAS IDE

Python Dispatch Tools

Two new CLI tools were created to automate service coordination:

File: /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py

This script orchestrates boat cleaning scheduling:

python dispatch_boat_cleaner.py \
  --boat-id BOAT_ID \
  --service-provider PROVIDER \
  --date YYYY-MM-DD \
  --time HH:MM

The script:

  • Checks boat availability by querying the master calendar via the Lambda API
  • Creates a "Cleaning Hold" event on the calendar to block the time slot
  • Sends a confirmation email to the service provider
  • Logs the operation to a dispatch log file for audit trails

File: /Users/cb/Documents/repos/tools/platform_inbox_scraper.py

Reads emails from service platforms (Gmail API integration) and generates dispatch tasks. This tool:

  • Connects to Gmail API (credentials from repos.env)
  • Filters for emails from specific senders (boat rental platforms, service coordinators)
  • Extracts booking/service request details
  • Creates dashboard cards or calendar events automatically

File: /Users/cb/Documents/repos/tools/deploy_inbox_scraper.sh

Bash wrapper that runs the scraper and commits results to the dashboard. This allows scheduled execution (via cron or Lambda scheduled events) without manual intervention.

Integration Points

The systems talk to each other via standard APIs and file stores:

  • Dashboard → Lambda: Calendar display queries Lambda for current events; photo uploads trigger SES email notifications
  • Lambda → Google Calendar: Reads/writes via Google Calendar API; credentials held in Lambda environment
  • Google Calendar ↔ Apps Script: Apps Script polls Calendar API every 15 minutes, pushes to external platforms
  • Python Tools → Lambda: dispatch_boat_cleaner.py calls Lambda to add events and verify availability
  • Email Services: SES (AWS Simple Email Service) handles outbound notifications; Gmail API handles inbound scraping

Key Decisions

Why not use Calendar webhooks instead of polling? Google Calendar doesn't support real-time webhooks; polling via Apps Script is the standard approach. 15-minute intervals balance responsiveness against API quota consumption.

Why Lambda as the calendar API proxy? Keeps credentials server-side, enforces authentication, and allows rate limiting. Direct client access to Google Calendar API would require distributing credentials.

Why Python CLI tools instead of dashboard buttons? CLI tools integrate with cron, can be invoked by other systems, and handle batch operations efficiently. Dashboard buttons are added later for user-friendly access to the same underlying functions.

Why iCal for platform sync? Both GetMyBoat and Boatsetter support iCal feeds natively. This avoids building platform-specific integrations; we push one feed format that all platforms understand.

What's Next