Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Reactivating Multi-Channel Calendar Sync: Rebuilding OAuth Consent and Trigger Infrastructure in Google Apps Script

What Was Done

We diagnosed and fixed a critical blocker preventing automated booking synchronization from Boatsetter (and future platforms) into the ops calendar. The issue was threefold: (1) OAuth tokens had expired at the GAS level, (2) the time-based triggers that drive sync were never activated, and (3) the setup function needed to be executed to wire everything together. The fix involved re-authorizing the GAS project, activating the trigger infrastructure, and validating the iCal fetch pipeline.

Technical Details: The Root Cause

The calendar sync infrastructure was written months ago but never fully activated. Here's the architecture:

  • Primary sync engine: sites/queenofsandiego.com/CalendarSync.gs
  • Setup function: calendarSyncSetup() at line 355
  • Test/validation function: testSync() at line 563

The file contains three scheduled sync functions, but they were never registered with Google's trigger system:

  • syncAllChannels() — runs every 30 minutes, fetches iCal feeds from configured platforms (Boatsetter, Airbnb, etc.) and writes to the BookingLedger tab
  • sendDailyReconciliation() — daily at 7:30 AM PT, emails a summary to ops
  • cleanupOldEntries() — maintenance function for ledger hygiene

Without calendarSyncSetup() being run, these functions existed in the code but had no registered triggers in the Apps Script runtime. Additionally, the Gmail and Calendar OAuth scopes had expired, meaning even if the triggers fired, any attempt to write to the calendar or send email would fail with permission errors.

The Fix: Three Sequential Steps

Step 1: Re-authorize OAuth Scopes

GAS OAuth is project-wide, not per-function. When any function accesses Gmail or Calendar APIs, the user must consent. We triggered re-authorization by running the testSync() function, which exercises both:

// sites/queenofsandiego.com/CalendarSync.gs:563
function testSync() {
  Logger.log('Testing Boatsetter iCal fetch...');
  const boatsetter = fetchIcal(ICAL_FEEDS.boatsetter);
  Logger.log(`Fetched ${boatsetter.length} events`);
  
  // Attempt a calendar write (triggers Calendar OAuth)
  testCalendarWrite();
  
  // Attempt an email send (triggers Gmail OAuth)
  testEmailSend();
}

Opening the GAS editor at https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit, selecting testSync from the function dropdown, and clicking Run triggers the consent dialogs. The execution log confirms both scopes were granted.

Step 2: Register Time-Based Triggers

Once OAuth was re-authorized, running calendarSyncSetup() registers the triggers:

// sites/queenofsandiego.com/CalendarSync.gs:355
function calendarSyncSetup() {
  // Remove any existing triggers to avoid duplicates
  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 daily reconciliation at 7:30 AM PT
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .atMinute(30)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .onWeekDay(ScriptApp.WeekDay.TUESDAY)
    .onWeekDay(ScriptApp.WeekDay.WEDNESDAY)
    .onWeekDay(ScriptApp.WeekDay.THURSDAY)
    .onWeekDay(ScriptApp.WeekDay.FRIDAY)
    .create();
  
  // Confirm in logs
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('BookingLedger')
    .appendRow(['Setup complete', new Date()]);
}

After running this function, the left sidebar's clock icon (Triggers panel) shows two active time-based triggers. No manual calendar or email permissions are needed — the GAS service account uses the project-level OAuth tokens already authorized in Step 1.

Step 3: Validate the Pipeline

Run testSync() again and check the Execution Log. Success looks like:

9:57:20 AM    Notice    Execution started
9:57:22 AM    Info     Fetching Boatsetter iCal...
9:57:23 AM    Info     Parsed 3 events from Boatsetter feed
9:57:24 AM    Info     Writing 2 new bookings to BookingLedger
9:57:24 AM    Info     Sending reconciliation email to ops@queenofsandiego.com
9:57:24 AM    Notice    Execution completed

If you see Exception: You do not have permission to access the requested resource, the OAuth re-authorization didn't stick — re-run Step 1.

Infrastructure: iCal Feed Configuration

The sync engine reads from a central configuration array in CalendarSync.gs:

const ICAL_FEEDS = {
  boatsetter: 'https://boatsetter.com/ical/xxxxx',
  // airbnb: '...',  // to be added when live
  // vrbo: '...',    // to be added when live
};

const SPREADSHEET_ID = 'YOUR_OPS_SHEET_ID';
const BOOKING_LEDGER_TAB = 'BookingLedger';

Each platform's iCal URL is stored here, and the syncAllChannels() function iterates over ICAL_FEEDS, parsing events and deduplicating by source and booking ID before writing to the BookingLedger tab in the ops spreadsheet.

Key Decisions: Why This Approach

  • Time-based triggers over webhooks: GAS webhooks are complex to set up and require each platform to support push notifications. Polling every 30 minutes trades real-time for simplicity and covers the typical Jada booking velocity.
  • OAuth re-authorization via testSync(): Instead of manually opening permission dialogs, we embedded a test function that exercises both Gmail and Calendar scopes, making it clear which APIs the project touches.
  • Ledger-based reconciliation: Rather than syncing directly to the calendar, we write to a BookingLedger tab first. This creates an audit trail, allows manual review, and decouples sync frequency from calendar API rate limits.
  • Dedu