Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Fixing Boatsetter iCal Sync: OAuth Re-authorization and Trigger Activation in Google Apps Script

What Was Done

Boatsetter booking auto-sync wasn't working despite having the iCal URL integrated into the codebase. The root cause: the Google Apps Script (GAS) project had never been initialized with the required time-based triggers, and both Gmail and Calendar OAuth scopes had expired or been revoked. This post walks through the three-step fix process that got the sync pipeline operational.

The Problem: Three Blockers

  • No trigger activation: The calendarSyncSetup() function in sites/queenofsandiego.com/CalendarSync.gs (line 355) was never executed, so the Apps Script scheduler had no trigger definitions registered.
  • Expired Gmail OAuth: The Gmail scope token used by sendDailyReconciliation() had expired, blocking outbound reconciliation emails.
  • Revoked Calendar OAuth: The Google Calendar write scope had been revoked, preventing event creation even if the triggers fired.

Without all three of these pieces in place, the entire sync loop—fetch iCal → parse events → write to calendar → send summary email—would fail silently.

Technical Details: The Fix Process

Step 1: OAuth Re-authorization

Google Apps Script manages OAuth consent at the project level, not the individual deployment. When you run any function that requires a specific scope (Gmail, Calendar, etc.), GAS triggers the Google consent flow if the scope hasn't been authorized yet, or if the token has expired.

The fix: Run the testSync() function first (same file, line 563). This function exercises both the Calendar write scope and the Gmail scope:

// File: sites/queenofsandiego.com/CalendarSync.gs:563
function testSync() {
  Logger.log("Testing Boatsetter iCal sync...");
  syncAllChannels();
  Logger.log("Test complete. Check execution log above.");
}

By running testSync(), the GAS editor will prompt the user with Google's OAuth consent dialog. You'll see something like "This app wants access to your Gmail account" and "This app wants to manage your Google Calendar." Clicking Allow grants the necessary scopes and refreshes the token.

Why this works: GAS scopes are declared in the manifest (appsscript.json) but only enforced when a function that uses those scopes actually executes. The testSync() function calls syncAllChannels(), which internally calls:

  • Calendar.Events.insert() → requires Calendar scope
  • GmailApp.sendEmail() → requires Gmail scope

Running the test forces these scopes to be evaluated, triggering the consent prompt.

Step 2: Activate Time-Based Triggers

Once OAuth is fixed, the actual trigger setup happens via calendarSyncSetup() (line 355):

// File: sites/queenofsandiego.com/CalendarSync.gs:355
function calendarSyncSetup() {
  // Clear any existing triggers to avoid duplicates
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(t => ScriptApp.deleteTrigger(t));
  
  // Register syncAllChannels to run every 30 minutes
  ScriptApp.newTrigger("syncAllChannels")
    .timeBased()
    .everyMinutes(30)
    .create();
  
  // Register daily reconciliation email at 7:30 AM PT
  ScriptApp.newTrigger("sendDailyReconciliation")
    .timeBased()
    .atHour(7)
    .everyDays(1)
    .create();
  
  SpreadsheetApp.getActiveSpreadsheet()
    .insertSheet("BookingLedger");
  
  Logger.log("CalendarSync setup complete:");
  Logger.log("  - BookingLedger tab created in ops sheet");
  Logger.log("  - syncAllChannels: every 30 minutes");
  Logger.log("  - sendDailyReconciliation: daily at 7:30am PT");
}

This function does two things:

  1. Registers time-based triggers: Uses ScriptApp.newTrigger() to create two scheduled tasks. The 30-minute sync interval ensures Boatsetter bookings are pulled into Google Calendar within a reasonable window. The 7:30 AM PT reconciliation email (sent to operations) provides a daily summary of all bookings across channels.
  2. Creates the BookingLedger sheet: This is the backing spreadsheet where sync metadata and booking records are stored.

After running this function, you can verify the triggers were created by checking the Apps Script editor sidebar: click the clock icon (Triggers) and you should see two entries: syncAllChannels and sendDailyReconciliation.

Step 3: Verify the Sync Works

Run testSync() again and monitor the execution log. You're looking for output like:

9:57:20 AM    Notice    Execution started
9:57:21 AM    Info     Fetching iCal from Boatsetter...
9:57:22 AM    Info     Parsed 3 events from Boatsetter iCal feed
9:57:23 AM    Info     Creating 3 calendar events in primary calendar
9:57:24 AM    Info     Synced Boatsetter bookings successfully
9:57:24 AM    Notice    Execution completed

If you see Exception: You do not have permission to access the requested resource, the OAuth re-auth didn't complete. Go back to Step 1 and ensure you clicked Allow on both consent prompts.

Infrastructure: GAS Project Details

Project URL: https://script.google.com/d/1HiEgjBrCGrnOIvr27nIk1E1qoR1pwKmWLigl191Tz0xq7LautJrIp9Ii/edit

Associated files:

  • CalendarSync.gs – Main sync logic, includes iCal parsing and Calendar API calls
  • appsscript.json – Project manifest; declares required OAuth scopes: calendar, gmail, spreadsheets
  • ICAL_FEEDS array in CalendarSync.gs – Contains Boatsetter iCal URL and placeholders for future integrations (Viator, Airbnb, VRBO)

Related but separate GAS project: The Viator email scanner (jadaCalendarScanSetup() in JadaCalendarDashboard.gshttps://script.google.com/d/1dDpSK8JZda7XUpKIGlyyAX19KLL4JqFjYVtpcunB5ZE3-NMX_9v0lQJ5/edit