Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

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

The Problem

The Boatsetter iCal integration was fully coded and wired into the system, but syncs weren't happening. Three blockers prevented bookings from flowing into the calendar:

  • The calendarSyncSetup() trigger had never been initialized
  • Gmail OAuth token had expired
  • Calendar OAuth had been revoked

Without the trigger, even valid credentials wouldn't matter. Without valid credentials, the trigger would fail silently on each execution attempt.

Technical Details: The Fix Sequence

Step 1: OAuth Re-authorization (Both Services)

Google Apps Script bundles OAuth scopes at the project level. When you first run any function that touches Gmail or Calendar APIs, GAS prompts for consent. If those tokens expire or are revoked, re-running a function that uses those scopes triggers the re-consent flow.

The key insight: calendarSyncSetup() only touches SpreadsheetApp and ScriptApp (creating the BookingLedger tab and registering time-based triggers). It doesn't actually invoke Gmail or Calendar APIs, so it won't prompt for re-authorization. We needed to run a different function first.

File: sites/queenofsandiego.com/CalendarSync.gs:563

Function to run first: testSync()

This test function actually exercises the iCal fetch, calendar writes, and Gmail sends — all the operations that require Gmail and Calendar scopes. Running it triggers the OAuth consent dialogs.

Process in the GAS editor:

  • Open: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  • Function dropdown → select testSync
  • Click Run
  • Google prompts: "This app needs access to..." → click Allow
  • May prompt twice: Gmail scope first, then Calendar scope — allow both
  • Check the Execution Log for any permission errors

Step 2: Activate Time-Based Triggers

File: sites/queenofsandiego.com/CalendarSync.gs:355

Function to run: calendarSyncSetup()

This function performs three critical tasks:

  • Creates the BookingLedger tab in the ops spreadsheet (if it doesn't exist)
  • Registers a time-based trigger for syncAllChannels() — every 30 minutes
  • Registers a time-based trigger for sendDailyReconciliation() — daily at 7:30 AM PT

Why two separate triggers? The Boatsetter sync is fast (single iCal fetch + calendar writes), so 30-minute intervals are sufficient for real-time booking updates. The reconciliation email is heavier (aggregates all platforms, formats a digest) and only needed once daily.

Process:

  • In the same GAS editor, Function dropdown → select calendarSyncSetup
  • Click Run
  • Check left sidebar: clock icon → Triggers tab
  • Verify two new time-based triggers appear with the correct schedule

Step 3: Verify the Full Flow

Run testSync() again (same file, line 563) and watch the Execution Log:

9:57:20 AM    Notice    Execution started
9:57:24 AM    Info      Fetching Boatsetter iCal...
9:57:24 AM    Info        5 events from Boatsetter
9:57:24 AM    Info      Writing 5 events to calendar...
9:57:24 AM    Info      CalendarSync complete. New bookings: 5
9:57:24 AM    Info      Sending reconciliation email...
9:57:24 AM    Notice    Execution completed

If you see Exception: You do not have permission to access this resource, the OAuth re-auth didn't complete. Go back to Step 1 and ensure you allowed both scopes.

Architecture: Why This Design?

The Boatsetter integration uses a pull-based iCal model rather than webhooks:

  • Why iCal polling? Boatsetter's free tier doesn't offer webhook callbacks. Polling is the only way to get real-time updates without custom integrations.
  • Why 30 minutes? Most user expectations for calendar sync are "within the hour." 30 minutes balances freshness against quota usage (GAS allows ~20,000 function calls/day for a standard project). At 48 calls/day (2 per hour), we're well under quota.
  • Why separate triggers? Allows each job to scale independently. If reconciliation emails start taking too long, we can adjust that schedule without affecting sync frequency.

The BookingLedger tab in the ops spreadsheet is the source of truth for what was actually written to the calendar. This is critical for debugging: if a booking doesn't appear in the calendar, we check the ledger to see if it was attempted and why it failed.

Key Decisions

Why run testSync() before calendarSyncSetup()? Setup functions in GAS often don't exercise all permissions. By running the test first, we ensure the OAuth consent dialogs fire while we're watching, making failures visible immediately rather than discovering them later when the triggers execute silently.

Why store the iCal URL in CalendarSync.gs rather than a config sheet? Boatsetter URLs are per-listing and shouldn't change frequently. Keeping them in code (with comments) makes them easy to audit during code review. If platforms change URLs, we update code, run calendarSyncSetup() again, and the next 30-minute cycle picks up the new feed.

Why the BookingLedger tab? It's a write-only log of every event the sync attempted to create. If there's a gap between "Boatsetter says we have a booking" and "the calendar shows it," the ledger tells us whether it was a sync failure (not attempted) or a permissions issue (attempted but failed).

What's Next

The Boatsetter sync is now live. The next integration point is adding additional iCal feeds:

  • Viator email scanner: File sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs:364 → function jadaCalendarScanSetup(). Run this once in its own GAS project: https://script.google.