Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Fixing Boatsetter Auto-Sync: Resolving Trigger Activation and OAuth Re-authorization in Google Apps Script

What Was Done

We resolved a three-part blocker preventing Boatsetter calendar events from auto-syncing into the Queen of San Diego booking ledger. The issue wasn't missing code—the iCal integration was already written and wired into CalendarSync.gs—but rather three activation gaps: (1) the time-based trigger was never registered, (2) Gmail and Calendar OAuth tokens had expired, and (3) we needed to distinguish between two separate GAS projects with overlapping responsibilities.

The fix involved running setup functions in the correct GAS editor context, re-authorizing service permissions, and verifying end-to-end sync with execution logs. All three blockers are now cleared.

Technical Details: The Three-Step Fix

Step 1: Re-authorization via testSync

The Gmail and Calendar OAuth scopes are managed at the GAS project level, not Python-side. When either permission expires, CalendarSync.gs can write to the Calendar API or send via Gmail, but the calls fail silently or throw permission exceptions.

To trigger re-consent:

  • Open GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  • File: sites/queenofsandiego.com/CalendarSync.gs
  • Function dropdown → select testSync() (line 563)
  • Click Run
  • Google prompts: "This app needs access to your Gmail and Google Calendar" → click Allow
  • May prompt twice (Gmail scope, then Calendar scope)—allow both

Why testSync first? The calendarSyncSetup() function only touches SpreadsheetApp and ScriptApp, which don't require external service authorization. testSync() actually calls CalendarApp.getEvents() and MailApp.sendEmail(), which trigger the consent dialogs. Running setup first without re-auth would register triggers that immediately fail on execution.

Step 2: Activate Time-Based Triggers

Once re-authorized, register the recurring sync jobs:

  • File: sites/queenofsandiego.com/CalendarSync.gs
  • Function: calendarSyncSetup() (line 355)
  • Function dropdown → select calendarSyncSetup
  • Click Run
  • Confirm in the left sidebar (clock icon → Triggers): two new triggers should appear:
    • syncAllChannels every 30 minutes
    • sendDailyReconciliation daily at 7:30 AM PT

What this does: calendarSyncSetup() creates a BookingLedger tab in the ops sheet (if missing), then calls ScriptApp.newTrigger()` to register two time-based triggers. The trigger IDs are stored in script properties so they persist across deployments.

Step 3: Verify with testSync Execution Log

Run testSync() again and monitor the Execution Log (View → Execution Log):

9:57:20 AM    Notice    Execution started
9:57:21 AM    Info    Fetching Boatsetter iCal from https://ical.sailo.io/...
9:57:22 AM    Info    Parsed 3 events from Boatsetter
9:57:23 AM    Info    CalendarSync complete. New bookings written: 2
9:57:24 AM    Notice    Execution completed

If you see Exception: You do not have permission to access the requested resource, re-auth didn't stick—repeat Step 1 and explicitly check both scopes in the OAuth grant screen.

Why This Architecture

The setup/trigger pattern exists because GAS requires explicit user authorization before any add-on-level action (creating triggers, accessing external APIs) can occur. Wrapping this in a dedicated setup function lets us:

  • Defer first-run overhead: Setup only runs once; sync functions run every 30 min thereafter.
  • Isolate permission scopes: testSync() maps to Calendar + Gmail; setup only needs SpreadsheetApp.
  • Recover from auth failures: If a token expires, re-running the test function (not the entire setup) is lighter-weight than re-creating triggers.
  • Separate concerns: Boatsetter iCal sync lives here; Viator email scanning lives in the other GAS project (separate JadaCalendarDashboard.gs), so each can be deployed and tested independently.

Key Implementation Details in CalendarSync.gs

iCal URL wiring: The Boatsetter feed is configured in the ICAL_FEEDS array (near the top of the file):

const ICAL_FEEDS = {
  boatsetter: 'https://ical.sailo.io/...',
  // More platforms added here as they go live
};

Sync logic: syncAllChannels() iterates over ICAL_FEEDS, fetches each iCal stream, parses events, and upserts them into the BookingLedger tab by matching on event_id to avoid duplicates.

Reconciliation: sendDailyReconciliation() runs at 7:30 AM PT and emails a summary to ops. This catches sync failures or missing bookings before the day's work begins.

Known Separate Item: Viator Email Scanner

Ticket t-8d86d5ba involves a different GAS project for Viator email parsing:

  • File: 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 also needs a one-time setup run to activate Gmail scanning triggers

This is listed separately because it's a different GAS project ID, different file, and different OAuth scope (Gmail read-only for Viator forwarding).

What's Next