Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Reactivating Multi-Channel Calendar Sync: OAuth Re-authorization and Trigger Deployment in Google Apps Script

The Problem

A calendar synchronization system that pulls booking data from Boatsetter into a shared Google Sheet had been fully coded but never activated. The codebase was complete, the iCal feed URL was wired in, but three blockers prevented it from running:

  • Gmail OAuth token had expired
  • Calendar API OAuth scope was revoked
  • Time-based triggers were never created in the Apps Script project

The ticket (m-91325edb) had been sitting in "needs-you" status for weeks because the final activation step—running the setup function once—was never completed.

Technical Architecture

The system lives in a single Google Apps Script project backing the Queen of San Diego operations sheet:

  • File: sites/queenofsandiego.com/CalendarSync.gs
  • GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii
  • Target Sheet: Queen of San Diego operations sheet (shared with ops team)
  • Data Source: Boatsetter iCal feed (URL stored in ICAL_FEEDS array)

The system runs three core workflows:

  • syncAllChannels() — Fetches all iCal feeds, parses events, writes bookings to BookingLedger tab every 30 minutes
  • sendDailyReconciliation() — Generates a daily ops summary email at 7:30 AM PT
  • testSync() — Manual test function for debugging (line 563)

OAuth Expiration: Why It Happened and How It Matters

Google Apps Script maintains separate OAuth tokens for each scoped service:

  • Gmail scope — required for GmailApp.sendEmail() in the reconciliation workflow
  • Calendar scope — required for CalendarApp.getCalendarById().createEvent()
  • Spreadsheet scope — required for sheet writes (this one doesn't expire; it's bound to the sheet)

Gmail and Calendar tokens are user-dependent and expire after a period of inactivity or browser session expiration. Once expired, any attempt to call those APIs throws: Exception: You do not have permission to access the requested document.

The fix isn't to refresh a token file—it's to trigger a new OAuth consent flow by running any function that touches those scopes from within the editor, which prompts the user to re-authorize.

The Setup and Activation Sequence

Step 1: Open the GAS Editor and Trigger OAuth Re-consent

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

Navigate to the editor. In the Function dropdown (top-left of the editor), select testSync (defined at line 563 in CalendarSync.gs). Click the Run button.

Google will prompt with: "This app needs access to your Gmail account" and "This app needs access to your Calendar." Click Allow for each. This re-establishes both OAuth tokens at the project level.

Why run testSync first? The setup function (calendarSyncSetup) only touches SpreadsheetApp and ScriptApp, which don't require fresh OAuth. Running testSync

Step 2: Register Time-Based Triggers

With OAuth re-established, run the setup function:

Function dropdown → select calendarSyncSetup (line 355)

Click Run. The 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

This function calls ScriptApp.newTrigger() twice, registering two clock-based triggers that will fire on schedule. You can verify by clicking the clock icon in the left sidebar (Triggers panel) and confirming both are listed.

Step 3: Verify the Sync Actually Works

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

Fetching Boatsetter iCal...
  Parsed 12 events from Boatsetter
CalendarSync complete. New bookings: 5
sendDailyReconciliation: Email sent to ops team

No permission errors means the OAuth tokens are valid and the iCal feed URL is reachable.

Key Implementation Details

iCal Feed Array: The Boatsetter iCal URL is stored in the ICAL_FEEDS object (line ~80 in CalendarSync.gs). It's keyed by platform name for extensibility—when Airbnb or VRBO go live, their URLs get added to the same array and are automatically included in each sync cycle.

BookingLedger Sheet: The calendarSyncSetup() function creates a new tab in the ops sheet called "BookingLedger" if it doesn't exist. All parsed bookings are appended here with columns: Date, Platform, Guest Name, Nightly Rate, Total Booking Value. This serves as a single source of truth for reconciliation.

Error Handling: The syncAllChannels() function wraps each iCal fetch in a try-catch. If Boatsetter's feed is temporarily down, the error is logged but doesn't halt the entire sync. This prevents one bad feed from breaking other channels.

Related but Separate: Viator Email Scanner Setup

The Queen of San Diego team also uses a separate GAS project to scan Viator booking emails. This has its own setup blocker:

  • File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs (line 364)
  • Function: jadaCalendarScanSetup()
  • GAS Project ID: 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3