Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Monthly Charter Revenue Reporting: Google Sheets API Integration & OAuth Token Management

This post documents the infrastructure and automation work completed to generate the May monthly charter revenue report for JADA operations. The system integrates Gmail contact discovery, Google Sheets API calls, DynamoDB queries, and automated email delivery—all orchestrated through a patched OAuth reauthentication handler on an EC2 instance.

Problem Statement

Manual monthly reporting required:

  • Querying charter records from DynamoDB across multiple regions (crew-dispatch and charter-chats tables)
  • Cross-referencing guest lists and revenue figures from multiple sources (Quinn and Jonathan trip sheets, Gmail booking records)
  • Formatting and validating revenue calculations against historical report templates
  • Distributing formatted xlsx files to stakeholders via email

The bottleneck was credential management: the Google OAuth token was stale, the reauth script had a path bug, and there was no reliable way to refresh credentials on the shared EC2 host without manual intervention.

Architecture & File Organization

All work was executed on EC2 instance 34.239.233.28 (Ubuntu, jada-key.pem identity). The repository structure: 


/home/ubuntu/repos/queenofsandiego/          # Main JADA operations repo
├── reauth_google.py                         # OAuth token refresh handler
├── send_statement.py                        # Email delivery script
└── /secrets/                                # Secured credentials dir (0700)
    ├── google_creds.json
    └── unified_token.json

/tmp/                                        # Scratch space for report generation
├── gmail_jennifer.py                        # Gmail contact search
├── build_sheet.py                           # Xlsx generation from DynamoDB
├── build_may_tab.py                         # May-specific report tab
└── upload.py                                # Google Drive upload

DynamoDB tables queried:

  • crew-dispatch (us-east-1): Charter records with guest names, dates, totals
  • charter-chats (us-west-2): Booking context and revenue metadata

Google Drive destination: JADA business folder for the master workbook; email attachment sent to operations stakeholders.

Technical Implementation: OAuth Token Refresh

Problem: The original reauth_google.py contained a hardcoded absolute path that failed when called from different working directories: 


# BROKEN - before patch
token_path = "/Users/cb/.claude/secrets/unified_token.json"

When run on EC2, this path didn't exist. The fix dynamically resolved the token location relative to the script's home directory: 


# PATCHED - after fix
import os
script_dir = os.path.dirname(os.path.abspath(__file__))
repo_root = os.path.dirname(script_dir)  # Back out one level
token_path = os.path.join(repo_root, ".secrets", "unified_token.json")

The patched script was deployed with backup preservation: 


# On EC2:
cp reauth_google.py reauth_google.py.backup
# [Deploy patched version]
python3 reauth_google.py  # Verify syntax and token refresh

Why this approach: Relative pathing avoids hardcoded user paths and works across different deployment environments. The token refresh now succeeds, and subsequent API calls (Gmail search, Sheets operations, Drive uploads) inherit the valid credentials from unified_token.json.

Data Pipeline: DynamoDB → Xlsx → Email

Step 1: Query Charter Data

build_sheet.py executes boto3 scans against crew-dispatch to retrieve all May charter records: 


dynamodb = boto3.resource('dynamodb', region_name='us-east-1')
table = dynamodb.Table('crew-dispatch')
response = table.scan(
    FilterExpression=Attr('date').between('2024-05-01', '2024-05-31')
)

For each record, the script extracts charter total and guest list. Revenue calculations are validated against the prior April report to ensure consistency (reportable-revenue rule: only include charters with confirmed payments marked in metadata).

Step 2: Generate Xlsx with openpyxl

The May tab was built from a Template sheet already present in the master workbook. The script:

  • Iterates over DynamoDB records and populates row data (guest names, dates, amounts)
  • Applies number formatting (currency, two decimals) using openpyxl style API
  • Calculates totals and footnotes matching the April format
  • Writes to /tmp/jada_may_statement.xlsx

from openpyxl import load_workbook
from openpyxl.styles import numbers

wb = load_workbook('template.xlsx')
ws = wb['May']
for row_idx, record in enumerate(charters, start=2):
    ws[f'A{row_idx}'] = record['guest_name']
    ws[f'B{row_idx}'] = record['date']
    ws[f'C{row_idx}'].value = record['total']
    ws[f'C{row_idx}'].number_format = '$#,##0.00'

wb.save('/tmp/jada_may_statement.xlsx')

Step 3: Upload to Google Drive & Email

The generated xlsx is uploaded to the JADA business folder via Google Drive API, then a standalone single-tab attachment is sent via Gmail: 


# upload.py uses Drive API
drive_service = build('drive', 'v3', credentials=creds)
file_metadata = {'name': 'May_2024_Charter_Report.xlsx', 'parents': [folder_id]}
drive_service.files().create(body=file_metadata, media_body=...).execute()

# send_statement.py uses Gmail API
gmail_service = build('gmail', 'v1', credentials=creds)
message = create_message_with_attachment(
    sender='jada-ops@sailjada.com',
    to=['sergio@sailjada.com'],
    subject='May 2024 Charter Revenue Report',
    message_text='...',
    attachment_file='/tmp/jada_may_statement.xlsx'
)
gmail_service.users().messages().send(userId='me', body=message).execute()

Key Decisions & Trade-offs

Why boto3 for DynamoDB instead of DocumentClient? boto3 is the Python AWS SDK standard. It provides consistent error handling, credential resolution via IAM roles on EC2, and seamless integration with openpyxl for downstream processing.

Why relative paths in reauth_google.py? The script lives in the repo root. Using os.path.abspath(__file__) to resolve the script's own location, then traversing