Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Migrating Event Management from Google Apps Script to Lambda: Building a Unified Calendar Sync System

Overview

This session involved replacing a fragmented event management system with a centralized Lambda-based calendar synchronization architecture. The primary challenge was consolidating multiple booking platforms (GetMyBoat, Boatsetter, Google Calendar) into a single source of truth while maintaining backward compatibility with existing Google Apps Script deployments.

What Was Done

1. Audit and Discovery Phase

Before making architectural changes, we performed a comprehensive inventory of the existing system:

  • Located all .clasp.json files across the repository to map Google Apps Script projects to their remote IDs
  • Identified CalendarSync.gs in /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/ as the primary calendar synchronization handler
  • Found Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py serving as the API Gateway backend
  • Discovered multiple email polling intervals and sendEmail calls scattered across the GAS codebase that needed consolidation

2. CalendarSync.gs Refactoring

The Google Apps Script file underwent significant restructuring to support the new Lambda-backed architecture:

  • Consolidated all polling intervals into a single configurable parameter rather than hard-coded delays
  • Refactored email notification logic to use environment-based configuration stored in repos.env
  • Added request/response logging to Lambda API calls for debugging platform-specific issues (iCal parsing, timezone handling)
  • Implemented retry logic with exponential backoff for external API calls to GetMyBoat and Boatsetter

3. Lambda Function Enhancement

The lambda_function.py was expanded to handle calendar operations through explicit action routing:


def handler(event, context):
    action = event.get('action')
    
    if action == 'add-calendar-event':
        return add_calendar_event(event['payload'])
    elif action == 'list-calendar-events':
        return list_calendar_events(event.get('filters', {}))
    elif action == 'sync-platform-bookings':
        return sync_platform_bookings(event['platform'])
    # ... additional actions

This routing pattern allows CloudFormation or the API Gateway to invoke specific operations without redeploying the entire function. The Lambda function now serves as the authoritative calendar management layer, with Google Apps Script delegating complex operations upstream.

4. API Gateway Integration

Created POST endpoints under the API Gateway v2 configuration to expose Lambda actions:

  • /calendar/add-event — Accepts event objects and creates calendar entries
  • /calendar/list — Returns filtered event lists for dashboard population
  • /calendar/sync — Triggers platform-specific synchronization (GetMyBoat, Boatsetter)

Each endpoint requires Bearer token authentication validated against a token stored in repos.env. This prevents unauthorized calendar manipulation while allowing legitimate administrative tools (dashboards, scripts) to trigger operations.

Technical Architecture Decisions

Why Move Away from Pure Google Apps Script?

GAS is excellent for quick automation within the Google ecosystem, but has inherent limitations:

  • Execution time limits: 6-minute timeout makes bulk operations problematic
  • Rate limiting: Google enforces stricter quotas on Apps Script compared to Lambda
  • Multi-platform integration: Calling third-party APIs (GetMyBoat, Boatsetter) with authentication and error handling is clunky in GAS vs. Python
  • Testing and CI/CD: Python Lambda functions integrate naturally with existing deployment pipelines; GAS requires clasp tooling

The decision was to use GAS as a thin client that delegates heavy lifting to Lambda, rather than attempting everything server-side in Apps Script.

Credential Management Strategy

Credentials for platform APIs (GetMyBoat, Boatsetter) and Google Calendar are stored in Lambda environment variables configured through CloudFormation, not hardcoded in version control. The repos.env file contains references to these secrets manager entries, not the actual credentials.

This follows the principle of least privilege: GAS doesn't need OAuth tokens; it calls Lambda endpoints with a dashboard token. Lambda has platform credentials but never exposes them to client-side code.

Deployment and Rollout

GAS Deployment via clasp

Updated CalendarSync.gs was pushed to the remote Google Apps Script project using:


cd /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/
clasp push --force

The --force flag overwrites the remote version, which is safe here because the local version is the canonical source and is version-controlled in Git.

Lambda Redeployment

The Lambda function is packaged and deployed via a shell script (inferred from the file modification history but not directly shown). The deployment process likely:

  • Zips the lambda_function.py and any dependencies
  • Updates the function code via AWS CLI
  • Does NOT modify the function configuration (env vars, role, timeout) unless CloudFormation is involved

Integration Points and Data Flow

Platform Booking → Lambda: GetMyBoat and Boatsetter send webhooks to API Gateway endpoints when bookings are created/modified. Lambda parses these, normalizes event data, and creates entries in Google Calendar.

Google Calendar ← Lambda: GAS calls Lambda's list-calendar-events action periodically to refresh the dashboard view of upcoming events. This is more efficient than GAS querying Google Calendar directly, as it allows filtering and caching at the Lambda layer.

GAS ← Dashboard: The dashboard (served from CloudFront) calls GAS via Apps Script web apps (deprecated but still in use) or directly invokes the Lambda API for non-calendar operations.

Key Metrics and Monitoring

The refactored system allows for granular monitoring:

  • CloudWatch Logs for Lambda invocations, platform API errors, and calendar sync latency
  • GAS execution logs now include timestamps for external API calls, making bottlenecks visible
  • API Gateway metrics (response time, error rate) visible per endpoint

What's Next

Future improvements should focus on:

  • Event deduplication: Implement idempotency keys to prevent duplicate calendar entries if webhooks are retried
  • Timezone normalization: Boatsetter and GetMyBoat may return times in different zones; Lambda should standardize to UTC internally
  • Webhook signature validation: Verify HMAC signatures on incoming platform webhooks to