Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Reactivating Multi-Channel Calendar Sync: OAuth Recovery and Trigger Deployment

Boatsetter bookings weren't auto-syncing to the operations calendar despite weeks of development work. The root cause wasn't missing code—it was three straightforward blockers: expired OAuth tokens, unactivated time-based triggers, and scattered setup functions across two separate Google Apps Script projects. Here's how we diagnosed and fixed it.

The Problem: Three Layers of Failure

The infrastructure was built but dormant:

  • Expired OAuth: Gmail and Calendar scopes in the main GAS project had been revoked or expired, so even if sync ran, writes would fail with permission errors.
  • Inactive triggers: The time-based triggers for syncAllChannels (every 30 minutes) and sendDailyReconciliation (daily at 7:30am PT) were never registered in the GAS Apps Script runtime.
  • Split project architecture: Setup functions were scattered across two separate GAS projects—CalendarSync in one, Viator email scanning in another—creating confusion about what needed to run where.

File and Function Mapping

The ticket referenced vague function names. Here's the exact breakdown:

  • Primary sync logic: sites/queenofsandiego.com/CalendarSync.gs (GAS project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii)
    • calendarSyncSetup() at line 355 — registers triggers and creates the BookingLedger tab in the ops sheet
    • testSync() at line 563 — manual test function to verify auth and iCal fetch
    • syncAllChannels() — called by the 30-minute trigger; fetches from ICAL_FEEDS array
  • Viator scanner (separate project): sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs (GAS project ID: 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5)
    • jadaCalendarScanSetup() at line 364 — separate trigger registration for Viator email scanning

Fix Sequence: OAuth → Triggers → Verification

Step 1: Re-authorize OAuth scopes

GAS OAuth is project-level and tied to the executing user. Opening the editor and running any function triggers the consent dialog for required scopes (Gmail, Calendar, Sheets).

1. Navigate to: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
2. Function dropdown → select "testSync"
3. Click Run
4. Google prompts: "This app needs access to Gmail, Google Calendar, Google Sheets"
5. Click Allow (may prompt twice for different scopes)
6. Watch the Execution Log for "Fetching Boatsetter iCal..." or permission errors

Why testSync first? Because calendarSyncSetup() only touches SpreadsheetApp and ScriptApp scopes—it won't trigger Gmail and Calendar consent dialogs. Testing first ensures all three OAuth flows complete before activating the trigger.

Step 2: Activate time-based triggers

1. In the same editor, Function dropdown → select "calendarSyncSetup"
2. Click Run
3. Check the Triggers panel (clock icon in left sidebar)
4. Verify two new triggers exist:
   - syncAllChannels: every 30 minutes
   - sendDailyReconciliation: daily 7:30 AM America/Los_Angeles

The calendarSyncSetup() function (lines 355–380) creates the BookingLedger tab in the ops sheet and registers the triggers programmatically using ScriptApp.newTrigger(). This is idempotent—running it twice won't duplicate triggers.

Step 3: Verify sync execution

Run testSync again and inspect the Execution Log for:
✓ "Fetching Boatsetter iCal..."
✓ "N events added to calendar"
✓ "CalendarSync complete. New bookings: N"
✗ "Exception: You do not have permission to..."

The testSync() function calls syncAllChannels() directly, which fetches the Boatsetter iCal URL stored in the ICAL_FEEDS array (defined around line 75) and writes matching events to the Google Calendar tied to the SpreadsheetApp context.

Why the Architecture Matters

The code was already written correctly. The CalendarSync.gs file includes:

  • iCal URL parsing: Fetches the Boatsetter feed, parses VEVENT blocks, extracts title, start time, and end time
  • Deduplication logic: Checks the BookingLedger tab to prevent duplicate calendar entries
  • Multi-channel support: The ICAL_FEEDS object supports Boatsetter, Sailo, and Viator—ready for expansion as platforms go live
  • Gmail reconciliation: Sends a daily digest email summarizing new bookings and sync errors

The issue was purely operational: the code wasn't executing because the trigger pipeline was never activated. This is a common pattern in GAS projects where setup functions are one-time operations—forgetting to run them leaves powerful automation dormant.

Separate Item: Viator Email Scanner Setup

The Viator email scanning (file: JadaCalendarDashboard.gs, function: jadaCalendarScanSetup()) lives in a different GAS project and still requires a manual setup run. This is tracked separately and uses Gmail's GmailApp API to scan incoming Viator confirmation emails and extract booking details.

Navigate to: https://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit
Function dropdown → select "jadaCalendarScanSetup"
Click Run

Key Decisions and Trade-offs

  • Why manual OAuth instead of Service Account? Service Accounts require domain-wide delegation (admin OAuth consent) and calendar resource delegation, which adds friction. User-level OAuth via the standard GAS flow is faster for internal tools and doesn't require Google Workspace domain admin approval.