Automation of financial accounting: Jobber → Google Sheet via N8N
A comprehensive and fault-tolerant automation system has been implemented for the cleaning business. The main task is to set up automatic data transfer from the CRM Jobber to Google Sheets using the n8n platform for accurate calculation of payments to cleaning teams (percentage and hourly model).
What has been done (Technical implementation details):
- Sectional data model ("Constructor"): The visit row in the table is divided into independent zones (Visit, Invoice, Payment, Calculation). Each n8n flow updates only its fields, which eliminates conflicts during parallel processing of webhooks.
- Dynamic addressing (Header-based): n8n reads column numbers by keys in the first row of the table. The manager can freely swap columns — the integration will not break.
- Idempotency and duplicate protection: Unique keys event_id are generated, and atomic upsert mechanics are implemented through n8n Data Table. Repeated webhooks are automatically filtered out, preventing duplicates.
- Flexible financial distribution:
- An algorithm has been written to determine the Cash Taker among several teams on the visit based on their type and ID.
- Logic for splitting multi-visit invoices has been implemented (if one invoice is issued for several cleanings, amounts are divided equally).
- Manual Editing mode (Manual Lock): A data protection mechanism has been created. If the manager checks the manual correction box on any visit row, the automation completely freezes updates to this object to avoid overwriting manual edits.
- Fail-safe and monitoring:
- An error queue — Dead Letter Queue (Failed_Events) has been integrated directly into Google Sheets for convenient analysis by the manager.
- An Exponential Backoff retry policy has been set up to bypass API limits (429, 5xx). A system of 24 custom Telegram alerts (divided by branches: Info, Warnings, Errors) has been created for instant notification of discrepancies in amounts, absence of teams in directories, or API errors.
- Load optimization: The calculation of final payments (V), cleaning amounts (N), and discrepancies (R) has been completely offloaded to Google Sheets formulas within the row, reducing the number of API requests and speeding up system performance.
Technology stack:
- n8n (Workflow creation, n8n Data Table for logging and deduplication)
- Jobber API (GraphQL, Webhooks, Event verification via HMAC-SHA256)
- Google Sheets API (Batch updates, working with formulas)
- JavaScript / Node.js (Regular expressions, parsing line items, and data filtering logic within n8n nodes)
- Telegram Bot API (Routing notifications by topics)
Business result:
The client received a fully autonomous financial showcase. Manual work by the accountant/manager has been minimized — the system itself collects data on completed work, calculates net income after parking and taxes, divides tips, and calculates salaries for cleaners. All non-standard cases or financial discrepancies are highlighted via Telegram, ensuring 100% control over finances.
What has been done (Technical implementation details):
- Sectional data model ("Constructor"): The visit row in the table is divided into independent zones (Visit, Invoice, Payment, Calculation). Each n8n flow updates only its fields, which eliminates conflicts during parallel processing of webhooks.
- Dynamic addressing (Header-based): n8n reads column numbers by keys in the first row of the table. The manager can freely swap columns — the integration will not break.
- Idempotency and duplicate protection: Unique keys event_id are generated, and atomic upsert mechanics are implemented through n8n Data Table. Repeated webhooks are automatically filtered out, preventing duplicates.
- Flexible financial distribution:
- An algorithm has been written to determine the Cash Taker among several teams on the visit based on their type and ID.
- Logic for splitting multi-visit invoices has been implemented (if one invoice is issued for several cleanings, amounts are divided equally).
- Manual Editing mode (Manual Lock): A data protection mechanism has been created. If the manager checks the manual correction box on any visit row, the automation completely freezes updates to this object to avoid overwriting manual edits.
- Fail-safe and monitoring:
- An error queue — Dead Letter Queue (Failed_Events) has been integrated directly into Google Sheets for convenient analysis by the manager.
- An Exponential Backoff retry policy has been set up to bypass API limits (429, 5xx). A system of 24 custom Telegram alerts (divided by branches: Info, Warnings, Errors) has been created for instant notification of discrepancies in amounts, absence of teams in directories, or API errors.
- Load optimization: The calculation of final payments (V), cleaning amounts (N), and discrepancies (R) has been completely offloaded to Google Sheets formulas within the row, reducing the number of API requests and speeding up system performance.
Technology stack:
- n8n (Workflow creation, n8n Data Table for logging and deduplication)
- Jobber API (GraphQL, Webhooks, Event verification via HMAC-SHA256)
- Google Sheets API (Batch updates, working with formulas)
- JavaScript / Node.js (Regular expressions, parsing line items, and data filtering logic within n8n nodes)
- Telegram Bot API (Routing notifications by topics)
Business result:
The client received a fully autonomous financial showcase. Manual work by the accountant/manager has been minimized — the system itself collects data on completed work, calculates net income after parking and taxes, divides tips, and calculates salaries for cleaners. All non-standard cases or financial discrepancies are highlighted via Telegram, ensuring 100% control over finances.
Odessa 
Available for hire