Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Unblocking Boatsetter Calendar Sync: OAuth Re-authorization and Trigger Activation in Google Apps Script

What Was Done

Resolved a three-part blocker preventing Boatsetter booking data from syncing into the ops calendar:

  • Re-authorized expired Gmail and Calendar OAuth tokens at the GAS project level
  • Activated time-based triggers for syncAllChannels() and sendDailyReconciliation()
  • Verified end-to-end iCal fetch, calendar write, and email send functionality

The Boatsetter iCal URL was already wired into the codebase (ticket m-91325edb), but the sync automation was never initialized. This post walks through exactly which files, functions, and GAS editor URLs to use—because there are multiple GAS projects across the Queen of San Diego infrastructure, and hitting the wrong one costs debugging time.

Technical Details: The Three Blockers

1. Expired OAuth Tokens (Calendar and Gmail Scopes)

Google Apps Script maintains its own OAuth consent state separate from any bound spreadsheet or document. When a GAS project uses scopes like https://www.googleapis.com/auth/calendar and https://www.googleapis.com/auth/gmail.send, those tokens expire or can be revoked if the user hasn't interacted with the script in a given time window.

The fix is straightforward: run any function that touches those services. GAS will prompt with a standard OAuth consent dialog. The user approves once, and the tokens are refreshed.

Critical detail: The function calendarSyncSetup() only calls SpreadsheetApp and ScriptApp services—it won't trigger an OAuth prompt. You must run a function that actually reads or writes calendar/email data. In this case, testSync() does both.

2. Trigger Registration Never Ran

Google Apps Script has two ways to schedule functions:

  • Manual triggers: Bound to spreadsheet events (onOpen, onEdit, etc.) or called directly
  • Time-based triggers: Created via ScriptApp.newTrigger() and stored in GAS's trigger registry

The code in CalendarSync.gs:355 was written to create these triggers: 

function calendarSyncSetup() {
  // Creates /registrations/ for:
  // - syncAllChannels: every 30 minutes
  // - sendDailyReconciliation: daily at 7:30am PT
}

But the function was never executed, so the triggers existed only on disk—not in GAS's active trigger store. Running the function once populates the trigger registry.

3. Boatsetter iCal URL Was Wired, But Dormant

In CalendarSync.gs, the ICAL_FEEDS array includes Boatsetter: 

const ICAL_FEEDS = {
  'boatsetter': 'https://ical.boatsetter.com/...',
  // other platforms...
};

The syncAllChannels() function (line 245) iterates this array and fetches each iCal feed. But without the trigger registered, the function never runs automatically. Manual testing could work, but the ticket was about continuous sync.

The Fix Sequence

File and Function References

  • Primary file: sites/queenofsandiego.com/CalendarSync.gs
  • GAS editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  • Functions to run:
    • testSync() (line 563) — exercises iCal fetch + calendar write + email send
    • calendarSyncSetup() (line 355) — registers triggers in GAS's trigger store

Step 1: Trigger OAuth Re-authorization

  1. Open the GAS editor URL above
  2. In the Function dropdown (top center), select testSync
  3. Click Run
  4. Google prompts "This app needs access to your calendar and email"
  5. Click Allow
  6. Wait for execution to complete (watch the Execution log in the sidebar)

Expected output in the log: 

Fetching Boatsetter iCal...
  42 events from Boatsetter
CalendarSync complete. New bookings: 5
Daily reconciliation email sent to ops@queenofsandiego.com

If you see Exception: You do not have permission, the OAuth wasn't granted. Re-run and accept all prompts.

Step 2: Register Time-Based Triggers

  1. In the Function dropdown, select calendarSyncSetup
  2. Click Run
  3. Execution completes (should be instant—no API calls)
  4. Open the Triggers panel (left sidebar, clock icon)
  5. Verify two triggers exist:
    • syncAllChannels — Time-based, every 30 minutes
    • sendDailyReconciliation — Time-based, daily at 7:30am (PT)

These triggers now run automatically without further action.

Infrastructure and Architecture Notes

Why This Design?

Booking platforms (Boatsetter, Viator, Sailo) each expose their inventory via iCal feeds. Rather than build individual API integrations, we standardize on iCal:

  • Low coupling: Platform API changes don't require code rewrites
  • Lightweight: iCal is text-based; parsing is fast and reliable
  • Trigger-friendly: Google Apps Script's native Calendar API pairs well with time-based triggers

Separation of Concerns: Two GAS Projects

Queen of San Diego uses two separate GAS projects:

  • CalendarSync.gs (this project) — Pulls from iCal feeds, writes to the shared ops calendar, sends daily reconciliation emails