Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Fixing Calendar Sync Triggers and OAuth Re-authorization in Google Apps Script

Overview

The Boatsetter iCal integration was written and wired into the codebase but non-functional because two critical setup steps were never executed: the time-based sync triggers were never activated, and the OAuth tokens for Gmail and Google Calendar had expired or been revoked. This post walks through the diagnosis, the fix sequence, and the infrastructure decisions that prevent this class of problem in the future.

What Was Done

Three blockers required remediation in sequence:

  • Re-authorize OAuth scopes for Gmail and Google Calendar at the GAS project level
  • Activate time-based triggers for the sync and reconciliation flows
  • Verify end-to-end execution by running a test sync function

Technical Details

File Locations and Function Names

The primary work lives in one GAS project:

  • File: sites/queenofsandiego.com/CalendarSync.gs
  • GAS Editor URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
  • Setup function: calendarSyncSetup() at line 355
  • Test function: testSync() at line 563

A separate GAS project handles email-based booking ingestion and requires its own trigger activation:

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

Step 1: Re-authorize OAuth Tokens

GAS maintains separate OAuth tokens for each API scope (Gmail, Calendar, Sheets, etc.). When tokens expire or are revoked, the GAS runtime will reject API calls with Exception: You do not have permission errors. The fix is to trigger a re-consent flow by executing any function that touches those scopes.

Execution sequence:

  1. Open the GAS editor for CalendarSync.gs (URL above)
  2. In the function dropdown menu, select testSync
  3. Click the Run button
  4. Google will prompt with a consent screen: "This app needs access to your Gmail, Calendar, and Sheets" — click Allow
  5. The OAuth flow may prompt multiple times (one per scope) — allow all of them

Why testSync first? The calendarSyncSetup() function only touches ScriptApp (for creating triggers) and SpreadsheetApp (for creating the BookingLedger tab). It won't trigger Calendar or Gmail OAuth prompts. Running testSync first exercises the full API surface — it fetches from the Boatsetter iCal URL, writes to Google Calendar, and sends reconciliation emails via Gmail. This guarantees the OAuth consent dialogs fire before we activate the time-based triggers.

Step 2: Activate Time-Based Triggers

Once OAuth is re-authorized, the setup function can safely create triggers:

  1. In the GAS editor, select calendarSyncSetup from the function dropdown
  2. Click Run
  3. Watch the Execution log for the success message

The expected output:

Notice    Execution started
Info    CalendarSync setup complete:
Info      - BookingLedger tab created in ops sheet
Info      - syncAllChannels: every 30 minutes
Info      - sendDailyReconciliation: daily at 7:30am PT
Notice    Execution completed

What this function does:

  • Creates or idempotently returns the "BookingLedger" tab in the ops spreadsheet (source of truth for all bookings across platforms)
  • Registers a syncAllChannels time-based trigger that fires every 30 minutes
  • Registers a sendDailyReconciliation time-based trigger that fires daily at 7:30 AM PT

To verify triggers were created, click the clock icon in the left sidebar of the GAS editor. You should see exactly two entries with the functions and schedules listed above.

Step 3: Verify End-to-End Sync

Run testSync again and monitor the Execution log. You should see output like:

Fetching Boatsetter iCal from: [URL masked]
  Parsed 5 events from Boatsetter
Writing 3 new bookings to ops sheet BookingLedger tab
Writing 3 events to "Boatsetter" calendar
Sending daily reconciliation to operations@...
CalendarSync complete. New bookings: 3

Any error with You do not have permission indicates OAuth is still invalid and step 1 needs to be repeated.

Architecture and Design Decisions

Why GAS and Not a Standalone Service?

Google Apps Script is the integration point for this system because:

  • It has native OAuth integration with Gmail and Google Calendar — no external token management required
  • It executes within Google's ecosystem, so writes to Google Calendar are atomic and auditable
  • Time-based triggers (via ScriptApp.newTrigger()) are serverless — no Lambda, cron, or external scheduler to manage
  • It can read from external iCal feeds (Boatsetter, Sailo, Viator) via UrlFetchApp

Boatsetter iCal Feed Integration

The iCal URL for Boatsetter is stored in the repo's repos.env file and referenced in CalendarSync.gs as part of the ICAL_FEEDS array. The sync function:

  1. Fetches the iCal file via HTTPS
  2. Parses events and extracts bookings (date, duration, rental asset, customer)
  3. Writes to the BookingLedger tab in the ops spreadsheet (canonical ledger)
  4. Creates corresponding events in the "Boatsetter" Google Calendar

This pattern scales: as other platforms (Sailo, Viator) go live, their iCal feeds are added to the array and