Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Event Calendar Synchronization and Service Dispatch: A Case Study in Multi-Platform Integration

This session involved building and integrating several autonomous systems to handle calendar event management, service coordination, and email-driven workflow automation across multiple platforms. The challenge was consolidating scattered manual processes into a cohesive infrastructure capable of handling calendar events, email threading, and external service dispatch with minimal human intervention.

The Core Problem

The organization was managing calendar events, booking coordination, and service scheduling across multiple disconnected systems: Google Calendar, email inboxes, external booking platforms, and manual spreadsheet tracking. This created bottlenecks, missed events, and required excessive human coordination. The goal was to build a system where:

  • Calendar events could be synchronized programmatically from multiple sources
  • Email-driven tasks could trigger calendar updates automatically
  • Service scheduling (boat cleaning, logistics) could be dispatched via scripts
  • All actions would be logged and trackable

Architecture Overview

The solution leverages several AWS and Google Cloud services in concert:

  • Google Apps Script (GAS) as the primary orchestration layer
  • AWS Lambda for calendar API exposure and event management
  • API Gateway for HTTPS endpoints and authentication
  • Google Calendar API as the system of record for events
  • Gmail API for email-driven workflow triggers
  • Python automation scripts for service dispatch and platform integration

Key Component: CalendarSync.gs

The centerpiece is a Google Apps Script file located at:

/Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs

This script handles:

  • Calendar event creation via API calls — Exposes a Lambda function endpoint that accepts JSON payloads with event details (title, date, attendees, description)
  • Email polling — Periodically checks Gmail for messages matching specific criteria (sender, subject keywords, labels)
  • Event enrichment — Parses email content to extract dates, locations, and participant information
  • Conflict detection — Checks for overlapping calendar events before insertion
  • Audit logging — Records all calendar operations with timestamps and status

The script was deployed to a Google Apps Script project within the rady-shell-events directory structure, allowing it to access the organization's Google Calendar via service account authentication.

Lambda API Integration

A Lambda function was configured to expose calendar operations via HTTP. The function accepts POST requests with an action parameter that determines operation type:

  • add-calendar-event — Creates a new calendar entry
  • list-calendar-events — Returns upcoming events within a date range
  • get-event-details — Fetches full details for a specific event
  • update-calendar-event — Modifies an existing event

The endpoint is secured via API Gateway with token-based authentication. Requests include the bearer token stored in repos.env:

curl -X POST https://[API-GATEWAY-ENDPOINT]/calendar \
  -H "Authorization: Bearer [TOKEN]" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "add-calendar-event",
    "title": "Sea Scout Wednesday Hold",
    "date": "2025-05-07",
    "startTime": "19:00",
    "duration": 120,
    "description": "Calendar hold for weekly meeting"
  }'

The Lambda function is configured with environment variables pointing to the Google Calendar ID and service account credentials (stored in AWS Secrets Manager, not in the Lambda code itself).

Service Dispatch Automation

Two Python scripts were created in the tools directory to handle external service coordination:

dispatch_boat_cleaner.py — Located at /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py

This script:

  • Parses booking platform credentials from environment variables
  • Queries service availability calendars (GetMyBoat, Boatsetter, or custom platforms)
  • Sends dispatch notifications via email (AWS SES) or webhook
  • Logs service requests to a DynamoDB table for audit trails

platform_inbox_scraper.py — Located at /Users/cb/Documents/repos/tools/platform_inbox_scraper.py

This script:

  • Connects to email inboxes via IMAP or Gmail API
  • Extracts structured data from booking confirmations, cancellations, and service updates
  • Triggers Lambda functions to update calendar state
  • Sends summary digests to specified recipients

deploy_inbox_scraper.sh — Deployment script at /Users/cb/Documents/repos/tools/deploy_inbox_scraper.sh

This handles:

  • Installing Python dependencies in a Lambda layer
  • Packaging scripts as Lambda function code
  • Triggering CloudWatch Events to run on a schedule (e.g., every 15 minutes)

Email-Driven Workflow

The system monitors specific Gmail labels and sender addresses. When emails arrive matching criteria (e.g., from Carole about bookings), the inbox scraper:

  1. Extracts key data (dates, service types, participant counts)
  2. Calls the calendar API to create or update events
  3. Generates a dashboard card summarizing the action
  4. Sends a notification email to the workflow coordinator for approval if needed

This creates a semi-autonomous loop where email becomes a valid input channel for calendar updates, reducing manual data entry.

Key Technical Decisions

Why Google Apps Script for orchestration? — GAS has native access to Google Calendar and Gmail APIs, native service account authentication, and executes within Google's infrastructure, eliminating cold-start latency and authentication complexity for Google APIs.

Why Lambda for the HTTP API layer? — Lambda decouples the HTTP interface from GAS execution, allowing external systems (dashboard, mobile apps, third-party services) to trigger calendar operations without direct access to GAS projects. API Gateway provides authentication and rate limiting.

Why Python for service dispatch? — Python's ecosystem offers mature libraries for IMAP, HTTP requests, and data parsing. The scripts can run as Lambda functions, EC2 cron jobs, or local development tools without code changes.

Environment variable strategy — Credentials are loaded from repos.env during development and from AWS Secrets Manager during production deployment. This allows the same code to work locally and in production without credential duplication.

Integration with Dashboard

The dashboard (served from /tmp/dashboard_index.html with a production version in the website repo) displays: