Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Fixing Boatsetter Auto-Sync: OAuth Re-Authorization and Trigger Activation in Google Apps Script

Ticket m-91325edb had been sitting in needs-you for weeks. The Boatsetter iCal integration was fully coded and wired into our booking sync pipeline, but nothing was actually happening. The root cause turned out to be a classic GAS (Google Apps Script) deployment pattern we'd overlooked: the sync triggers were never activated, and the OAuth tokens had expired. Here's how we fixed it, and why the structure matters for future integrations.

What Was Done

We executed three sequential fixes to restore the Boatsetter booking auto-sync:

  • Re-authorized Gmail and Calendar OAuth scopes by running testSync() in the GAS editor
  • Activated time-based triggers by running calendarSyncSetup()
  • Verified end-to-end functionality by monitoring execution logs during a test sync

The entire process took ~10 minutes but required knowing exactly which file, function, and GAS project URL to use — something that wasn't documented clearly, which is why we now enforce file paths and function names in all handoffs.

Technical Details: The CalendarSync.gs Architecture

The booking synchronization lives in a single Google Apps Script project. Here's the exact structure:

  • File: sites/queenofsandiego.com/CalendarSync.gs
  • GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii
  • GAS Editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

This file handles three distinct responsibilities:

  1. iCal Ingestion: Fetches booking data from multiple platforms (Boatsetter, Sailo, Viator) via iCal URLs stored in environment variables
  2. Calendar Writes: Parses iCal events and writes them to Google Calendar
  3. Daily Reconciliation: Sends a Gmail summary to ops with booking deltas and anomalies

The Boatsetter iCal URL is loaded in the ICAL_FEEDS array (line ~45), which the syncAllChannels() function iterates over every 30 minutes.

OAuth Scope Issues

GAS has two layers of authorization:

  • Script-level OAuth: Triggered when a function actually exercises a scope (e.g., calling CalendarApp.getCalendarById() or GmailApp.sendEmail())
  • Trigger-level OAuth: Inherited from whoever ran the trigger setup function

The problem: calendarSyncSetup() only touches ScriptApp (to create triggers) and SpreadsheetApp (to create the BookingLedger tab). It never calls Calendar or Gmail methods, so no consent dialogs fire. When the triggers fire later and try to write to Calendar or send Gmail, they fail with permission errors because the OAuth tokens had expired in the interim.

The fix: Run testSync() first, which actually calls both CalendarApp and GmailApp methods. This triggers the browser consent flow twice — once for Calendar, once for Gmail — and caches fresh tokens.

Step-by-Step Execution

Step 1: Re-authorize OAuth Tokens

  1. Open the GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  2. Function dropdown (top-left) → select testSync (line 563)
  3. Click Run
  4. Google prompts: "This app needs access to your Google Account" → click Allow
  5. Wait ~10 seconds; it may prompt a second time for a different scope → click Allow again
  6. Check Execution Log (View → Execution Log) for any errors. A successful run shows: 
    Fetching Boatsetter iCal...
[N] events from Boatsetter
CalendarSync complete. New bookings: [N]

Step 2: Activate Time-Based Triggers

  1. Function dropdown → select calendarSyncSetup (line 355)
  2. Click Run
  3. Execution log should show: 
    CalendarSync setup complete:
  - BookingLedger tab created in ops sheet
  - syncAllChannels: every 30 minutes
  - sendDailyReconciliation: daily at 7:30am PT
  - Next step: add iCal URLs to ICAL_FEEDS array as platforms go live
  4. Verify in the sidebar: click the clock icon (Triggers) → confirm you see two rows:
    • syncAllChannels — Time-based (30 minutes)
    • sendDailyReconciliation — Time-based (daily, 7:30am)

Step 3: Verify the Integration

Run testSync one more time and watch for the Boatsetter fetch in the logs. No Exception: You do not have permission messages = success.

Key Decisions

Why consolidate everything in one CalendarSync.gs file? Because all three booking sources (Boatsetter, Sailo, Viator email) feed into the same calendar and daily reconciliation email. Splitting them across projects would require managing OAuth and scheduling independently, which increases operational complexity. The tradeoff is that CalendarSync.gs is ~800 lines — we mitigate this with clear section comments and the one-function-per-responsibility pattern.

Why 30-minute sync intervals instead of real-time webhooks? iCal feeds don't support webhooks; we'd need to poll anyway. 30 minutes is a reasonable balance between freshness (ops sees bookings within 30min) and API quota usage (GAS has generous limits but they're still finite). If we onboard a platform that supports webhooks (e.g., Boatsetter API v2), we can add a separate event-driven flow without touching CalendarSync.gs.

Why separate the Viator email scanner? The Viator integration runs in a different GAS project (JadaCalendarDashboard.gs in