Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Debugging Calendar Sync Failures Across Multiple Booking Platforms: GAS Triggers, OAuth Expiration, and iCal Integration

Executive Summary

During a routine operations review, we discovered that Boatsetter bookings are not automatically syncing to our internal JADA calendar system despite having the integration code fully written. Root cause analysis revealed three distinct failures: (1) the Google Apps Script (GAS) trigger for calendar synchronization was never activated, (2) Gmail and Calendar OAuth tokens have expired or been revoked, and (3) downstream platform integrations (Viator, Sailo) are blocked waiting for the same GAS infrastructure fix. This post documents the debugging process, architectural decisions, and remediation steps.

What Was Done

We performed a comprehensive audit of the calendar synchronization pipeline across three integrated booking platforms:

  • Boatsetter (primary booking source) — iCal feed configured but sync disabled
  • Viator — awaiting API integration response; manual blocking required until resolved
  • Sailo — ready to consume JADA iCal feed once GAS infrastructure is repaired

The investigation traced calendar sync failures from the entry point (Boatsetter booking received May 30) through the GAS middleware layer to OAuth credential rot in both Gmail and Google Calendar APIs.

Technical Details: The Calendar Sync Architecture

Current Architecture

Our calendar synchronization uses Google Apps Script (GAS) as the integration hub, stored in the shared Google Drive under the JADA project workspace. The primary sync logic lives in:

GoogleAppsScript/CalendarSync.gs

This file contains the Boatsetter iCal URL and implements the core sync function:

function calendarDashboardSetup() {
  // Initializes triggers and validates OAuth permissions
  // Must be run manually once from the GAS editor
}

The function is designed to:

  • Parse the Boatsetter iCal feed (URL hardcoded in config section)
  • Transform iCal events into Google Calendar format
  • Write events to the JADA Internal calendar via Calendar API
  • Set up time-based triggers for periodic sync (intended: every 4 hours)

Why This Approach?

GAS was chosen because:

  • Cost — no server infrastructure required; runs within Google's quota system
  • Native OAuth — no token management; GAS handles auth delegation automatically
  • Calendar API access — direct integration with Google Calendar without intermediaries
  • Scheduled execution — built-in trigger system eliminates need for external schedulers (Lambda, cron)

However, this architecture has a critical weakness: it requires explicit human activation and is opaque to monitoring.

Root Cause #1: Trigger Was Never Activated

The calendarDashboardSetup() function creates time-based triggers via the Apps Script API. However, it must be invoked at least once manually from the Google Apps Script editor. This step was never performed.

Evidence: Ticket `m-91325edb` (status: needs-you) documents this task. No execution logs exist in the GAS Execution Transcript for May 2024 onward.

Remediation:

  1. Open the GAS project in the Google Drive (JADA workspace)
  2. Click the play/run icon next to calendarDashboardSetup
  3. Approve OAuth scopes when prompted (Gmail, Calendar, Drive)
  4. Verify in Apps Script Triggers that syncCalendarEvents now shows an active time-based trigger (4-hour interval)

Root Cause #2: OAuth Token Expiration and Revocation

Even after activating the trigger, the sync will fail because OAuth credentials are no longer valid:

  • Gmail OAuth (`t-8d86d5ba`): Access token has expired. The refresh token may also be revoked if the token was invalidated server-side.
  • Calendar OAuth: The Calendar API authorization has been explicitly revoked (likely during a security audit or device deauthorization).

When GAS attempts to call CalendarApp.insertEvent() or GmailApp.getInboxThreads(), both will return authorization errors without logging visible to the dashboard.

Why this happens: Google OAuth tokens in GAS are long-lived but not infinite. Refresh tokens can be invalidated if:

  • The user changes their password
  • The user explicitly revokes app access in myaccount.google.com/permissions
  • Google detects suspicious activity and auto-revokes
  • More than 6 months elapse without the token being used

Remediation: Re-authorization is required. After activating the trigger in Step 1 above, the GAS runtime will prompt for new OAuth consent. This is a one-time step per API scope.

Infrastructure: Downstream Platform Integrations

Viator Integration

Viator (a third-party booking platform partner) has replied to our API integration request (ticket `t-ad4b92d7`). The response is awaiting review in the shared inbox at jadasailing@gmail.com. Until this ticket is resolved and Viator's API credentials are provisioned, bookings made on their platform must be manually blocked in their supplier portal:

https://supplier.viator.com/en/partner-portal

For the May 30 booking specifically: log into supplier.viator.com, navigate to the calendar section, and manually block the time window to prevent overbooking.

Sailo Integration

Sailo is configured to consume our JADA calendar via iCal. The iCal feed is published at (JADA Internal Calendar settings > Subscribe), and Sailo pulls from this URL. Once the GAS trigger is activated and OAuth is renewed, Sailo will automatically sync our events without further action.

GetMyBoat

No action needed — the listing is not yet live in the GetMyBoat platform.

Key Decisions and Architectural Lessons

  • Central GAS hub vs. Lambda microservices: We chose GAS for simplicity, but this introduces a single point of failure (OAuth expiration) and poor observability (no CloudWatch logs). A future iteration should consider migrating to AWS Lambda + Secrets Manager for better monitoring and credential rotation.
  • Manual trigger activation: The requirement to manually run calendarDashboardSetup() is a process smell. Ideally, this should be triggered automatically during environment provisioning (via terraform or a setup script).
  • iCal as the universal format: Using iCal (RFC 5545) as the bridge format between Boatsetter → JADA → downstream platforms is sound — it's platform-agnostic and widely supported. However, relying on iCal feed polling (instead of webhooks) introduces a 4-hour sync delay.

What's Next