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 technical challenges: setting up a receipt management interface for a trailer rental business and debugging/deploying an automated port sheet generation system that integrates Google Calendar, Google Sheets, and AWS Lambda. Here's a detailed breakdown of the infrastructure changes, architectural decisions, and lessons learned.

Part 1: Receipt Management System Deployment for quickdumpnow.com

Problem: The https://quickdumpnow.com/books endpoint was returning the homepage landing page instead of a dedicated receipts management interface, despite the HTML file existing locally.

Root Cause: The CloudFront distribution had a custom error response configuration that redirects all 404s back to the homepage. Since the S3 object for the books page didn't exist or wasn't properly keyed, CloudFront returned a 404, which was then intercepted and redirected.

Technical Implementation:

  • File Path: /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html — the source file for the receipt management page
  • S3 Deployment: Uploaded to two keys in the quickdumpnow S3 bucket:
    • books/index.html — for direct access
    • books — for pretty URL routing (without the filename)
  • robots.txt Update: Modified /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to block search engine crawling of the receipts management area (since it's likely internal-use only)
  • CloudFront Invalidation: Invalidated the following paths to clear edge cache:
    • /books
    • /books/
    • /books/index.html
    • /robots.txt

Key Decision: Dual S3 Keys for Pretty URLs

We uploaded the HTML to both books/index.html and books to ensure compatibility across browsers and clients. Some clients expect the trailing slash and index.html behavior (traditional HTTP server behavior), while others request the directory name directly. This dual-key approach is simpler than configuring S3 website hosting with complex routing rules.

Infrastructure Notes:

  • CloudFront distribution ID remains consistent; invalidations propagate to all edge locations within 30–60 seconds
  • The custom error response on the distribution is intentional for the business landing page but required careful coordination for subpaths like /books
  • robots.txt changes propagate immediately and don't require CloudFront invalidation since it's a text file, but we invalidated it anyway for safety

Part 2: Automated Port Sheet Generation System

Problem: A manual process for generating monthly port sheets (tracking boat charters and revenue) needed automation. The system required:

  • Reading Google Calendar events to extract charter data
  • Formatting data into a monthly spreadsheet (Port Log sheet in Google Sheets)
  • Handling OAuth token refresh for calendar API access
  • Sending summary emails to stakeholders

Architecture Overview:

The solution uses a multi-layer integration:

  • Google Apps Script (/Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs) — Server-side spreadsheet automation that reads and writes to the Port Log sheet
  • Python CLI Tool (/Users/cb/Documents/repos/tools/jada_port_sheet.py) — Local script that pulls calendar data, formats it, and invokes the Apps Script via the Sheets API
  • OAuth Token Management (/Users/cb/Documents/repos/tools/reauth_jada_calendar.py) — Standalone script to refresh Google Calendar OAuth tokens
  • AWS Lambda — Runs jada_port_sheet.py on a schedule, storing the refresh token as an environment variable

Detailed Implementation Steps:

1. Identifying the Correct Spreadsheet and Tab Structure

The JADA Port Log 2026 spreadsheet (in Google Drive) contains multiple tabs (sheets). Initial confusion arose from unclear tab naming and IDs. We resolved this by:

  • Reading all tabs using the Google Sheets API to identify the correct sheetId (not the spreadsheet ID, but the individual tab ID)
  • Finding the Port Log tab headers: Month, Charter Name, Captain, Vessel, Charter Type, Revenue, Notes
  • Creating an "April" tab with the month label pre-filled
  • Appending the first charter entry: Joseph Zurek, $1,845.72

2. Excel File Format Compatibility

The port sheet was originally in .xls format (Excel 97-2003). We encountered issues reading and writing it:

  • Installed openpyxl (modern Excel library) with the flag --break-system-packages
  • Used xlrd to read the original XLS structure (row heights, column widths, cell formatting)
  • Built a new XLSX file preserving the original template format and data, then uploaded it back to the same Google Drive file ID

3. OAuth Token Refresh Challenge

The critical bottleneck was calendar API token expiration. Google OAuth tokens expire after ~1 hour; Lambda needed a persistent refresh token to obtain new access tokens automatically.

Solution: Created reauth_jada_calendar.py 

# High-level flow (pseudocode)
1. Start local OAuth server on http://localhost:8765
2. User authorizes the app in browser
3. Capture the authorization code
4. Exchange code for access + refresh tokens
5. Save refresh token to file for Lambda to use

Implementation details:

  • Verified that both Drive and Calendar credential files shared the same OAuth client ID (for consistency)
  • Checked that redirect URI in the OAuth client configuration matched http://localhost:8765/callback
  • Ensured the script granted the necessary scopes: calendar.readonly, drive.readonly, spreadsheets
  • Handled port conflicts by terminating stale Python processes on port 8765 before starting the auth server

4. Lambda Integration

Once the refresh token was obtained, we pushed it to AWS Lambda as an environment variable:

aws lambda update-function-configuration \
  --function-name jada-port-sheet \
  --environment Variables={GCAL_REFRESH_TOKEN=<