Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Unblocking Boatsetter Calendar Sync: Reauthorizing Google Apps Script and Activating Time-Based Triggers

The Problem

A booking integration for Boatsetter had been written and deployed but wasn't syncing calendar data. The root cause: three distinct blockers existed simultaneously:

  • OAuth tokens expired: Gmail and Google Calendar permissions in Google Apps Script (GAS) had revoked, causing any write or read attempts to fail with permission errors.
  • Triggers never activated: The time-based triggers that poll the Boatsetter iCal feed and sync bookings were never registered in the GAS project.
  • Unclear entrypoint: Multiple GAS files across the project left ambiguity about which function to run and where.

This post walks through the fix: reauthorizing GAS, activating the correct triggers, and testing end-to-end sync.

What Was Done

Step 1: Reauthorize Google Apps Script

Google Apps Script maintains separate OAuth scopes for each service it interacts with. When a user hasn't granted consent in a while, or revokes permissions from their Google account settings, GAS will reject all operations on that scope until reauthorized.

The fix is simple: run any function in the GAS editor that touches the protected resource. When you hit the Run button, GAS prompts the user for consent, and the permission is re-granted.

File: sites/queenofsandiego.com/CalendarSync.gs

Function: testSync() (line 563)

Steps:

  1. Open the GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  2. Function dropdown (top-center) → select testSync
  3. Click Run
  4. GAS triggers a consent dialog: "This app needs access to Gmail, Google Calendar, and Google Sheets." → click Allow
  5. The permission prompt may appear twice (once for Gmail, once for Calendar) — allow both
  6. Watch the Execution Log for the test to complete

Why this works: testSync() calls MailApp.sendEmail(), CalendarApp.getCalendarById(), and reads the spreadsheet. Each triggers OAuth re-consent for its respective scope.

Step 2: Activate Time-Based Triggers

GAS allows scheduling functions to run on timers (e.g., "every 30 minutes") or at specific times (e.g., "daily at 7:30am PT"). These are registered by the ScriptApp.newTrigger() API, which must be called once to set up the schedule.

File: sites/queenofsandiego.com/CalendarSync.gs

Function: calendarSyncSetup() (line 355)

Steps:

  1. In the same GAS editor, Function dropdown → select calendarSyncSetup
  2. Click Run
  3. Check the Execution Log for success message (should be instant)
  4. Verify in the left sidebar: click the clock icon (Triggers) and confirm two new entries:
syncAllChannels — Timer — Every 30 minutes
sendDailyReconciliation — Clock-based — Daily 7:30am PT

What this function does:

  • Creates a BookingLedger tab in the operations spreadsheet (if it doesn't exist)
  • Registers syncAllChannels() to run every 30 minutes, fetching all configured iCal feeds (including Boatsetter) and writing new bookings to the ledger
  • Registers sendDailyReconciliation() to run at 7:30am PT each day, sending a summary email to operations

Step 3: Verify End-to-End Sync

Now that triggers are active and OAuth is re-granted, test the full sync pipeline:

Function: testSync() again (line 563)

  1. Run testSync() one more time
  2. In the Execution Log, look for:
Info    Fetching Boatsetter iCal feed from: https://...
Info    Parsed N events from Boatsetter
Info    Fetching calendar...
Info    Writing N new bookings to BookingLedger
Info    Sending daily reconciliation email to ops@...

If you see permission errors: Something went wrong with OAuth re-consent. Go back to Step 1 and repeat—sometimes the consent dialog needs two attempts, or the browser cache needs clearing.

Technical Details: How Calendar Sync Works

The CalendarSync.gs file implements a multi-channel booking aggregator. Here's the architecture:

  • iCal Feeds Array: ICAL_FEEDS object (top of file) maps platform names (e.g., "boatsetter", "viator") to their iCal URLs. Boatsetter's URL is already configured here.
  • Poll & Parse: syncAllChannels() iterates all feeds, fetches each, parses events using a custom iCal parser, and deduplicates by booking ID.
  • Write & Notify: New bookings are written to the BookingLedger tab (a hidden ledger in the ops spreadsheet). Timestamps and crew assignments are logged for reconciliation.
  • Daily Email: sendDailyReconciliation() queries the ledger for the past 24 hours and emails a digest to ops with counts, revenue, and any anomalies.

Key design decision: Boatsetter's iCal feed is the source of truth, not their API. This decouples us from rate limits and avoids OAuth headaches with Boatsetter (which would require their API credentials). The iCal standard is stable and widely supported.

Separate Item: Viator Email Scanner Setup

There's a second GAS project that scans Viator emails and creates calendar events. This also needs trigger activation, but it's in a different file:

  • File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs
  • Function: jadaCalendarScanSetup() (line 364)
  • G