Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Monthly Revenue Reporting: Building a Google Sheets + Gmail Pipeline for Charter Operations

Over the course of a development session, we built an end-to-end automated reporting system that pulls charter data from DynamoDB, formats it into Excel workbooks, uploads to Google Drive, and distributes statements via Gmail. This post covers the architecture, technical decisions, and implementation details that made this possible.

What Was Done

The core objective was to automate JADA's monthly revenue reporting workflow—specifically, generating a May statement that aggregates charter records, calculates reportable revenue, and emails it to stakeholders. The system had to:

  • Query charter records from DynamoDB tables (crew-dispatch and charter-chats) across multiple AWS regions
  • Extract relevant booking and revenue data for a given month
  • Generate properly formatted Excel workbooks with multiple tabs (one template, one month-specific)
  • Authenticate with Google Drive and Sheets APIs using OAuth2
  • Upload the workbook and send distributable statements via Gmail
  • Handle token refresh failures gracefully
  • Preserve report format consistency across months

Technical Architecture

Data Pipeline

The pipeline consists of several modular Python scripts deployed to an EC2 instance at 34.239.233.28:

  • /tmp/gmail_contacts.py — Queries Gmail to find prior statement recipients and extract email addresses from historical booking confirmations
  • /tmp/sheet_inspect.py and /tmp/sheet_diag.py — Diagnostic tools to inspect Google Sheets structure, tab names, and cell values (used to reverse-engineer the report format)
  • /tmp/build_may_tab.py — Builds the May-specific tab by querying DynamoDB records, applying business logic (reportable-revenue filtering), and populating a pre-formatted template
  • /tmp/upload.py — Handles Drive API authentication and workbook upload
  • /tmp/send_statement.py — Sends the final statement attachment via Gmail using the Gmail API

The EC2 instance uses a shared credentials directory at ~/.secrets/ (permissions locked to 0700) containing OAuth tokens and Google service account credentials. A patched version of reauth_google.py handles token refresh with improved error handling.

Data Sources

Charter data lives in DynamoDB across two AWS regions. We queried:

  • crew-dispatch table — Contains full charter records with guest names, trip dates, captain assignments, and base amounts
  • charter-chats table — Stores booking and communication metadata

Schema inspection revealed that reportable revenue is calculated by filtering charters based on confirmation status and trip date. The prior April report was used as a format reference to ensure consistency—footnotes, number formatting, and cell layout were reverse-engineered from existing workbooks in the JADA business folder.

Google Workspace Integration

OAuth2 flow uses three scopes:

  • https://www.googleapis.com/auth/drive.file — Upload and manage files on Drive
  • https://www.googleapis.com/auth/spreadsheets — Read/write Google Sheets (for format inspection)
  • https://www.googleapis.com/auth/gmail.send — Send emails via Gmail

Credentials are stored in ~/.secrets/google_creds.json on the EC2 box. Token refresh was initially failing due to incorrect token-handling logic in the original reauth_google.py`; the patched version (deployed after syntax validation) corrects the refresh flow and provides better error diagnostics.

Key Technical Decisions

Why Python on EC2 Rather Than Lambda

This workflow involves multiple sequential API calls (DynamoDB → Drive → Gmail) with potential token refresh cycles. Lambda's 15-minute execution limit and cold-start overhead made EC2 more suitable. The instance acts as a persistent "ops box" where long-running diagnostic queries and iterative development are easier to handle.

Local Excel Generation with openpyxl

Rather than using Google Sheets API to populate cells directly, we generate a complete .xlsx file locally on the EC2 instance using openpyxl, then upload it to Drive. This approach:

  • Avoids API quota issues with large batch writes
  • Allows complex formatting (merged cells, number formats, borders) to be applied before upload
  • Enables local validation and inspection before sending
  • Separates build concerns from distribution concerns

The workbook is built from a template tab, with a new tab created per month. Number formatting is explicitly set (e.g., "$#,##0.00") to ensure currency values display consistently.

Diagnostic-First Development

Rather than building the final pipeline blindly, we created diagnostic scripts first:

  • sheet_inspect.py — Reads the target Drive file and lists all tabs, cell contents, and formatting rules
  • gmail_diag.py — Tests Gmail API connectivity and token state
  • sheet_diag.py — Inspects Sheets API responses to understand data types

This "observe first" approach revealed the exact format expected (footnotes, total rows, number formatting) and allowed us to match it exactly without guesswork.

Infrastructure & Deployments

Credentials Management

Secrets are stored in /Users/cb/.claude/projects/-Users-cb/memory/MEMORY.md on the local development machine (never in code or git). On the EC2 instance, credentials are read from ~/.secrets/google_creds.json with strict permissions (0700 on the directory, 0600 on files). SSH keys for the instance are managed via ~/.ssh/config with the identity file jada-key.pem.

Deployment Pattern

Python scripts are developed locally, scp'd to the EC2 instance (via `scp script.py ubuntu@34.239.233.28:/tmp/`), syntax-checked remotely, then executed. Artifacts (generated xlsx files) are scp'd back to the Mac for inspection before final upload/distribution.

Token Refresh Patch

The original reauth_google.py 


# Key change: correct refresh token usage
if 'refresh_token' in creds_dict:
    creds = Credentials(
        token=creds_dict.get('access_token'),
        refresh_token=creds_dict['refresh_token'],
        token_uri=token_uri,
        client_id=client_id,
        client_secret=client_secret,
        scopes=scopes
    )
    if creds.expired and creds.refresh_token:
        creds.refresh(Request())

This ensures tokens are refreshed before API calls and errors are caught early.

What's Next

The June 27 work builds on