Automating Venue Operations: Replacing Google Apps Script with Lambda-Backed Calendar Sync

```html

What Was Done

We replaced a fragile Google Apps Script (GAS) implementation with a serverless architecture combining AWS Lambda, API Gateway, and direct Google Calendar API calls. The old system relied on time-based triggers and email polling in GAS; the new system uses Python-based event processing, JSON configuration files, and Lambda-backed REST endpoints to manage calendar operations for the Rady Shell venue.

Specifically, we:

Migrated calendar event creation from GAS email triggers to Lambda-invoked API calls
Built a JSON-driven campaign scheduler for bulk event operations
Integrated third-party boat platform scrapers (GetMyBoat, Boatsetter) into the deployment pipeline
Established a dashboard webhook system to track and trigger venue operations
Documented the handoff between manual task management and autonomous scheduling

Architecture: From GAS Triggers to Event-Driven Lambda

The Problem with Apps Script

The original CalendarSync.gs file (located at /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs) was tightly coupled to Gmail polling. It would:

Poll Gmail inbox at fixed intervals for specific email patterns
Parse event details from unstructured email bodies
Call Google Calendar API directly with hardcoded service account credentials
Lack visibility into scheduling failures
Require manual GAS project management and secret rotation

This approach made it difficult to add new event types, debug failures, or audit what actually got scheduled.

New Architecture: Lambda + API Gateway + JSON Config

The replacement uses three components:


┌─────────────────────────────────────────────────────────┐
│ Dashboard Task Card                                     │
│ (e.g., "Add 7 Sea Scout Wednesday calendar holds")     │
└──────────────────────┬──────────────────────────────────┘
                       │ (triggers webhook)
                       ▼
┌──────────────────────────────────────────────────────────┐
│ API Gateway v2 Route: POST /calendar/add-event           │
│ (Auth: Dashboard token from repos.env)                  │
└──────────────────────┬──────────────────────────────────┘
                       │
                       ▼
┌──────────────────────────────────────────────────────────┐
│ Lambda Function: RadyShellDashboardAPI                   │
│ Runtime: Python 3.11                                     │
│ Handler: calendar_operations.handle_request()           │
└──────────────────────┬──────────────────────────────────┘
                       │
                       ▼
┌──────────────────────────────────────────────────────────┐
│ Google Calendar API                                      │
│ (Service account: rady-shell-calendar@[project].iam...) │
└──────────────────────────────────────────────────────────┘

Key advantage: Events are now defined in machine-readable JSON config files that can be version-controlled, audited, and easily extended without touching code.

Implementation Details

Campaign Scheduler Configuration

Created /Users/cb/Documents/repos/tools/campaign_schedule.json:


{
  "campaigns": [
    {
      "name": "Sea Scout Wednesday Holds",
      "description": "Weekly hold for sea scout meetings",
      "recurring": {
        "start_date": "2024-04-29",
        "end_date": "2024-12-31",
        "day_of_week": "Wednesday",
        "time": "18:00",
        "duration_minutes": 120
      },
      "calendar_id": "primary",
      "color_id": "3"
    }
  ]
}

This JSON structure allows scheduling recurring events, managing color coding (for dashboard visualization), and updating dates without redeployment.

Lambda Function Design

The Lambda function at `/Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs` was refactored into modular Python code:

calendar_operations.py: Core handler that routes requests by action type (add-calendar-event, list-events, etc.)
google_calendar_client.py: Wrapper around the Google Calendar API with retry logic and error handling
config_loader.py: Loads and validates campaign JSON files from S3
auth_handler.py: Validates incoming dashboard tokens against environment variable DASHBOARD_API_TOKEN

Request routing example:


# Lambda handler signature
def handle_request(event, context):
    action = event['queryStringParameters'].get('action')
    
    if action == 'add-calendar-event':
        return add_calendar_event(event['body'])
    elif action == 'list-events':
        return list_calendar_events(event['body'])
    # ... additional actions

This pattern lets us add new calendar operations without modifying the API Gateway routes—just add a new elif branch and the corresponding handler function.

Third-Party Integration: Boat Platform Scraper

Created /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py and /Users/cb/Documents/repos/tools/platform_inbox_scraper.py to:

Scrape booking confirmations from GetMyBoat and Boatsetter APIs
Extract venue rental dates and guest counts
Feed data into the campaign scheduler for auto-calendar-population

These scripts run on a CloudWatch Events schedule (currently daily at 2 AM UTC) and write parsed bookings to S3 at s3://[bucket-name]/venue-bookings/[date].json.

Deployment Pipeline

Created /Users/cb/Documents/repos/tools/deploy_campaign_scheduler.sh to orchestrate the full deployment:


#!/bin/bash
set -e

# 1. Package Lambda function
cd /Users/cb/Documents/repos/tools
zip -r function.zip calendar_operations.py google_calendar_client.py config_loader.py auth_handler.py

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

# 3. Reload campaign config from S3
aws s3 cp campaign_schedule.json \
  s3://[bucket-name]/rady-shell/campaign_schedule.json

# 4. Invoke test event
aws lambda invoke \
  --function-name RadyShellDashboardAPI \
  --payload '{"action":"list-events","date":"