Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing the Boatsetter Calendar Sync: OAuth Recovery and Trigger Activation in Google Apps Script

What Was Done

The Boatsetter iCal integration was technically complete in code but non-functional due to three distinct blockers:

  • Gmail OAuth token had expired (ticket t-8d86d5ba)
  • Google Calendar OAuth was revoked, preventing calendar writes
  • The time-based trigger system was never activated — the sync functions existed but were never scheduled to run

This investigation and fix involved re-authorizing the Apps Script project, activating the trigger infrastructure, and validating the end-to-end sync pipeline.

Technical Details

Step 1: OAuth Re-Authorization

Google Apps Script maintains separate OAuth scopes for Gmail and Calendar at the project level. Unlike Python or Node.js OAuth flows, GAS scopes are implicit and tied to whichever APIs the code calls. When a scope is revoked or expires, the entire execution fails.

File: sites/queenofsandiego.com/CalendarSync.gs

Function: testSync() (line 563)

The fix: open the GAS editor, select testSync from the function dropdown, and click Run. This triggers Google's consent dialog for any missing scopes:

function testSync() {
  const result = syncAllChannels();
  Logger.log('Test sync result:', result);
}

Why testSync and not calendarSyncSetup()? Because calendarSyncSetup() only touches SpreadsheetApp and ScriptApp — it doesn't trigger Gmail or Calendar permission prompts. The testSync function actually calls syncAllChannels(), which attempts to fetch iCal URLs, parse events, write to the Calendar, and send Gmail notifications. That's what triggers the OAuth consent dialogs.

After clicking Allow on the consent screen (may appear twice — once for Gmail, once for Calendar), the OAuth tokens are refreshed at the GAS project level and remain valid for future executions.

Step 2: Trigger Activation

File: sites/queenofsandiego.com/CalendarSync.gs

Function: calendarSyncSetup() (line 355)

After OAuth is restored, activate the time-based triggers:

function calendarSyncSetup() {
  const scriptId = ScriptApp.getScriptId();
  
  // Remove existing triggers
  ScriptApp.getProjectTriggers().forEach(trigger => {
    ScriptApp.deleteTrigger(trigger);
  });
  
  // Create new triggers
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
    
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .nearMinute(30)
    .everyDays(1)
    .create();
    
  SpreadsheetApp.getActiveSpreadsheet()
    .insertSheet('BookingLedger')
    .setFrozenRows(1);
}

This function does three things:

  • Clears any stale triggers (prevents duplicates if run multiple times)
  • Creates syncAllChannels as a 30-minute repeating trigger
  • Creates sendDailyReconciliation as a daily trigger at 7:30am PT
  • Creates the BookingLedger sheet to store booking records

After running this, check the GAS editor's left sidebar (clock icon → Triggers) to confirm both are registered. The execution log will confirm success:

9:57:24 AM    Info    CalendarSync setup complete:
9:57:24 AM    Info      - BookingLedger tab created in ops sheet
9:57:24 AM    Info      - syncAllChannels: every 30 minutes
9:57:24 AM    Info      - sendDailyReconciliation: daily at 7:30am PT

Step 3: Validation

Run testSync() again and monitor the execution log. A successful sync will show:

Fetching Boatsetter iCal...
  12 events from Boatsetter
Parsing events...
Writing to calendar...
CalendarSync complete. New bookings: 3
Sending daily reconciliation email...

If you see Exception: You do not have permission to access the requested resource, OAuth is still revoked — repeat Step 1.

Architecture and Integration Points

CalendarSync.gs is the synchronization hub for multiple booking platforms. Its architecture reflects the operational structure:

  • iCal Feed Array (ICAL_FEEDS): Stores URLs for each booking platform. Boatsetter's URL is configured here but only active if the trigger fires.
  • Target Calendar: Writes to the shared operations Google Calendar, making bookings visible across teams.
  • Booking Ledger (BookingLedger sheet): Maintains a transaction log of all synced bookings, enabling reconciliation and audit trails.
  • Daily Reconciliation Email: Sends a summary to ops@queenofsandiego.com every morning at 7:30am PT with new bookings and any sync errors.

The GAS project ID is 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii. This is distinct from the Viator email scanner project (JadaCalendarDashboard.gs), which has its own project ID and trigger setup at line 364.

Key Decisions

Why 30-minute sync intervals? Boatsetter and other platforms update their iCal feeds continuously. A 30-minute window provides near-real-time visibility for ops while staying within GAS free tier execution quotas (6 hours/day). For critical same-day bookings, this is sufficient.

Why OAuth at the GAS level vs. service account? The Calendar and Gmail services are owned by the ops@ account, not a service account. Using the ops@ user's OAuth is simpler than setting up service-to-service delegation and avoids the overhead of managing separate credentials.

Why separate triggers for sync and reconciliation? The reconciliation email is expensive — it queries the full booking ledger and generates a formatted summary. Running it hourly would be wasteful. Running sync at 30-min intervals keeps the ledger fresh, and the daily email provides a consolidated view.

What's Next

With triggers now active and OAuth restored, Boatsetter bookings will auto-sync every 30 minutes. Two related items remain:

  • Viator email scanner setup: The jadaCalendarScanSetup() function in the second GAS project (Jada