Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Monthly Revenue Reports: Google Sheets API Integration with OAuth Token Refresh

This post documents the infrastructure and automation work completed to generate JADA's monthly revenue reports programmatically, integrating Gmail, Google Sheets, and DynamoDB to produce consolidated financial statements for stakeholder distribution.

What Was Done

The task was to automate generation of May's monthly revenue report—a multi-source aggregation pulling charter bookings from DynamoDB, guest details from Gmail, and formatting the output as an Excel workbook for upload to Google Drive and distribution via email. The challenge: the existing OAuth token refresh mechanism was failing silently, breaking all downstream API calls.

  • Diagnosed and patched the Google token refresh flow in reauth_google.py
  • Built Python scripts to extract charter data from DynamoDB tables across regions
  • Implemented Gmail contact and booking search to enrich charter records with guest names
  • Generated formatted Excel workbooks with multi-tab structure matching historical report templates
  • Automated upload to Google Drive and distribution to stakeholders

Technical Architecture

Data Flow

The report pipeline chains four distinct sources:

  • DynamoDB (crew-dispatch table): Charter bookings with dates, amounts, captain, and guest count
  • DynamoDB (charter-chats table): Supplementary charter metadata and state transitions
  • Gmail API: Historical booking emails from Jennifer Sanderson to extract guest names and booking context
  • Google Sheets API: Template sheet for report formatting and Drive upload destination

Each source was queried independently, deduplicated on charter ID, and reconciled against prior month templates to ensure reportable-revenue classification rules were applied consistently.

OAuth Token Refresh Failure & Fix

The core blocker was in reauth_google.py on the EC2 host at ~/repos/shipcaptaincrew/reauth_google.py. The script handles Google OAuth credential refresh for long-running jobs that need Gmail, Drive, and Sheets API access.

The Problem: The refresh_access_token() function was catching all exceptions silently and returning None instead of propagating errors. When the token refresh failed—due to a stale refresh token or permission scope mismatch—the script would return a falsy value, and downstream code would attempt API calls with None credentials, failing cryptically.

The Fix: Modified reauth_google.py to:

  • Log actual error messages from the Google API response
  • Distinguish between transient network errors (retry) and fatal auth errors (raise)
  • Verify the unified token includes required scopes (gmail.search, sheets, drive.file)
  • Return explicit status so calling code knows if refresh succeeded

After patching and redeploying with backup, the token refresh succeeded and unlocked access to all downstream APIs.

Data Extraction Scripts

Four Python scripts were written to EC2's /tmp/ directory (transient layer) for data gathering:

build_sheet.py — Orchestrator script that:

  • Calls reauth_google.py to obtain valid credentials
  • Queries DynamoDB crew-dispatch table with date range filters
  • Pulls matching records from charter-chats for metadata enrichment
  • Invokes Gmail search for booking emails from Jennifer Sanderson
  • Formats results into openpyxl workbook object
  • Uploads to Google Drive and returns file ID

gmail_contacts.py — Gmail search module:


# Search for booking confirmations from Jennifer
query = 'from:jennifer@jada.com subject:booking May 2024'
results = service.users().messages().list(userId='me', q=query).execute()

# For each result, extract guest names and amounts from message body
for msg_id in results['messages']:
    msg = service.users().messages().get(userId='me', id=msg_id['id']).execute()
    # Parse headers and body for guest list, charter date, amount

sheet_inspect.py — Validation utility that reads back uploaded workbook to verify:

  • All tabs present (Template, May, etc.)
  • Formulas intact (e.g., SUM cells for monthly totals)
  • Number formats preserved (currency, date)
  • No silent data corruption during upload

build_may_tab.py — Report-specific formatter:

  • Reads the Template tab schema to understand row/column structure
  • Creates new "May" worksheet with identical layout
  • Populates charter records from DynamoDB in chronological order
  • Applies "reportable revenue" filter (excludes internal sails, test records)
  • Calculates subtotals per captain, per week, per month
  • Preserves number formats and footnote conventions from prior months

DynamoDB Queries

The crew-dispatch table (primary charter source) is partitioned by region (us-east-1, us-west-2) with structure:

  • Partition Key: charter_id (UUID)
  • Sort Key: booking_date (ISO 8601)
  • Attributes queried: captain, guest_count, total_amount, status, created_at, updated_at

Query filtered on booking_date between May 1 and May 31, 2024, using DynamoDB's KeyConditionExpression:


response = dynamodb.query(
    TableName='crew-dispatch',
    KeyConditionExpression='booking_date BETWEEN :start AND :end',
    ExpressionAttributeValues={
        ':start': '2024-05-01',
        ':end': '2024-05-31'
    }
)

The charter-chats table (secondary enrichment) was sampled via full table scan with filters for matching charter_ids, extracting proposal state and notes fields.

Google Sheets & Drive Integration

The workbook upload destination is a Google Drive folder shared with the JADA finance team. Using the Drive API with scope https://www.googleapis.com/auth/drive.file:

  • Created new file with mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  • Set file name to JADA_Revenue_May_2024.xlsx
  • Added parent folder ID (stored in settings) to place it in the finance folder hierarchy
  • Set sharing permissions to allow view-only access for non-editors via email list

The Sheets API was used only for template inspection (reading the Template tab to infer schema), not for writing—openpyxl handled all workbook generation locally to avoid round-trip latency and API quota strain.

Key Decisions

Why op