Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Boatsetter Calendar Sync: OAuth Reauth, Trigger Activation, and iCal Integration

What Was Done

The Boatsetter booking platform wasn't syncing to the operations calendar despite having iCal feed integration code already written. The root cause was twofold: (1) the time-based triggers in Google Apps Script had never been activated, and (2) Gmail and Calendar OAuth tokens had expired, which would cause permission failures on write operations. We fixed this by re-authorizing the GAS project and running the setup function to register the triggers.

The Three Blockers

  • Triggers not registered: The calendarSyncSetup() function in CalendarSync.gs had never been executed, so the time-based trigger for syncAllChannels() (every 30 minutes) and sendDailyReconciliation() (daily at 7:30am PT) didn't exist in the GAS runtime.
  • Gmail OAuth expired: The service account token for sending daily reconciliation emails needed re-consent.
  • Calendar OAuth revoked: The Google Calendar API scope had been revoked, so even if triggered, writes to the ops sheet's BookingLedger would fail with permission errors.

Technical Details: File Locations and Functions

The core files involved:

  • File: sites/queenofsandiego.com/CalendarSync.gs
  • Setup function: calendarSyncSetup() at line 355
  • Test function: testSync() at line 563
  • GAS Editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

This GAS project is bound to a Google Sheet in the ops folder that tracks all multi-channel bookings (Boatsetter, Viator, Sailo, and direct). The CalendarSync.gs script pulls iCal feeds from each platform and writes normalized booking records to the BookingLedger tab.

Step-by-Step Fix

Step 1: Re-authorize OAuth Scopes

OAuth tokens in GAS are tied to the script's declared scopes. When you first open the editor and run a function that requires Gmail or Calendar access, GAS triggers a consent dialog. This is required because we're using the user's own Gmail and Calendar APIs, not a service account.

Action:

  • Open the GAS editor (link above)
  • In the function dropdown, select testSync
  • Click the Run button
  • Google displays: "This app needs access to your Gmail and Calendar" → click Allow
  • You may see two consent prompts (Gmail scope, then Calendar scope) — authorize both

Why this works: testSync() is a minimal function that exercises both the Calendar write path and Gmail send path without actually syncing all channels. It forces the re-consent dialog before we run the larger setup.

Step 2: Register the Time-Based Triggers

GAS time-based triggers are stored in the script's runtime metadata. Simply having the function defined doesn't create the trigger — you must call the setup function at least once.

Action:

  • In the same editor, select calendarSyncSetup from the function dropdown
  • Click Run
  • Watch the execution log for confirmation

Expected log output:

9:57:20 AM    Notice    Execution started
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
9:57:24 AM    Info      - Next step: add iCal URLs to ICAL_FEEDS array as platforms go live
9:57:24 AM    Notice    Execution completed

To verify the triggers registered, click the clock icon in the left sidebar (Triggers view). You should see two time-based triggers listed.

Step 3: Verify the iCal Fetch Works

Now that OAuth is re-authorized and triggers are registered, run testSync() again to confirm the Boatsetter iCal feed is actually being fetched and parsed.

Expected log output (success):

Fetching Boatsetter iCal from https://ical.boatsetter.com/...
  Parsed 3 events from Boatsetter
Syncing to BookingLedger tab...
Writing 3 new bookings to sheet
CalendarSync complete. New bookings: 3
Sending daily reconciliation email to ops@...

Expected log output (failure — old state):

Exception: You do not have permission to access the Calendar API.

If you see the permission error, step 1 didn't fully take. Try running testSync() a second time.

Architecture: iCal Feed Integration Pattern

The CalendarSync script implements a multi-source booking aggregator:

  • ICAL_FEEDS array (CalendarSync.gs:30): Stores iCal URLs for each booking platform. Currently includes Boatsetter and Sailo; Viator and direct bookings are handled separately via email scanning (JadaCalendarDashboard.gs).
  • syncAllChannels() (line 120): Triggered every 30 minutes. Fetches each iCal feed, parses events using RFC 5545 parsing logic, and writes normalized records to the BookingLedger.
  • sendDailyReconciliation() (line 200): Triggered daily at 7:30am PT. Computes daily totals from BookingLedger and sends an email summary to ops.
  • BookingLedger tab: A sheet in the ops folder that acts as the source of truth for all bookings across all channels. Each row is a single booking record with normalized fields (date, time, platform, count, revenue, etc.).

Why this pattern: Instead of maintaining separate feeds for each platform, we normalize everything into a single sheet. This makes reconciliation, reporting, and downstream dashboards simple — they just read the BookingLedger.

Key Decision: OAuth Re-Consent Over Service Account

We chose user-based OAuth (requires manual re-consent) rather than upgrading to a service account. Reasons:

  • Lower infrastructure complexity: Service accounts require creating a separate Google Cloud project, managing JSON credentials, and handling credential rotation. For an internal tool, the extra overhead isn't justified.
  • Audit trail