Gmail Extract Archive

Gmail Extract & Archive Pipeline

Extract Gmail messages into ~/gmail-archive/ — a dedicated private repo as the single source of truth for all email data. Every message → markdown. Every attachment → saved. Every spreadsheet → parsed to CSV. Legal scan mandatory. Delete from inbox after successful archive.

Prerequisites

• gmail-multi-account skill configured (3 accounts with OAuth tokens)
• ~/gmail-archive/ repo exists (created via gh repo create vamseeachanta/gmail-archive --private)
• openpyxl installed in repo: uv pip install openpyxl pyyaml
• Deny-list copied: config/deny-list.yaml from workspace-hub

Repo Structure

gmail-archive/
├── README.md                 # Archive conventions
├── config/
│   ├── accounts.yaml         # Account definitions (tokens external)
│   └── deny-list.yaml        # Legal scan patterns
├── data/
│   ├── {account}/
│   │   ├── messages/         # Individual .md extracts per message
│   │   ├── threads/          # Full thread .md extracts
│   │   ├── attachments/      # Downloaded files organized by thread
│   │   └── spreadsheets/     # Parsed JSON + CSV for each Excel
├── reports/
│   ├── digest/               # Daily digest outputs
│   └── cleanup/              # Cleanup tracking logs
└── scripts/
    └── extract.py            # Main extraction pipeline

Extraction Commands

Basic extraction (scan + preview)

cd ~/gmail-archive
uv run scripts/extract.py --account ace --query "from:sandsig.com in:inbox" --max 500 --dry-run

Extract + save (no delete)

uv run scripts/extract.py --account ace --query "from:sandsig.com in:inbox" --max 500

Extract + delete from Gmail

uv run scripts/extract.py --account ace --query "from:sandsig.com in:inbox" --max 500 --delete

Extract full threads

uv run scripts/extract.py --account skestates --query "subject:1099 has:attachment" --threads --delete

Force through legal violations

uv run scripts/extract.py --account personal --query "category:promotions" --max 1000 --delete --force

Common Queries

Key Learnings / Pitfalls

1. Headless OAuth limitation

The gmail-mcp-multiauth npm package requires a GUI browser for OAuth flow. On headless servers:

• Generate auth URL manually: accounts.google.com/o/oauth2/v2/auth?access_type=offline&scope=https://www.googleapis.com/auth/gmail.modify+https://www.googleapis.com/auth/gmail.settings.basic&response_type=code&client_id={client_id}&redirect_uri=urn:ietf:wg:oauth:2.0:oob
• Sign in via browser on another machine
• Extract authorization code from the page
• Exchange code for tokens via https://oauth2.googleapis.com/token
• Save tokens as ~/.gmail-{account}/credentials.json

2. Token refresh

OAuth access tokens expire in 1 hour. Always refresh at runtime using the refresh_token in credentials.json:

data = urllib.parse.urlencode({
    "client_id": ..., "client_secret": ...,
    "refresh_token": saved["refresh_token"], "grant_type": "refresh_token",
})

3. Legal scan is mandatory

Run against config/deny-list.yaml before every commit. Skip (don't archive) messages that match "block" patterns. Use --force only for known safe data.

4. Spreadsheet parsing

Gmail API returns attachments as base64. For spreadsheets:

1. Save the raw .xlsx to spreadsheets/
2. Parse with openpyxl: wb = openpyxl.load_workbook(xlsx_path, data_only=True)
3. Export each sheet to CSV: ws.iter_rows(values_only=True)
4. Also save full data as JSON for analysis

5. Category queries return 0

Gmail API category:promotions and similar queries may return 0 results via REST API even though the Gmail UI shows results. Use domain-based or sender-based queries instead: from:collide.io OR from:promote.weebly.com OR from:e.swimoutlet.com

6. Self-forwards are smaller than expected

The "personal→ace shuttle" pattern was estimated at 2,000-3,000 emails from sent-folder analysis. Actual count was only ~183. Most "shuttle" entries were brief notes ("FYI", "Print") without attachments, or were external emails received in the personal inbox, not actual file transfers.

Account-Specific Patterns

ace (vamsee.achanta@aceengineer.com)

• Sands IG CRE listings: from:sandsig.com → extract, parse property data, then delete
• Keep: github.com, openrouter.ai, substack.com (review), deeplearning.ai
• Delete: collide.io, promowebly.com, email.theparkingspot.com, etc.

personal (achantav@gmail.com)

• Keep: parentsquare.com (school), github.com, em1.turbotax.intuit.com (tax)
• Delete: promote.weebly.com, e.swimoutlet.com, mail.urbanairparks.com, lists.wikimedia.org, etc.
• Review: rigzonemail.com, info.marineinsight.com, m.learn.coursera.org

skestates (skestatesinc@gmail.com)

• Almost all emails are actionable business correspondence
• Sands IG CRE listings forwarded: extract, then delete
• Thread archive critical for tax/legal reference

Data Flow After Extraction

Extracted data feeds into:

• sabithaandkrishnaestates/docs/tax/ — tax documents, payment analyses
• aceengineer-admin/admin/contacts/ — contact databases
• assethold/data/ — CRE market intelligence
• achantas-data/ — personal/family docs