Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Boatsetter Calendar Auto-Sync: OAuth Re-authorization and Trigger Setup in Google Apps Script

Boatsetter bookings weren't syncing to the ops calendar despite having the iCal feed URL configured. Three blockers prevented the integration from working: the time-based trigger was never activated, Gmail OAuth had expired, and Calendar OAuth was revoked. This post walks through the diagnosis and fix, with exact file paths and function names so the next engineer can reproduce it.

The Problem: Three Blockers

  • No trigger registered: The sync code existed in CalendarSync.gs and the Boatsetter iCal URL was wired into the ICAL_FEEDS array, but calendarSyncSetup() had never been run to register the time-based triggers.
  • Expired Gmail OAuth: The script had lost permission to send reconciliation emails via Gmail.
  • Revoked Calendar OAuth: The script couldn't write events to the ops calendar, even if the iCal fetch succeeded.

All three are solved by opening the GAS editor, re-authorizing once, and running the setup function.

File Structure and Functions

The CalendarSync integration lives in one GAS project:

  • Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii
  • File path (local source): /Users/cb/Documents/repos/sites/queenofsandiego.com/CalendarSync.gs
  • GAS editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

Key functions in this file:

  • calendarSyncSetup() (line 355) — Initializes the BookingLedger sheet, registers time-based triggers, and sets up the sync schedule.
  • testSync() (line 563) — Fetches iCal feeds, parses events, writes to the calendar, and sends a dry-run email. Use this to verify auth and integration health.
  • syncAllChannels() — Runs every 30 minutes (triggered by time-based trigger). Fetches all configured iCal feeds and writes new bookings to the ops calendar.
  • sendDailyReconciliation() — Runs daily at 7:30am PT (triggered by time-based trigger). Summarizes the previous day's bookings and emails the reconciliation.

Note: There is a separate Viator email scanner in a different GAS project that also needs setup, but that's outside the scope of this fix (see "What's Next").

Step-by-Step Fix

Step 1: Re-authorize GAS and Trigger OAuth Consent

Open the GAS editor URL above. In the function dropdown at the top, select testSync and click Run. This function exercises all three OAuth scopes (Spreadsheet, Calendar, Gmail) in one call:

function testSync() {
  // Line 563 — exercises ScriptApp, Spreadsheet, Calendar, and Gmail scopes
  // Google will prompt for re-consent if tokens are expired or revoked
}

When you hit Run, Google will show an "This app needs access to..." dialog. Click Allow. You may see two or three consent screens (Gmail scope, Calendar scope, etc.). Allow all of them. Once consent is granted, GAS caches new OAuth tokens for 1 hour and uses refresh tokens for longer-lived access.

Watch the execution log in the sidebar. You should see output like:

Fetching Boatsetter iCal...
  [N] events from Boatsetter
CalendarSync complete. New bookings: [N]
Daily reconciliation for [date] sent to [ops email]

If you see Exception: You do not have permission to access the requested resource, the consent didn't stick — run testSync again and make sure you clicked Allow on all dialogs.

Step 2: Register the Time-Based Triggers

Now run calendarSyncSetup() (line 355). This function:

  • Creates the BookingLedger tab in the ops sheet (if it doesn't exist).
  • Registers syncAllChannels to run every 30 minutes.
  • Registers sendDailyReconciliation to run daily at 7:30am PT.

Check the left sidebar (clock icon → Triggers). You should now see two entries:

syncAllChannels — Time-driven — Every 30 minutes
sendDailyReconciliation — Time-driven — Day timer — 7:30 AM to 8:30 AM

If the triggers don't appear, the function may have thrown an error. Check the execution log and fix the underlying issue before proceeding.

Step 3: Verify the iCal Feed is Wired

Open the CalendarSync.gs file and find the ICAL_FEEDS array (around line 30-50). You should see Boatsetter's iCal URL listed:

const ICAL_FEEDS = {
  boatsetter: "https://icalendar.boatsetter.com/...",
  sailo: "https://...",
  // ...
};

If it's missing or commented out, add it (the URL is stored in repos.env in the project root). Redeploy the script via clasp push if you make changes.

Step 4: Final Verification

Wait 30 minutes for the first syncAllChannels trigger to fire automatically, or run testSync again immediately to verify everything works end-to-end. Watch the ops calendar for new Boatsetter events to appear in the next 5–10 minutes.

Why This Happened

Google Apps Script OAuth tokens are short-lived (1 hour) and must be refreshed regularly. If a script hasn't been run in weeks or months, the tokens expire. The re-consent flow (triggered by calling any function that needs a scope) issues new tokens.

Time-based triggers are separate from script code — they must be registered explicitly via ScriptApp.newTrigger() and don't inherit auth state from manual runs. Once registered, they run as the authorized user (usually the project owner) on Google's infrastructure.

Infrastructure and Deployment

CalendarSync.gs is deployed via Google Apps Script's native deployment system, not via a Lambda or external API. Changes are pushed with:

clasp push

from the local repo root. The project ID is read from .clasp.json in the project directory