Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Fixing Calendar Sync for Boatsetter: OAuth Re-authorization and Trigger Activation in Google Apps Script

The Boatsetter booking integration sat dormant for weeks despite having all the code in place. The iCal feed was wired, the sync logic was complete, but three critical blockers prevented any data from flowing: the trigger was never activated, Gmail OAuth had expired, and Calendar OAuth was revoked. Here's how we diagnosed and fixed each one.

The Problem: Three Layers of Failure

When ticket m-91325edb landed in needs-you, the symptoms were straightforward: no Boatsetter bookings appeared in the ops calendar. Investigation revealed:

  • Trigger Not Activated: The setup function calendarSyncSetup() in sites/queenofsandiego.com/CalendarSync.gs (line 355) was written but never executed. This function registers two time-based triggers—one to sync all channels every 30 minutes, another to send daily reconciliation emails at 7:30am PT.
  • OAuth Expiration: Gmail OAuth had expired (ticket t-8d86d5ba). The GAS runtime couldn't send reconciliation emails.
  • Calendar OAuth Revoked: Calendar API permissions were revoked, so even if the sync ran, it couldn't write bookings to the calendar.

The root cause wasn't code quality—it was a missing operational step. In Google Apps Script projects, time-based triggers must be explicitly created at runtime. Writing the trigger registration code isn't enough; someone has to execute the function that calls ScriptApp.newTrigger().

Technical Details: What Each Fix Does

Step 1: Re-authorizing OAuth Scopes

Google Apps Script manages OAuth consent at the project level, not per-function. When GAS needs Calendar or Gmail APIs, it prompts the first time that scope is accessed—not when the code is written or deployed.

The fix: run the testSync() function (CalendarSync.gs, line 563) which exercises both the Gmail and Calendar APIs. This triggers Google's consent dialog. The function:

  • Fetches the Boatsetter iCal URL from the ICAL_FEEDS array
  • Parses calendar events
  • Writes test entries to the BookingLedger tab in the ops sheet
  • Attempts to send a test email via Gmail

Running this function causes GAS to prompt: "This app needs access to your Google Calendar and Gmail." Accept the consent dialog, and the OAuth tokens are refreshed.

Why this approach? calendarSyncSetup() only touches SpreadsheetApp and ScriptApp, so it wouldn't trigger the Calendar/Gmail consent. We needed to run a function that actually uses those APIs.

Step 2: Activating Time-Based Triggers

After OAuth is refreshed, execute calendarSyncSetup() (CalendarSync.gs, line 355). This function:

  • Creates a BookingLedger tab in the ops sheet (if it doesn't exist)
  • Registers a time-driven trigger for syncAllChannels() to run every 30 minutes
  • Registers a time-driven trigger for sendDailyReconciliation() to run daily at 7:30am PT

After this executes successfully, you should see in the GAS editor sidebar (clock icon → Triggers) two active entries:

  • syncAllChannels – time-based, 30-minute intervals
  • sendDailyReconciliation – time-based, daily at 07:30

The trigger registration uses ScriptApp.newTrigger(functionName).timeBased().everyMinutes(30).create() pattern, which persists even if the deployment is re-run.

Step 3: Verification

Run testSync() again and watch the Execution Log. Successful output should show:

Fetching Boatsetter iCal...
  N events from Boatsetter
CalendarSync complete. New bookings: N
Gmail send successful for daily reconciliation

No "Exception: You do not have permission" errors means auth is working. If you see permission errors, the OAuth consent didn't complete—repeat Step 1.

Infrastructure and File Locations

The CalendarSync integration spans two GAS projects and one shared ops spreadsheet:

  • Primary Sync Project: Script ID 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii – editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit. Contains CalendarSync.gs with sync logic, trigger registration, and test functions.
  • Dashboard Scan Project: Script ID 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5 – editor URL: https://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit. Contains JadaCalendarDashboard.gs with the Viator email scanner, which also needs trigger activation via jadaCalendarScanSetup() (line 364).
  • Ops Spreadsheet: The BookingLedger tab is created in the shared ops sheet, which is referenced by both projects. This is the single source of truth for all booking data.

The ICAL_FEEDS array in CalendarSync.gs (around line 50) contains URLs for each booking platform. Boatsetter's iCal URL is already populated from repos.env, so no additional configuration is needed—the sync will pull Boatsetter bookings immediately once triggers are active.

Key Architectural Decisions

Why 30-minute sync intervals instead of real-time? Google Apps Script free tier doesn't support webhooks or event-driven triggers for external calendar feeds. Time-based polling is the only reliable pattern. 30 minutes balances responsiveness (bookings appear within half an hour) against quota limits (1440 executions per day max on free tier).

Why separate projects for sync and dashboard? The sync project runs autonomously on a schedule and only needs SpreadsheetApp, Calendar, and Gmail APIs. The dashboard project handles email scanning and UI logic, requiring Gmail API with more aggressive parsing. Separating them prevents OAuth scope creep and makes each one easier to reason about.

Why the BookingLedger tab instead of a separate sheet? Keeping all booking data in one sheet simplifies reconciliation formulas and makes it easier for humans to audit conflicts when a booking appears in multiple channels.

What's Next

With CalendarSync now active, the next