Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Fixing Calendar Sync Auto-Triggers in Google Apps Script: OAuth Re-Authorization and Time-Based Trigger Setup

The Problem

A Boatsetter iCal feed integration had been coded and wired into the system, but bookings weren't syncing to the operations calendar. The root cause: the sync trigger was never activated, and OAuth tokens (Gmail and Calendar) had expired or been revoked. This meant even if the trigger fired, writes would fail with permission errors.

The ticket (m-91325edb) required two separate GAS projects to be initialized, each with distinct setup functions and OAuth scopes. Finding the right file and function names in a multi-project environment was the first hurdle.

What Was Done

Three sequential fixes resolved the issue:

  • Re-authorized OAuth tokens for both Gmail and Calendar scopes in Google Apps Script
  • Activated time-based triggers to run calendar sync and daily reconciliation functions on schedule
  • Verified the integration with a test sync execution that pulled Boatsetter iCal data and wrote to the calendar

Technical Details: The Fix Sequence

Step 1 — OAuth Token Re-Authorization

Google Apps Script manages OAuth scopes differently than standalone OAuth flows. When you run a GAS function that touches a specific Google API (Gmail, Calendar, Sheets, etc.), the script prompts for consent only on first run or after revocation.

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

Why run testSync before calendarSyncSetup? Because calendarSyncSetup() only initializes triggers and creates a BookingLedger sheet tab—it doesn't actually invoke the Gmail or Calendar APIs. The OAuth consent dialogs are triggered only when the code executes API calls. Running testSync() performs a live iCal fetch and attempts calendar writes, which forces the browser to display the Google consent screen.

In the GAS editor:

1. Open: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit
2. Function dropdown menu → select "testSync"
3. Click the Run button (play icon)
4. Browser displays: "This app needs access to your Gmail account"
5. Click Allow → second prompt for Calendar scope
6. Click Allow again
7. Watch the Execution Log pane for results

The logs should show either successful iCal parsing or a specific API error. If you see Exception: You do not have permission to perform this action, the OAuth re-auth didn't complete—repeat the flow.

Step 2 — Activate Time-Based Triggers

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

This function does three things:

  • Creates a BookingLedger tab in the operations spreadsheet if it doesn't exist
  • Registers a syncAllChannels trigger to run every 30 minutes
  • Registers a sendDailyReconciliation trigger to run daily at 7:30 AM PT

The trigger registration uses GAS's ScriptApp.newTrigger() API, which stores triggers server-side. Once registered, they run on the schedule even if you close the editor.

// From CalendarSync.gs (simplified)
function calendarSyncSetup() {
  createBookingLedgerTab_();
  
  // Remove any existing triggers to avoid duplicates
  ScriptApp.getProjectTriggers().forEach(trigger => {
    ScriptApp.deleteTrigger(trigger);
  });
  
  // Create new triggers
  ScriptApp.newTrigger('syncAllChannels')
    .timeBased()
    .everyMinutes(30)
    .create();
  
  ScriptApp.newTrigger('sendDailyReconciliation')
    .timeBased()
    .atHour(7)
    .nearMinute(30)
    .inTimezone('America/Los_Angeles')
    .everyDays(1)
    .create();
  
  Logger.log('CalendarSync setup complete...');
}

In the GAS editor:

1. Function dropdown → select "calendarSyncSetup"
2. Click Run
3. Check the left sidebar (clock icon) → Triggers pane
4. Verify two triggers are listed:
   - syncAllChannels (every 30 minutes)
   - sendDailyReconciliation (daily 7:30 AM)

Step 3 — Verify End-to-End Sync

Run testSync() again to confirm the full pipeline:

1. Function dropdown → select "testSync"
2. Click Run
3. Check Execution Log for output like:
   
   Fetching Boatsetter iCal...
   Parsed 5 events from Boatsetter
   Writing 3 new events to calendar...
   Calendar writes complete
   Reconciliation email sent to ops@example.com
   
   No "Exception" lines = success

Separate Infrastructure: Viator Email Scanner

A second GAS project handles email-based booking imports from Viator (the activities booking platform). This lives in a different project with its own OAuth scope (Gmail full access).

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

This function was a separate ticket (t-8d86d5ba) and uses the same trigger pattern. The key difference: it scans the Gmail inbox for booking confirmations from Viator and parses them into the calendar, rather than pulling from an iCal URL. Same GAS architecture, different business logic.

Key Architectural Decisions

  • Why two separate GAS projects? Each project has its own script ID, deployment history, and OAuth consent screen. Viator integration needed full Gmail inbox access (to scan for emails), while CalendarSync only needs to send emails (narrower scope). Splitting them reduces blast radius if one needs to be disabled.
  • Why time-based triggers instead of webhooks? GAS doesn't natively support inbound webhooks. Time-based triggers are the built-in mechanism for periodic execution. 30-minute intervals balance freshness (bookings appear in calendar within 30 min of being made) with API