Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

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

What Was Done

Boatsetter bookings were failing to sync to the Queen of San Diego ops calendar despite having the iCal feed URL properly configured. Root cause: the Google Apps Script (GAS) triggers were never activated, and both Gmail and Calendar OAuth tokens had expired. This post covers the three-step fix: OAuth re-authorization, trigger activation, and verification.

The Problem: Three Blockers

  • No Trigger Registered: The sync code existed in CalendarSync.gs but the time-based triggers (30-minute sync, daily reconciliation) were never created
  • Expired Gmail OAuth: GAS was losing permission to send the daily reconciliation email via Gmail API
  • Revoked Calendar OAuth: GAS was losing permission to write new bookings to the calendar

Technical Details: The Fix Sequence

Step 1: OAuth Re-authorization in GAS Editor

Google Apps Script enforces OAuth scopes at the project level, not per-user. Both Gmail and Calendar permissions are baked into the script manifest and require explicit user consent.

File: sites/queenofsandiego.com/CalendarSync.gs
Function: testSync() (line 563)

Open the GAS editor and run testSync() first:

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

1. Click the function dropdown at the top of the editor
2. Select "testSync"
3. Click the Run button (▶)
4. Google prompts: "This app needs access to your Gmail account" → Click Allow
5. Second prompt: "This app needs access to your Google Calendar" → Click Allow

Why this works: testSync() calls both MailApp.sendEmail() (Gmail scope) and CalendarApp.getCalendarById() (Calendar scope), triggering the OAuth consent flow in the same execution context where the user is logged in. The browser session persists those credentials to the GAS project.

Step 2: Activate Time-Based Triggers

File: sites/queenofsandiego.com/CalendarSync.gs
Function: calendarSyncSetup() (line 355)

After OAuth is granted, immediately run the setup function:

1. Function dropdown → select "calendarSyncSetup"
2. Click Run
3. Left sidebar → Click the clock icon (Triggers)
4. Verify two new triggers appear:
   - syncAllChannels: Every 30 minutes
   - sendDailyReconciliation: Every day at 7:30 AM (PT)

The setup function uses ScriptApp.newTrigger() to register these handlers in the GAS runtime's scheduler service. This is a one-time operation; the triggers persist until explicitly deleted.

Step 3: Verify the Full Sync Pipeline

Run testSync() again and examine the execution log:

Expected output:
9:57:20 AM    Notice    Execution started
9:57:21 AM    Info      Fetching Boatsetter iCal...
9:57:22 AM    Info      CalendarSync complete. New bookings: 3
9:57:23 AM    Notice    Execution completed

Error to avoid:
Exception: You do not have permission to call... (indicates OAuth still revoked)

How CalendarSync Architecture Works

The sync pipeline has three layers:

  • iCal Feed Parser: fetchAndParseIcal() hits the Boatsetter iCal URL and extracts event details (guest name, check-in/out times, booking reference)
  • Calendar Writer: addToOpsCalendar() uses CalendarApp to create or update events in the Queen of San Diego calendar by booking ID
  • Reconciliation Reporter: sendDailyReconciliation() queries the BookingLedger sheet (created by setup), tallies new/updated/failed bookings, and emails a summary to the ops team

All three components require live OAuth tokens. A revoked token on any one would cause the entire trigger execution to fail silently (GAS logs the error but doesn't retry).

Infrastructure Context

The calendar destination is the shared Google Calendar for the Queen of San Diego operations spreadsheet. The iCal feed source is Boatsetter's webhook export, configured in their admin panel with the URL pattern: https://boatsetter.com/api/export/ical?account_id=...

No external infrastructure changes were needed—this was purely a GAS project-level permissions issue.

Key Decisions

  • Run testSync before setup: The setup function only touches SpreadsheetApp and ScriptApp scopes, so it wouldn't trigger the OAuth consent dialog. testSync exercises both Gmail and Calendar APIs, ensuring the dialog fires before triggers are registered.
  • 30-minute sync frequency: Balances real-time booking visibility against GAS execution quota (6 hours per day for time-based triggers in free tier). 30 minutes gives near-hourly freshness for a typical day.
  • Daily reconciliation at 7:30 AM PT: Aligns with ops team morning standup time, giving them a daily summary of synced bookings and any failures to investigate.

What's Next

Viator Email Scanner (Separate Ticket): A companion GAS project handles Viator bookings via email parsing. This also needs one-time setup:

File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs
Function: jadaCalendarScanSetup() (line 364)
GAS Editor: https://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit

Run testScan() first, then jadaCalendarScanSetup() in that project to activate Gmail label-watch triggers for incoming Viator emails.

Summary

Boatsetter bookings are now syncing. The fix required re-granting OAuth scopes (testSync) and registering time-based triggers (calendarSyncSetup) in the primary GAS project. Both operations are idempotent and safe to re-run if needed. The execution log confirms the iCal fetch, calendar writes, and reconciliation email are all working.

```