Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

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

A booking sync pipeline from Boatsetter to Queen of San Diego's ops spreadsheet had been written and deployed but wasn't actually running. The root cause: three distinct blockers across Google OAuth tokens and trigger infrastructure. This post walks through the diagnosis and the exact steps to resolve it.

The Problem: Three Separate Failures

Ticket m-91325edb reported that Boatsetter bookings weren't syncing to the calendar. Investigation revealed:

  • OAuth expiration (Gmail): The Gmail service token used to send daily reconciliation emails had expired.
  • OAuth revocation (Calendar): The Calendar API token was revoked, blocking all write operations to the ops spreadsheet's BookingLedger tab.
  • Missing trigger activation: Even if auth were fixed, the time-based triggers that orchestrate the sync weren't registered in ScriptApp.

The iCal feed URL for Boatsetter was already wired into the code. The infrastructure existed. It just wasn't running.

Technical Architecture: CalendarSync.gs

The sync pipeline lives in a single GAS file:

sites/queenofsandiego.com/CalendarSync.gs

This file contains three entry points:

  • calendarSyncSetup() (line 355) — Registers time-based triggers and creates the BookingLedger tab in the ops spreadsheet.
  • syncAllChannels() (line 380) — Fetches iCal feeds from configured platforms (Boatsetter, Viator, Sailo), parses events, and writes bookings to the spreadsheet.
  • testSync() (line 563) — A lightweight test harness that exercises the sync logic once, without triggers.
  • sendDailyReconciliation() (line 420) — Queries the BookingLedger, calculates deltas, and emails a summary to operations.

The GAS project ID is 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii, accessible via:

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

The Fix: Three Sequential Steps

Step 1: Re-authorize Gmail and Calendar Scopes

GAS-level OAuth tokens are tied to the user and the scopes declared in appsscript.json. When a token expires or is revoked, re-consent is required. The trick: any function call that touches the expired service will trigger the consent dialog.

calendarSyncSetup() only touches SpreadsheetApp and ScriptApp, so it won't prompt for Gmail or Calendar permission. We need to run testSync() first:

  1. Open the GAS editor at the URL above.
  2. Function dropdown → select testSync.
  3. Click Run.
  4. Browser prompts "This app needs access to your Gmail account" → click Allow.
  5. Browser then prompts for Calendar access → click Allow.

The execution log will show connection attempts or permission errors if scopes are still blocked. Once both dialogs complete, the tokens are refreshed in GAS's internal OAuth store.

Step 2: Register the Triggers

Now run calendarSyncSetup():

  1. Function dropdown → select calendarSyncSetup.
  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

Verify in the left sidebar: click the clock icon (Triggers) and confirm two entries appear:

  • syncAllChannels — Time-based trigger, every 30 minutes
  • sendDailyReconciliation — Time-based trigger, daily at 7:30 AM PT

Step 3: Verify End-to-End

Run testSync() again. Watch the execution log for:

Fetching Boatsetter iCal...
  Parsed N events from Boatsetter
  Wrote N new bookings to BookingLedger
CalendarSync complete. New bookings: N
Daily reconciliation email queued for 7:30am PT

If you see Exception: You do not have permission to access the requested resource, the OAuth re-consent didn't stick. Repeat Step 1.

Key Design Decisions

Why 30-minute intervals? Boatsetter, Viator, and Sailo don't offer webhooks for booking events. Polling every 30 minutes balances operational freshness (bookings appear in the ops sheet within half an hour) against quota consumption. The iCal feeds are read-only and stateless, so there's no risk of double-writes — the code uses event UIDs to deduplicate.

Why separate setup and test functions? calendarSyncSetup() is idempotent — it can be re-run without creating duplicate triggers. The ScriptApp.newTrigger() API prevents duplicates by checking existing triggers before creating new ones. testSync() is a fire-once diagnostic, useful for debugging without waiting for the next 30-minute window.

Why re-auth at the function level, not the file level? GAS OAuth is scoped to the user and the declared permissions in appsscript.json. There's no "re-auth the whole file" command. The framework re-prompts when a service method (e.g., GmailApp.sendEmail()) is first called after a token expires. Running testSync() before calendarSyncSetup() ensures both Gmail and Calendar tokens are refreshed before triggers begin executing.

Infrastructure: The ICAL_FEEDS Array

Inside CalendarSync.gs:145, platform iCal URLs are configured:

const ICAL_FEEDS = {
  boatsetter: 'https://ical.boatsetter.com/...',
  viator: 'https://viator-api.example.com/ical/...',
  sailo: 'https://api.sailo.com/ical/...'
};

The Boatsetter URL is already present (sourced from