Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

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

What Was Done

The Queen of San Diego booking system had a critical blocker: the Boatsetter iCal integration was implemented in code but never actually running. Three distinct problems combined to prevent any syncs:

  • OAuth tokens for Gmail and Google Calendar had expired
  • Calendar OAuth was explicitly revoked
  • The time-based triggers that should execute syncs were never registered

This meant ticket m-91325edb was stuck in a "needs-you" state despite the infrastructure being fully coded. The fix required three sequential steps: re-authorization, trigger activation, and verification.

Technical Details: The Problem and Solution

Root Cause Analysis

The codebase contained all necessary logic in sites/queenofsandiego.com/CalendarSync.gs, with the Boatsetter iCal URL wired into the ICAL_FEEDS array. However, two foundational issues prevented execution:

  • Missing Setup Execution: The function calendarSyncSetup() (line 355 in CalendarSync.gs) — which registers the AppEngine time-based triggers and creates infrastructure — had never been called. The ticket comment incorrectly referred to calendarDashboardSetup(), which exists in a different GAS project.
  • Expired Credentials: Even if triggers fired, the OAuth scopes for Gmail and Calendar were no longer valid, causing permission failures.

Step 1: OAuth Re-authorization

Google Apps Script maintains its own OAuth consent flow separate from standalone Python token files. When you open the editor and execute any function, GAS checks permissions and prompts for consent if scopes are missing or revoked.

Action: Run the testSync() function (line 563 in CalendarSync.gs) in the GAS editor:

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

Function dropdown → testSync → Run

This triggers two consent prompts sequentially:

  • Gmail Scope: Required for GmailApp.sendEmail() calls in the daily reconciliation digest
  • Calendar Scope: Required for CalendarApp.getCalendarById() to write bookings

Both must be allowed before proceeding.

Step 2: Trigger Activation

After re-authorization, immediately run calendarSyncSetup() (line 355):

Function dropdown → calendarSyncSetup → Run

This function performs three critical tasks:

  • Creates the "BookingLedger" tab in the operations spreadsheet if it doesn't exist
  • Registers syncAllChannels as a time-based trigger (executes every 30 minutes)
  • Registers sendDailyReconciliation as a time-based trigger (executes daily at 7:30 AM PT)

You can verify trigger registration by clicking the clock icon (Triggers) in the left sidebar. You should see:

syncAllChannels → Time-driven → Every 30 minutes
sendDailyReconciliation → Time-driven → Day timer → 7:30 AM - 8:30 AM

Step 3: Verification

Run testSync() again to verify the full pipeline works. Check the Execution Log (View → Execution Log) for success output:

9:57:20 AM    Notice    Execution started
9:57:21 AM    Info    Fetching Boatsetter iCal...
9:57:22 AM    Info      Parsed N events from Boatsetter
9:57:23 AM    Info    Writing bookings to QueenOfSanDiego calendar...
9:57:24 AM    Info    CalendarSync complete. New bookings: N
9:57:25 AM    Notice    Execution completed

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

Infrastructure: GAS Project Architecture

The solution involves two separate Google Apps Script projects, each with distinct responsibilities:

CalendarSync Project (Primary)

  • Script ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii
  • File: CalendarSync.gs
  • Trigger Functions:
    • syncAllChannels() — Fetches all iCal feeds (Boatsetter, Airbnb, VRBO, etc.) and writes events to Google Calendar
    • sendDailyReconciliation() — Sends daily summary email with booking counts and revenue
  • Data Source: ICAL_FEEDS array contains URLs for each platform's iCal feed
  • Target Calendar: "QueenOfSanDiego" calendar (shared between all stakeholders)

JadaCalendarDashboard Project (Secondary)

  • Script ID: 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5
  • File: JadaCalendarDashboard.gs
  • Trigger Function: jadaCalendarScanSetup() — Sets up email scanning for Viator bookings (separate from iCal)
  • Status: Still pending initial execution (separate ticket t-8d86d5ba)

Key Decisions

Why Two GAS Projects?

Separating CalendarSync and JadaCalendarDashboard serves several purposes:

  • Scope isolation: Each project requests only the minimum necessary OAuth scopes
  • Trigger independence: CalendarSync can run on a strict 30-minute interval without interference from email scanning logic
  • Deployment safety: Changes to one system don't risk breaking the other
  • Scaling: If Viator email volume grows, jadaCalendarScanSetup can be tuned separately

Time-Based vs. Event-Driven Triggers

CalendarSync uses