Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

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

Overview

This session involved two parallel infrastructure projects: deploying a receipt upload system for a trailer rental business at quickdumpnow.com/books, and resolving authentication issues with an automated monthly port sheet generator that pulls data from Google Calendar and Sheets.

Project 1: Quickdumpnow Books Page Deployment

Problem Statement

The quickdumpnow.com/books endpoint was returning the site homepage instead of a dedicated receipt management page. This was a CloudFront configuration issue combined with missing S3 object deployments.

Technical Implementation

The site structure uses:

  • S3 Origin: quickdumpnow.com bucket in us-east-1
  • CloudFront Distribution: Custom domain with custom error responses
  • Local Source: /Users/cb/Documents/repos/sites/quickdumpnow.com/

The CloudFront distribution was configured with a 404 error response rule that redirects all missing objects back to the homepage. This is a common pattern for single-page applications, but it was preventing direct access to the /books path.

The deployment strategy required uploading the books page twice to S3 to support both URL formats:

# Deploy to books/index.html (for /books/ with trailing slash)
aws s3 cp /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html \
  s3://quickdumpnow.com/books/index.html \
  --content-type text/html

# Deploy to books (for /books without trailing slash - pretty URLs)
aws s3 cp /Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html \
  s3://quickdumpnow.com/books \
  --content-type text/html

Additionally, the robots.txt was updated to block the books directory from search engine indexing, preventing the receipt system from appearing in search results:

User-agent: *
Disallow: /books/

CloudFront Cache Invalidation

After uploading objects to S3, CloudFront cache invalidations were issued for both URL formats:

aws cloudfront create-invalidation \
  --distribution-id [DISTRIBUTION_ID] \
  --paths "/books" "/books/" "/robots.txt"

CloudFront invalidations typically propagate within 30-60 seconds across edge locations. The distribution was verified to have proper origin configuration and custom error response handling.

Project 2: JADA Port Sheet Automation and Calendar Token Reauthentication

Problem Statement

The automated port sheet generator script (jada_port_sheet.py) was failing to authenticate with Google Calendar API due to expired or invalid credentials. The system needed to generate an April 2026 port sheet entry for a $1,845.72 charter payment.

Google Sheets Integration Architecture

The port sheet system involves multiple interconnected components:

  • Primary Source: Google Calendar events containing charter booking details
  • Data Store: JADA Port Log 2026 spreadsheet (multi-tab structure with Month tabs)
  • Template: Excel file maintaining row height/column width formatting from original Sheraton port sheet
  • Destination: Email delivery to Maria, Edwin, and Carolina

The authentication flow required managing two separate OAuth2 credential sets:

  • Drive API credentials: For reading/writing spreadsheet files
  • Calendar API credentials: For pulling event data

Token Refresh Implementation

The existing calendar credential file contained an expired or stale refresh_token. A new reauthentication script was created to refresh the token without requiring manual browser interaction:

# File: /Users/cb/Documents/repos/tools/reauth_jada_calendar.py

import google.auth.transport.requests
from google.oauth2.credentials import Credentials
import json

# Load existing credentials with stale refresh_token
with open('calendar_credentials.json', 'r') as f:
    creds_data = json.load(f)

creds = Credentials.from_authorized_user_info(creds_data)

# Attempt refresh with current refresh_token
request = google.auth.transport.requests.Request()
creds.refresh(request)

# Save updated access_token and refresh_token
with open('calendar_credentials.json', 'w') as f:
    json.dump(json.loads(creds.to_json()), f)

This approach reuses the existing refresh_token to obtain a new access_token without requiring a full OAuth2 authorization flow. The refresh token is valid indefinitely unless explicitly revoked by the user.

Excel Format Preservation

The original port sheet was an Excel file with specific formatting (row heights, column widths, merged cells). To preserve this format while automating data entry, the script was updated to:

  • Download the original Excel file
  • Parse the template structure using openpyxl
  • Create or update month-specific tabs (e.g., April 2026)
  • Insert charter entries in the correct row format
  • Upload the modified file back to Google Drive
pip install openpyxl --break-system-packages

# Script reads existing Excel structure:
# - Template sheet contains the column definitions and formatting
# - March tab contains example data
# - April tab created with same formatting, populated with Zurek charter entry

Charter Entry Data

The script appends entries to the Google Sheet with the following format:

Entry: Joseph Zurek Charter
Amount: $1,845.72
Date: [Extracted from Google Calendar event]
Tab: April 2026

Port Resolution

During local OAuth2 testing, the script attempted to bind to port 8765 for the browser redirect callback. A stale Python process was holding the port, preventing new authentication flows. The port was freed by terminating the process:

lsof -i :8765  # Identify process holding port
kill -9 [PID]   # Force terminate

AWS Lambda Deployment

The updated calendar GCAL_REFRESH_TOKEN was pushed to AWS Lambda environment variables for the automated port sheet generation function. This allows the Lambda function to call the Google Calendar API without manual token refresh between executions.

Infrastructure Decisions

  • S3 Dual-Upload Pattern: Uploading to both /books and /books/ ensures compatibility with different URL formats and CloudFront caching behavior
  • Token Refresh Over Reauth: Using the existing refresh_token` avoids requiring manual browser authorization, making the system fully automated
  • Excel Format Preservation: Using openpyxl