my-sync-us-revenue

US Revenue Sync

Bill.com new invoices + Plaid bank deposits → Google Sheets 2026 미국 매출 sheet auto-sync.

Core Script

scripts/daily/sync_us_revenue.py

How to run: uv run (same as launchd plist)

/opt/homebrew/bin/uv run \
  --with python-dotenv --with requests \
  --with google-auth --with google-api-python-client \
  scripts/daily/sync_us_revenue.py [--dry-run] [--year YYYY]

Execution Flow

1. Always dry-run first

/opt/homebrew/bin/uv run \
  --with python-dotenv --with requests \
  --with google-auth --with google-api-python-client \
  scripts/daily/sync_us_revenue.py --dry-run

Show the results to the user:

Number of new invoices added
Number of deposit matches (Stripe/YouTube/content)
Unpaid list

2. Run after user confirmation

If the user approves after seeing the dry-run results, re-run without --dry-run :

/opt/homebrew/bin/uv run \
  --with python-dotenv --with requests \
  --with google-auth --with google-api-python-client \
  scripts/daily/sync_us_revenue.py

3. Result Summary

What to show the user after running:

Number of invoices added
Number of matched deposits + company name/amount
Remaining unpaid list
Whether Slack notification was sent

Automatic Execution (launchd)

~/Library/LaunchAgents/net.eoeoeo.sync-us-revenue.plist

Interval: Every 1 hour (StartInterval: 3600)
Log: scripts/daily/sync_us_revenue.log (RotatingFileHandler, 1MB x 3 backups)
stdout/stderr: scripts/daily/sync_us_revenue.stdout.log, .stderr.log

How It Works

Network check — check_network() DNS pre-check, silently exits if unavailable
Bill.com — login (2 retries) → add new invoices to sheet
Plaid — query Chase/Hanmi deposits → classify Stripe/YouTube/content → match to sheet
Duplicate prevention — already_matched consume Plaid transactions for rows already marked as deposit confirmed using a set
Slack notification — when a new deposit is detected, #all-프로젝트현황 channel auto-notification

Bill.com and Plaid run independently — if one fails, the other still works normally.

Main Options

Option	Description
`--dry-run`	Output results only without changing the sheet
`--year`	Target year (default: current year)

Environment Variables (.env)

The script automatically loads the project root .env:

BILL_COM_USERNAME / BILL_COM_PASSWORD / BILL_COM_ORG_ID / BILL_COM_API_KEY
PLAID_CLIENT_ID / PLAID_SECRET / PLAID_ACCESS_TOKEN_CHASE / PLAID_ACCESS_TOKEN_HANMI
SLACK_BOT_TOKEN — for sending deposit notifications

Precautions

scripts/daily/는 .gitignore excluded — local only, cannot be committed to git
--dry-run If run without it, Google Sheets will be modified directly
Matching multiple items with the same amount depends on order — if mismatching is suspected, manually check the sheet
Slack notification channel: C048XKTV5LK (#all-프로젝트현황)

Troubleshooting

Symptom	Cause	Solution
No notification for 4+ days	Consecutive DNS failures after macOS sleep	Check stderr.log, check MacBook network status
Same amount mismatched	Unpaid row order + matching by amount only	Delete the incorrect deposit date from the sheet → re-run
Bill.com login failure	API credentials expired/changed	`.env` check, renew in the Bill.com dashboard
Plaid token expired	Change bank password	`python scripts/plaid_link_connect.py` Reconnect