Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Reactivating Multi-Channel Calendar Sync: OAuth Recovery and Trigger Setup in Google Apps Script

The Queen of San Diego booking pipeline had a silent failure: Boatsetter inventory was being fetched and parsed correctly, but the calendar sync wasn't actually running. This post walks through diagnosing and fixing three interconnected blockers: expired OAuth tokens, inactive triggers, and trigger setup functions that need explicit invocation.

The Problem: Three Layers of Failure

Calendar sync for Boatsetter (and other platforms) was written and deployed, but:

  • OAuth tokens expired — Gmail and Calendar scopes had revoked permissions
  • Triggers were never registered — The setup function that creates time-based triggers was never executed
  • Two separate GAS projects needed setup — CalendarSync and JadaCalendarDashboard are in different projects with different trigger requirements

This is a common pattern in Google Apps Script deployments: the code is correct, but the runtime state (triggers + OAuth consent) hasn't been initialized.

Technical Details: File Locations and Functions

The primary file involved:

sites/queenofsandiego.com/CalendarSync.gs

Key functions in this file:

  • calendarSyncSetup() (line 355) — Creates time-based triggers and initializes the BookingLedger sheet
  • testSync() (line 563) — Executes a single sync cycle; triggers OAuth consent dialogs
  • syncAllChannels() (line 201) — Main sync loop; called every 30 minutes by trigger
  • sendDailyReconciliation() (line 290) — Sends daily email summary; called daily at 7:30am PT

A secondary setup needed in a separate GAS project:

sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs

Function:

  • jadaCalendarScanSetup() (line 364) — Registers triggers for Viator email scanning (separate issue from Boatsetter)

The Fix: Three Sequential Steps

Step 1: Re-authorize OAuth Scopes

Google Apps Script manages OAuth at the project level. When a user hasn't explicitly granted permission, or the grant expires, functions that touch Gmail or Calendar APIs will fail silently or throw permission errors.

The fix is to trigger the OAuth consent dialog by running any function that requires those scopes:

1. Open GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
2. Select testSync from the function dropdown
3. Click Run
4. Google prompts: "This app needs access to your Google Account"
5. Click Allow (may prompt twice for Gmail and Calendar scopes separately)

Why this step first? The calendarSyncSetup() function only touches SpreadsheetApp and ScriptApp, so it won't trigger consent dialogs. But testSync() calls syncAllChannels(), which writes to Calendar and sends via Gmail, so it will force the consent flow.

Step 2: Register the Triggers

With OAuth re-authorized, run the setup function:

1. In the same GAS editor, select calendarSyncSetup from the function dropdown
2. Click Run
3. Watch the execution log for:
   - "BookingLedger tab created in ops sheet"
   - "syncAllChannels: every 30 minutes"
   - "sendDailyReconciliation: daily at 7:30am PT"
4. Verify in the sidebar (clock icon → Triggers): two new triggers should appear

The calendarSyncSetup() function does this internally:

// Pseudo-code of what calendarSyncSetup() does
ScriptApp.newTrigger('syncAllChannels')
  .timeBased()
  .everyMinutes(30)
  .create();

ScriptApp.newTrigger('sendDailyReconciliation')
  .timeBased()
  .atHour(7)  // 7:30am PT = 14:30 UTC (adjusted for PT offset)
  .everyDays(1)
  .create();

Step 3: Verify the Sync Works

Run testSync() again and examine the execution log:

// Expected output on success
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      Found 3 events from Boatsetter
9:57:24 AM    Info      CalendarSync complete. New bookings: 2
9:57:25 AM    Notice    Execution completed

// If auth is still broken, you'll see:
Exception: You do not have permission to call Calendar.Events.insert

If you see the permission exception, repeat Step 1 — the OAuth dialog may not have completed fully, or the grant was rejected.

Architecture: Why This Pattern Exists

Google Apps Script's trigger system requires a "setup once, run forever" pattern:

  • Triggers are project state, not code — They exist in Google's infrastructure, not in your .gs file. Deploying new code doesn't create triggers; you must call ScriptApp explicitly.
  • OAuth is user-level consent — The first time a function tries to access Gmail or Calendar, Google intercepts and asks the user. This can't happen in non-interactive contexts (like Cloud Tasks or scheduled runs), so you must pre-authorize.
  • Separation of concerns — The sync logic is in syncAllChannels(), but the operational setup (triggers + sheets) is in calendarSyncSetup(). This makes it testable: testSync() can run the logic without triggers, or with manual invocation.

The iCal Integration

The actual Boatsetter integration is in syncAllChannels() and uses the iCal URL stored in an environment variable (checked via PropertiesService):

// Inside syncAllChannels()
const boatsetterUrl = PropertiesService.getUserProperties().getProperty('BOATSETTER_ICAL_URL');
const response = UrlFetchApp.fetch(boatsetterUrl);
const events = parseICalendar(response.getContentText());
// Write events to Calendar

The iCal feed is polled every 30 minutes by the trigger, compared against existing calendar events, and new bookings are inserted. This avoids duplicates via a checksum or ID matching strategy.

Key Decision: Separate GAS Projects for Different Workflows

The setup discovered that there are two GAS projects in use:

  • Cal