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

Overview

This post covers two distinct engineering efforts completed during a single development session: (1) deploying a receipt management interface for a trailer rental business at quickdumpnow.com/books, and (2) building an automated port sheet generation system integrated with Google Calendar and Google Sheets for JADA sailing charters.

Part 1: quickdumpnow.com Receipt Management Deployment

What Was Done

The quickdumpnow.com domain previously served only a static landing page. We needed to add a receipt management interface at the /books path. The challenge: CloudFront was configured with a custom 404 error response that redirected all missing objects back to the homepage, masking deployment issues.

Technical Details

We modified three files in the local repository:

• /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html — The receipt management UI component
• /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt — Updated to block indexing of the /books path for privacy

The deployment pipeline involved:

# Identify the S3 bucket and CloudFront distribution
Find quickdumpnow site files
List quickdumpnow site directory

# Deploy assets
Upload books page and updated robots.txt to S3

# Invalidate CDN cache
Invalidate CloudFront for books paths and robots.txt

A critical discovery emerged during testing: the CloudFront distribution returned the homepage for requests to /books instead of serving the new page. Investigation revealed the distribution had a blanket 404 error response routing all missing objects to the origin's root. This is a common pattern for single-page application (SPA) deployments, but it masked our deployment until we uploaded the object with the correct S3 key.

Infrastructure Decisions

S3 Object Keys: We uploaded the books page to two S3 keys: books/index.html (for explicit requests with trailing slash) and bare books (for pretty URLs without file extensions). This dual-key approach ensures CloudFront serves the page regardless of whether the client requests /books or /books/.

robots.txt Modifications: Added rules to prevent search engine crawling of the receipt section, protecting user financial data from indexing.

CloudFront Invalidation: We invalidated both /books* and /robots.txt paths to ensure the CDN edge caches were refreshed. Propagation completes in 30–60 seconds across CloudFront's global edge network.

Status

https://quickdumpnow.com/books is now live and ready for receipt uploads. The next phase requires backend receipt storage logic, likely involving S3 presigned URLs for direct browser-to-S3 uploads with server-side validation.

Part 2: Automated Port Sheet Generation for JADA Sailing

What Was Done

Built an end-to-end automated system to generate, format, and email port sheets (financial summaries of sailing charters) directly from Google Calendar and Google Sheets, eliminating manual spreadsheet management.

Technical Architecture

The system consists of four components:

• Google Apps Script (GAS): /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs — Reads calendar events and sheet data, generates formatted port sheets
• Python Port Sheet Module: /Users/cb/Documents/repos/tools/jada_port_sheet.py — Core business logic for parsing calendar data and generating XLSX with preserved formatting
• Calendar Reauthentication Script: /Users/cb/Documents/repos/tools/reauth_jada_calendar.py — Handles OAuth2 token refresh cycles for Google Calendar API
• Frontend Integration: /Users/cb/Documents/repos/sites/queenofsandiego.com/events.html — Updated to trigger port sheet generation

Google Sheets Integration: The Port Log

The JADA Port Log 2026 Google Sheet contains multiple tabs (one per month). The structure:

Headers: Date | Boat/Charter | People | Amount | Notes
Example: 2026-04-15 | Joseph Zurek Charter | 6 | $1845.72 | Afternoon sail

We read the sheet programmatically using the Google Sheets API v4:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://www.googleapis.com/auth/spreadsheets']
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)

# Open the Port Log sheet
sheet = client.open_by_key('SHEET_ID_HERE')
april_tab = sheet.worksheet('April')

# Append Zurek charter entry
april_tab.append_row(['2026-04-15', 'Joseph Zurek Charter', '6', '$1845.72', 'Afternoon sail'])

Calendar OAuth2 Token Management

The Google Calendar API requires OAuth2 tokens that expire every hour. Rather than storing service account credentials (which have different permissions), we use a user OAuth2 flow with refresh tokens stored securely. The reauth_jada_calendar.py script handles token lifecycle:

# Initial OAuth flow (runs on port 8765)
# User authenticates, server receives authorization code
# Exchange code for access_token and refresh_token

# Subsequent calls: use refresh_token to get new access_token
POST https://oauth2.googleapis.com/token \
  -d "client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET&refresh_token=REFRESH_TOKEN&grant_type=refresh_token"

# Token is then pushed to AWS Lambda environment variables for port sheet execution

Why OAuth2 instead of service accounts? Service accounts have limited Google Calendar access unless the domain admin grants domain-wide delegation. User OAuth2 allows direct calendar access without administrative setup, simplifying deployment.

Excel Format Preservation

A key challenge: the original port sheet was an XLS file with specific column widths, row heights, and formatting. Python's openpyxl library allowed us to read the original XLS structure and rebuild it programmatically:

pip install openpyxl
python3 -c "from openpyxl import load_workbook; wb = load_workbook('port_sheet_template.xlsx'); print(wb.sheetnames)"

The jada_port_sheet.py` script reads the template file, preserves all formatting (column widths, fonts, borders), and writes new month tabs with charter data while maintaining visual consistency.

# Inside jada_port_sheet.py email function
from google.oauth