Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Replacing Google Apps Script with Lambda: Automating Calendar Sync and Event Management for Multi-Site Operations

What Was Done

Migrated event scheduling and calendar synchronization from Google Apps Script (GAS) to AWS Lambda, replacing a manual polling-based system with serverless functions that manage calendar events across multiple event sites. The system now handles calendar syncs, email notifications, and event lifecycle management through a unified Lambda API gateway endpoint.

The Problem: GAS Limitations at Scale

The original architecture relied on Google Apps Script bound to a Google Sheet, with time-based triggers polling for changes and sending emails via Gmail API. This approach had critical limitations:

  • Email quota constraints: GAS/Gmail API rate limits made scaling to multiple event types problematic
  • No centralized state: Event status lived in spreadsheet cells, making cross-site coordination difficult
  • Manual polling overhead: Frequent script executions wasted quota and added latency
  • Hard to test: Changes required deploying to GAS and testing through the UI
  • Credential management: Secrets embedded in GAS projects scattered across multiple deployments

Architecture: Lambda + API Gateway + CloudWatch

The new system separates concerns into discrete Lambda functions behind a single API Gateway endpoint:


API Gateway v2 HTTP API
    ↓
/calendar/{action} routes
    ↓
Lambda: shipcaptaincrew_lambda_function.py
    ├── add-calendar-event (POST)
    ├── list-calendar-events (GET)
    ├── get-event-details (GET)
    └── update-event-status (POST)
    ↓
Google Calendar API (via service account)
    ↓
Calendar: Sea Scout events, ash scattering, corporate events

File structure:

  • /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py — Main handler with action routing
  • CalendarSync.gs (deprecated) → Replaced by Lambda endpoints
  • Google Calendar service account credentials stored in Lambda environment (encrypted via AWS Secrets Manager)

Technical Implementation Details

Action Routing Pattern

The Lambda function uses a dispatch pattern to route requests by action type:


def lambda_handler(event, context):
    action = event.get('action') or event.get('queryStringParameters', {}).get('action')
    
    if action == 'add-calendar-event':
        return add_calendar_event(event)
    elif action == 'list-calendar-events':
        return list_calendar_events(event)
    elif action == 'get-event-details':
        return get_event_details(event)
    elif action == 'update-event-status':
        return update_event_status(event)
    else:
        return error_response('Unknown action', 400)

This pattern allows adding new operations without modifying the routing layer. Each action is independently testable and can have its own error handling.

Calendar Event Creation with Validation

Events for Sea Scout Wednesday holds follow a strict schema to prevent data corruption:


POST /calendar/add-calendar-event
Content-Type: application/json

{
  "action": "add-calendar-event",
  "title": "Sea Scout Wednesday Hold",
  "start_time": "2026-04-15T19:00:00",
  "end_time": "2026-04-15T21:00:00",
  "calendar_id": "[service-account@appspot.gserviceaccount.com]",
  "description": "Weekly training session, Spreckels Organ Pavilion, Balboa Park"
}

Validation occurs at two levels:

  • Client-side: API Gateway request models validate required fields before invoking Lambda
  • Handler-side: Python code validates datetime formats, checks for conflicts, verifies calendar access

Google Calendar Service Account Integration

Authentication uses a service account (not user OAuth), which provides:

  • Non-expiring credentials: No refresh token management needed
  • Domain-level scopes: Access multiple calendars under a single identity
  • Audit trail: All changes attributed to the service account, visible in Calendar audit logs

The service account email is granted calendar.edit and calendar.manage permissions on target calendars through Google Workspace Admin console (one-time setup per calendar).

Deployment Strategy

Deployment script: /Users/cb/Documents/repos/tools/deploy_calendar_scheduler.sh


#!/bin/bash
# Package Lambda function with dependencies
cd /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew
pip install -r requirements.txt -t package/
cp lambda_function.py package/
cd package
zip -r ../function.zip .

# Update Lambda function code
aws lambda update-function-code \
  --function-name shipcaptaincrew_lambda \
  --zip-file fileb://../function.zip \
  --region us-west-2

# Verify deployment
aws lambda invoke --function-name shipcaptaincrew_lambda response.json

Deployments use SAM (Serverless Application Model) for infrastructure-as-code; CloudFormation templates define the API Gateway endpoint and Lambda execution role with minimal privileges.

Multi-Site Event Coordination

The system manages events across four distinct event properties:

  • queenofsandiego.com — Primary corporate events and ship charters
  • carole.dangerouscentaur.com — Personal event site (PayPal integrated)
  • quickdumpnow.com — Marketing campaign scheduler
  • Rady Shell at Jacobs (internal calendar) — Birthday parties, afternoon events

Each calendar has a dedicated service account with scoped permissions. Event data flows through a centralized `campaign_schedule.json` file that serves as a single source of truth for promotional calendars:


{
  "campaigns": [
    {
      "id": "sea-scout-wednesday-spring",
      "name": "Sea Scout Weekly Holds",
      "event_type": "training",
      "start_date": "2026-04-08",
      "occurrences": 7,
      "frequency": "weekly",
      "calendar_target": "sea-scout-calendar@appspot.gserviceaccount.com"
    }
  ]
}

A secondary script (`campaign_scheduler.py`) reads this manifest and invokes the Lambda API to batch-create recurring event instances, decoupling the scheduling logic from calendar operations.

Monitoring and Error Handling

CloudWatch Logs automatically captures all Lambda invocations. Key metrics:

  • Error rate: Tracked per action type; alerts trigger if failures exceed 5% over 5 minutes
  • Latency: Calendar API calls typically complete in 200–600