Deploying a Receipts Management Page and Automating Charter Port Sheet Entry to Google Sheets

This post covers two concurrent infrastructure improvements made to support Queen of San Diego's operations: deploying a receipts upload interface for the quickdumpnow.com trailer rental business, and automating charter entry logging to a port sheet via Google Apps Script and Python.

Part 1: Receipts Page Deployment to quickdumpnow.com

What Was Done

The /books endpoint on quickdumpnow.com was returning the homepage instead of a dedicated receipts management page. We diagnosed a CloudFront caching issue, deployed the correct landing page, and updated site configuration to prevent search engine indexing.

Technical Details

The directory structure at /Users/cb/Documents/repos/sites/quickdumpnow.com/ contained a books/ subdirectory with index.html, but the page wasn't being served. Root cause: CloudFront distribution had a custom error response rule configured to redirect all 404s to the homepage, masking the missing S3 object.

Our deployment process involved three files:

• /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html — receipts landing page
• /Users/cb/Documents/repos/sites/quickdumpnow.com/robots.txt — updated to block /books from indexing
• S3 bucket: quickdumpnow.com (exact bucket name)

We uploaded the books page to two S3 keys to support both URL patterns:

s3://quickdumpnow.com/books/index.html
s3://quickdumpnow.com/books

The dual-key approach ensures CloudFront resolves /books (directory without trailing slash) without forcing a 301 redirect to /books/, reducing latency for client requests.

After upload, we invalidated the CloudFront distribution cache for both paths:

aws cloudfront create-invalidation --distribution-id [DISTRIBUTION_ID] --paths "/books" "/books/*"

Invalidation completed within 30–60 seconds. The robots.txt update blocks the /books endpoint from being crawled, preventing the receipts page from appearing in search results (appropriate since it's an internal business tool, not public-facing content).

Infrastructure Configuration

• S3 Bucket: quickdumpnow.com
• CloudFront Distribution: Default distribution for quickdumpnow.com domain
• Origin Configuration: S3 static website hosting with custom error responses enabled
• Cache Invalidation: Wildcard paths /books and /books/*

The CloudFront distribution's custom error response rule (HTTP 404 → homepage) is intentional for the primary landing page but prevents subdirectory pages from being discoverable unless explicitly invalidated after upload.

Part 2: Automating Charter Port Sheet Entry

What Was Done

Implemented an automated workflow to log charter booking data (vessel name, amount, date) into a structured port sheet stored in Google Drive. This eliminates manual data entry and creates an auditable record for accounting and operations.

Technical Architecture

The system consists of two components working in tandem:

• Google Apps Script — /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs
• Python Automation Script — /Users/cb/Documents/repos/tools/jada_port_sheet.py

The workflow reads from a Google Sheet called "JADA Port Log 2026" (a shared Drive resource), appends new entries in the correct format, and maintains monthly tabs for organization.

Data Structure and Format

The port sheet follows a specific structure with multiple tabs:

• Month tab: Contains month label (e.g., "April 2026")
• Monthly entry tabs: One tab per month with charter entries
• Template tab: Defines expected row structure and formatting

Each charter entry requires:

Charter Name: "Joseph Zurek"
Vessel/Charter Type: Defined in template
Amount: $1,845.72 (numeric format)
Date: Entry date in YYYY-MM-DD format

The challenge was determining the exact cell layout and column widths from an Excel file. We used xlrd library to introspect the original .xls file structure:

import xlrd
workbook = xlrd.open_workbook(filename)
sheet = workbook.sheet_by_name('Template')
for row_idx in range(sheet.nrows):
    print(f"Row {row_idx}: {sheet.row_values(row_idx)}")

This revealed the expected column positions and allowed us to build a replacement .xlsx file (using openpyxl`) that preserved the original formatting.

# Install required packages
pip install openpyxl google-auth-oauthlib

# Authenticate to Google Drive
from google.auth.transport.requests import Request
from google.oauth2.service_account import Credentials

# Download existing port sheet from Drive
# Append new entry to April 2026 tab
# Re-upload to same Drive file ID (preserving link)