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 Apps Script

This session involved two distinct engineering efforts: deploying a receipt management landing page for a trailer rental business and automating recurring port sheet generation for vessel chartering operations. Both required careful infrastructure coordination across S3, CloudFront, Google Drive, Google Sheets, Google Apps Script, and AWS Lambda.

Part 1: Receipt Management System Deployment

What Was Done

The quickdumpnow.com domain needed a functional receipts page at /books to replace a non-functional landing page. The existing site structure had a local books directory, but the page wasn't deployed and CloudFront was misconfigured, causing all requests to fall back to the homepage.

Technical Details

The deployment involved three coordinated changes:

  • S3 Object Upload: Updated /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html with corrected model IDs and pushed to the S3 bucket serving quickdumpnow.com. Uploaded to both s3://quickdumpnow.com/books/index.html and s3://quickdumpnow.com/books (bare key) to support pretty URLs without file extensions.
  • robots.txt Blocking: Modified /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to block crawler access to the /books path using Disallow: /books. This prevents the receipts directory from being indexed, which is appropriate for business-sensitive financial records.
  • CloudFront Invalidation: Issued cache invalidation for paths /books and /books/* to purge stale homepage responses. Additionally invalidated /robots.txt to ensure updated crawl rules took effect immediately.

Infrastructure Pattern: Custom Error Responses

During deployment, we discovered that the CloudFront distribution for quickdumpnow.com had a custom error response configured to redirect all 404s to the homepage. This is a common pattern for single-page applications, but it masked missing S3 objects during development. The distribution configuration included:

  • Origin pointing to the S3 bucket backend
  • Error response rule: HTTP 404 → redirect to homepage index
  • Behavior patterns for root and wildcard paths

This pattern works well for marketing sites (users never see 404s) but requires explicit CloudFront invalidation when deploying new static content. Simply uploading to S3 isn't sufficient; the CDN cache must be cleared for new objects to become visible.

Deployment Result

After invalidation, https://quickdumpnow.com/books became accessible within 30–60 seconds (CloudFront propagation time). The page is now live and ready for receipt uploads. Future improvements should include:

  • Server-side form handling (Lambda + API Gateway) for receipt upload
  • DynamoDB table for receipt metadata (date, amount, category)
  • Signed S3 URLs for upload and retrieval workflows
  • CloudWatch logging to track access patterns

Part 2: Automating Port Sheet Generation

Background Context

JADA (a sailing business) maintains a "Port Log" spreadsheet tracking charter bookings and vessel movements. Previously, port sheet generation was manual. A recent charter generated $1,845.72 in revenue, requiring a new entry in the April 2026 port sheet.

Technical Architecture

The automation pipeline spans multiple Google services and custom Python scripts:

  • Google Sheets (Source): JADA Port Log 2026 spreadsheet with monthly tabs (March, April, etc.). Each tab contains structured charter data: vessel name, dates, client, revenue.
  • Google Apps Script (Transformation): /Users/cb/Documents/repos/queenofsandiego.com/PortSheetReporter.gs reads the Port Log sheet, formats data, and generates an email-ready HTML report.
  • Python Integration Layer: Two scripts handle authentication and execution:
    • /Users/cb/Documents/repos/tools/jada_port_sheet.py — Main script that reads the Port Log via Google Sheets API, processes entries, and calls PortSheetReporter.gs
    • /Users/cb/Documents/repos/tools/reauth_jada_calendar.py — OAuth 2.0 reauthentication utility to refresh Google Calendar API credentials
  • AWS Lambda: Deployed environment variable storing GCAL_REFRESH_TOKEN for server-side execution without local OAuth flows

Key Implementation Details

Credential Management: The system uses separate OAuth 2.0 client IDs for Drive and Calendar APIs. Both have identical redirect URIs but different scopes:


# Drive API scope (read/write port log spreadsheet)
https://www.googleapis.com/auth/drive

# Calendar API scope (optional, for calendar integration)
https://www.googleapis.com/auth/calendar

The refresh token is stored in Lambda environment variables (encrypted at rest via AWS KMS) rather than committed to the repository.

Excel File Handling: The port sheet template is an Excel file (.xls format) stored in Google Drive. To preserve formatting while adding new entries, the workflow:

  • Downloads the Excel file from Drive
  • Uses openpyxl library to parse and modify cell values preserving formatting
  • Reads the "Template" sheet structure to understand row/column layouts
  • Writes new entries (e.g., Zurek charter: April 2026, $1,845.72) in the correct cells
  • Uploads the modified file back to Drive with the same file ID

Installation required: pip install openpyxl --break-system-packages (necessary in some managed Python environments)

Port Management: Local development required OAuth 2.0 callback handling on port 8765. During testing, stale Python processes blocked the port. Resolution involved identifying the process ID and terminating it:


# Check what's using port 8765
lsof -i :8765

# Kill the process (replace PID with actual process ID)
kill -9 PID

Execution Flow

  1. Run jada_port_sheet.py with refresh token from Lambda environment
  2. Script authenticates to Google Sheets API, reads Port Log 2026
  3. Parses April 2026 tab, identifies existing entries and column structure
  4. Downloads Excel template from Drive
  5. Uses openpyxl to write new charter entry (Joseph Zurek, April 2026, $1,845.72) in correct row
  6. Uploads updated Excel file back to Drive
  7. Calls PortSheetReporter.gs to format as HTML email