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

This session involved two parallel infrastructure projects: standing up a receipt management endpoint for a trailer rental business, and debugging/completing an automated port sheet generation pipeline that integrates Google Drive, Google Sheets, Google Calendar, and AWS Lambda.

Part 1: QuickDumpNow Books Endpoint Deployment

Problem: The domain https://quickdumpnow.com/books was returning the homepage instead of a dedicated receipt upload interface.

Root Cause: The S3 bucket had a custom CloudFront error response configuration (a common pattern for single-page apps) that redirects all 404s to the homepage. The books/index.html file existed locally but hadn't been deployed to S3.

Deployment Process:

Located the static site files at /Users/cb/Documents/repos/sites/quickdumpnow.com/
Updated books/index.html with the correct model ID reference (the original had a placeholder)
Updated robots.txt to block the /books path from indexing (since this is an internal receipt upload tool, not public-facing content)
Uploaded both books/index.html and a bare books key to the S3 bucket (the bare key ensures CloudFront serves the index for pretty URLs)
Invalidated the CloudFront distribution cache for both /books and /books/* paths

Why Two S3 Keys? CloudFront doesn't automatically append index.html the way a traditional web server does. By uploading to both books/index.html (for explicit requests) and a bare books key (for directory-style requests), we ensure the page serves correctly regardless of whether a user requests /books or /books/. This is a common pattern when using S3 + CloudFront for static sites.

Result: Within 30–60 seconds of the CloudFront invalidation, https://quickdumpnow.com/books began serving the dedicated receipt page. The robots.txt exclusion ensures search engines don't index this internal tool.

Part 2: Automated Port Sheet Generation Pipeline

Context: A sailing business (JADA) needed an automated monthly port sheet that compiles charter data, formats it into an Excel workbook, and emails it to stakeholders. The system integrates:

Google Sheets (Port Log and Charter data)
Google Drive (source and destination for workbooks)
Google Calendar (to pull event details)
AWS Lambda (to run the monthly job)
Email delivery (via Google Drive/Sheets APIs)

Infrastructure and File Structure

The port sheet system is built around two Python scripts:

/Users/cb/Documents/repos/tools/jada_port_sheet.py — the main orchestrator that reads from Google Sheets, queries Google Calendar, formats data, and generates the Excel workbook
/Users/cb/Documents/repos/tools/reauth_jada_calendar.py — a credential refresh utility for maintaining valid OAuth tokens

Why Two Scripts? Google OAuth tokens expire. The calendar token (used for pulling event details) has a separate lifecycle from the Drive token. By splitting the reauth logic into its own script, we can run it as a separate Lambda function or cron job to refresh credentials independently without disrupting the main port sheet generation.

Authentication and Token Management

The system uses two separate Google Cloud service accounts / OAuth client IDs:

Drive Client: Used to read/write Excel files in Google Drive
Calendar Client: Used to query Google Calendar for charter event details

Credentials are stored as:

Local development: ~/.secrets/ directory (git-ignored)
Production (Lambda): Environment variable GCAL_REFRESH_TOKEN injected via Lambda configuration

Token Refresh Flow: The reauth_jada_calendar.py script exchanges a refresh token for a short-lived access token. This token is then passed to jada_port_sheet.py`, which uses it to make calendar API calls. The refresh token itself is never transmitted; only the short-lived access token leaves the Lambda environment.



Verification Commands:

# Check if the calendar token is valid
python3 jada_port_sheet.py --dry-run

# Refresh calendar credentials if expired
python3 reauth_jada_calendar.py

# Verify the new token was written
cat ~/.secrets/gcal_token.json | grep -i access_token

Port Sheet Data Format and Google Sheets Integration

The port sheet workbook structure mirrors an existing Excel template with specific formatting requirements:


  Template Tab: Static reference sheet with column headers and formatting
  Month Tabs: One tab per month (e.g., "April 2026") containing individual charter entries
  Each entry captures: charter name, date, vessel, crew, port details, revenue


The source data comes from two Google Sheets tabs in the JADA Port Log 2026 sheet:


  Port Log: Master list of all ports and charter details
  Charter Data: Detailed information about each charter event


Why Not Store Everything in Sheets? Google Sheets is excellent for real-time data entry and collaboration, but Excel workbooks provide better formatting control, are easier to share with non-technical stakeholders, and can be archived for audit purposes. The automated pipeline reads from Sheets (the source of truth) and generates Excel (the distribution format).

Data Transformation Example

A single charter entry from the previous session:

Charter: Joseph Zurek
Date: 2026-04-15
Vessel: Primary Boat
Amount: $1,845.72


The script reads this from Google Sheets, queries Google Calendar for any related events, and formats it into the Excel "April 2026" tab using the template's cell styles and formulas.

Lambda Deployment and AWS Credentials

The port sheet generation runs in AWS Lambda with:


  Environment variable: GCAL_REFRESH_TOKEN (the Google Calendar OAuth refresh token)
  IAM role with permissions to write CloudWatch logs
  Python runtime with dependencies: openpyxl (Excel file handling), google-auth, google-api-python-client


Token Update Process: When the calendar refresh token is rotated (either via local reauth or a manual refresh), the new token is pushed to Lambda via the AWS CLI:

aws lambda update-function-configuration \
  --function-name