Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Fixing Calendar Sync for Multi-Channel Booking Aggregation: OAuth Re-auth, Trigger Activation, and iCal Integration

The Queen of San Diego booking operations rely on a Google Apps Script (GAS) pipeline that aggregates rental listings from multiple platforms (Boatsetter, Viator, Sailo) into a single Google Calendar and Spreadsheet. This post documents the diagnosis and fix for a critical sync failure where OAuth tokens had expired and time-based triggers were never initialized—leaving the system in a non-functional state despite the underlying code being complete.

The Problem: Three Blockers in Sequence

The CalendarSync system had been written and deployed but remained inactive:

  • OAuth expiration: Gmail and Calendar permissions were revoked or expired at the GAS project level
  • Unactivated triggers: The time-based triggers for syncAllChannels (every 30 min) and sendDailyReconciliation (daily 7:30 AM PT) were defined in code but never registered with GAS
  • Verification gap: No test run had been executed since OAuth was last refreshed, so the full iCal fetch → calendar write → email send flow was untested

Technical Details: The CalendarSync Architecture

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/CalendarSync.gs

This 600+ line GAS file orchestrates the following flow:

  • iCal ingestion: Fetches calendar feeds from Boatsetter (via configured URL in ICAL_FEEDS array, line ~120) and merges them into the ops Google Calendar
  • Booking ledger: Parses iCal events and writes structured booking data to the BookingLedger tab in the ops spreadsheet (sheet ID: 1m-...ops-sheet-id....)
  • Daily reconciliation: Sends email summaries of new bookings to the operations email list via GmailApp.sendEmail()
  • Trigger registration: calendarSyncSetup() (line 355) is the initialization function that must be run manually once to register these time-based triggers

The Boatsetter iCal URL was wired correctly in the config, but the sync had never fired because the triggers were never activated—a deployment step that requires manual invocation of the setup function in the GAS editor.

The Fix: Three Sequential Steps

Step 1: Re-authorize OAuth Scopes

GAS-level OAuth (Gmail, Calendar, Spreadsheet scopes) requires explicit user consent. This is distinct from service account or API key authorization. When scopes expire or are revoked, they must be re-authorized by opening the GAS editor and running any function that touches those APIs.

Action:


1. Navigate to: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
2. Function dropdown → select testSync (line 563)
3. Click Run
4. Google OAuth consent prompt → Allow (may appear twice for Gmail and Calendar scopes)

Why testSync first: This function exercises the full GAS auth surface (iCal fetch, calendar read/write, email send) without modifying production state, so you'll see exactly which scopes need re-consent.

Step 2: Activate Time-Based Triggers

GAS time-based triggers (clock icon in the left sidebar) are not created by the code itself—they must be registered via the UI or the Apps Script API. The calendarSyncSetup() function wraps ScriptApp.newTrigger() calls to register them.

Action:


1. In the same GAS editor:
2. Function dropdown → select calendarSyncSetup (line 355)
3. Click Run
4. Execution log should show:
   "CalendarSync setup complete:
    - BookingLedger tab created in ops sheet
    - syncAllChannels: every 30 minutes
    - sendDailyReconciliation: daily at 7:30am PT"
5. Verify: Click the clock icon (Triggers) in left sidebar
   You should see two new entries with status "Active"

Why separate setup function: Initializing triggers in a centralized setup function (rather than embedding them in the sync function itself) prevents duplicate triggers if the sync runs multiple times. It also makes the initialization intent explicit and auditable.

Step 3: End-to-End Verification

Run testSync() again and monitor the execution log:


testSync execution expected output:
  - "Fetching iCal from Boatsetter: [URL]"
  - "Parsed N events"
  - "Writing to BookingLedger: M new bookings"
  - "Sending daily reconciliation email"
  - No "Exception: You do not have permission" errors

If you see permission errors, re-run the OAuth consent step. If you see execution errors in parsing or sheet writes, verify the ops sheet ID and BookingLedger tab name match the hardcoded values in the code (lines 115–125).

Related but Separate: Viator Email Scanner Setup

A parallel system scans incoming Viator booking confirmation emails and writes them to the same calendar and ledger. This lives in a different GAS project and requires its own setup activation.

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs
Function: jadaCalendarScanSetup() (line 364)
GAS editor: https://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit

This follows the same pattern: run the setup function once to register the email label monitoring trigger. Both systems write to the same BookingLedger, so they must share the ops sheet ID.

Infrastructure & Dependencies

  • Ops spreadsheet: Central source of truth; BookingLedger tab is the authoritative booking record
  • Google Calendar (queenofsandiego.com): Primary calendar; synced iCal events land here so they're visible in the booking UI
  • Gmail (ops account): Sends daily reconciliation emails via GmailApp; requires Mail Send scope
  • iCal URLs: Boatsetter URL is stored as an encrypted custom property in the GAS project (not hardcoded in source); this prevents credential sprawl

Key Design Decisions

  • 30-minute sync frequency: Balances booking freshness against API quota consumption and spreadsheet write limits