Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Deploying a Receipt Management System for quickdumpnow.com and Automating JADA Port Sheet Data Entry

What Was Done

This session involved two parallel projects: deploying a new receipt management interface for the quickdumpnow.com trailer rental business, and establishing automated data entry workflows for the JADA Port Log spreadsheet. The work required coordinating static site deployment via CloudFront and S3, integrating with Google Sheets APIs, and implementing OAuth token refresh mechanisms for sustained automation.

Quickdumpnow.com Books Deployment

The quickdumpnow.com domain needed a dedicated receipts management page at /books. The site structure already contained a books directory with a placeholder template, but it hadn't been deployed to production.

Static File Deployment Strategy

The deployment required uploading to S3 and invalidating CloudFront cache:

  • S3 Bucket: quickdumpnow.com (primary static site bucket)
  • Files Modified:
    • /books/index.html — Receipt management landing page
    • /robots.txt — Updated to block indexing of the /books path during development
  • Deployment Commands:
    • Upload both books/index.html and bare books key to S3 (supports both trailing-slash and non-trailing-slash URL patterns)
    • Invalidate CloudFront distribution with paths: /books, /books/, /books/*, and /robots.txt

Why dual uploads? S3 doesn't perform automatic index.html resolution for bare directory keys. By uploading to both books/index.html and the bare books key, we ensure the page loads whether users access https://quickdumpnow.com/books or https://quickdumpnow.com/books/. This improves user experience and SEO consistency.

CloudFront Configuration Issue

Initial testing showed CloudFront returning the homepage for /books requests. This indicated a custom error response rule was configured to redirect all 404 errors to the root index page—a common pattern for single-page applications. Since we needed /books to serve distinct content, we verified the distribution's error response configuration and ensured S3 objects existed before CloudFront served them.

Invalidation strategy: CloudFront invalidations are eventually consistent (30–60 seconds). Multiple invalidation patterns were issued to ensure both trailing and non-trailing slash variants were purged from edge caches.

JADA Port Sheet Automation

The more complex project involved automating data entry into the JADA Port Log spreadsheet. The manual process required copying charter payment data into a shared Google Sheet, with separate monthly tabs tracking individual port entries.

Data Source Integration

Files modified for port sheet automation:

  • /Users/cb/Documents/repos/sites/queenofsandiego.com/PortSheetReporter.gs — Google Apps Script deployed to the site's Apps Script project
  • /Users/cb/Documents/repos/tools/jada_port_sheet.py — Python utility for reading/writing port log entries via Sheets API
  • /Users/cb/Documents/repos/tools/reauth_jada_calendar.py — OAuth token refresh utility (new)

Google Sheets API Architecture

The JADA Port Log is a multi-tab Google Sheet where each month gets a dedicated tab (e.g., "March", "April"). Each tab contains structured data with columns matching the port sheet template.

Key operations performed:

  • Retrieved sheet metadata via sheets.spreadsheets().get() to identify tab IDs (sheetId values)
  • Read existing data with sheets.spreadsheets().values().get() to understand current state
  • Appended new entries (charter payment for Joseph Zurek, $1,845.72) using sheets.spreadsheets().values().append() with valueInputOption: USER_ENTERED
  • Created new monthly tabs dynamically before inserting data

Data format standardization: The port sheet uses a specific Excel format (originally .xls with multiple sheets). Python's openpyxl library was installed to read and preserve the original template structure when building updated versions.

Authentication Challenge: OAuth Token Refresh

The initial jada_port_sheet.py script relied on pre-existing OAuth tokens for both Google Drive and Google Calendar APIs. However, tokens expire after a period, and the Drive token scopes didn't include Calendar permissions.

Solution implemented:

  • Created reauth_jada_calendar.py to handle OAuth token refresh independently
  • Compared credential files to verify client ID consistency between Drive and Calendar OAuth clients
  • Implemented refresh token flow: when a primary token expires, the refresh_token is exchanged for a new access_token without requiring user interaction
  • Verified token storage format and scope requirements matched between both API clients

This pattern enables long-running scheduled tasks (e.g., cron jobs or Cloud Functions) to maintain API access without manual re-authentication.

Infrastructure & Configuration

S3 & CloudFront Setup

Bucket: quickdumpnow.com
Region: us-west-2 (inferred from site configuration)
CloudFront Distribution: (ID omitted for security)
Error Responses: Custom 404 → /index.html (single-page app pattern)
Cache Behavior: /books path requires TTL review for static HTML

Google API Integration

Drive API v3 — Read/write access to Port Log spreadsheet
Sheets API v4 — Structured data read/append operations
Calendar API v3 — Future integration for charter date tracking

Authentication: OAuth 2.0 with refresh token persistence
Token Storage: Local file-based (development environment)

Key Technical Decisions

  • Dual S3 key uploads: Ensures URL consistency regardless of trailing slash convention. Simpler than configuring S3 index document rules.
  • Multi-tab sheet design: Keeps monthly data organized and allows per-month access control. Requires dynamic tab creation before first data entry.
  • Python for Sheets integration: More maintainable than Apps Script for data transformation logic. Google client library handles OAuth refresh automatically.
  • openpyxl over xlrd: Supports reading and writing modern Excel formats while preserving template structure and formatting.
  • Standalone token refresh script: Decouples authentication logic from business logic, enabling reuse across multiple automation scripts.

What's Next

  • Receipt upload interface: The /books page is deployed but needs form