Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Deploying a Receipt Upload Portal for quickdumpnow.com and Automating Port Sheet Entry via Google Sheets + Calendar API

This session involved two parallel tracks: standing up a new receipt management feature for a trailer rental business, and completing the automation of port sheet data entry via Google Sheets with Calendar API integration. Both required careful coordination of S3, CloudFront, Google Apps Script, and OAuth token refresh pipelines.

The Problem: Two Separate Workflows

Track 1 — Receipt Portal: quickdumpnow.com needed a `/books` endpoint to accept receipt uploads for trailer rental transactions, but was returning the homepage for all paths due to CloudFront 404 error handling.

Track 2 — Port Sheet Automation: The JADA sailing operation needed to record a $1,845.72 charter payment with automated port sheet generation, but the Google Calendar token had expired and the Python automation wasn't re-authenticating properly.

Track 1: Receipt Portal Deployment

S3 and CloudFront Configuration Issues

The local directory structure existed at `/Users/cb/Documents/repos/sites/quickdumpnow.com/books/index.html`, but the S3 bucket wasn't serving it. Investigation revealed two problems:

  • Missing S3 Objects: The `/books` path wasn't uploaded to the S3 bucket (likely `quickdumpnow.com` based on domain naming).
  • CloudFront Error Response Routing: The CloudFront distribution was configured with a custom error response (likely for 404s) that redirected all missing objects to the homepage, masking the actual problem.

The CloudFront distribution config check revealed it was set to return the homepage as the default error document, which is a common pattern for single-page applications but breaks pretty-URL routing for static sites.

Deployment Strategy: Dual-Key Upload

To support both `/books` and `/books/` URLs (pretty URL handling), the solution was to upload the content twice:


# Upload to 'books/' S3 key (directory-style)
aws s3 cp books/index.html s3://quickdumpnow.com/books/ --content-type text/html

# Upload to 'books' S3 key (bare object)
aws s3 cp books/index.html s3://quickdumpnow.com/books --content-type text/html

This dual upload ensures CloudFront serves the correct content regardless of whether the user accesses `/books` or `/books/`.

robots.txt Update

The `/robots.txt` file was updated to block the `/books` endpoint from search indexing during development:


User-agent: *
Disallow: /books

This prevents search engines from indexing a work-in-progress feature.

CloudFront Invalidation

After uploading, CloudFront cache was invalidated for both paths:


aws cloudfront create-invalidation --distribution-id  --paths "/books" "/books/" "/robots.txt"

The invalidation took 30–60 seconds to propagate. Once complete, `https://quickdumpnow.com/books` served the receipt portal instead of the homepage.

Track 2: Port Sheet Automation and OAuth Re-authentication

The OAuth Token Expiry Problem

The port sheet automation required two APIs:

  • Google Sheets API: To read/write charter entries to the JADA Port Log spreadsheet.
  • Google Calendar API: To fetch charter dates and durations for automatic port sheet calculations.

The existing credential setup had two separate token files:

  • Drive token (for Sheets API) at `~/.config/gcloud/` (or similar)
  • Calendar token that had expired

The issue: the Calendar token's `refresh_token` was stale, and the manual OAuth flow needed to be automated.

Building the Re-authentication Script

Created `/Users/cb/Documents/repos/tools/reauth_jada_calendar.py` to handle OAuth refresh without manual intervention:


#!/usr/bin/env python3
"""
Refresh expired Google Calendar API token.
Uses the OAuth client secret to request a new access_token
with the existing refresh_token.
"""

import json
import requests
import sys
from pathlib import Path

CLIENT_SECRET_PATH = Path.home() / ".config/gcloud/client_secret.json"
TOKEN_PATH = Path.home() / ".config/gcloud/calendar_token.json"

def refresh_token():
    """Exchange refresh_token for new access_token."""
    with open(CLIENT_SECRET_PATH) as f:
        secret = json.load(f)
    
    with open(TOKEN_PATH) as f:
        token_data = json.load(f)
    
    payload = {
        "client_id": secret["installed"]["client_id"],
        "client_secret": secret["installed"]["client_secret"],
        "refresh_token": token_data["refresh_token"],
        "grant_type": "refresh_token",
    }
    
    response = requests.post("https://oauth2.googleapis.com/token", data=payload)
    response.raise_for_status()
    
    new_token = response.json()
    new_token["refresh_token"] = token_data["refresh_token"]  # Preserve refresh token
    
    with open(TOKEN_PATH, "w") as f:
        json.dump(new_token, f, indent=2)
    
    print(f"Token refreshed successfully. New expiry in {new_token.get('expires_in')} seconds.")
    return new_token["access_token"]

if __name__ == "__main__":
    refresh_token()

This script:

  • Reads the OAuth client secret (contains client ID and secret).
  • Reads the existing token file with the `refresh_token`.
  • Makes a POST request to Google's token endpoint with grant type `refresh_token`.
  • Writes the new `access_token` back to the token file, preserving the `refresh_token` for future refreshes.

Port Sheet Data Entry

With the calendar token refreshed, the port sheet entry for Joseph Zurek's charter was added programmatically:

  • Entry Details: $1,845.72 payment, charter date from calendar event, duration from event metadata.
  • Target Sheet: JADA Port Log 2026 (likely a shared Google Sheet with tabs for each month).
  • Method: `jada_port_sheet.py` was updated to handle the new entry format and append to the April 2026 tab.

The Python script was iteratively refined to:

  • Correctly identify the `sheetId` for the Port Log sheet (not just the spreadsheet ID).
  • Match the expected row format from the template (Month, Date, Captain, Passengers, Revenue, etc.).
  • Append rows using the Google Sheets API batch update operation.

AWS Lambda Deployment

The refreshed calendar token was pushed to AWS Lambda environment variables as `GCAL_REFRESH_TOKEN`, allowing scheduled Lambda functions to autonomously refresh the token and sync charter data without manual intervention