Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Event Logistics: Building a Calendar Sync System for Complex Scheduling Requirements

This post details the architecture and implementation of a calendar synchronization system that bridges Google Calendar, multiple booking platforms, and a custom dashboard. The system handles event coordination for maritime operations with strict scheduling constraints and multi-stakeholder notification requirements.

The Problem: Manual Calendar Management at Scale

Managing events across multiple platforms—Google Calendar, third-party booking systems, and internal dashboards—creates operational friction. Each platform required manual data entry, leading to scheduling conflicts, missed notifications, and coordination delays. For operations like ours with recurring holds, special events, and time-sensitive bookings, this manual process was unsustainable.

System Architecture Overview

The solution consists of three interconnected layers:

  • Data Source Layer: Google Calendar API as the source of truth
  • Synchronization Layer: Google Apps Script running on a scheduled trigger
  • Integration Layer: AWS Lambda exposing REST endpoints for dashboard and external systems

This three-layer approach decouples calendar logic from downstream consumers while maintaining a single authoritative source.

Technical Implementation: CalendarSync.gs

The core synchronization logic lives in /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs.

The GAS script performs these functions:

  • Polls the Google Calendar API on a scheduled interval (currently configurable, typically 5-15 minutes)
  • Extracts event metadata: title, start/end times, description, attendees
  • Formats event data into a standardized JSON payload
  • Sends events to a Lambda API endpoint via email (SES integration) or direct HTTP POST
  • Logs all sync operations for audit trails

The script uses Google's built-in CalendarApp API to retrieve events from the primary calendar:

const calendar = CalendarApp.getDefaultCalendar();
const events = calendar.getEvents(startDate, endDate);
const eventData = events.map(event => ({
  id: event.getId(),
  title: event.getTitle(),
  start: event.getStartTime(),
  end: event.getEndTime(),
  description: event.getDescription()
}));

Events are then formatted and transmitted to the Lambda endpoint specified in environment variables. The script includes retry logic with exponential backoff to handle transient network failures.

Lambda Integration: Calendar Event API Endpoint

The Lambda function exposes a REST API through API Gateway v2 with two primary actions:

  • add-calendar-event: Accepts event data and writes to Google Calendar
  • list-calendar-events: Returns current calendar state for dashboard consumption

Authentication is token-based, with the dashboard token stored in repos.env and passed in request headers. The Lambda function validates the token before processing any request.

Example invocation for adding an event:

curl -X POST https://[api-gateway-endpoint]/calendar \
  -H "Authorization: Bearer [dashboard-token]" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "add-calendar-event",
    "title": "Sea Scout Wednesday Hold",
    "start": "2024-04-28T14:00:00Z",
    "end": "2024-04-28T16:00:00Z",
    "description": "Recurring weekly hold for Scout activities"
  }'

The Lambda function integrates with the Google Calendar API using OAuth credentials stored securely in Lambda environment variables. This decouples credential management from the GAS scripts and centralizes API interactions.

Event Synchronization: Practical Example

During this session, we synchronized recurring Sea Scout holds across seven Wednesday dates. Rather than manually creating calendar entries, the process involved:

  1. Defining event metadata (title, time window, recurrence pattern) in a dashboard card
  2. Lambda accepting the batch request with seven event instances
  3. For each event, Lambda calls Google Calendar API to create the entry
  4. CalendarSync.gs detects the new events on its next poll cycle
  5. Dashboard automatically refreshes to reflect calendar state

This eliminates manual calendar management while maintaining an audit trail of all changes.

Key Infrastructure Decisions

Why Google Calendar as the Source of Truth?

Google Calendar provides:

  • Robust API with strong consistency guarantees
  • Built-in conflict detection and color-coding
  • Mobile app synchronization for field teams
  • Sharing and permission controls via Google Workspace

Storing events in Calendar rather than a custom database reduces operational overhead and leverages existing infrastructure.

Why Google Apps Script as the Sync Engine?

GAS provides native access to Google Calendar without additional authentication layers. The script runs in Google's infrastructure, avoiding external polling dependencies. Scheduled triggers ensure consistent polling without maintaining a separate cron service.

Why Lambda for the REST API?

Lambda provides:

  • Serverless scaling for bursty event creation patterns
  • Native integration with API Gateway for REST endpoints
  • CloudWatch Logs for debugging and monitoring
  • Fine-grained IAM roles for credential isolation

Centralizing API logic in Lambda reduces code duplication across dashboard and third-party integrations.

Deployment and Configuration

The system configuration spans multiple files:

  • CalendarSync.gs: Deployed to Google Apps Script project via .clasp.json configuration
  • Lambda function code: Deployed via AWS SAM or Terraform (Infrastructure as Code)
  • Environment variables: Stored in repos.env, including API Gateway endpoint and dashboard token
  • API Gateway configuration: Routes defined in API Gateway v2 console or CloudFormation

The .clasp.json file in the GAS project directory maps the local repository to the Google Apps Script project ID, enabling version control and collaborative development:

{
  "scriptId": "[GAS-PROJECT-ID]",
  "rootDir": "./apps-script-replacement"
}

Monitoring and Observability

The system logs all operations to CloudWatch for Lambda and Google's Apps Script execution logs:

  • Lambda logs: /aws/lambda/[function-name] in CloudWatch
  • GAS logs: Viewable in Apps Script editor's Execution log
  • API calls: Logged in API Gateway access logs with request/response metadata

Dashboard integration includes real-time event counters and last-sync timestamps, providing operators visibility into system health.

Next Steps: Platform Integration

Future work includes:

  • Bidirectional sync with third-party booking platforms (GetMyBoat, Boatsetter) via their iCal endpoints
  • Slack notifications for high-priority events