Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Event Calendar Synchronization Across Multiple Booking Platforms

Overview

This session focused on consolidating calendar management across disparate boat rental and event booking platforms into a unified Google Calendar-driven workflow. The challenge: multiple platforms (GetMyBoat, Boatsetter, and internal JADA scheduling) were maintaining separate calendars with no synchronization mechanism, creating conflicts and double-bookings. The solution involved leveraging Google Apps Script (GAS) to build a bi-directional calendar sync system and integrating it with AWS Lambda for programmatic event management.

What Was Done

  • Updated CalendarSync.gs — The Google Apps Script file at /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs was modified across multiple iterations to correct polling intervals, update email notification logic, and ensure proper OAuth2 credential handling for external calendar APIs.
  • Deployed Lambda-based calendar API — Verified the existing Lambda function handling calendar operations (located in the shipcaptaincrew project) and confirmed it exposes a REST endpoint via API Gateway for programmatic event creation.
  • Added recurring event blocks — Programmatically populated Google Calendar with 7 weekly Sea Scout Wednesday holds via the Lambda add-calendar-event action, preventing accidental booking during training windows.
  • Mapped GAS projects to repositories — Created a definitive registry by scanning all .clasp.json files across the codebase to establish which local directories correspond to which Google Apps Script projects, enabling proper version control and deployment workflows.

Technical Architecture

Calendar Sync Flow

The system operates in three layers:

  • Source Layer: External platform APIs (GetMyBoat iCal feeds, Boatsetter webhooks) push events into a staging calendar.
  • Transformation Layer: CalendarSync.gs polls the staging calendar at configurable intervals, validates events against business rules, and applies transformations (timezone corrections, duration normalization, booking reference mapping).
  • Destination Layer: Synchronized events are written to the primary operational Google Calendar, with status updates pushed back to the source platforms via their respective APIs.

CalendarSync.gs Implementation Details

The GAS script uses the following key components:


// Core polling function with configurable interval
function syncCalendarEvents() {
  const stagingCalId = PropertiesService.getUserProperties().getProperty('STAGING_CAL_ID');
  const primaryCalId = CalendarApp.getDefaultCalendar().getId();
  const syncInterval = 5; // minutes
  
  // Retrieve events from staging calendar
  const now = new Date();
  const future = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);
  const stagingEvents = CalendarApp.getCalendarById(stagingCalId)
    .getEvents(now, future);
  
  stagingEvents.forEach(event => {
    const syncStatus = event.getDescription().match(/SYNC_STATUS:([A-Z_]+)/);
    if (!syncStatus || syncStatus[1] !== 'SYNCED') {
      // Apply business logic validation
      if (validateEventAgainstRules(event)) {
        syncEventToPrimary(event, primaryCalId);
      }
    }
  });
}

// Validation logic for business rules
function validateEventAgainstRules(event) {
  const minBookingHours = 4;
  const maxGroupSize = 12;
  
  const duration = (event.getEndTime() - event.getStartTime()) / (1000 * 60 * 60);
  if (duration < minBookingHours) return false;
  
  const guestCount = parseInt(event.getDescription().match(/GUESTS:(\d+)/)[1] || '0');
  if (guestCount > maxGroupSize) return false;
  
  return true;
}

The script was updated to fix:

  • Polling interval from 15 minutes to 5 minutes for faster synchronization
  • Email notification logic to alert booking managers only when conflicts are detected
  • OAuth2 token refresh to prevent credential expiration during long-running deployments

Lambda Integration

The Lambda function located at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py exposes calendar operations via API Gateway. The function routes requests to handlers based on the action parameter:


# Relevant action handlers
actions = {
    'add-calendar-event': handle_add_event,
    'list-calendar-events': handle_list_events,
    'update-calendar-event': handle_update_event,
    'delete-calendar-event': handle_delete_event,
    'check-availability': handle_check_availability
}

# Example: Adding a recurring event
def handle_add_event(params):
    service = build_calendar_service()
    event = {
        'summary': params['summary'],
        'description': params['booking_id'],
        'start': {'dateTime': params['start_time']},
        'end': {'dateTime': params['end_time']},
        'recurrence': params.get('recurrence', [])
    }
    return service.events().insert(calendarId='primary', body=event).execute()

The API Gateway endpoint (v2) routes POST requests to this Lambda function with authentication via dashboard token stored in environment variables.

Key Decisions and Trade-offs

GAS vs. Lambda for Polling

Decision: Keep polling logic in GAS; use Lambda only for on-demand operations.

Rationale: GAS has native Google Calendar integration with built-in OAuth2 handling. Lambda requires additional credential management and would add latency for real-time syncs. GAS time-driven triggers execute reliably within Google's infrastructure, eliminating cold-start concerns.

Staging Calendar Pattern

Decision: Implement a three-calendar architecture (staging → primary → audit).

Rationale: External APIs write to staging, preventing accidental overwrites of manual entries. Primary calendar is the operational source of truth. Audit calendar captures all changes for compliance and debugging.

Event Validation at Sync Time

Decision: Enforce business rules (minimum booking duration, group size limits) during sync, not at source API submission.

Rationale: Decouples validation from external platforms, allowing rule changes without API modifications. Failed events remain in staging with rejection reason in event description for manual review.

Deployment and Version Control

GAS projects are mapped to local repositories using .clasp.json configuration files. The CalendarSync.gs project is located at:


# Project configuration
File: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/.clasp.json
ScriptId: [stored in local env]

# Deployment command
clasp push
# Syncs all changes in CalendarSync.gs to the GAS project

What's Next

  • Bidirectional sync completion — Implement event update