Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Upload Portal and Automating Port Sheet Generation with Google Sheets + Lambda

This session involved two parallel infrastructure projects: standing up a receipt management system for a trailer rental business, and fully automating the monthly port sheet workflow that generates charter revenue reports. Both required coordinating static site hosting, serverless execution, OAuth token management, and email delivery.

Project 1: Receipt Portal Deployment to quickdumpnow.com/books

The trailer rental business needed a receipt upload destination at https://quickdumpnow.com/books. The site structure already existed locally but hadn't been deployed to production.

Static Site Structure

  • Local path: /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html
  • S3 bucket: quickdumpnow.com (CloudFront-backed)
  • CloudFront distribution: points to the S3 bucket origin
  • Route53: quickdumpnow.com ALIAS record points to CloudFront distribution

Deployment Process

The challenge was that CloudFront had a custom error response rule redirecting all 404s to the homepage. This made sense for SPA routing but broke attempts to access /books as a directory-style path.

Solution: Upload the books page under two S3 keys to handle both URL patterns:


# Upload to explicit path for /books/ requests
aws s3 cp books/index.html s3://quickdumpnow.com/books/index.html

# Upload to bare /books key for pretty URL handling
aws s3 cp books/index.html s3://quickdumpnow.com/books

Then invalidate CloudFront to clear edge cache:


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

robots.txt Update

Updated /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt to block indexing of the receipts area, keeping it private to the business:


User-agent: *
Disallow: /books

This prevents search engines from discovering or caching receipt metadata while keeping the site publicly crawlable otherwise.

Project 2: Automated Port Sheet Generation Pipeline

The port sheet workflow automates monthly charter revenue reporting. Previously this was manual; now it's fully serverless with OAuth-based Google Sheets integration and Lambda execution.

Architecture Overview

  • Data source: JADA Port Log 2026 spreadsheet (Google Sheets)
  • Orchestration: /Users/cb/Documents/repos/tools/jada_port_sheet.py (Python, runs locally or in Lambda)
  • Authentication: OAuth 2.0 refresh token stored in Lambda environment variables
  • New auth tool: /Users/cb/Documents/repos/tools/reauth_jada_calendar.py (handles token refresh)
  • Apps Script: /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs (Google Apps Script bound to the sheet)
  • Delivery: Email via Gmail API to Maria, Edwin, and Carolina

Google Sheets Schema

The JADA Port Log 2026 spreadsheet has multiple tabs:

  • Template tab: Row formats and column structure (Month, Date, Vessel, Captain, etc.)
  • Monthly tabs: One per month (March 2026, April 2026, etc.) containing actual entries
  • Data format: Specific column positions with row heights and formatting preserved via openpyxl

The Python script reads from Google Sheets API using the spreadsheet ID, extracts all rows from the current month tab, and formats them for email delivery.

OAuth Token Management Challenge

The original calendar credentials had limited scope. Initial attempts to refresh the token failed because:

  • Problem: The Drive API client had Google Cloud console credentials (service account-like), but calendar API required user OAuth
  • Root cause: Two different OAuth applications with different credentials
  • Solution: Created reauth_jada_calendar.py to perform full OAuth flow on port 8765, obtaining a fresh refresh token with proper scopes

The reauth script:


# Binds to local port 8765 for OAuth callback
# Opens browser for user consent
# Extracts authorization code from callback
# Exchanges code for refresh + access tokens
# Stores GCAL_REFRESH_TOKEN for Lambda environment

Key decision: Store only the refresh token in Lambda, never the full credentials file. The script regenerates access tokens on each port sheet run.

Port Sheet Data Entry

Added a charter entry for Joseph Zurek with payout $1845.72:

  • Sheet ID: Found the correct sheetId integer for the April tab via API introspection
  • Row format: Date, Vessel Name, Captain Name, Payout Amount
  • Preservation: Used openpyxl to maintain row heights, column widths, and formatting from the template
  • Append method: Google Sheets API append_values with raw values, allowing the sheet's formatting rules to apply

Lambda Deployment

Updated the Lambda function with new environment variable:


GCAL_REFRESH_TOKEN=[refreshed_token_value]

This allows the port sheet script to obtain short-lived access tokens without hardcoding credentials:


def get_calendar_access_token(refresh_token):
    """Exchange refresh token for access token"""
    # POST to https://oauth2.googleapis.com/token
    # Returns access_token valid for 1 hour

Email Delivery

The port sheet script reads all entries from the current month tab, formats them into a markdown/HTML email, and sends via Gmail API to:

  • Maria (operations lead)
  • Edwin (accounting)
  • Carolina (admin)

Email includes:

  • Month and year
  • Formatted charter table (Vessel, Captain, Payout)
  • Total revenue sum
  • Links to the full port log sheet for live data

Key Decisions

  • Dual S3 key upload: Handles both /books and /books/ URL patterns without CloudFront rewrites
  • Refresh token only in Lambda: Reduces secret surface area and allows token rotation