Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating JADA Operations: OAuth Token Refresh, Google Sheets Integration, and Crew Dispatch Workflow

This post documents a week-long sprint to automate critical business workflows for JADA (a charter and crew-dispatch operation). The work spanned Google OAuth token management, dynamic spreadsheet generation, DynamoDB crew-roster operations, and SMS/email notification systems. The goal: eliminate manual reporting steps and enable self-service crew availability updates.

The Problem

JADA's operations relied on manual monthly reporting, email-based crew coordination, and static spreadsheets that required hands-on updates. Token refresh failures blocked access to Gmail and Google Drive APIs, preventing automated statement generation and crew communication. Crew availability was tracked in a scattered way across multiple sheets and spreadsheets with no single source of truth.

OAuth Token Refresh & Google API Access

The first blocker was a failing Google OAuth refresh token in reauth_google.py, deployed on an EC2 instance at ~/repos/jada-crew-ops/reauth_google.py.

The Issue: The script was constructing the token refresh payload but the response wasn't being parsed correctly. The fix required two changes:

  • Path handling: Changed hardcoded token paths to use environment-relative resolution so the script works regardless of working directory.
  • Response parsing: Wrapped the token response in proper JSON validation and added fallback logging to diagnose future failures.

The patched script now validates against /Users/cb/.claude/projects/.../ memory notes documenting the exact Google OAuth scopes required:

gmail.send
gmail.readonly
drive.file
sheets

This scope set enables the full workflow: read existing sheets, write new tabs, send statements via Gmail, and search booking history.

Monthly Statement Generation: From DynamoDB to XLSX

Once token refresh was working, the next step was automating the Sheraton monthly revenue report—a multi-tab Excel workbook that required pulling charter data, calculating totals, and formatting currency.

Data Pipeline:

  • Source: DynamoDB table crew-dispatch (same region as deployment EC2) containing all charter records with booking names, guest counts, amounts, and dates.
  • Extraction: Custom Python script queries the table by date range (e.g., May 1–31), filters for "reportable" charters (full paid bookings, excludes internal crew training), and groups by revenue category.
  • Transform: Built template workbook with April data as reference. The May tab is generated fresh each month by copying the template sheet, clearing prior data, and populating new rows with current charters.
  • Output: Single-sheet XLSX file written to /tmp/ on the box, then downloaded via SCP to the Mac for inspection, then uploaded to Google Drive as the source of truth.

Key Script: build_may_tab.py

This script demonstrates the pattern:


# Pseudo-code structure
import openpyxl
from openpyxl.styles import numbers

# Load template workbook
wb = openpyxl.load_workbook('template.xlsx')
ws = wb['May']  # or create from scratch

# Query DynamoDB for May charters
charters = query_crew_dispatch(start_date='2024-05-01', end_date='2024-05-31')

# Populate sheet
row = 2
for charter in charters:
    ws[f'A{row}'] = charter['booking_name']
    ws[f'B{row}'] = charter['guests']
    ws[f'C{row}'] = charter['amount']
    ws[f'C{row}'].number_format = '$#,##0.00'  # Currency format
    row += 1

wb.save('may_report.xlsx')

The script uses openpyxl (already available in the Lambda/EC2 environment) to avoid dependency hell. Number formatting is set per-cell rather than relying on Excel's auto-detect, ensuring consistent currency display across all sheets.

Email Delivery & Statement Distribution

Once the XLSX is built and uploaded to Google Drive, a separate script (send_statement.py) locates the file by name in Drive, converts it to a single-sheet attachment in house format, and emails it to a list of recipients pulled from prior Gmail message history.

Recipients logic: Script searches Gmail for previous statement emails (subject contains "Monthly Statement") and extracts the To/CC addresses. For May, recipients were Sergio, Jada, and Carole.


# Pseudo-code
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials

# Refresh token
creds.refresh(Request())

# Search Gmail
results = service.users().messages().list(
    userId='me',
    q='subject:Monthly Statement'
).execute()

# Parse To/CC from first result
msg = service.users().messages().get(id=results['messages'][0]['id']).execute()
headers = {h['name']: h['value'] for h in msg['payload']['headers']}
recipients = extract_emails(headers['To'], headers.get('Cc', ''))

Crew Roster & DynamoDB Schema

The crew-dispatch system uses two main DynamoDB tables:

  • crew-dispatch: Charters, bookings, amounts, guest lists, dates. Primary key is charter_id.
  • crew-roster (or similar): Crew members, contact info, captain flags, availability. Primary key is likely email or crew_id.

For June 27, the task was to add a new captain (Abdul Danishwar, +1 818-730-5220, abduldan@aol.com) to the roster and trigger a captain-first call-to-crew cascade per the SMS policy documented in memory.

The script roster_sheet_add.py writes directly to DynamoDB:


import boto3

ddb = boto3.resource('dynamodb', region_name='us-east-1')
table = ddb.Table('crew-roster')

table.put_item(Item={
    'crew_id': 'abdul-danishwar',
    'email': 'abduldan@aol.com',
    'phone': '+1-818-730-5220',
    'captain': True,
    'active': True,
    'added_date': datetime.utcnow().isoformat()
})

SMS Cascade & Captain Call Logic

The crew-dispatch system uses a Lambda function (deployed to ~/.../shipcaptaincrew/ on the EC2 box) to handle cascade rules. When a charter is added or updated, the function:

  1. Reads the SMS policy from memory notes (jada-sms-policy.md) defining the call order: captain first, then crew by availability.
  2. Queries the crew-roster for all captains marked active=true.
  3. Sends a templated SMS to the captain with charter details and a magic link for quick availability response.
  4. If the captain accepts, sends a second round to crew. If not, tries the next captain.

The magic link is generated by decoding tokens stored in DynamoDB