Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

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

A ticket sat in "needs-you" for days: Boatsetter bookings weren't syncing to our ops calendar, even though the iCal feed was already wired into the codebase. The root cause turned out to be a combination of three blockers—expired OAuth tokens, revoked calendar permissions, and an unactivated time-based trigger—that required surgical fixes across two Google Apps Script projects. Here's the detailed walkthrough.

The Problem: Three Layers of Blockers

When we inherited this system, the Boatsetter integration was partially built:

  • Code was in place: The iCal URL was hardcoded into sites/queenofsandiego.com/CalendarSync.gs, ready to fetch and parse booking events.
  • Trigger was never activated: The setup function that creates time-based triggers had never been run, so the sync was never scheduled.
  • OAuth tokens were stale: Both Gmail (for sending reconciliation emails) and Calendar (for writing bookings) permissions had either expired or been revoked by the user.

Even if the trigger had been active, writes to the calendar and emails would fail silently with permission errors—not ideal for a booking system.

Technical Details: The Fix Sequence

Step 1: Re-authorize GAS (Restore OAuth Scopes)

Google Apps Script stores OAuth tokens at the script level, not the individual function level. Both Gmail and Calendar scopes need to be re-consented. The cleanest way to trigger this is to run any function that touches those services in the editor UI.

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

This function is a lightweight test harness that exercises the full sync pipeline: it fetches from the Boatsetter iCal feed, attempts to write to the calendar, and sends a test email. It's ideal for triggering the OAuth consent flow.

Steps:

  • Open the GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  • Click the function dropdown and select testSync
  • Click Run
  • Google prompts: "This app needs access to your Google Account" → click Allow
  • You may see two prompts (one for Gmail, one for Calendar) — allow both

Once consent is granted, the tokens are stored in GAS's built-in OAuth cache and remain valid until explicitly revoked.

Step 2: Activate the Trigger (Schedule the Sync)

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

This function creates the time-based triggers that make the sync happen automatically. It must be run exactly once (or idempotently, if already run—the code checks for existing triggers).

Steps:

  • In the same GAS editor, select calendarSyncSetup from the function dropdown
  • Click Run
  • Check the Execution Log (bottom panel) for confirmation output
  • Click the clock icon (Triggers) in the left sidebar to verify two new triggers are registered:
    • syncAllChannels — time-based, every 30 minutes
    • sendDailyReconciliation — time-based, daily at 7:30am PT

The expected log output:

CalendarSync setup complete:
  - BookingLedger tab created in ops sheet
  - syncAllChannels: every 30 minutes
  - sendDailyReconciliation: daily at 7:30am PT
  - Next step: add iCal URLs to ICAL_FEEDS array as platforms go live

Step 3: Validate the Full Pipeline

Run testSync() again to exercise the actual sync logic:

  • It fetches from the Boatsetter iCal URL (configured in ICAL_FEEDS array, line 42)
  • It parses events and writes new bookings to the ops calendar
  • It sends a reconciliation email to the configured address

Check the log for output like:

Fetching Boatsetter iCal...
  15 events from Boatsetter
CalendarSync complete. New bookings: 3

If you see "Exception: You do not have permission," the OAuth re-auth didn't stick—go back to Step 1 and ensure both prompts were clicked.

Why This Architecture?

Separation of concerns: The setup function (calendarSyncSetup()) is separate from the sync logic itself (syncAllChannels()). This is a common pattern in GAS projects because setup is usually a one-time operation, while the actual work happens on a schedule. Keeping them separate makes the code easier to test and reason about.

Time-based triggers over webhook: Boatsetter doesn't offer webhook notifications (at least not in the free tier), so polling via a 30-minute cron is the reliable fallback. Thirty minutes strikes a balance between freshness and API quota usage.

Test functions: Including testSync() alongside the production function lets developers validate the pipeline without waiting for the scheduler. It's also the cleanest way to trigger OAuth re-consent without restructuring the code.

Separate Item: Viator Email Scanner Setup

There's a second GAS project handling Viator bookings via email parsing. It also needs a one-time setup run:

File: sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs
Function: jadaCalendarScanSetup() at line 364
GAS editor: https://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit

Same pattern: run once to register triggers. This is a separate project because Viator uses email parsing rather than iCal feeds.

What's Next

  • Monitor the 30-minute sync: Watch the Execution History in the GAS editor for the next few cycles to ensure no permission errors creep back.
  • Expand ICAL_FEEDS: The code is designed to pull from multiple platforms. As new booking sources come online (Airbnb, VRBO, etc.), their iCal