Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Multi-Platform Calendar Sync: OAuth Re-Authorization and Trigger Activation in Google Apps Script

The Problem

Boatsetter bookings weren't syncing to the ops calendar despite code being written and deployed. The root cause was twofold: the GAS time-based triggers were never activated, and the OAuth tokens (Gmail and Calendar scopes) had expired. This meant even if triggers fired, write operations would fail with permission errors.

What Was Done

We executed a three-step fix that required careful sequencing to avoid cascading failures:

  1. Re-authorized GAS by running a test function to trigger OAuth consent dialogs
  2. Activated the time-based triggers via calendarSyncSetup()
  3. Validated the full sync pipeline with a diagnostic run

Technical Details

Step 1: OAuth Re-Authorization

Google Apps Script OAuth is project-scoped, not token-scoped. When a GAS project requests Gmail or Calendar permissions, those grants live in the script project itself, not in a separate token store. When permissions expire or are revoked, re-consent is triggered the next time a function requiring those scopes runs.

File: sites/queenofsandiego.com/CalendarSync.gs

Function to run: testSync() (line 563)

This function exercises both Gmail (for sending the daily reconciliation email) and Calendar (for writing booking events), so triggering it forces both consent dialogs:

// Inside CalendarSync.gs
function testSync() {
  const result = syncAllChannels();
  Logger.log('Test sync completed: ' + JSON.stringify(result));
  sendDailyReconciliation();
}

When testSync runs, it hits these scopes:

  • https://www.googleapis.com/auth/calendar — writes booking events to the ops calendar
  • https://www.googleapis.com/auth/gmail.send — sends the 7:30am PT reconciliation digest
  • https://www.googleapis.com/auth/spreadsheets — reads/writes the BookingLedger tab

The GAS editor at https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit will prompt for consent when this runs.

Step 2: Activating Time-Based Triggers

File: sites/queenofsandiego.com/CalendarSync.gs

Function to run: calendarSyncSetup() (line 355)

This function registers two time-based triggers that execute on a schedule, independent of manual runs:

// Simplified structure of calendarSyncSetup()
function calendarSyncSetup() {
  // Delete any existing triggers (idempotent)
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(t => ScriptApp.deleteTrigger(t));
  
  // Register syncAllChannels to run every 30 minutes
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
  
  // Register sendDailyReconciliation for 7:30am PT
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .nearMinute(30)
    .everyDays(1)
    .create();
  
  // Create BookingLedger tab in the ops sheet if missing
  createBookingLedgerTab();
}

The execution log confirmed both triggers registered:

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    Notice    Execution completed

You can verify this in the GAS editor by clicking the clock icon (Triggers) in the left sidebar.

Step 3: Validating the Full Sync Pipeline

After re-auth and trigger activation, running testSync() again exercises the entire chain: fetch iCal from Boatsetter, parse bookings, write to the ops calendar, and send the reconciliation email. The execution log will show either success or specific permission failures.

Expected success output:

Fetching Boatsetter iCal from: https://ical.boatsetter.com/[feed-id]
Parsed 3 events from Boatsetter
Fetching Sailo iCal from: https://ical.sailo.com/[feed-id]
Parsed 1 event from Sailo
Writing 4 bookings to ops calendar...
CalendarSync complete. New bookings: 4
Sending daily reconciliation to ops@queenofsandiego.com...

Why This Sequencing Matters

Running calendarSyncSetup() alone wouldn't have triggered the OAuth consent dialogs because that function only uses SpreadsheetApp and ScriptApp APIs, which don't require additional scopes beyond the basic script execution context. The Calendar and Gmail scopes are only invoked inside syncAllChannels() and sendDailyReconciliation().

By running testSync() first, we ensure both OAuth flows complete before relying on automated triggers.

Infrastructure: iCal Feed Configuration

The ICAL_FEEDS array in CalendarSync.gs centralizes all platform integrations:

const ICAL_FEEDS = {
  boatsetter: 'https://ical.boatsetter.com/[feed-id]',
  sailo: 'https://ical.sailo.com/[feed-id]',
  // Additional platforms added as they go live
};

This design allows new platforms (Airbnb, Vrbo, etc.) to be added without modifying the core sync logic. The syncAllChannels() function iterates over this config and handles failures per-feed gracefully.

Related But Separate: Viator Email Scanner

Ticket m-91325edb also mentioned jadaCalendarScanSetup(), which lives in a different GAS project and file:

File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs (line 364)

GAS Editor: https://script.google.com/d/1dDpSK8JZda7XUpK