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

What Was Done

The Boatsetter iCal integration was written and deployed but non-functional due to three blockers: (1) expired Gmail OAuth tokens, (2) revoked Calendar API permissions, and (3) inactive time-based triggers in Google Apps Script. This post documents the resolution process, the exact functions involved, and why the OAuth model in GAS requires re-consent after permission revocation.

Technical Details: The Three Blockers

Blocker 1: Expired Gmail OAuth and Revoked Calendar Permissions

Google Apps Script maintains OAuth tokens at the project level, not the script level. When a user revokes Calendar access in their Google Account settings or when Gmail OAuth expires naturally, GAS doesn't auto-refresh for sensitive scopes. The next execution attempt fails silently or throws Exception: You do not have permission in the execution log.

The fix is counterintuitive: you don't re-authorize in a settings UI. Instead, you open the GAS editor and run any function that touches the affected scope. GAS intercepts the execution, prompts the user with a consent dialog, and stores fresh tokens.

Blocker 2: Inactive Triggers

The Boatsetter sync code existed in sites/queenofsandiego.com/CalendarSync.gs but the time-based triggers—syncAllChannels (every 30 minutes) and sendDailyReconciliation (daily at 7:30am PT)—were never created. Without triggers, the functions never execute, regardless of OAuth state.

Blocker 3: Function Name Mismatch in Ticket

Ticket m-91325edb referenced calendarDashboardSetup(), but the actual function in CalendarSync.gs is calendarSyncSetup() (line 355). The confusion likely arose during refactoring or handoff documentation.

Resolution Workflow

Step 1: Re-authorize OAuth Scopes

File: sites/queenofsandiego.com/CalendarSync.gs
Function: testSync() (line 563)


// Open the GAS editor:
https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

// In the function dropdown, select testSync and click Run.
// GAS prompts: "This app needs access to Gmail and Google Calendar"
// Click Allow for each scope.

The testSync() function is lightweight—it calls fetchIcalEvents() (line 480) and logBookings() (line 510) without writing to Calendar or Gmail. Its purpose is to exercise all OAuth scopes without side effects. Once it completes without permission errors, OAuth is valid.

Step 2: Activate Triggers

File: sites/queenofsandiego.com/CalendarSync.gs
Function: calendarSyncSetup() (line 355)


// In the same GAS editor, function dropdown → select calendarSyncSetup
// Click Run.
// Expected log output:
// "CalendarSync setup complete:
//   - BookingLedger tab created in ops sheet
//   - syncAllChannels: every 30 minutes
//   - sendDailyReconciliation: daily at 7:30am PT"

This function:

  • Creates the BookingLedger tab in the ops Google Sheet (idempotent; skips if exists)
  • Registers syncAllChannels as a 30-minute time-based trigger via ScriptApp.newTrigger()
  • Registers sendDailyReconciliation as a daily 7:30am PT trigger

You can verify triggers are live by clicking the clock icon in the left sidebar of the GAS editor. Both should appear in the Triggers panel with their schedule.

Step 3: Verify Sync Execution

Run testSync() again and inspect the Execution Log:


Expected success output:
9:57:20 AM    Notice    Execution started
9:57:22 AM    Info    Fetching Boatsetter iCal from: https://ical.boatsetter.com/...
9:57:23 AM    Info    Extracted 8 events from Boatsetter
9:57:24 AM    Info    CalendarSync complete. New bookings: 5
9:57:24 AM    Notice    Execution completed

Error indicator (auth still broken):
Exception: You do not have permission to access the requested resource.
    at fetchIcalEvents (CalendarSync.gs:480:15)

If you see the error, return to Step 1 and ensure both Gmail and Calendar consent dialogs were explicitly allowed.

Infrastructure Context: Why This Pattern Exists

Google Apps Script's OAuth model differs from traditional server-side OAuth flows. GAS tokens are scoped to the user running the script and the project ID, not to individual functions. When permissions are revoked or expire, GAS doesn't auto-prompt; instead, the runtime fails. This design forces visibility: if a script stops working due to auth, the developer must interact with the GAS editor to fix it, rather than silently failing in production.

For the Boatsetter integration, the iCal feed fetch (line 480) requires no auth, but writing to Google Calendar (line 495) and sending emails via Gmail (line 530) do. A single expired token blocks the entire chain.

Separate Item: Viator Email Scanner

Note: Ticket t-8d86d5ba (Viator email scanner OAuth) is a separate GAS project:

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 requires running jadaCalendarScanSetup() once to activate its trigger, but it's in a different GAS project. Do not confuse the two GAS editor URLs.

Key Decisions and Trade-offs

  • Why run testSync before calendarSyncSetup? The setup function only touches SpreadsheetApp and ScriptApp, which typically don't prompt for consent. If Gmail/Calendar tokens are expired, calendarSyncSetup will succeed (creating the triggers) but the actual sync functions will fail on first execution. Pre-running testSync ensures OAuth is fresh before triggers activate.
  • Why 30-minute sync intervals? Boatsetter bookings are real-time but GAS has a 6-hour min