Excel to QuickBooks Online Automated Invoicing (n8n)

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.

| Category | Excel Field | QuickBooks Field | n8n Expression / Strategy | | :--- | :--- | :--- | :--- | | **Customer** | `Customer_Name` | `DisplayName` | `{{ $json["Customer_Name"] }}` | | **Contact** | `Customer_Email` | `PrimaryEmailAddr` | `{{ $json["Customer_Email"].toLowerCase() }}` | | **Line Item** | `Item_Amount` | `Amount` | `{{ parseFloat($json["Item_Amount"]) }}` | | **Line Item** | `Item_Name` | `Description` | `{{ $json["Item_Name"] }}` | | **Global** | `Invoice_Date` | `TxnDate` | `{{ $now.toFormat('yyyy-MM-dd') }}` (Default) | | **Metadata** | `Excel_Row_ID` | `PrivateNote` | `{{ "Synced from Excel Row: " + $json["row_index"] }}` |

• **Credential Scope**: Ensure your Microsoft OAuth includes `Files.ReadWrite.All` or you won't be able to update the row after processing. • **Data Formatting**: QuickBooks requires numeric values for amounts. If Excel provides a string (e.g., "$100.00"), use `.replace(/[^\d.]/g, '')` in an n8n Expression. • **Polling Delay**: The Excel Trigger is polling-based (usually 1-5 mins). Do not expect instant 'real-time' triggers on the community version. • **Missing Product IDs**: QuickBooks Invoice creation will fail if the `ItemRef` (Product/Service ID) does not match exactly what is in your QuickBooks Chart of Accounts. • **Rate Limits**: QuickBooks API has strict throttling. If processing >100 rows, add a 'Wait' node or use the 'Split In Batches' node (batch size: 10) to avoid 429 Errors.

- [ ] **Table Check**: My Excel data is formatted as an official Excel Table (Insert > Table). - [ ] **Test Trigger**: Run the 'Microsoft Excel' node manually; verify it fetches the correct row data. - [ ] **Expression Validation**: In the QuickBooks node, verify that price/amount fields show as 'Number' type in the n8n expression preview. - [ ] **Customer Branching**: Delete a test customer in QBO and run the flow to ensure the 'Create Customer' logic triggers correctly. - [ ] **Write-back Confirmation**: Confirm the QuickBooks Invoice ID appears in the Excel column after a successful run. - [ ] **Execution Log**: Check the 'Executions' tab in n8n for any red 'Error' bubbles during the end-to-end test.