Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Portal for QuickDumpNow and Automating Port Sheet Generation with Google Apps Script

This session tackled two distinct infrastructure challenges: deploying a receipts management portal for a trailer rental business, and building end-to-end automation for charter port sheet generation. Both required coordination across multiple services—S3, CloudFront, Google Drive, Google Apps Script, and AWS Lambda.

The QuickDumpNow Books Portal Problem

The quickdumpnow.com domain needed a new receipts page at /books, but the deployment had stalled with CloudFront returning the homepage instead of the intended receipt portal. The root cause: a CloudFront distribution configured with a custom error response that redirects all 404s to the homepage, masking the real issue that the S3 objects didn't exist yet.

S3 and CloudFront Deployment

The solution required deploying the books page to the S3 bucket backing quickdumpnow.com with proper key structure. Web servers treat trailing slashes and bare keys differently, so we deployed to both:

  • /books/index.html — The actual HTML file in S3
  • /books (bare key) — Allows CloudFront to serve the pretty URL without exposing the filename

This dual-key pattern is essential for clean URLs. Without the bare /books key, CloudFront would either return 404 (triggering the error response) or require explicitly requesting /books/index.html.

We also updated robots.txt to block the /books path, ensuring search engines don't index internal receipt management pages. The file was edited locally and deployed alongside the books page:

User-agent: *
Disallow: /books/
Disallow: /admin/

CloudFront Invalidation

After uploading the S3 objects, we invalidated CloudFront cache for two paths:

  • /books
  • /books/*
  • /robots.txt

CloudFront invalidations are asynchronous and typically propagate globally within 30–60 seconds. We avoided the /* wildcard pattern, which would invalidate the entire distribution and incur higher costs.

The Port Sheet Automation Challenge

The second part of this session focused on automating charter port sheet generation and distribution—a workflow that was previously manual and error-prone.

Understanding the Data Pipeline

The port sheet system integrates multiple Google services:

  • Google Drive: Stores JADA Port Log 2026, an Excel file containing monthly tabs for charter entries
  • Google Apps Script: PortSheetReporter.gs in the queenofsandiego.com Apps Script project handles authentication, reads the port log, and sends email reports
  • Google Calendar API: Provides charter scheduling data
  • AWS Lambda: Hosts the Python orchestration script that triggers port sheet generation

The workflow: Python script → Google Calendar API → Apps Script → Port Log spreadsheet → Email to stakeholders (Edwin, Maria, Caro).

Debugging the Authentication Chain

The initial challenge was token refresh failures. We discovered the calendar token was stale and couldn't be refreshed with the stored refresh_token. The solution required understanding how credentials are scoped and stored:

  • Google Drive API requires https://www.googleapis.com/auth/drive scope
  • Google Calendar API requires https://www.googleapis.com/auth/calendar scope
  • Separate credential files were being used for each service, each with their own refresh_token

We inspected both credential files to verify client IDs matched between the Drive and Calendar credentials, then created a new reauthentication script to refresh the calendar token with proper OAuth flow.

The Reauthentication Script

Created /Users/cb/Documents/repos/tools/reauth_jada_calendar.py to handle OAuth token refresh locally. The script:

  • Reads the existing client secret from the credentials file
  • Uses Flask to run a local OAuth callback server on port 8765
  • Generates a fresh authorization URL for user consent
  • Captures the authorization code and exchanges it for new tokens
  • Saves the new refresh_token to disk
  • Pushes the refreshed token to AWS Lambda via environment variables

Port 8765 was chosen to avoid conflicts with commonly-used development ports. During execution, a stale Python process was holding the port, requiring cleanup before the script could bind successfully.

Port Log Data Structure

The JADA Port Log 2026 is an Excel file with monthly tabs. Each month (e.g., "April") contains rows with this structure:

Column A: Month
Column B: Date
Column C: Captain
Column D: Boat
Column E: Destination
Column F: Gross Revenue
Column G: Notes

The existing March tab served as the template. We preserved all existing data and created an "April" tab, adding the charter entry:

  • Month: April
  • Date: [Charter date]
  • Captain: Joseph Zurek
  • Boat: [Charter vessel]
  • Destination: Port of San Diego
  • Gross Revenue: $1845.72
  • Notes: [Any charter-specific notes]

We used the openpyxl library to programmatically read the original Excel structure, preserve formatting (row heights, column widths), and write the updated port log back to Drive.

Apps Script and Email Integration

The PortSheetReporter.gs Apps Script file reads the port log from Drive, formats monthly summaries, and sends email reports. The script:

  • Authenticates using the stored Drive API credentials
  • Fetches the port log file from Drive by ID
  • Parses the requested month's tab (e.g., "April")
  • Generates a formatted email with all entries and totals
  • Sends via Gmail API to Edwin, Maria, and Caro

Infrastructure and Deployment

The port sheet automation lives across multiple environments:

  • Local development: jada_port_sheet.py and reauth_jada_calendar.py in the tools directory
  • AWS Lambda: Orchestration script with environment variable GCAL_REFRESH_TOKEN for token persistence
  • Google Apps Script: Cloud project associated with queenofsandiego.com
  • Google Drive: Shared Drive folder containing the port log file

Deployment of the refreshed calendar token to Lambda was done via environment