Automated Chart of Accounts & Invoice Sync from Google Sheets (n8n)

1. **Create Workflow**: In n8n, click 'Create New Workflow' and name it 'Sheets to QuickBooks Sync'. 2. **Configure Google Sheets Trigger**: Add the 'Google Sheets Trigger' node. Set the event to 'On Row Added'. Connect your Google Credentials and select the specific Spreadsheet and Sheet Name. 3. **Add an Edit Fields Node**: Use an 'Edit Fields' (formerly Set) node to map sheet columns to clean variable names. Use n8n Expressions (e.g., `{{ $json["Account Name"] }}`) to cast data types (e.g., ensuring 'Amount' is a Number). 4. **Credential Setup**: Navigate to 'Credentials' in n8n and add 'QuickBooks Online OAuth2'. Follow the redirect to authorize n8n to access your QuickBooks company file. 5. **Duplicate Prevention Logic**: Add a QuickBooks Node with the 'Get Many' action and a Filter expression to check if the `Name` or `ExternalID` already exists in QuickBooks. 6. **Implement IF Node**: Use an 'If' node to check the result of the previous search. If 'List is Empty', proceed to create; otherwise, skip or update. 7. **Configure QuickBooks Action**: Add a 'QuickBooks' node. Select the Resource (e.g., Account or Invoice) and Operation (Create). Map fields using the n8n expression picker from the Edit Fields node. 8. **Write-Back Mechanism**: Add a final 'Google Sheets' node with the 'Update Row' action. Map the QuickBooks `Id` returned from the creation step to a 'QBO_ID' column in your sheet to prevent future duplicates. 9. **Configure Error Handling**: Click the '...' on the QuickBooks node and select 'On Error -> Continue'. Add an 'Error Trigger' node to handle system-wide failures and send a notification to your preferred channel.

| Google Sheet Column | QuickBooks Field | n8n Expression / Transformation | Requirement | | :--- | :--- | :--- | :--- | | Account Name | `Name` | `{{ $json["Account Name"].trim() }}` | Required | | Account Number | `AcctNum` | `{{ $json["Code"].toString() }}` | Optional | | Category | `Classification` | `{{ $json["Type"] }}` (Asset, Liability, etc) | Required | | Description | `Description` | `{{ $json["Notes"] || 'No description provided' }}` | Optional | | Balance | `OpeningBalance` | `{{ parseFloat($json["Balance"]) }}` | Optional | | Currency | `CurrencyRef` | `{{ $json["Currency"] || 'USD' }}` | Required |

* **Memory Limits**: When processing thousands of rows, avoid the 'All at Once' setting. Use the 'Split in Batches' node (batch size: 50) to prevent n8n service crashes. * **OAuth Refresh**: QuickBooks OAuth tokens expire. If self-hosting, ensure your n8n instance is accessible via a static URL for the redirect/refresh flow to work. * **String Truncation**: QuickBooks has a strict 100-character limit for `Name` fields. Use `{{ $value.substring(0, 100) }}` in your mapping to avoid 400 Bad Request errors. * **Polling vs. Webhooks**: The Google Sheets trigger in n8n usually polls. Adjust the 'Poll Times' in the trigger settings to balance between 'near-instant' and server resource consumption.

- [ ] **Node Execution**: Run the workflow manually once and ensure all nodes turn green. - [ ] **Data Integrity**: Check the QuickBooks 'Chart of Accounts' or 'Invoices' list to verify the test record appeared correctly. - [ ] **Write-Back Confirmation**: Verify that the Google Sheet row now contains the QuickBooks ID in the designated column. - [ ] **Validation Check**: Input a purposely malformed row (e.g., text in a currency column) to ensure your error handler catches it. - [ ] **Activation**: Toggle the workflow to 'Active' so it runs automatically on the set trigger interval.