Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Boat Platform Integration and Calendar Synchronization Across Multiple Event Systems

What Was Done

This session involved consolidating booking data from multiple boat rental platforms (GetMyBoat, Boatsetter) into a unified Google Calendar system, then syncing that calendar to event websites managed by Google Apps Script. The goal was to eliminate manual calendar management and ensure consistent event visibility across the Queen of San Diego's booking infrastructure.

Key accomplishments:

  • Verified existing platform credentials in CalendarSync.gs and enabled iCal feed polling
  • Created dispatch automation for boat cleaning services tied to calendar events
  • Configured Google Apps Script to poll external platform calendars every 15 minutes
  • Deployed updated CalendarSync.gs code to the GAS replacement project
  • Integrated Lambda-based calendar API for programmatic event management

Technical Architecture

Calendar Synchronization Pipeline

The system uses a three-tier architecture for calendar management:

  • Source Layer: GetMyBoat and Boatsetter platforms expose iCal feeds containing booking data
  • Aggregation Layer: Google Apps Script (CalendarSync.gs) runs on a 15-minute polling interval, fetching and parsing iCal feeds
  • Distribution Layer: Master Google Calendar (managed by the Lambda function) serves as the single source of truth for all downstream applications

CalendarSync.gs is deployed to the GAS project at /repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs. The script uses Apps Script's UrlFetchApp to retrieve iCal feeds:


const response = UrlFetchApp.fetch(iCalFeedUrl);
const icalData = response.getContentText();
// Parse events from iCal format
// Create/update events in target calendar

The polling interval is deliberately conservative (15 minutes) to avoid API quota exhaustion while maintaining near-real-time sync for booking changes. This trade-off prioritizes reliability over latency, since boat booking changes are rarely time-critical within a 15-minute window.

Lambda Calendar API Integration

The Lambda function at /repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py provides HTTP endpoints for calendar operations via API Gateway. This enables programmatic event creation without manual Google Calendar access.

The Lambda function supports multiple actions, dispatched via the `action` parameter:

  • list-events — Returns all events within a date range
  • add-calendar-event — Creates a new calendar event with title, description, date/time, and optional attendees
  • get-event — Retrieves a specific event by ID

The API Gateway endpoint is protected by an API key stored in environment variables. Calls follow this pattern:


POST /calendar
Authorization: Bearer {TOKEN}
Content-Type: application/json

{
  "action": "add-calendar-event",
  "title": "Sea Scout Wednesday Hold",
  "start_time": "2024-04-24T18:00:00Z",
  "end_time": "2024-04-24T20:00:00Z",
  "description": "Reserved slot for Sea Scout training"
}

Dispatch Automation for Service Integration

A new script at /repos/tools/dispatch_boat_cleaner.py monitors calendar events and triggers boat cleaning service dispatches. The script:

  • Queries the Lambda calendar API for events tagged with cleaning requirements
  • Calculates cleaning slot timing based on event duration and turnaround requirements
  • Submits dispatch requests to external cleaning service APIs
  • Logs all dispatch confirmations to a tracking database

This automation removes the manual step of reviewing calendar bookings and calling cleaning services. Instead, when a booking is confirmed in GetMyBoat or Boatsetter, the iCal feed updates, CalendarSync.gs propagates it to the master calendar, and dispatch_boat_cleaner.py automatically schedules cleaning.

Infrastructure and Deployment

Google Apps Script Project Structure

The CalendarSync.gs replacement project is mapped via .clasp.json at /repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/.clasp.json. This file contains the GAS project ID and associates the local directory with the cloud project:


{
  "scriptId": "{PROJECT_ID}",
  "rootDir": "."
}

Deployment to GAS uses the clasp CLI. Code is pushed to the GAS cloud environment, and the script runs on schedule triggers defined within the GAS project settings. The 15-minute polling interval is configured via a time-driven trigger in the Apps Script editor.

Lambda and API Gateway Configuration

The Lambda function is configured with:

  • Runtime: Python 3.11
  • Timeout: 60 seconds (necessary for iCal parsing and Google Calendar API calls)
  • Memory: 512 MB
  • Environment variables: Google Calendar credentials, API authorization tokens

API Gateway v2 HTTP endpoint exposes the Lambda function. A custom domain can be configured in Route53 for user-friendly URLs, though the function currently uses the auto-generated API Gateway URL.

Key Decisions and Rationale

Why 15-Minute Polling Instead of Event-Driven Webhooks?

GetMyBoat and Boatsetter's webhook availability is inconsistent. Polling via iCal feeds is more reliable because:

  • iCal is a standardized format supported by all platforms
  • No dependency on platform webhook infrastructure or their SLA promises
  • Easier debugging—we can manually fetch and inspect iCal feeds if sync fails
  • No firewall/networking issues from external services calling our infrastructure

The trade-off is slightly delayed synchronization (up to 15 minutes) and higher API usage. For boat rentals, this latency is acceptable since bookings are typically confirmed hours in advance.

Dual Calendar API Approach (GAS + Lambda)

CalendarSync.gs handles automated polling, while the Lambda API allows manual event creation. This separation provides:

  • Robustness: If Lambda is unavailable, GAS polling continues automatically
  • Flexibility: External systems can add events without GAS script modifications
  • Auditability: Each system owns specific event types, making it clear which source created each calendar entry

Dispatch Automation Trigger

Rather than having a human manually schedule cleaning each time a booking confirms, dispatch_boat_cleaner.py monitors the calendar and creates cleaning tasks automatically. This reduces friction and ensures consistent turnaround timing. The script runs as a Lambda function on a 10-minute schedule (independent of the 15-minute calendar sync interval) to catch newly-added bookings quickly.

What's Next

  • Webhook Migration: As platform APIs mature, consider migrating from polling to event webhooks