Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Replacing Google Apps Script with Lambda: Automating Calendar Sync and Boat Platform Integration

This session involved migrating away from Google Apps Script (GAS) for calendar synchronization and implementing a more robust serverless architecture using AWS Lambda. The work included integrating multiple boat rental platforms, establishing automated event scheduling, and refactoring legacy email-based workflows into API-driven systems.

What Was Done

  • Migrated calendar sync logic from Google Apps Script (CalendarSync.gs) to AWS Lambda with direct Google Calendar API integration
  • Built a Python-based boat cleaner dispatch system (dispatch_boat_cleaner.py) to automate task scheduling
  • Created email campaign automation infrastructure with templated HTML blasts and JSON-based scheduling
  • Established email scraping and response automation via Gmail API for multi-domain operations
  • Deployed dashboard updates for real-time task tracking and approval workflows
  • Integrated calendar event creation through Lambda API endpoints with token-based authentication

Technical Details: Calendar Sync Architecture

The original Google Apps Script implementation relied on time-based triggers and manual polling. This created several operational pain points:

  • Polling inefficiency: GAS was checking for calendar updates at fixed intervals, consuming quota unnecessarily
  • Error opacity: Failures in GAS execution logs weren't surfaced to operational dashboards
  • Cold start delays: Initial GAS executions experienced unpredictable latency
  • Limited integrations: Difficult to coordinate with external boat platform APIs (GetMyBoat, Boatsetter)

The replacement architecture uses:


AWS Lambda (Node.js/Python runtime)
  ↓
Google Calendar API v3 (OAuth 2.0 service account)
  ↓
API Gateway v2 (HTTP endpoint with token auth)
  ↓
Dashboard frontend (token-based requests)

The Lambda function exposes multiple calendar actions, including add-calendar-event, which accepts structured event data and handles Google Calendar API calls. Authentication uses a custom token stored in environment variables (managed via Lambda configuration, not hardcoded).

Boat Platform Integration

Three distinct workflows were established for boat cleaning and scheduling:

1. Dispatch Automation (dispatch_boat_cleaner.py)

This script:

  • Reads booking data from GetMyBoat and Boatsetter APIs
  • Determines turnaround requirements based on sequential bookings
  • Generates dispatch tasks with location, timing, and vessel details
  • Logs output to a task management system for human review

Key decision: Rather than automatically executing dispatch requests, the system generates structured tasks that require human confirmation. This prevents cascading failures if booking data is stale or conflicting.

2. Calendar Hold Generation

The system now automatically reserves calendar blocks for routine operations (e.g., "Sea Scout Wednesday" holds for seven consecutive weeks). These are added via:


POST /api/calendar/add-event
Authorization: Bearer {DASHBOARD_TOKEN}
Content-Type: application/json

{
  "action": "add-calendar-event",
  "event": {
    "summary": "Sea Scout Wednesday Hold",
    "start": "2025-04-30T19:00:00Z",
    "end": "2025-04-30T21:00:00Z",
    "recurrence": "WEEKLY",
    "count": 7,
    "calendarId": "primary"
  }
}

The Lambda function normalizes timezone handling and delegates directly to Google Calendar API, eliminating the GAS intermediary layer.

3. Email Campaign Scheduling

For marketing and operational outreach, a new campaign scheduler was implemented:

  • campaign_scheduler.py: Core scheduling logic with cron-like rule evaluation
  • campaign_schedule.json: Configuration file defining blast timing, recipient lists, and template selection
  • Template directory: /tools/templates/ with HTML email templates for each domain (e.g., rady_shell_blast1.html)
  • Deployment script: deploy_campaign_scheduler.sh for pushing updates to Lambda

The campaign system is decoupled from calendar events, allowing independent scheduling of marketing emails while calendar holds are managed separately.

Email Scraping and Response Automation

A new email workflow was established to reduce manual reply overhead:

  • platform_inbox_scraper.py: Uses Gmail API to poll inbox for unread messages from specific senders
  • Extracts key data from email bodies (dates, requests, action items)
  • Routes responses through AWS SES with templated replies
  • Logs all communication back to dashboard for audit trail

This system currently handles multi-domain operations including carole.dangerouscentaur.com and quickdumpnow.com. Response templates are stored in /tools/templates/ and rendered with contextual data before sending.

Infrastructure and Deployment

File Structure:


/Users/cb/Documents/repos/
├── sites/
│   ├── queenofsandiego.com/
│   │   └── rady-shell-events/
│   │       └── apps-script-replacement/
│   │           └── CalendarSync.gs (migrated logic)
│   └── carole.dangerouscentaur.com/
│       └── [dashboard files, site content]
├── tools/
│   ├── dispatch_boat_cleaner.py
│   ├── platform_inbox_scraper.py
│   ├── campaign_scheduler.py
│   ├── deploy_campaign_scheduler.sh
│   ├── deploy_inbox_scraper.sh
│   └── templates/
│       ├── rady_shell_blast1.html
│       ├── rady_shell_blast2.html
│       └── qdn_blast1.html
└── agent_handoffs/
    └── ACTIVE.md (task tracking)

AWS Resources:

  • Lambda function: Calendar API endpoint (exact name stored in repos.env)
  • API Gateway v2: HTTP endpoint for dashboard requests
  • SES: Email sending for campaign blasts and responses
  • Secrets Manager/Parameter Store: GAS project IDs, OAuth credentials (referenced via .clasp.json files)

Google Cloud Integration:

  • Google Calendar API v3: Service account credentials for direct event manipulation
  • Gmail API: Message retrieval for inbox scraping
  • Apps Script: Legacy GAS projects identified via .clasp.json mapping (found via find . -name ".clasp.json")

Key Architectural Decisions

Why Lambda over continued GAS: Lambda provides deterministic execution, integrated error handling, and seamless AWS service orchestration. GAS triggers are unreliable for time-sensitive operations, and error reporting is opaque.