Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Calendar Sync Failures: Re-authorizing OAuth and Activating Time-Based Triggers in Google Apps Script

What Was Done

Boatsetter booking synchronization was failing silently because two critical blockers prevented the calendar pipeline from executing:

  • Expired OAuth tokens: Gmail and Google Calendar permissions had expired or been revoked at the GAS (Google Apps Script) project level
  • Inactive time-based triggers: The setup function that registers automatic sync intervals had never been executed
  • Stale ticket context: Ticket m-91325edb was waiting for manual intervention, but the blocking function name in documentation was incorrect

The fix involved three discrete steps: re-authorizing OAuth scopes, activating the trigger registry, and validating the iCal fetch pipeline.

Technical Details: The CalendarSync.gs Architecture

The calendar synchronization pipeline lives in a single Google Apps Script project deployed across two primary files:

  • sites/queenofsandiego.com/CalendarSync.gs — iCal ingestion, booking ledger updates, and trigger management
  • sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs — Email-based event scanning (separate concern, separate GAS project)

Root cause analysis: The CalendarSync.gs project uses two OAuth scopes:

  • https://www.googleapis.com/auth/gmail.send — for reconciliation email dispatch
  • https://www.googleapis.com/auth/calendar — for writing bookings to the ops calendar

Both are declared in the appsscript.json manifest but are only "activated" when GAS explicitly requests consent. The calendarSyncSetup() function (line 355) registers triggers but doesn't invoke any Gmail or Calendar APIs — so those consent dialogs never fire. Once triggers execute syncAllChannels() and sendDailyReconciliation(), the expired tokens cause permission errors, and the job silently fails.

Step 1: Re-authorizing OAuth Scopes

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

The testSync() function is a lightweight validation routine that exercises both problematic scopes:

function testSync() {
  const boatsetterUrl = ICAL_FEEDS.boatsetter;
  const events = parseICalUrl(boatsetterUrl); // hits Calendar API
  Logger.log(`Fetched ${events.length} events from Boatsetter`);
  
  // Optional: send a test email to trigger Gmail scope
  GmailApp.sendEmail("ops@...", "CalendarSync Test", "OAuth re-auth successful");
}

Execution steps:

  1. Navigate to GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  2. Function dropdown (top-center) → select testSync
  3. Click Run
  4. A permissions dialog appears: "This app needs access to your Gmail account and Google Calendar" → click Allow
  5. If two prompts appear (one per scope), allow both
  6. Check the Execution Log (bottom panel) — should show "Fetched N events from Boatsetter" with no auth errors

Why this approach: Calling GAS functions that touch Gmail or Calendar APIs is the only reliable way to refresh OAuth consent at the project level. The browser session is already authenticated (you're in Google Drive), so the consent flow is frictionless.

Step 2: Activating Time-Based Triggers

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

This function registers two ScriptApp time-based triggers:

function calendarSyncSetup() {
  // Remove any existing triggers
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  
  // Schedule syncAllChannels every 30 minutes
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
  
  // Schedule sendDailyReconciliation at 7:30 AM PT
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .nearMinute(30)
    .inTimezone('America/Los_Angeles')
    .everyDays(1)
    .create();
  
  SpreadsheetApp.getActive()
    .getSheetByName('BookingLedger')
    .getRange('A1')
    .setValue('Setup complete at ' + new Date());
}

Execution steps:

  1. Same GAS editor tab
  2. Function dropdown → select calendarSyncSetup
  3. Click Run
  4. Check the left sidebar (clock icon) → Triggers panel
  5. You should see two new entries: syncAllChannels (Every 30 minutes) and sendDailyReconciliation (Daily at 7:30 AM)

Why this approach: ScriptApp.newTrigger() is the only way to register persistent, server-side scheduled execution in GAS. Unlike client-side JavaScript, these triggers survive editor closure and continue running on Google's infrastructure. Without this registration, the iCal fetch and reconciliation email never execute.

Step 3: Validation and Monitoring

Run testSync() a second time to confirm:

  • No permission errors: OAuth tokens are now valid
  • Execution log shows iCal fetch: The Boatsetter URL is reachable and returns valid events
  • Booking count is non-zero: Calendar writes would succeed if triggered

The actual sync will begin within 30 minutes as the first syncAllChannels trigger fires. Monitor progress in Executions (left sidebar, play icon).

Separate: Viator Email Scanner Activation

File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs:364
Function: jadaCalendarScanSetup()
GAS editor (