zenith-finance-gsheets-sync

name	zenith-finance-gsheets-sync
description	Synchronize Zenith Finance ledger and transactions to Google Sheets with automatic updates. Creates/maintains a spreadsheet with Dashboard (summary, charts) and History (full transaction log) sheets.
version	1.0.0
author	Hermes Agent
license	MIT
metadata	{"hermes":{"tags":["Finance","Google Sheets","Sync","Automation","Zenith"]}}

Zenith Finance → Google Sheets Sync

Automatically syncs Zenith Finance data (Ledger.md and Transactions.md) to a Google Spreadsheet for dashboard visualization and historical tracking.

Trigger

• User mentions "sync zenith to sheets", "google sheets zenith", "export finance to sheets"
• After any Zenith Finance transaction update (can be called automatically)
• Manual sync request

Prerequisites\n1. Google Workspace OAuth setup completed (google-workspace skill authenticated) with scope including https://www.googleapis.com/auth/drive\n2. Google Sheets API and Google Drive API enabled in Google Cloud project\n3. Zenith Finance data files exist:\n - ~/Documents/Obsidian/Notes/Finance/Ledger.md\n - ~/Documents/Obsidian/Notes/Finance/Transactions.md\n4. Optional but recommended: Download a copy of the Google Sheet for manual verification if needed (as user did with /Users/adityahimawan/Downloads/Zenith Finance Dashboard (4).xlsx)\n

Storage

• Spreadsheet ID stored at: ~/.hermes/zenith_spreadsheet_id.txt
• If no ID exists, creates new spreadsheet named "Zenith Finance Dashboard"

Sheets Structure

1. Dashboard (first sheet): Summary metrics and charts
   • Detailed metrics and formulas as specified in the JSON structure
2. History (second sheet): Full transaction log
   • Columns: Timestamp, Description, Amount, Income/Expense, Category, Source, Balance
   • Append-only format matching Transactions.md
3. Category (third sheet): Category list for reference
   • Single column list of transaction categories (A1:A9)
   • Used for data validation and reference

Dashboard Metrics Specification

Based on the provided JSON structure, the Dashboard sheet should be updated with exact cell positions:

Key Metrics:

• B2: "Metric" / C2: "Value" (header)
• B3: "Total Income" / C3: =SUMIFS(History!C:C, History!D:D, "Income")
• B4: "Total Expenses" / C4: =SUMIFS(History!C:C, History!D:D, "Expense")
• B5: "Net Savings" / C5: =C3-C4
• B6: "Last Updated Now" / C6: =NOW()
• B7: "Last Updated" / C7: [static timestamp from last sync]
• B9: "ACCOUNT BALANCES" / C9: "Value" (header)

Account Balance Formulas (B10:B15 label, C10:C15 value):

• B10: "BCA (Main Bank)" / C10: =SUMIFS(History!C:C, History!F:F, B10, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B10, History!D:D, "Expense")
• B11: "Blu (Digital Bank)" / C11: =SUMIFS(History!C:C, History!F:F, B11, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B11, History!D:D, "Expense")
• B12: "Gopay (E-Wallet)" / C12: =SUMIFS(History!C:C, History!F:F, B12, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B12, History!D:D, "Expense")
• B13: "Dompet (Cash on Hand)" / C13: =SUMIFS(History!C:C, History!F:F, B13, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B13, History!D:D, "Expense")
• B14: "RDN (Saham)" / C14: =SUMIFS(History!C:C, History!F:F, B14, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B14, History!D:D, "Expense")
• B15: "Emas (Tabungan)" / C15: =SUMIFS(History!C:C, History!F:F, B15, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B15, History!D:D, "Expense")

Derived Metrics:

• B16: "Total Liquid Assets" / C16: =SUM(C10:C15)

Monthly Summary (B18:B21):

• B18: ="MONTHLY SUMMARY (" & TEXT(TODAY(), "mmmm") & ")" / C18: "Value" (header)
• B19: "Monthly Income" / C19: Complex FILTER/SUM formula for current month
• B20: "Monthly Expenses" / C20: Complex FILTER/SUM formula for current month
• B21: "Monthly Net Savings" / C21: =C19-C20

Category Summary (B23 onward):

• B23: "CATEGORY SUMMARY" / C23: "Value" (header)
• B24: Array formula for category-based calculations
• B25:B32: Individual category totals (Expense, Entertainment, Transport, Food, Salary, Other Income, Work)

Workflow

1. Auth Check: Verify Google OAuth token is valid and has required scopes
2. Spreadsheet ID: Load existing ID or create new spreadsheet
3. Sheet Setup: Ensure Dashboard and History sheets exist (rename default sheet to History, add Dashboard)
4. Data Extraction: Parse Ledger.md and Transactions.md
5. Update Sheets:
   • Dashboard: Update account balances and summary formulas
   • History: Append new transactions since last sync
6. Verification: Confirm update success

Detailed Steps\n\n### 1. Authentication Validation\nbash\n# Check if authenticated (adjust path if needed)\npython3 /Users/adityahimawan/.hermes/skills/productivity/google-workspace/scripts/setup.py --check\n# Should return: AUTHENTICATED\n# \n# Note: If your Hermes installation is in a different location, you may need to:\n# 1. Find your google-workspace setup script (usually in ~/.hermes/skills/productivity/google-workspace/scripts/)\n# 2. Or set HERMES_HOME environment variable: HERMES_HOME=/path/to/.hermes python3 setup.py --check\n

2. Spreadsheet Management

• If ~/.hermes/zenith_spreadsheet_id.txt exists: use that ID
• Else: create new spreadsheet via Drive API:

  file_metadata = {
      'name': 'Zenith Finance Dashboard',
      'mimeType': 'application/vnd.google-apps.spreadsheet'
  }
  file = drive_service.files().create(body=file_metadata, fields='id').execute()
  spreadsheet_id = file.get('id')
  

3. Sheet Configuration

• Ensure we have three sheets in order: Dashboard (index 0), History (index 1), Category (index 2)
• Rename/reorder sheets as needed to match this structure
• Uses Google Sheets API batchUpdate

4. Data Parsing

Ledger.md parsing:

• Extract balances for: BCA, Blu, Gopay, Dompet, RDN, Emas
• Handle both numeric values and gram-based gold (Emas)

Transactions.md parsing:

• Extract lines matching pattern: [YYYY-MM-DD HH:MM] | Description | RpAmount | Income/Expense | Category | Source | Balance
• Determine last synced timestamp to avoid duplicates

5. Dashboard Updates

Update cells with current values according to the JSON structure:

• B2: "Metric" (static label)
• C2: "Value" (static label)
• B3: "Total Income" / C3: =SUMIFS(History!C:C, History!D:D, "Income")
• B4: "Total Expenses" / C4: =SUMIFS(History!C:C, History!D:D, "Expense")
• B5: "Net Savings" / C5: =C3-C4
• B6: "Last Updated Now" / C6: =NOW()
• B7: "Last Updated" / C7: [static timestamp from last sync]
• B9: "ACCOUNT BALANCES" / C9: "Value" (header)
• B10: "BCA (Main Bank)" / C10: =SUMIFS(History!C:C, History!F:F, B10, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B10, History!D:D, "Expense")
• B11: "Blu (Digital Bank)" / C11: =SUMIFS(History!C:C, History!F:F, B11, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B11, History!D:D, "Expense")
• B12: "Gopay (E-Wallet)" / C12: =SUMIFS(History!C:C, History!F:F, B12, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B12, History!D:D, "Expense")
• B13: "Dompet (Cash on Hand)" / C13: =SUMIFS(History!C:C, History!F:F, B13, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B13, History!D:D, "Expense")
• B14: "RDN (Saham)" / C14: =SUMIFS(History!C:C, History!F:F, B14, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B14, History!D:D, "Expense")
• B15: "Emas (Tabungan)" / C15: =SUMIFS(History!C:C, History!F:F, B15, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B15, History!D:D, "Expense")
• B16: "Total Liquid Assets" / C16: =SUM(C10:C15)
• B18: ="MONTHLY SUMMARY (" & TEXT(TODAY(), "mmmm") & ")" / C18: "Value" (header)
• B19: "Monthly Income" / C19: [complex FILTER/SUM formula for current month]
• B20: "Monthly Expenses" / C20: [complex FILTER/SUM formula for current month]
• B21: "Monthly Net Savings" / C21: =C19-C20
• B23: "CATEGORY SUMMARY" / C23: "Value" (header)
• B24: [array formula for category-based calculations]
• B25:B32: Individual category totals (Expense, Entertainment, Transport, Food, Salary, Other Income, Work)
• Account balances: B8:B13 values with labels in A8:A13

6. History Append

• Format new rows as: [timestamp, description, amount, income/expense, category, source, balance]
• Append to History!A:G using sheets.append with INSERT_ROWS

7. Category Sheet Management

• Ensure Category sheet exists as third sheet (index 2)
• Update Category sheet with single column list of unique categories from Transactions.md
• Format: A1: "Category" (header), A2:A[n]: unique category values
• Used for data validation and reference in Dashboard calculations

7. Error Handling

• If auth fails: guide user to re-run google-workspace setup
• If spreadsheet not found: create new one
• If sheet missing: recreate missing sheets
• Validate data before writing

Known Bugs & Fixes (2026-04-13)

Bug 1: Regex Missing Type Column

Transactions.md has 7 fields but regex only captured 6 (missing Income/Expense).

# ❌ Wrong (6 groups)
r'\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2})\]\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+)'

# ✅ Correct (7 groups — adds Income/Expense type)
r'\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2})\]\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+)'

Bug 2: google_api.py Argument Format

google_api.py sheets append/update expects --values '<json>' NOT --values-file <path>.

# ❌ Wrong
run_gapi("sheets", "append", sid, range, "--values-file", temp_file)

# ✅ Correct
values_json = json.dumps(rows)
run_gapi("sheets", "append", sid, range, "--values", values_json)

Bug 3: Google Sheets Strips Leading Zeros

Timestamp "2026-04-10 07:22" becomes "2026-04-10 7:22" in Google Sheets, breaking dedup. Always normalize timestamps before comparing:

def normalize_timestamp(ts):
    ts = str(ts).strip()
    try:
        dt = datetime.strptime(ts, '%Y-%m-%d %H:%M')
        return dt.strftime('%Y-%m-%d %H:%M')
    except ValueError:
        try:
            dt = datetime.strptime(ts, '%Y-%m-%d %-H:%M')
            return dt.strftime('%Y-%m-%d %H:%M')
        except ValueError:
            return ts

Bug 4: Dedup Key Must Include Amount

Same timestamp + different description is rare, but same timestamp + same description + different amount happens (e.g., two GoRide rides same day). Use timestamp|description|amount as unique key.

Bug 5: Dashboard Formulas (B3:C6) Must NOT Be Overwritten

User manages Dashboard formulas manually. Sync should ONLY update B7:C7 (Last Updated label + timestamp). NEVER write to B3:C6.

Correct Sync Logic

1. Parse Transactions.md → list of transactions with unique_key = timestamp|desc|amount
2. Read History sheet existing data (A2:C) → normalize timestamps → build existing_keys set
3. Filter: new_transactions = md_transactions NOT in existing_keys
4. Append only new rows to History!A:G
5. Update Dashboard!B7:C7 with timestamp (nothing else)

Output Format

Upon successful sync:

✅ Zenith Finance synced to Google Sheets
📊 Spreadsheet: https://docs.google.com/spreadsheets/d/[ID]
📅 Last sync: [timestamp]
📝 Transactions added: [count]
💰 Accounts updated: 6
📋 Dashboard: B3-C7 updated with Income/Expense/Savings/timestamps

Example Usage

After recording a new transaction in Zenith Finance:

# Manual sync
hermes-agent "sync zenith finance to google sheets"

# Or it can be triggered automatically after zenith-finance skill runs

Dependencies

• google-workspace skill (authenticated)
• Python packages: google-api-python-client, google-auth-httplib2, google-auth-oauthlib
• Available in Hermes agent venv

Critical: Sync Script Bugs Fixed (2026-04-13)

The sync_wrapper.py at ~/.hermes/skills/finance/zenith-finance-sync/scripts/sync_wrapper.py had bugs that broke sync. Fixes applied:

Bug 1: Regex only captured 6 groups, Transactions.md has 7 fields

Format: [YYYY-MM-DD HH:MM] | Desc | Amount | Type | Category | Source | Balance

# ❌ Wrong (missing Income/Expense type)
re.match(r'\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2})\]\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+)', line)

# ✅ Correct (7 capture groups)
re.match(r'\[(\d{4}-\d{2}-\d{2} \d{2}:\d{2})\]\s*\|\s*(.+?)\s*\|\s*\|(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+?)\s*\|\s*(.+)', line)

Bug 2: Unpacking mismatch

# ❌ Wrong (6 values)
timestamp_str, description, amount_str, category, source, balance_str = match.groups()

# ✅ Correct (7 values — added tx_type)
timestamp_str, description, amount_str, tx_type, category, source, balance_str = match.groups()

Bug 3: google_api.py expects --values not --values-file

# ❌ Wrong
run_gapi("sheets", "append", spreadsheet_id, "History!A:F", "--values-file", temp_file)

# ✅ Correct — pass JSON string directly
values_json = json.dumps(body["values"])
run_gapi("sheets", "append", spreadsheet_id, "History!A:G", "--values", values_json)

Bug 4: Dashboard formulas referenced wrong column

# ❌ Wrong (Income/Expense is column D, not E)
['Total Income', '=SUMIF(History!E:E, "Income", History!C:C)']
['Total Expenses', '=SUMIF(History!E:E, "Expense", History!C:C)']
['Net Savings', '=B2-B3']

# ✅ Correct (column D = Income/Expense type, SUMIFS syntax)
['Total Income', '=SUMIFS(History!C:C, History!D:D, "Income")']
['Total Expenses', '=SUMIFS(History!C:C, History!D:D, "Expense")']
['Net Savings', '=B3-B4']

Full corrected sync_wrapper.py

Reference file: ~/.hermes/skills/finance/zenith-finance-sync/scripts/sync_wrapper.py

Notes

• Sync is one-way: Obsidian → Google Sheets (not bidirectional)
• Formulas in Dashboard sheet allow for Google Sheets native calculations
• History sheet preserves full transaction audit trail
• To reset sync: delete ~/.hermes/zenith_spreadsheet_id.txt and re-run
• Clear ~/.hermes/zenith_sync_state.json after fixing script to force full re-sync

Troubleshooting & Verification

Based on common issues encountered during sync execution:

If hermes-agent command not found:

1. Check installation: which hermes-agent or look in ~/.hermes/bin/
2. Ensure Hermes is properly installed and in your PATH
3. You can manually trigger sync by running the skill directly if needed

Verification steps after sync:

1. Local data check: Confirm Transactions.md contains all expected transactions
2. Google Sheet structure: Verify 3 sheets exist (Dashboard, History, Category)
3. Dashboard formulas: Check exact cell positions match JSON specification:
   • C3: =SUMIFS(History!C:C, History!D:D, "Income")
   • C4: =SUMIFS(History!C:C, History!D:D, "Expense")
   • C5: =C3-C4
   • C6: =NOW()
   • C10: =SUMIFS(History!C:C, History!F:F, B10, History!D:D, "Income") - SUMIFS(History!C:C, History!F:F, B10, History!D:D, "Expense")
4. History sheet: New transactions should be appended to column A-G
5. Category sheet: Should contain unique categories from Transactions.md

Expected impact verification: After adding a transaction (e.g., 50,000 Expense, Housing, BCA):

• Total Expenses (C4) should increase by transaction amount
• Net Savings (C5) should decrease by transaction amount
• BCA Balance (C10) should decrease by transaction amount (if expense from BCA)

Common issues:

• Authentication problems: Re-run google-workspace setup skill
• Missing spreadsheet: Skill will create new one if ID file missing
• Sheet structure issues: Skill will recreate missing sheets
• Data parsing errors: Check Transactions.md format matches [YYYY-MM-DD HH:MM] | Description | RpAmount | Income/Expense | Category | Source | Balance