Deploying a Receipt Upload Portal for quickdumpnow.com 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 completing automation for a port sheet generation pipeline that integrates Google Drive, Google Sheets, Google Calendar, and AWS Lambda. Both required careful handling of static site deployment, OAuth token refresh, and cross-service API orchestration.

Part 1: Receipt Portal Deployment to quickdumpnow.com

Problem: The https://quickdumpnow.com/books endpoint was returning the homepage instead of a dedicated receipt upload interface, blocking the business from collecting trailer rental receipts.

Root Cause: The CloudFront distribution had a custom error response redirecting all 404s to the homepage. The /books/index.html file existed locally but was never deployed to S3, and the S3 object keys weren't configured to support pretty URLs.

Deployment Strategy:

• Fixed the S3 object key model: Rather than relying on a single books/index.html key, we uploaded to both:
  • s3://quickdumpnow.com/books/index.html (explicit path)
  • s3://quickdumpnow.com/books/ (directory-style key for pretty URL routing)
• Updated robots.txt: Added Disallow: /books to prevent search engines from indexing the receipt form during development.
• Invalidated CloudFront: Issued cache invalidations for /books and /books/* patterns across the distribution to purge the 404 redirect responses.

Key Infrastructure:

• S3 Bucket: quickdumpnow.com (regional bucket, static website hosting)
• CloudFront Distribution: Configured with custom error responses; the 404 error response rule was directing all missing objects to the homepage
• Route53: quickdumpnow.com A-record alias pointing to CloudFront distribution

The deployment was verified by checking CloudFront distribution config (DistributionConfig.DefaultRootObject, CustomErrorResponses) to understand the 404 behavior before making changes.

Part 2: Port Sheet Automation Pipeline

Problem: Manual port sheet generation for a sailing charter business required multiple steps: reading charter data from a Google Sheet, generating a formatted port sheet file, and emailing it to stakeholders. The process was ad-hoc and error-prone.

Architecture Overview:

The solution integrates four cloud services into a cohesive workflow:

• Google Drive: Stores the port sheet template (Excel file with multiple sheets)
• Google Sheets: Maintains the JADA Port Log 2026 spreadsheet with tabs for each month
• Google Calendar: Provides event data (charters, crew availability)
• AWS Lambda: Orchestrates the entire workflow via Python

Data Pipeline and File Handling

Port Log Sheet Structure: The JADA Port Log 2026 Google Sheet contains multiple tabs (one per month). Each tab has columns for Date, Vessel, Captain, Crew, and Revenue. A new entry for the April 2026 charter required:

• Reading the existing Port Log sheet structure via Google Sheets API
• Creating an April tab if it didn't exist
• Formatting and appending the new charter entry: Joseph Zurek charter, $1,845.72 revenue

Excel Template Handling: The port sheet template is an Excel file (.xls) stored in Google Drive. The process required:

• Downloading the file from Drive as binary data
• Reading its structure using xlrd (legacy Excel reader) to understand row heights, column widths, and existing data formats
• Converting to .xlsx format using openpyxl for programmatic manipulation
• Preserving the original formatting (row heights, column widths, styles) when adding new month tabs
• Uploading the updated file back to the same Drive file ID

Code Modifications: Two main Python scripts were created/updated:

• /Users/cb/Documents/repos/tools/jada_port_sheet.py
  • Core logic for reading the Port Log sheet, formatting entries, and sending emails
  • Updated multiple times to handle correct sheet IDs and row formatting
  • Final version: reads charter data, builds the port sheet, sends to stakeholders (Edwin, Maria, Caro)
• /Users/cb/Documents/repos/tools/reauth_jada_calendar.py
  • OAuth 2.0 refresh token handler for Google Calendar API
  • Newly created to solve token expiration issues
  • Implements a local HTTP server on port 8765 to handle Google's OAuth callback
  • Persists the refresh token to environment variables and AWS Lambda secrets

OAuth Token Management and Lambda Integration

Challenge: The initial calendar token expired, preventing the port sheet script from reading Google Calendar events. Google's OAuth refresh token flow requires careful credential management across local development and Lambda production environments.

Solution: A dedicated reauthentication script was built to:

1. Start a local HTTP server listening on port 8765
2. Redirect the user to Google's OAuth consent screen
3. Capture the authorization code in the callback
4. Exchange it for a new refresh token
5. Store the refresh token as a Lambda environment variable via the AWS SDK

Credential Separation: The system maintains two separate OAuth client credentials:

• Drive client: Used for reading/writing port sheet templates
• Calendar client: Used for reading charter events

Each has its own credential file and token storage. This separation ensures that token expiration in one API doesn't block operations in another, and allows scoped permissions (e.g., read-only calendar, read-write drive).

Key Decisions and Trade-offs

1. Excel Library Choice: We used xlrd for reading the legacy .xls file and openpyxl for the .xlsx conversion. While openpyxl can read .xlsx natively, it cannot parse all properties of .xls files. Using both libraries allowed us to preserve row heights and column widths from the original template.

2. Google Sheets API vs. Sheets as CSV: We chose the Sheets API (with correct sheet IDs and ranges) over simple CSV export because the API provides row-level control and handles formatting metadata that CSV cannot preserve.

3. Port 8765 for