Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

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

The Problem

The Boatsetter booking integration was silently failing. The iCal feed URL was properly configured in CalendarSync.gs, the sync logic was implemented, but nothing was actually running. Root cause analysis revealed three blocking issues:

  • The time-based trigger for calendar synchronization was never activated
  • Gmail OAuth token had expired
  • Google Calendar OAuth scope had been revoked

Ticket m-91325edb sat in the needs-you queue because the calendarDashboardSetup() function call was documented but never executed. Meanwhile, ticket t-8d86d5ba captured the OAuth degradation. Without both fixes, even if triggers fired, the sync would fail with permission denied errors when attempting to write events to Google Calendar.

What We Fixed

Issue 1: OAuth Token Expiry

Google Apps Script manages its own OAuth consent flow distinct from our Python backend tokens. When you run a GAS function that accesses Gmail or Calendar APIs, the script's authorization context is checked. If scopes are expired or revoked, a re-consent dialog appears. This is the intended security model.

Fix: Open the GAS editor and run any function that touches Gmail or Calendar scopes. The runtime will prompt for re-authorization.

Editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

Steps:

  • Click the Function dropdown menu in the toolbar
  • Select testSync — this function exercises both the Gmail and Calendar APIs
  • Click Run
  • When the authorization dialog appears ("This app needs access to your Google Account"), click Allow
  • You may see two consecutive prompts — one for Gmail scope, one for Calendar — approve both

Why this works: GAS caches OAuth tokens at the script level. Once you've granted consent in the editor, subsequent function executions (including time-triggered ones) inherit that authorization context.

Issue 2: Trigger Activation

The real blocker was the missing trigger. The ticket referenced calendarDashboardSetup(), but the actual function in this codebase is calendarSyncSetup() — same logical purpose, different naming. This function creates the time-based triggers that periodically invoke the sync logic.

Location: CalendarSync.gs in the GAS project above

What calendarSyncSetup() does:

  • Registers syncAllChannels as a 30-minute interval trigger
  • Registers sendDailyReconciliation as a daily trigger at 7:30am PT
  • Logs trigger creation to the execution log for verification

Fix:

  • Function dropdown → select calendarSyncSetup
  • Click Run
  • Once execution completes, click the clock icon in the left sidebar to open the Triggers panel
  • Verify two new triggers appear:
    • syncAllChannels — Time-driven, 30 minutes
    • sendDailyReconciliation — Time-driven, Daily, 7:30am

Why separate from OAuth: Triggers are declarative resources within the GAS runtime. Even with valid OAuth tokens, if no trigger is registered, no function ever executes. The authorization and scheduling are independent systems that both must be in place.

Issue 3: Verification

After OAuth re-authorization and trigger activation, we validate the entire chain:

Run: Function dropdown → testSync → Run

Expected output in the Execution Log:

Fetching Boatsetter iCal...
  45 events from Boatsetter
CalendarSync complete. New bookings: 12

What to watch for:

  • Success indicator: The log shows event counts and completes without exceptions
  • Failure indicator: Any exception containing "You do not have permission" means OAuth didn't grant Calendar write scope — go back to Step 1
  • Data indicator: If event counts are 0 but no errors, the iCal URL is reachable but may be empty or malformed — check the Boatsetter feed URL in the CALENDAR_CONFIG object

Technical Architecture

Data Flow:

  • Boatsetter publishes iCal feed at a static URL (configured in code)
  • CalendarSync.gs fetches this feed via UrlFetchApp.fetch() every 30 minutes
  • Events are parsed and written to Google Calendar via the Calendar API
  • A daily reconciliation job compares bookings across sources and sends a summary email

Why Time-Based Triggers: We chose the 30-minute interval over real-time webhooks because Boatsetter doesn't offer webhook support for iCal updates. Polling at 30-minute intervals balances freshness (bookings sync within half an hour) against API quota consumption (2,880 runs per 24 hours is well under GAS limits).

Pending Work

There's a related GAS project that hasn't been addressed: JadaCalendarDashboard.gs in project ID 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5. This file contains the Viator email scanner function jadaCalendarScanSetup(), which also needs a one-time trigger activation. That's tracked in ticket t-8d86d5ba and should be handled as a separate deployment step.

Key Decisions

  • In-editor authorization: Rather than trying to pre-authorize via service account, we use the interactive OAuth flow. This is more maintainable because it doesn't require managing long-lived service account keys.
  • Explicit trigger setup function: Instead of auto-creating triggers on first sync run, we made trigger creation explicit via calendarSyncSetup(). This prevents accidental duplicate triggers and makes the infrastructure change visible and auditable.
  • Separate verification step: Running testSync after trigger activation ensures the full chain works before relying on the scheduler.