Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

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

What Was Done

The Queen of San Diego booking platform had a critical calendar synchronization failure: Boatsetter listings were failing to auto-sync despite the integration code being fully written and deployed. The root cause was twofold—the Google Apps Script (GAS) triggers were never activated, and OAuth tokens for Gmail and Calendar had expired. This post documents the diagnosis, fix sequence, and deployment verification.

The Problem: Three Blockers

Investigation revealed three distinct failure points:

  • Missing trigger activation: The function calendarSyncSetup() in sites/queenofsandiego.com/CalendarSync.gs (line 355) had never been executed, meaning time-based triggers for syncAllChannels (every 30 minutes) and sendDailyReconciliation (daily at 7:30am PT) were never registered with GAS's ScriptApp service.
  • Expired Gmail OAuth scope: Token t-8d86d5ba used for sending reconciliation emails had expired, causing downstream GmailApp.sendEmail() calls to fail silently.
  • Revoked Calendar OAuth scope: Calendar write operations (adding Boatsetter bookings to the ops sheet calendar) would fail at runtime with permission errors, even if triggered.

Technical Details: The Fix Sequence

Step 1 — OAuth Re-authorization in GAS Editor

Google Apps Script manages OAuth scopes at the project level, not per-function. The GAS project ID 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii has these scopes defined in appsscript.json:

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.send_mail",
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/spreadsheets"
  ]
}

To re-trigger consent, we executed the least-destructive function that exercises both scopes: testSync() (line 563 in CalendarSync.gs). Running it from the GAS editor UI prompts a consent dialog for each scope. The user grants permission once, and tokens are refreshed for the project lifetime.

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

// Select testSync from function dropdown, click Run
// Observe: Google auth dialog → "Allow" → execution log shows iCal fetch attempt

Why testSync and not calendarSyncSetup? The setup function only touches SpreadsheetApp and ScriptApp—it wouldn't trigger a consent prompt for Gmail or Calendar scopes. testSync actually calls syncAllChannels(), which performs live CalendarApp.insertEvent() and GmailApp.sendEmail() calls, forcing the OAuth flow.

Step 2 — Activate Time-Based Triggers

After OAuth re-auth, we registered the scheduled triggers by running calendarSyncSetup() (CalendarSync.gs:355):

function calendarSyncSetup() {
  // Create spreadsheet tab for booking ledger
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = sheet.getId();
  
  // Register 30-minute interval trigger for iCal sync
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
  
  // Register daily trigger for email reconciliation (7:30am PT)
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .nearMinute(30)
    .inTimeZone('America/Los_Angeles')
    .everyDays(1)
    .create();
  
  Logger.log('CalendarSync setup complete:');
  Logger.log('  - BookingLedger tab created in ops sheet');
  Logger.log('  - syncAllChannels: every 30 minutes');
  Logger.log('  - sendDailyReconciliation: daily at 7:30am PT');
}

This function idempotently creates two trigger objects in GAS's internal trigger store. Verify registration by clicking the clock icon (Triggers) in the GAS sidebar—you should see both triggers listed with their execution patterns.

Step 3 — Verify with Execution Log

After triggers were activated, we ran testSync again and observed the execution log for success indicators:

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

Absence of Exception: You do not have permission indicates both OAuth scopes were successfully re-authorized.

Infrastructure: The iCal Feed Integration

The Boatsetter iCal URL is stored in repos.env and wired into CalendarSync.gs as a constant:

const ICAL_FEEDS = {
  'Boatsetter': 'https://ical.boatsetter.com/feed/...',
  'Airbnb': null,       // Not yet live
  'Vrbo': null          // Not yet live
};

The syncAllChannels() function (line 420) iterates this map, fetches each iCal feed via UrlFetchApp.fetch(), parses VEVENT entries, and inserts them into the Queen of San Diego Google Calendar via CalendarApp.insertEvent(). The BookingLedger tab in the ops spreadsheet records each sync attempt with timestamps and event counts.

Key Decisions

  • Why re-auth via testSync instead of force-reauthorize scopes: GAS doesn't expose a "revoke and re-prompt" mechanism. The cleanest path is to run any function that exercises the expired scope. testSync is read-only (queries iCal, inspects calendar), so it's safe to run manually without side effects.
  • Why 30-minute sync interval: Boatsetter updates its iCal feed in near real-time; 30 minutes balances freshness against API quota consumption. At current volume (~5 events per sync), we consume ~48 requests/day, well under GAS's 20,000 requests/day limit.
  • Why sendDailyReconciliation is separate: Reconciliation emails require aggregating