Automating Event Calendar Synchronization Across Multiple Booking Platforms

What Was Done

This session focused on consolidating event scheduling across multiple third-party booking platforms (GetMyBoat, Boatsetter, and internal Google Calendar) into a unified synchronization system. The primary challenge: event data was siloed across platforms with no single source of truth, creating operational friction and manual reconciliation overhead.

The solution leveraged Google Apps Script (GAS) as a lightweight orchestration layer, with AWS Lambda as the API backend, to create a bidirectional sync mechanism that:

• Polls external booking platform iCalendar feeds for new reservations
• Normalizes event data into a standard format
• Writes events to a master Google Calendar via the Calendar API
• Exposes a REST API endpoint for dashboard integration
• Maintains audit logs of all synchronization operations

Technical Architecture

Google Apps Script Layer (CalendarSync.gs)

The primary synchronization logic lives in /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs. This file implements a time-triggered function that executes on a configurable polling interval (currently 15 minutes, deployed via `deployments.json`).

Core Functions:

• syncExternalCalendars() — Main orchestrator; iterates through configured iCalendar feed URLs and triggers normalization
• fetchICalendarFeed(url) — Retrieves iCal data from external booking platforms; implements retry logic with exponential backoff
• parseICalendar(icalData) — Converts iCal format to JavaScript objects; handles timezone conversion to UTC
• normalizeEvent(rawEvent, source) — Maps platform-specific fields to internal schema; tags events by origin platform
• writeToMasterCalendar(event) — Calls Google Calendar API to create/update events; maintains idempotency via external event IDs
• logSyncOperation(status, details) — Writes audit trail to Google Sheets for debugging and compliance

Why GAS? Google Apps Script was chosen because it has native Calendar API access, requires no OAuth token refresh management in this context, and can be deployed as a standalone project with simple time-based triggers. The tight integration with Google Calendar (same Google Workspace account) eliminates cross-account authentication complexity.

AWS Lambda Backend

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py

The Lambda function serves as a REST API for the dashboard and mobile interfaces. It handles requests with actions routed via a switch statement:

if action == 'list-calendar-events':
    events = calendar_service.events().list(
        calendarId='primary',
        timeMin=datetime.now(timezone.utc).isoformat(),
        maxResults=50,
        singleEvents=True,
        orderBy='startTime'
    ).execute()
    return events.get('items', [])

elif action == 'add-calendar-event':
    event = {
        'summary': payload['title'],
        'description': payload.get('description', ''),
        'start': {'dateTime': payload['start_time'], 'timeZone': 'America/Los_Angeles'},
        'end': {'dateTime': payload['end_time'], 'timeZone': 'America/Los_Angeles'},
        'extendedProperties': {
            'private': {'source': payload.get('source', 'manual')}
        }
    }
    created = calendar_service.events().insert(calendarId='primary', body=event).execute()
    return created

API Gateway Integration: The Lambda is exposed via API Gateway v2 (HTTP API, not REST API for cost efficiency). Endpoints follow the pattern:

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

{
  "action": "add-calendar-event",
  "title": "Sea Scout Wednesday Hold",
  "start_time": "2025-04-30T18:00:00",
  "end_time": "2025-04-30T20:00:00",
  "source": "sea-scouts"
}

The token-based auth prevents unauthorized calendar manipulation while allowing the dashboard to trigger bulk operations (e.g., adding 7 recurring Scout holds in a single batch request).

Integration Points

GetMyBoat & Boatsetter iCalendar Feeds

Both platforms expose iCalendar URLs in their booking settings. These feeds are polled from CalendarSync.gs and include:

• SUMMARY — Booking title (vessel name + renter name)
• DTSTART/DTEND — Reservation window with timezone data
• DESCRIPTION — Booking reference ID and platform source
• UID — Unique identifier for idempotency (prevents duplicate event creation on re-polls)

The normalization step extracts the platform source and booking ID, then stores them in the Calendar event's extendedProperties field so downstream systems can link back to the original booking for fulfillment.

Dashboard Integration

The dashboard (/tmp/dashboard_index.html) displays a unified calendar view populated via the Lambda API. When operators add holds or blocks (e.g., "Scout Wednesday 6–8 PM"), the frontend sends a request to the add-calendar-event action, which immediately reflects in the master calendar and is visible to all downstream systems.

Key Decisions & Rationale

Polling vs. Webhooks: External booking platforms don't reliably support outbound webhooks, so we implemented polling with a 15-minute interval. This trades slight staleness for operational simplicity and no inbound firewall changes.

External Event IDs for Idempotency: The iCal UID is stored as the Calendar event's externalEventId` to ensure that if CalendarSync.gs runs twice before the external feed updates, we don't duplicate events. Query logic checks for existing events by this ID before inserting.

clasp push
clasp deploy --description "Sync v1.2: Add boat platform polling"