Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Fixing Calendar Sync Triggers and OAuth Re-Authorization in Google Apps Script

After weeks of development work, the Boatsetter iCal integration was written and deployed but silently failing in production. The root cause wasn't code—it was three overlapping infrastructure issues: missing trigger activation, expired OAuth tokens, and unclear which GAS project needed which setup. This post walks through the diagnosis and fix, with exact file paths and function names.

The Problem: Three Blockers

  • Missing trigger: The sync function was written but never activated in the time-based trigger system
  • Expired Gmail OAuth: Token grant had expired; any email send would fail with permission denied
  • Revoked Calendar OAuth: Calendar write permissions were no longer valid

All three had to be fixed for the system to work. The tricky part: there are two separate GAS projects in this repo, and they handle different integrations.

File Organization: Two GAS Projects

The codebase has two independent Google Apps Script projects, each with its own deployment and OAuth scopes:

  • CalendarSync (primary booking aggregator):
    File: sites/queenofsandiego.com/CalendarSync.gs
    GAS Editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
    Scopes: spreadsheets, calendar, gmail
    Responsibility: Fetch iCal feeds (Boatsetter, Sailo, Viator), merge into ops spreadsheet, send daily reconciliation emails
  • JadaCalendarDashboard (Viator email scanner):
    File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs
    GAS Editor: https://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit
    Scopes: gmail, spreadsheets
    Responsibility: Scan Viator confirmation emails, extract booking details, write to dashboard

The distinction matters: CalendarSync does the heavy lifting for all channels, but JadaCalendarDashboard was the legacy email-parsing layer. During this session, we ensured both were properly initialized.

Step 1: Re-Authorize OAuth Tokens

GAS OAuth tokens are project-level, not user-level. When a token expires or is revoked, the remedy is to trigger any function that uses that scope. The system will prompt for re-consent.

Function: testSync() in CalendarSync.gs:563
What it does: Performs a dry-run of the full sync pipeline—fetches iCal, parses events, attempts calendar write, prepares email—without committing changes. 

// In GAS editor, Function dropdown → select testSync
// Click Run
// Google prompts: "This app needs access to Gmail, Calendar, Spreadsheets"
// Click Allow (may appear twice, once per scope)

Why testSync first? The setup function (calendarSyncSetup()) only touches SpreadsheetApp and ScriptApp—it doesn't trigger Gmail or Calendar scopes. Running testSync exercises all three, so you get all consent dialogs in one session.

Step 2: Activate Time-Based Triggers

Once OAuth is refreshed, register the two production triggers:

Function: calendarSyncSetup() in CalendarSync.gs:355
What it does: Creates or updates two time-based triggers in the GAS trigger management system. 

function calendarSyncSetup() {
  // Removes any existing triggers to avoid duplicates
  removeAllTriggers_();
  
  // Register syncAllChannels: every 30 minutes
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
  
  // Register sendDailyReconciliation: daily at 7:30 AM PT
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .nearMinute(30)
    .inTimezone('America/Los_Angeles')
    .everyDays(1)
    .create();
  
  // Create or clear BookingLedger tab in ops spreadsheet
  // ... setup code ...
}

Verification: After running, check the Triggers sidebar (clock icon on left) in the GAS editor. You should see both triggers listed with status "Active".

Execution log output: 

9:57:20 AM    Notice    Execution started
9:57:24 AM    Info    CalendarSync setup complete:
9:57:24 AM    Info      - BookingLedger tab created in ops sheet
9:57:24 AM    Info      - syncAllChannels: every 30 minutes
9:57:24 AM    Info      - sendDailyReconciliation: daily at 7:30am PT
9:57:24 AM    Info      - Next step: add iCal URLs to ICAL_FEEDS array as platforms go live
9:57:24 AM    Notice    Execution completed

Step 3: Verify End-to-End Sync

Run testSync() again to verify the full pipeline. Watch the execution log for:

  • Boatsetter iCal fetch → parse → calendar write
  • Gmail send for daily reconciliation
  • No "You do not have permission" exceptions

The logs should show event counts from each feed: 

Fetching Boatsetter iCal from [URL]...
  5 events parsed from Boatsetter
Fetching Sailo iCal from [URL]...
  0 events parsed from Sailo (no new bookings this period)
CalendarSync complete. New bookings: 5
Writing to BookingLedger...
Preparing daily reconciliation email...

Architecture Decisions

Why separate projects? The Viator integration predates the unified iCal system. Keeping it in a separate project allows independent maintenance and avoids over-complicating scope grants. If Viator email parsing fails, the Boatsetter/Sailo sync continues unaffected.

Why 30-minute sync interval? Boatsetter and Sailo update their iCals near-