A fully automated workflow that detects new 'Ready' rows in Excel Online, validates the customer data, and creates line-item invoices in QuickBooks Online. This ensures 100% data accuracy and reduces the billing cycle from hours to seconds.
The Problem
Manual entry of sales data from spreadsheets into accounting software is prone to human error, resulting in billing discrepancies and delayed payments. For businesses managing high-volume billable items or project milestones in Excel, this manual sync creates a significant administrative bottleneck.
The Outcome
A fully automated workflow that detects new 'Ready' rows in Excel Online, validates the customer data, and creates line-item invoices in QuickBooks Online. This ensures 100% data accuracy and reduces the billing cycle from hours to seconds.
Step-by-Step Guide
1. **Set Up Credentials**: Navigate to 'Settings > Credentials' in n8n. Create 'Microsoft Account' (OAuth2) for Excel and 'QuickBooks Online' (OAuth2) credentials. Ensure the QuickBooks app is in 'Production' or 'Sandboxed' mode appropriately.
2. **Excel Trigger Node**: Add the 'Microsoft Excel' node. Set Resource to 'Row', Operation to 'On Row Added'. Select your Workbook and the specific Table (Note: Data must be in a 'Table' format in Excel Online).
3. **Data Filter (IF Node)**: Add an 'IF' node after the trigger to check if a specific column (e.g., 'Status') equals 'Ready' or 'Approved'. This prevents incomplete rows from firing the automation.
4. **Search Customer Node**: Add a 'QuickBooks Online' node. Set Operation to 'Get All' Customers. Use an n8n Expression to filter by email: `{{ $json["Email"] }}` to check if the client already exists.
5. **Conditional Branching (IF Node)**: Use an 'IF' node to check if the previous step returned any results. If empty (`true`), route to a 'Create Customer' node; otherwise, proceed to invoice creation.
6. **Create QuickBooks Customer (Optional)**: If the customer doesn't exist, use the QuickBooks node to 'Create' a customer, mapping fields like `Name`, `Email`, and `Phone` from the Excel trigger.
7. **Map Invoice Line Items**: Add the 'QuickBooks Online' node, Operation 'Create' Invoice. In the 'Line Items' section, use n8n Expressions to map: `Amount` -> `{{ $json["Total"] }}`, `Description` -> `{{ $json["Description"] }}`, and `Item Ref` -> your specific QBO Product ID.
8. **Update Excel Row**: Add another Microsoft Excel node. Set Operation to 'Update'. Map the 'Row Index' from the trigger and write the 'QuickBooks Invoice ID' back to a 'Sync ID' column in Excel to prevent duplicates.
9. **Error Handling**: Create an 'Error Trigger' node. Connect it to a 'Slack' or 'Email' node to notify the admin if a QuickBooks API limit is hit or if a product ID is missing.