Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

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

What Was Done

The Boatsetter iCal integration for the Queen of San Diego booking system was fully implemented but never activated. Three distinct blockers prevented it from running: (1) expired Gmail OAuth consent in Google Apps Script, (2) revoked Calendar API authorization, and (3) unregistered time-based triggers. This post documents the diagnosis and fix sequence, with emphasis on the exact file paths and function names required to restore functionality.

Problem Statement

Ticket m-91325edb had been in "needs-you" status because the implementation work was complete but the operational activation step was never executed. The codebase includes:

  • CalendarSync.gs (in sites/queenofsandiego.com/) — the main sync engine with Boatsetter iCal URL hardcoded
  • JadaCalendarDashboard.gs (in sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/) — the Viator email scanner and dashboard setup
  • Two separate GAS projects with distinct script IDs, each requiring independent trigger activation

When developers checked the execution logs, they saw permission errors: Exception: You do not have permission to call Calendar.Calendars.get(). This wasn't a code bug—it was a deployment state bug.

Root Cause Analysis

OAuth Scope Problem: Google Apps Script maintains per-project OAuth consent tokens. When you create a new GAS project and write code that calls Gmail or Calendar APIs, the project has declared those scopes in its manifest, but no user has yet granted them. The first function execution that touches those APIs triggers the consent dialog. If that function never runs, the token is never issued.

Why This Happened: CalendarSync.gs was written and deployed to production, but the activation function calendarSyncSetup() (line 355) was never manually invoked in the GAS editor. This function:

  • Creates the "BookingLedger" tab in the ops spreadsheet (SpreadsheetApp scope, already approved)
  • Registers two time-based triggers via ScriptApp (already approved)
  • Does NOT call Calendar or Gmail APIs itself

So the triggers got registered, but they pointed to functions (syncAllChannels and sendDailyReconciliation) that required Calendar and Gmail scopes. When those triggers fired, execution failed silently or logged permission errors.

The Three-Step Fix

Step 1: Re-authorize Both OAuth Scopes

Open the CalendarSync.gs GAS editor:

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

In the function dropdown, select testSync (line 563). Click the Run button. Google will prompt with a consent dialog listing the scopes this project needs:

  • https://www.googleapis.com/auth/gmail.send
  • https://www.googleapis.com/auth/calendar
  • https://www.googleapis.com/auth/spreadsheets

Click Allow. The OAuth token is now issued for this GAS project. The testSync function will execute, fetching the Boatsetter iCal feed and logging results.

Why testSync first? The setup function doesn't touch Gmail or Calendar, so it won't trigger the consent dialog. We need to run a function that actually exercises those APIs. testSync is lightweight and non-destructive.

Step 2: Register the Time-based Triggers

With OAuth now granted, switch the function dropdown to calendarSyncSetup (line 355) and click Run.

This function:

  • Creates a new sheet called "BookingLedger" in the ops spreadsheet (idempotent—safe to re-run)
  • Calls ScriptApp.newTrigger('syncAllChannels').timeBased().everyMinutes(30).create() — registers a 30-minute repeating trigger
  • Calls ScriptApp.newTrigger('sendDailyReconciliation').timeBased().atHour(7).nearMinute(30).everyDays(1).create() — registers a daily 7:30 AM PT trigger

After execution completes, verify trigger registration by clicking the clock icon in the left sidebar. You should see two new entries with status "Active".

Step 3: Verify End-to-End Functionality

Run testSync again. Watch the execution log. Expected output:

Fetching Boatsetter iCal...
  18 events from Boatsetter
CalendarSync complete. New bookings: 3
Sending daily reconciliation email...
Email sent to operations@queenofsandiego.com

If you see this, OAuth is working and the iCal parser is functional. If you see Exception: You do not have permission, the re-auth failed—try the Run step again.

Architecture: Why This Structure Exists

The split between CalendarSync.gs and JadaCalendarDashboard.gs

  • CalendarSync.gs: Sync engine. Pulls from external iCal feeds (Boatsetter, Sailo, Airbnb), writes to Google Calendar, sends email summaries. Runs on a schedule via time-based triggers.
  • JadaCalendarDashboard.gs: Dashboard and email scanner. Listens for Viator booking confirmations, extracts data, updates a dashboard sheet. Lives in a separate GAS project because it has different trigger patterns (email-based via Gmail filters).

Each GAS project has its own script ID and manages its own OAuth tokens. This compartmentalization means a failure in one sync engine doesn't block the other.

Key Decisions and Rationale

Why Boatsetter iCal and not API? Boatsetter's public API requires platform-wide authentication at company level. iCal feeds are per-listing and simpler to authenticate (URL-based). This design allows rapid integration of multiple platforms without complex OAuth proxy logic.

Why 30-minute sync interval? Balances freshness (bookings appear in calendar within 30 min) against quota usage. Google Apps Script time-based triggers have a 4-hour minimum execution time variance; 30 minutes keeps data reasonably current without excessive overhead.

Why separate setup function? Trigger registration is idempotent but expensive (each call adds a new trigger). The setup function is run once to initialize the schedule, not on every deployment. This separates "deploy the code" from "activate the schedule," reducing accidental duplicate triggers.