Automating Boat Service Dispatch and Calendar Synchronization: A Multi-Platform Integration

```html

This session focused on solving a critical operational problem: how to manage boat cleaning services across multiple booking platforms without manual intervention. The challenge involved integrating Google Calendar, Google Apps Script, multiple boat booking platforms, and custom Python dispatch tools into a cohesive system that could handle scheduling conflicts and automate notifications.

What Was Done

We built an automated boat service dispatch system that:

Created a Python dispatch orchestrator (/Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py) to validate and execute cleaning service requests
Extended Google Apps Script calendar synchronization (CalendarSync.gs) to pull boat availability from multiple platforms
Implemented platform-agnostic event scraping to detect booking conflicts across GetMyBoat, Boatsetter, and internal systems
Set up automated email notifications to service providers with job details and scheduling windows
Created a JSON configuration file (campaign_schedule.json) for managing recurring service schedules

Technical Architecture

The Dispatch Pipeline

The core system uses three complementary components:

1. Calendar Synchronization (Google Apps Script)

The existing CalendarSync.gs file in /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/ was extended to:

Poll Google Calendar API every 5 minutes for new boat-related events
Query platform booking APIs (GetMyBoat, Boatsetter) via OAuth 2.0 credentials stored in GAS Script Properties
Cross-reference availability windows to detect conflicts
Send alert emails via Gmail API when overlapping bookings are detected

Key GAS functions added:

function syncCalendarWithPlatforms() {
  const calendarId = 'primary';
  const now = new Date();
  const events = CalendarApp.getCalendarById(calendarId)
    .getEvents(now, new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000));
  
  for (let event of events) {
    validateAgainstPlatforms(event);
  }
}

2. Python Dispatch Orchestrator

The new dispatch_boat_cleaner.py script handles the actual coordination:

Receives webhook notifications from the Lambda calendar API
Parses job requirements (boat type, cleaning scope, time window)
Queries a local SQLite database of available service providers and their current utilization
Selects optimal provider based on proximity, availability, and specialization
Generates a formatted job assignment and sends it via SES
Logs the assignment to CloudWatch for audit trail

The dispatch logic prioritizes providers by:

def select_provider(job_requirements):
    providers = query_available_providers(
        location=job_requirements['dock_location'],
        availability_window=job_requirements['time_window'],
        specialization=job_requirements['service_type']
    )
    
    return sorted(providers, key=lambda p: (
        calculate_distance(p['location'], job_requirements['dock']),
        p['current_load'],
        -p['rating']
    ))[0]

3. Platform Integration Layer

Created platform adapters to normalize data from different booking systems:

GetMyBoat API client: Queries bookings via REST API, polls every 30 minutes
Boatsetter integration: Scrapes event feeds from iCal subscriptions, processes via platform_inbox_scraper.py
Internal calendar: Direct Google Calendar API calls with service account authentication

Email Campaign Infrastructure

For communicating job assignments and service confirmations, we set up templated email campaigns:

Created HTML templates in /Users/cb/Documents/repos/tools/templates/ with CSS inlining for email client compatibility
Built campaign_scheduler.py to queue and send emails via AWS SES at optimal times
Implemented unsubscribe tracking with links to quickdumpnow.com/unsubscribe/ to maintain compliance
Added logging to qdn_stats.py to track open rates, click-through rates, and delivery failures

Email workflow:

def dispatch_job_notification(provider_email, job_details):
    template = load_template('rady_shell_blast1.html')
    body = template.format(
        provider_name=job_details['assigned_to'],
        boat_name=job_details['vessel'],
        dock_location=job_details['location'],
        time_window=f"{job_details['start']} - {job_details['end']}",
        special_instructions=job_details['notes']
    )
    
    send_via_ses(
        to=provider_email,
        subject=f"Job Assignment: {job_details['vessel']} - {job_details['date']}",
        html_body=body,
        message_id=generate_tracking_id()
    )

Infrastructure & Deployment

Lambda Functions

The calendar API Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py handles:

Receiving HTTP requests via API Gateway v2
Authenticating requests with bearer tokens stored in environment variables
Supporting actions: add-calendar-event, list-events, delete-event, update-event
Direct Google Calendar API calls using service account credentials
Returning JSON responses with event IDs and confirmation details

The function validates incoming events for conflicts before committing to the calendar.

Deployment Scripts

Two deployment scripts manage different components:

deploy_inbox_scraper.sh: Packages platform_inbox_scraper.py and pushes to an EC2 instance running a cron scheduler
deploy_campaign_scheduler.sh: Deploys campaign_scheduler.py to the same instance, scheduled to run hourly

Both scripts use SSH with key-based authentication (no hardcoded passwords) and verify checksums before executing.

Configuration Management

All API keys, OAuth tokens, and platform credentials are stored in:

AWS Secrets Manager for production (referenced by Lambda and EC2 via IAM roles)
Environment files in repos (repos.env) for development only, never committed
Google Apps Script Properties for GAS functions (encrypted at rest by Google)