Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Calendar Sync Deadlock: GAS OAuth Recovery and Trigger Activation for Multi-Channel Booking Ingestion

The Problem

Boatsetter bookings weren't syncing to the ops calendar despite the integration code being complete and the iCal feed URL already wired in. Root cause analysis revealed three sequential blockers:

  • Expired Gmail OAuth — The GAS project had lost authorization to send reconciliation emails
  • Revoked Calendar OAuth — The GAS project couldn't write events to Google Calendar
  • Missing time-based triggers — The sync and reconciliation functions were never scheduled, so even with valid auth, nothing would run

This is a classic "works in isolation, fails in production" scenario where local testing masks permission and scheduling issues.

Technical Details: What We Fixed

File: sites/queenofsandiego.com/CalendarSync.gs

This Apps Script file contains the multi-channel booking sync engine. Three key functions:

  • testSync() (line 563) — Single-run test harness that fetches all iCal feeds, writes to calendar, and sends email. Used to verify auth and logic.
  • calendarSyncSetup() (line 355) — Idempotent setup function that creates the BookingLedger sheet tab and registers two time-based triggers via ScriptApp.newTrigger().
  • syncAllChannels() (line 412) — The main scheduled function, triggered every 30 minutes, that orchestrates the iCal fetch → parse → deduplicate → write → email pipeline.

The iCal feed array (around line 100) currently includes Sailo and a placeholder for Boatsetter. The Boatsetter URL was already in repos.env and loaded correctly, but the ScriptApp.newTrigger() calls in calendarSyncSetup() had never been executed.

Why This Matters

Google Apps Script distinguishes between user-level permissions (granted when you run or edit a script) and scheduled trigger execution (which runs under the project's service account context, but still needs initial user consent). The setup function registers the triggers, but you must run it at least once in the GAS editor to persist them in the project.

Recovery Steps

Step 1: Trigger OAuth Re-consent

Open the GAS editor at https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit.

Select testSync from the function dropdown and click Run. Google will prompt:

This app wants to access your Google Account
- Gmail (to send emails)
- Google Calendar (to create/update events)

Click Allow for each scope. This revalidates the project's OAuth tokens.

Step 2: Register the Triggers

After re-auth succeeds, select calendarSyncSetup from the function dropdown and click Run. The execution log should show:

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

Verify in the left sidebar: click the clock icon (Triggers). You should see two entries with "Status: OK".

Step 3: Validate the Full Pipeline

Run testSync again. Watch the Execution Log for lines like:

Fetching iCal feeds...
  Sailo: 3 events
  Boatsetter: 2 events
Deduplicating and writing to calendar...
New bookings written: 5
Sending daily reconciliation email to ops...
CalendarSync complete.

If you see "Exception: You do not have permission to access the calendar," the OAuth re-consent didn't stick — repeat Step 1.

Infrastructure Context

GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii

Ops Spreadsheet: Shared Google Sheet where the BookingLedger tab stores deduped bookings. This serves as the source of truth for the calendar and email reconciliation.

Target Calendar: The GAS project writes to the Rady Shell ops calendar via the Calendar API.

iCal Feed Sources:

  • Sailo (already active)
  • Boatsetter (newly enabled, URL from repos.env)
  • Viator (separate scanner, different GAS project)

Key Decisions

  • Why 30-minute sync interval? Boatsetter and Sailo typically propagate new bookings within 5–10 minutes; 30 minutes balances freshness against API rate limits and quota overhead. Daily reconciliation emails run separately at 7:30am PT to avoid noise.
  • Why idempotent setup? calendarSyncSetup() checks if BookingLedger exists and if triggers are already registered before creating them. This allows re-running without duplicate triggers.
  • Why OAuth re-consent via testSync? calendarSyncSetup() only uses SpreadsheetApp and ScriptApp APIs, which don't prompt for Gmail or Calendar permissions. Running testSync first exercises the full scope set, forcing consent dialogs.

What's Next

Related but separate: The Viator email scanner lives in a different GAS project (JadaCalendarDashboard.gs, script ID 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5). Its jadaCalendarScanSetup() function also needs to be run once to register triggers for the Gmail-to-calendar scanner.

Monitoring: Check the Execution Log in the GAS editor every few hours over the next day to confirm syncAllChannels() is running on schedule and sendDailyReconciliation() delivers emails. If either fails, the error message will pinpoint the cause.

Expansion: As additional booking platforms go live (Airbnb, VRBO, etc.), add their iCal feed URLs to the ICAL_FEEDS array in