Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Calendar Sync Failures: Re-authorizing GAS OAuth and Activating Triggers for Multi-Channel Booking Ingestion

What Was Done

Resolved a critical blocker preventing auto-sync of booking data from Boatsetter into the ops calendar system. The root cause was twofold: (1) GAS-level OAuth tokens for Gmail and Google Calendar had expired or been revoked, and (2) the time-based triggers that orchestrate the sync pipeline were never activated after code deployment.

The fix involved re-authorizing the Google Apps Script project and running the setup function once to register triggers. This unblocked two separate ticket clusters: m-91325edb (Boatsetter sync activation) and t-8d86d5ba (Gmail/Calendar auth).

Technical Details: The Two-Step Fix

Step 1: Trigger OAuth Re-consent

OAuth tokens in GAS are scoped at the project level, not at individual function level. Running any function that touches Gmail or Calendar APIs will prompt the consent screen if tokens are expired or revoked.

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

Function: testSync()

This is a lightweight test function that exercises both the Calendar and Gmail APIs without side effects:

function testSync() {
  Logger.log('Testing CalendarSync...');
  // Attempts iCal fetch (minimal)
  // Attempts Calendar.Events.list() — triggers Calendar scope consent
  // Attempts GmailApp.getInboxThreads() — triggers Gmail scope consent
}

Action: Open the GAS editor at https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit, select testSync from the function dropdown, and click Run. Google will prompt twice—once for Calendar scope, once for Gmail scope. Accept both.

Step 2: Activate Time-Based Triggers

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

Function: calendarSyncSetup()

This function idempotently registers two ScriptApp triggers:

  • syncAllChannels() — executes every 30 minutes
  • sendDailyReconciliation() — executes daily at 7:30 AM PT
function calendarSyncSetup() {
  // Remove old triggers (if any) to prevent duplicates
  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (trigger.getHandlerFunction() === 'syncAllChannels' || 
        trigger.getHandlerFunction() === 'sendDailyReconciliation') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Register new triggers
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
  
  ScriptApp.newTrigger('sendDailyReconciliation')
    .atHour(7)
    .everyDays(1)
    .create();
  
  // Create BookingLedger sheet if it doesn't exist
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss.getSheetByName('BookingLedger')) {
    ss.insertSheet('BookingLedger');
  }
}

Action: In the same GAS editor, select calendarSyncSetup and click Run. Check the left sidebar (clock icon → Triggers) to confirm both triggers are registered.

Why This Architecture Matters

iCal Feed Polling vs. Webhooks: The design uses time-based polling of iCal feeds (from Boatsetter, Sailo, and others) rather than webhooks. This was a deliberate choice because:

  • iCal is a simpler contract than per-platform webhook APIs, which have varying reliability, retry policies, and auth models.
  • Boatsetter's iCal URL is stable and requires no polling token management.
  • 30-minute polling windows are acceptable for booking workflows; same-minute sync isn't a business requirement.
  • Time-based triggers decouple from external service uptime.

Multi-Channel Ledger Pattern: All synced bookings flow into a single BookingLedger sheet with a source column. This allows downstream logic (reconciliation, conflict detection, reporting) to operate on a unified view without channel-specific branching.

Infrastructure Context

GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii

Backing Spreadsheet: "Ops - Jada QoSD" (managed via clasp config in sites/queenofsandiego.com/.clasp.json)

Data Flow:

  • ICAL_FEEDS array in CalendarSync.gs (line 25) contains URLs for Boatsetter, Sailo, and other platforms.
  • syncAllChannels() fetches each feed, parses events, and appends to BookingLedger sheet.
  • sendDailyReconciliation() queries the BookingLedger, compares with primary calendar, and emails a summary to ops.

Key Decisions and Trade-offs

Why Re-auth via testSync() Instead of Direct Function Call: While calendarSyncSetup() only uses SpreadsheetApp (which doesn't require Calendar/Gmail scopes), running it doesn't trigger the OAuth dialogs. By running testSync() first, we ensure consent is granted before the 30-minute polling begins. This prevents silent failures where triggers execute but can't write to calendar or send mail.

Idempotent Trigger Registration: calendarSyncSetup() deletes existing triggers before creating new ones. This prevents duplicate triggers from accumulating if the function is run multiple times. GAS doesn't provide a "get or create" pattern for triggers.

Why a Separate Viator Scanner: The Viator booking ingestion uses a different GAS project and function (jadaCalendarScanSetup() in JadaCalendarDashboard.gs) because Viator bookings arrive via email, not iCal. This required a separate Gmail scanner with its own OAuth scope. It's registered in a different GAS project (ID: 1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5) to isolate email label management.

Verification