Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

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

This session focused on two parallel objectives: standing up a receipt management interface for a trailer rental business and automating monthly port sheet generation that integrates with Google Calendar and email delivery. Both projects required coordinating static site deployment, serverless authentication flows, and Google Workspace API token management.

Part 1: Deploying the Books Receipt Page to quickdumpnow.com

Problem: The quickdumpnow.com domain had a landing page in place, but the `/books` path—intended as a receipt upload interface for the trailer rental business—was returning the homepage instead of a dedicated page. This was due to missing S3 object deployment and CloudFront error response configuration.

S3 Deployment Strategy

The site infrastructure uses S3 as the origin with CloudFront distribution caching. The local project structure at `/Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html` needed to be deployed as two S3 keys to support both pretty URLs and direct file access:

  • s3://quickdumpnow-web/books/ – For pretty URL requests to `/books/`
  • s3://quickdumpnow-web/books – For requests to `/books` without trailing slash

Uploading to both keys ensures CloudFront resolves `/books` requests correctly regardless of URI normalization. The robots.txt was updated to block this path from search indexing, appropriate for a business-internal receipt management interface.

CloudFront was then invalidated for paths /books, /books/, and /robots.txt to purge stale cache entries. Invalidation typically completes within 30–60 seconds depending on edge location distribution.

CloudFront Custom Error Response Issue

Initial testing showed `/books` still returning the homepage. Investigation revealed the CloudFront distribution had a custom error response rule: HTTP 404 errors were being redirected to the homepage (likely via error response page configuration). This is a common pattern for single-page applications, but it masks S3 object deployment issues.

The fix involved:

  • Verifying S3 object existence via AWS CLI
  • Confirming CloudFront origin configuration pointed to the correct S3 bucket
  • Invalidating the entire path prefix to bypass edge cache
  • Testing after cache invalidation completed

The custom error response configuration itself is correct for the site's architecture—it prevents broken link errors from exposing S3 not-found pages to users. The deployment process simply needed to account for it.

Part 2: Automated Port Sheet Generation Pipeline

Context: JADA (a sailing charter business) maintains a "Port Log" spreadsheet tracking charter entries, destinations, and revenue. Port sheets needed to be generated monthly, formatted consistently, and emailed to stakeholders (Edwin, Maria, and Caro). The process required integrating Google Sheets, Google Calendar, and email APIs.

Data Integration Challenges

The port sheet workflow lives across multiple Google Workspace resources:

  • JADA Port Log 2026 – Google Sheet with monthly tabs (March, April, etc.) containing charter entries
  • PortSheetReporter.gs – Google Apps Script file in `/Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs` (Apps Script is tied to a specific Google Sheet)
  • jada_port_sheet.py – Python script orchestrating the multi-step process via Google APIs
  • Google Calendar – Stores authentication tokens for downstream integrations

The challenge: Google Sheets and Calendar APIs use OAuth 2.0 with separate credential sets and token files. The Python script needed to manage both authentication flows independently.

Authentication Token Management

Two credential files exist in the project:

  • drive_credentials.json – OAuth client for Google Drive/Sheets API
  • calendar_credentials.json – OAuth client for Google Calendar API

Both are "Web application" type OAuth clients with matching redirect URIs (typically http://localhost:8080 or similar for local development).

Token files were stored separately:

  • JADA_DRIVE_TOKEN – Cached drive API token (refreshed as needed)
  • GCAL_REFRESH_TOKEN – Calendar refresh token, injected into AWS Lambda environment for production automation

jada_port_sheet.py reads both tokens and refreshes them if expired before making API calls. The calendar token lives in Lambda as an environment variable for automated scheduled runs.

Port Sheet Data Format and Excel Integration

The original port sheet was an XLS file with a specific structure:

  • Template tab – Static formatting, headers, column widths, row heights
  • Monthly tabs – One per month (March, April, etc.) with charter entries in rows
  • Entry format – Columns for date, destination, charter details, revenue, notes

Python's openpyxl library was used to read the existing XLS structure, preserve Template formatting, and write new monthly tabs with current entries. Installation required the --break-system-packages flag in the development environment.

A new entry for Joseph Zurek's charter (revenue: $1,845.72) was appended to the April 2026 tab, maintaining the original row/column formatting and formulas.

OAuth 2.0 Reauthentication Flow

A new script, reauth_jada_calendar.py, was created to handle calendar token refresh outside the main port sheet workflow. This is necessary when:

  • Calendar tokens expire and need manual refresh
  • Lambda environment variables need updating with fresh tokens
  • Local development requires interactive OAuth flow

The script:

  • Starts a local HTTP server on port 8765 to receive OAuth redirect
  • Opens the Google OAuth 2.0 authorization URL in the default browser
  • Exchanges the authorization code for a refresh token
  • Saves the refresh token to a file for later use
  • Displays the token value for environment variable injection

Port 8765 was chosen to avoid conflicts with common development ports (8000, 8080, 8888). During testing, a stale Python process was holding the port; it was killed to free the port for the new reauthentication flow.

Production Deployment via AWS Lambda

The final refresh token from the reauthentication flow was injected into the Lambda function environment as GCAL_REFRESH_TOKEN. This allows the Lambda function to:

  • Run on a schedule (e.g., first of each month)
  • Refresh the calendar token automatically
  • Execute the port sheet generation workflow
  • Send formatted port sheets via email to stakeholders

No interactive OAuth is needed in production since the refresh token persists indefin