Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Calendar Sync Triggers and OAuth in Google Apps Script: A Multi-System Auth Recovery

What Was Done

Boatsetter booking data wasn't flowing into the calendar system despite having the iCal feed URL configured. The root cause was a three-part failure: the GAS time-based trigger was never activated, Gmail OAuth had expired, and Calendar OAuth was revoked. This post walks through the exact file locations, functions, and steps taken to restore the sync pipeline.

The Problem: Three Distinct Blockers

  • Missing Trigger: The trigger setup function existed but was never executed, so no scheduled sync was running.
  • Expired Gmail OAuth: The Gmail scope (for sending reconciliation emails) was no longer authorized.
  • Revoked Calendar OAuth: The Calendar API scope for writing bookings to the spreadsheet was revoked.

Each blocker would have failed silently or thrown permission errors. The fix required re-authorization at the GAS project level, activation of the time-based triggers, and verification of the entire sync pipeline.

Technical Details: File Structure and Functions

Primary Sync File

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/CalendarSync.gs

This Google Apps Script file contains the complete calendar sync pipeline:

  • calendarSyncSetup() (line ~355): Initializes the spreadsheet schema, creates the BookingLedger tab, and registers two time-based triggers with ScriptApp.
  • syncAllChannels() (line ~420): Fetches iCal feeds (currently wired for Boatsetter), parses events, deduplicates against the ledger, and writes new bookings to the sheet. Runs every 30 minutes.
  • sendDailyReconciliation() (line ~480): Queries the BookingLedger for the previous day's bookings and sends a summary email via Gmail. Scheduled daily at 7:30 AM PT.
  • testSync() (line ~563): A manual test function that runs the full sync once, used to verify the pipeline and trigger OAuth consent dialogs.

The ICAL_FEEDS array (line ~50) is where iCal URLs are registered. Boatsetter's URL was already present but dormant.

Secondary Viator Scanner (Separate Project)

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/JadaCalendarDashboard.gs

Function: jadaCalendarScanSetup() (line ~364)

This is a separate GAS project responsible for scanning Viator confirmation emails and extracting booking data. It also requires a one-time setup trigger activation but was out of scope for this immediate fix.

Step-by-Step Recovery Process

Step 1: Re-authorize OAuth Scopes

GAS OAuth is project-level, not script-level. Opening the editor and executing any function that uses Gmail or Calendar APIs triggers the browser consent dialog.

Action: Opened the GAS editor at https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit, selected testSync from the function dropdown, and clicked Run. This triggered two sequential OAuth prompts—first for Gmail (sending reconciliation emails), then for Calendar (reading and writing bookings). Both were granted.

Step 2: Activate Time-Based Triggers

Action: Ran calendarSyncSetup() from the same dropdown. This function calls:

ScriptApp.newTrigger('syncAllChannels')
  .timeBased()
  .everyMinutes(30)
  .create();

ScriptApp.newTrigger('sendDailyReconciliation')
  .atHour(7)
  .everyDays(1)
  .create();

Both triggers were registered and are now visible in the GAS editor's left sidebar under the clock icon (Triggers view).

Step 3: Verify the Full Pipeline

Action: Ran testSync() again and monitored the Execution Log. Expected output:

Fetching Boatsetter iCal...
  N events found
CalendarSync complete. New bookings: M
Sending reconciliation email...

No Exception: You do not have permission errors appeared, confirming both OAuth scopes were valid.

Architecture Decisions and Why They Matter

Time-Based vs. Event-Based Triggers

We chose 30-minute polling over webhook-based triggers because:

  • Simplicity: iCal feeds are pull-only; Boatsetter doesn't expose webhooks in their free tier.
  • Resilience: A failed sync attempt doesn't break the next trigger; GAS will retry on the next interval.
  • Cost: GAS has generous quota for time-based triggers; 48 executions per day fits comfortably.

Deduplication Strategy

The BookingLedger tab (created by calendarSyncSetup()) stores a unique identifier for each booking (UID from the iCal event). The syncAllChannels() function checks this before writing, preventing duplicate entries if a sync runs twice or if the same booking appears in multiple feeds.

Email Reconciliation Timing

The daily reconciliation was set to 7:30 AM PT because:

  • It runs after a full night of potential bookings (bookings typically come in through the evening/night).
  • It runs before business hours, giving the team time to act on the summary.
  • GAS's atHour(7).everyDays(1) is timezone-aware to the project's sheet (set to PT).

Infrastructure: Where Everything Lives

  • GAS Project ID: 1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii (CalendarSync)
  • Target Spreadsheet: Ops sheet (referenced in the script as SpreadsheetApp.getActiveSpreadsheet())
  • iCal Source: Boatsetter feed URL (stored in ICAL_FEEDS['boatsetter'])
  • Email Recipient: Configured via the script's RECONCILIATION_EMAIL constant

What's Next