Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Multi-Platform Calendar Sync: OAuth Re-authorization, Trigger Activation, and iCal Integration

What Was Done

Three separate blockers prevented Boatsetter bookings from syncing into the operations calendar. The code was complete and deployed, but:

  • The time-based trigger that runs the sync had never been activated
  • Gmail OAuth token had expired
  • Calendar OAuth had been revoked

This post walks through the fix sequence, the architecture decisions that led to this setup, and how to verify everything works end-to-end.

Technical Details: The Three-Step Fix

Step 1: Re-authorize Google OAuth Scopes

The CalendarSync.gs file requires two OAuth scopes at the Google Apps Script level:

  • https://www.googleapis.com/auth/gmail.send — to send daily reconciliation emails
  • https://www.googleapis.com/auth/calendar — to write synced bookings into the calendar

When these tokens expire or are revoked, the script fails silently during execution. The fix is to trigger the OAuth re-consent flow:

// File: sites/queenofsandiego.com/CalendarSync.gs
// Function: testSync() at line 563
// GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii
// Editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

// Open the editor above, select testSync from the function dropdown, click Run.
// Google will prompt "This app needs access to Gmail and Calendar" — click Allow twice if needed.

Why this works: Google Apps Script enforces OAuth consent at the GAS project level, not the script file level. Running any function that touches Gmail or Calendar APIs triggers the consent UI. The testSync function specifically exercises both scopes by fetching an iCal feed, writing to the calendar, and sending a test email — making it ideal for re-authorization.

Step 2: Activate the Time-Based Triggers

After OAuth is cleared, activate the background sync jobs:

// File: sites/queenofsandiego.com/CalendarSync.gs
// Function: calendarSyncSetup() at line 355
// Editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

// In the GAS editor, select calendarSyncSetup from the function dropdown and click Run.
// This registers two time-based triggers in the ScriptApp scheduler:
//   - syncAllChannels() — every 30 minutes
//   - sendDailyReconciliation() — daily at 7:30am PT

What happens under the hood: The calendarSyncSetup() function:

  • Creates a new tab BookingLedger in the operations spreadsheet if it doesn't exist
  • Calls ScriptApp.newTrigger() to register two background jobs with Google's task scheduler
  • Stores trigger IDs for future cleanup or modification

You can verify triggers were registered by clicking the clock icon in the left sidebar of the GAS editor. You should see both jobs listed with their schedules.

Step 3: Verify the Sync Works End-to-End

Run testSync() again after activation. Watch the execution log for:

9:57:20 AM    Notice    Execution started
9:57:21 AM    Info      Fetching Boatsetter iCal from: [URL redacted]
9:57:22 AM    Info      Parsed 3 events from Boatsetter
9:57:23 AM    Info      Writing 3 events to calendar...
9:57:24 AM    Info      CalendarSync complete. New bookings: 3
9:57:24 AM    Notice    Execution completed

If you see Exception: You do not have permission, OAuth re-authorization didn't stick — go back to Step 1 and rerun testSync.

Architecture: Multi-Platform iCal Integration

The sync strategy is deliberately decoupled:

  • iCal feeds as the source of truth: Each booking platform (Boatsetter, Sailo, Viator, etc.) exposes events via iCal URLs. This is platform-agnostic and requires no custom API integration per platform.
  • Google Calendar as the unified view: All iCal feeds are parsed and written to a single calendar, giving Jada a single source for all bookings across channels.
  • Spreadsheet for reconciliation: The BookingLedger tab in the ops sheet logs every sync event for audit and reconciliation purposes.
  • Daily email digest: sendDailyReconciliation() emails a summary at 7:30am PT so changes are noticed even if the calendar app isn't open.

The ICAL_FEEDS array in CalendarSync.gs is where new platforms are onboarded:

// File: sites/queenofsandiego.com/CalendarSync.gs (around line 50)
const ICAL_FEEDS = {
  'Boatsetter': 'https://boatsetter.com/ical/user/[ID]',
  // Add new feeds here as platforms go live
  // 'Airbnb': 'https://airbnb.com/calendar/ical/[ID]',
  // etc.
};

Key Decisions

Why time-based triggers instead of webhooks? Google Apps Script doesn't support inbound webhooks at the free tier. Time-based triggers are reliable, require zero external infrastructure, and the 30-minute sync window is acceptable for booking visibility. If sub-minute latency becomes critical, this can be migrated to Cloud Tasks or Pub/Sub later.

Why iCal instead of platform-specific APIs? iCal is the universal standard for calendar data. It decouples the sync logic from each platform's API changes, pricing, or deprecations. If Boatsetter shuts down its API tomorrow, you still have iCal as a fallback.

Why spreadsheet logging? Spreadsheets are auditable, human-readable, and queryable. This is essential for reconciliation — if a booking appears in the calendar but not in the source sheet, something went wrong in the sync, and it's easy to spot.

Separate Item: Viator Email Scanner

The Viator booking scanner is in a different GAS project and still needs activation:

// File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs
// Function: