adityahimaone/hermes-agent-rtk-caveman/zenith-finance-gsheets-sync

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

1. History (second sheet): Full transaction log

• Columns: Timestamp, Description, Amount, Income/Expense, Category, Source, Balance
• Append-only format matching Transactions.md

1. 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

1. Verification: Confirm update success

Detailed Steps\n\n### 1. Authentication Validation\n``bash\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:

``python 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)