Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Deploying a Receipt Management System for quickdumpnow.com and Automating Port Sheet Generation with Google Sheets Integration

This session involved two parallel infrastructure tasks: deploying a receipt management interface for a trailer rental business and establishing an automated port sheet generation pipeline integrated with Google Calendar and Sheets. Both required careful orchestration of static site deployment, serverless functions, and OAuth credential management.

The Receipt Management System: Static Site Deployment with CloudFront

The primary objective was to transform https://quickdumpnow.com/books from a generic landing page into a functional receipt management interface for the trailer rental business.

Problem: CloudFront 404 Behavior

The initial issue was that accessing /books returned the homepage instead of a 404. This revealed a CloudFront custom error response configuration that was redirecting all 404s to the root index. While useful for SPA routing, this prevented serving the dedicated books page.

Solution: Strategic S3 Key Deployment

Rather than modifying the CloudFront distribution config (which would break other error handling), we deployed the books page to multiple S3 keys:

  • s3://quickdumpnow-static/books/index.html — the actual HTML file
  • s3://quickdumpnow-static/books/ — bare directory key for pretty URL routing

CloudFront's origin request behavior now correctly serves the file when the S3 object exists, bypassing the custom error response.

Robots.txt Blocking

The books page was added to /robots.txt with a Disallow: /books directive to prevent indexing during development. This prevents search engines from scraping an incomplete receipt submission interface.

Cache Invalidation

After uploading, we invalidated CloudFront paths:

aws cloudfront create-invalidation \
  --distribution-id [DISTRO_ID] \
  --paths "/books" "/books/*" "/robots.txt"

The invalidation typically completes in 30–60 seconds. Monitor status with:

aws cloudfront list-invalidations --distribution-id [DISTRO_ID]

The Port Sheet Automation Pipeline

The second major task was completing an automated port sheet generation system that pulls charter data, formats it for Google Sheets, and distributes it via email. This required integrating three separate credential flows.

Architecture Overview

The pipeline consists of:

  • jada_port_sheet.py — Python script that reads Google Drive spreadsheets, formats charter data, and sends email notifications
  • reauth_jada_calendar.py — OAuth 2.0 credential refresh utility for Google Calendar API
  • PortSheetReporter.gs — Google Apps Script deployed as Cloud Function in the JADA Sailing Google Workspace
  • AWS Lambda — Environment stores the refreshed Google Calendar token for server-side execution

Credential Management: The Multi-Auth Challenge

The complexity arose from requiring access to two separate Google Workspace scopes from a non-interactive environment:

  • Google Drive API — read the JADA Port Log 2026 spreadsheet and upload monthly updates
  • Google Calendar API — validate charter dates and user availability

These required separate OAuth 2.0 clients with different redirect URIs and token refresh flows.

Token Refresh Implementation

The reauth_jada_calendar.py script implements the OAuth 2.0 refresh grant:

python reauth_jada_calendar.py \
  --client-id [CLIENT_ID] \
  --client-secret [CLIENT_SECRET] \
  --refresh-token [EXISTING_REFRESH_TOKEN]

This generates a new access token without requiring interactive browser authentication. The script:

  1. Reads existing refresh token from the credential file
  2. Constructs a POST request to https://oauth2.googleapis.com/token
  3. Validates the response status and extracts the new access token
  4. Writes the updated token to disk for Lambda environment injection

Why this approach: AWS Lambda environment variables cannot trigger OAuth flows. By refreshing the token locally before deployment, we ensure the Lambda function has a valid credential without adding authentication complexity to the serverless runtime.

Port Sheet Data Format

The JADA Port Log 2026 spreadsheet in Google Drive uses a specific structure that had to be reverse-engineered from an existing Excel template. Each month tab contains:

  • Header row with "Month" and the month/year
  • Data rows with columns: Date, Captain, Vessel, Destination, Duration, Passengers, Comments, Cost, Revenue
  • Preserved formatting, column widths, and row heights from the original template

We used openpyxl to preserve this structure when adding new entries:

pip install openpyxl

The script downloads the current spreadsheet, adds the new April 2026 tab and the charter entry (Zurek, $1,845.72), and uploads it back using the same Drive file ID.

Email Distribution

After updating the spreadsheet, the port sheet is sent to stakeholders (Edwin, Maria, Caro) via Gmail API. The email includes:

  • The month being reported
  • Summary statistics from new entries
  • A link to the updated Drive spreadsheet
  • Timestamp and sender attribution

Infrastructure and Deployment Details

AWS Lambda Environment

The Lambda function environment stores:

  • GCAL_REFRESH_TOKEN — the refreshed Google Calendar API token
  • DRIVE_CREDENTIALS — Google Drive API OAuth credentials as JSON
  • Email configuration (sender address, recipient list)

Push the new token to Lambda:

aws lambda update-function-configuration \
  --function-name [FUNCTION_NAME] \
  --environment Variables={GCAL_REFRESH_TOKEN=[TOKEN]}

Port Discovery and Process Management

During OAuth local testing, the callback server binds to port 8765. If a previous session hangs, the port remains locked. Resolution:

lsof -i :8765
kill -9 [PID]

This prevents "Address already in use" errors when restarting authentication flows.

Key Technical Decisions

  • S3 dual-key deployment — Uploading to both books/index.html and books/ ensures CloudFront serves the correct object regardless of URL format, avoiding client-side redirects.
  • Local token refresh before Lambda deployment — Avoids embedding interactive OAuth logic in the serverless function, reducing complexity and improving cold-start performance.
  • Openpyxl for format preservation