Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

OAuth Token Refresh & Google Sheets Integration: Automating Monthly Revenue Reports for Ship Captain Crew

What Was Done

We deployed a patched OAuth token-refresh mechanism to the JADA operations EC2 instance to enable reliable, long-lived Google Sheets and Gmail API access. This unblocked automated generation of monthly revenue reports—specifically, a templated Excel workbook that pulls real charter data from DynamoDB, enriches it with booking context from Gmail, and delivers it via email to stakeholders.

The core problem: the existing reauth_google.py script on the box was failing to refresh expired tokens, which meant any long-running or scheduled task that touched Gmail or Sheets would eventually hit a 401. The fix involved patching the token-refresh logic, redeploying it, validating the refresh cycle, and then building the first end-to-end monthly report workflow to prove it works.

Technical Details

Token Refresh Patch

The original reauth_google.py (located at ~/repos/reauth_google.py on the EC2 box) was hitting a token-expiry wall. Rather than manually re-authenticate every month, we patched the script to properly handle the OAuth2 refresh-token grant flow.

Key changes:

  • Fixed the refresh logic to correctly invoke google.oauth2.service_account.Credentials.refresh() with a fresh Request() transport object
  • Ensured the patched script preserves the refresh token across refreshes so subsequent calls don't require re-auth
  • Validated syntax on the box before deploying to production (python3 -m py_compile reauth_patched.py)
  • Backed up the original before replacing (cp reauth_google.py reauth_google.py.bak)

After deployment, we confirmed token refresh by running:

python3 ~/repos/reauth_google.py
# Then verified the master Sheets doc was accessible and listed all tabs

Excel Workbook Generation

Built /tmp/build_sheet.py to generate a templated Excel workbook with the following workflow:

  • Query DynamoDB: Read all charter records from the crew-dispatch table (region: us-east-1) to extract May charters with total_amount fields
  • Enrich from Gmail: For each charter, search Gmail for booking confirmation emails from the guest name to pull additional context (guest list, special notes, final confirmation date)
  • Compute reportable revenue: Apply the business rule that only charters with a confirmed payment status and revenue amount >= threshold are included
  • Format as Excel: Use openpyxl to build a multi-tab workbook matching the house format (one tab per month, with totals row, footnotes, and currency formatting)
  • Upload to Drive: Push the completed workbook to Google Drive (specific folder ID in the JADA business folder) and make it available via the shared Sheets master doc

The script reads a prior report to understand the exact column headers and number-format rules, ensuring consistency month-to-month.

Email Delivery

Built /tmp/send_statement.py to:

  • Search Gmail for prior statement recipients (e.g., messages with subject containing "Monthly Statement") to extract a distribution list
  • Attach the newly generated Excel file
  • Send with an HTML body summarizing the reporting period, total revenue, and any notes from the master sheet footnotes

The script also validates recipient addresses before sending and logs delivery status for audit purposes.

Infrastructure & Data Flow

DynamoDB Tables:

  • crew-dispatch (us-east-1): Contains charter records with fields charter_id, sail_date, total_amount, guest_names, payment_status
  • crew-roster (us-east-1): Captain and crew availability; used for cascade crew-call logic

Google Services:

  • Sheets: Master workbook in the JADA business folder; accessed via drive.files().list() and sheets.spreadsheets().values().get()
  • Gmail: Search queries for booking confirmations and prior statements; read via gmail.users().messages().list() with search filters
  • Drive: Monthly report workbooks uploaded as Excel files; accessible via folder ID queries

EC2 Instance:

  • Host: ubuntu@34.239.233.28 (t3.medium, us-east-1)
  • Repos: ~/repos/ contains production scripts and credential store (~/.secrets/)
  • Credentials: Stored as encrypted JSON (Google service-account key, AWS IAM role via instance metadata)

Key Decisions

Why DynamoDB + Gmail enrichment? The crew-dispatch table is the source of truth for charters, but guest context and confirmation dates live in email. Rather than duplicate data, we query both systems in real time. This keeps the report fresh and means no additional data-entry burden.

Why openpyxl instead of pandas/xlsxwriter? Pandas has a heavier dependency footprint; we needed fine-grained control over cell formatting (currency, date formats) and multi-sheet workbooks. openpyxl gives us that control with minimal overhead and is already installed on the box.

Why patch the token-refresh in place rather than use a cron job? The business requirement is that reports must be generated on demand (when requested by a stakeholder) and also on a scheduled basis (month-end). A long-lived token (refreshed transparently) is simpler than a scheduled re-auth cron job, and it means any ad-hoc script that touches Gmail/Sheets just works without special setup.

Why distribute via email + Drive? Email ensures stakeholders get the report in their inbox with a known filename; Drive link in the email body lets them access historical copies. This dual approach supports both real-time notification and long-term audit trails.

What's Next

We're now ready to:

  • Schedule monthly generation: Add a cron job to /etc/cron.d/ that runs the report script on the last business day of each month
  • Extend to crew dispatch: Use the same token-refresh pattern to enable automated crew-call emails based on the captain cascade rule stored in DynamoDB
  • Add metrics: Log report generation time, row counts, and email delivery status to CloudWatch for visibility into the automation health
  • Iterate on templates: Collect feedback from stakeholders on column layout and footnote requirements; update the template function in the script as needed

The token-refresh patch is the foundation; everything else—cascading crew calls, dynamic crew availability, instant statement generation—now flows from a reliable, refreshed OAuth