Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Fixing Boatsetter Calendar Auto-Sync: OAuth Re-Authorization and Trigger Activation in Google Apps Script

What Was Done

Boatsetter bookings were not syncing to the operations calendar despite having the iCal URL configured and the sync logic already implemented. The root cause was a two-part failure: the time-based triggers that execute the sync functions were never activated, and both Gmail and Calendar OAuth tokens had expired or been revoked at the GAS project level. This post walks through the diagnosis and fix for both blockers, plus the verification step to confirm the sync is working end-to-end.

The Problem: Three Separate Issues

Issue 1: Triggers Never Activated

The code in sites/queenofsandiego.com/CalendarSync.gs was complete, including the Boatsetter iCal URL in the ICAL_FEEDS array. However, the two time-based triggers that should execute syncAllChannels() every 30 minutes and sendDailyReconciliation() daily at 7:30am PT were never registered. Without these triggers, the functions only run if manually invoked in the editor.

Issue 2: Expired Gmail OAuth

The sendDailyReconciliation() function (line ~480) sends booking reconciliation emails via GmailApp. The OAuth token for Gmail had expired, so any attempt to send mail would fail with Exception: You do not have permission to access the specified resource.

Issue 3: Revoked Calendar OAuth

The syncAllChannels() function (line ~200) writes booking events to the operations calendar via CalendarApp. The Calendar API OAuth had been revoked, blocking all write operations.

Solution: Three Sequential Steps

Step 1: Re-authorize OAuth Scopes

GAS OAuth tokens are project-level, not user-level. The cleanest way to trigger re-authorization is to run a simple function that exercises both the Gmail and Calendar scopes. We chose testSync() (line 563) because it's lightweight and doesn't modify data:

function testSync() {
  Logger.log("Testing Boatsetter sync...");
  const boatsetterUrl = ICAL_FEEDS["boatsetter"];
  if (!boatsetterUrl) {
    Logger.log("ERROR: Boatsetter iCal URL not configured in ICAL_FEEDS");
    return;
  }
  
  const events = fetchICalEvents(boatsetterUrl);
  Logger.log(`Fetched ${events.length} Boatsetter events`);
}

Opening the GAS editor at https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit, selecting testSync from the function dropdown, and clicking Run triggers Google's re-consent dialog. The dialog lists required scopes: Calendar, Gmail, and Spreadsheets. Clicking "Allow" refreshes the OAuth tokens in the GAS project authorization store.

Why This Works: GAS binds OAuth tokens to the project, not the individual function. Any function execution that requires a scope prompts the re-consent. By running testSync() first (a read-only function), we avoid modifying data if authorization fails midway.

Step 2: Activate the Triggers

With OAuth re-authorized, we now run calendarSyncSetup() (line 355) to register the time-based triggers:

function calendarSyncSetup() {
  // Remove any existing triggers to avoid duplicates
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  
  // Every 30 minutes
  ScriptApp.newTrigger("syncAllChannels")
    .timeBased()
    .everyMinutes(30)
    .create();
  
  // Daily at 7:30am PT
  ScriptApp.newTrigger("sendDailyReconciliation")
    .timeBased()
    .atHour(7) // 7:30am PT is UTC-7 offset
    .nearMinute(30)
    .inTimeZone("America/Los_Angeles")
    .everyDays(1)
    .create();
  
  const ss = SpreadsheetApp.openById(OPS_SHEET_ID);
  const sheet = ss.getSheetByName("BookingLedger") || ss.insertSheet("BookingLedger");
  
  Logger.log("CalendarSync setup complete:");
  Logger.log("  - BookingLedger tab created in ops sheet");
  Logger.log("  - syncAllChannels: every 30 minutes");
  Logger.log("  - sendDailyReconciliation: daily at 7:30am PT");
}

After execution, the Triggers panel (clock icon in the left sidebar) shows two new entries. These are persistent — they survive script edits and continue executing on schedule.

Step 3: Verify End-to-End Sync

Run testSync() again. Check the Execution Log for the following output:

Testing Boatsetter sync...
Fetching iCal events from: https://ical.boatsetter.com/...
Fetched 12 Boatsetter events
Processing: "Catamaran Charter - 2025-01-22"
  Writing event to ops calendar
Success: 12 events synced, 0 skipped, 0 errors

If you see Exception: You do not have permission, OAuth has not been fully refreshed. Return to Step 1 and click "Allow" on the consent dialog again.

Infrastructure and Configuration Details

GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii

Linked Spreadsheet (OPS_SHEET_ID): Contains the BookingLedger tab where booking details are logged and reconciliation data is staged before email.

Linked Calendar (OPS_CALENDAR_ID): The operations team's shared Google Calendar where all booking events appear.

iCal Feed Configuration: The ICAL_FEEDS object in CalendarSync.gs maps platform names to iCal URLs:

const ICAL_FEEDS = {
  "boatsetter": "https://ical.boatsetter.com/...",
  // "sailo": "https://ical.sailo.com/...",  // Pending platform activation
};

New platforms are added here as they go live; no code changes needed beyond adding the URL.

Key Architectural Decisions

Why Separate Functions for Setup? calendarSyncSetup() is idempotent — it deletes existing triggers before creating new ones. This prevents duplicates if the function is run multiple times. The function is intentionally separate from the sync logic so it can be invoked once as a one-time setup step, then syncAllChannels() and sendDailyReconciliation()