Automated Multi-Line Invoice Generation from Google Sheets to QuickBooks Online (n8n)

1. **Establish Credentials**: In n8n, go to 'Credentials' and set up 'Google Sheets OAuth2 API' and 'QuickBooks Online OAuth2 API'. Ensure your Google App has the 'Drive' and 'Sheets' scopes enabled. 2. **Configure Trigger**: Add a 'Google Sheets Trigger' node. Set the event to 'On Row Updated'. Select your Spreadsheet and Worksheet. Set the 'Poll Times' to your preferred frequency (e.g., every 5 minutes). 3. **Filter Logic**: Drag an 'If' node or 'Filter' node. Set the condition to check if the column `Status` is equal to `Ready`. This prevents draft rows from being processed. 4. **Search and Create Customer**: Add a 'QuickBooks Online' node. Set the Action to 'Get All' Customers. Use an n8n Expression to filter by email: `{{ $json.Email }}`. 5. **Conditional Branching (Check/Create)**: Use an 'If' node to check if the search returned a result. If not, follow with a QuickBooks 'Create' Customer node to ensure the invoice has a valid `CustomerRef`. 6. **Data Aggregation**: To handle multiple rows for one invoice, use the 'Aggregate' node. Group by `Customer Email` or `Customer ID`. Map fields like `Description`, `Amount`, and `Quantity` into a nested list that QuickBooks expects for line items. 7. **Map Line Items with Expressions**: In the QuickBooks 'Create Invoice' node, use the 'Fixed' or 'Expression' toggle for Line Items. Reference the aggregated data from the previous step. Use `{{ $node["Aggregate"].json["line_items"] }}`. 8. **Format Dates**: Ensure the date is in ISO 8601 format using n8n expressions: `{{ $now.format('yyyy-MM-dd') }}` for the `TxnDate` field. 9. **Finalize Invoice**: Set the `Financial Link` or `Memo` to include the Google Sheet URL for auditability. 10. **Feedback Loop**: Add a final Google Sheets 'Update Row' node to change the status from 'Ready' to 'Processed' and write back the QuickBooks `Invoice ID` and `Total Amount` for verification.

| Google Sheets Field | n8n Expression / Transformation | QuickBooks Online Field | Required | | :--- | :--- | :--- | :--- | | `Customer Email` | `{{ $json.email }}` | `PrimaryEmailAddr` / Search Key | Yes | | `Service Date` | `{{ $json.date.toISODate() }}` | `TxnDate` | Yes | | `Line Description` | `{{ $json.task_name }}` | `Line.Description` | Yes | | `Unit Price` | `{{ parseFloat($json.rate) }}` | `Line.Amount / UnitPrice` | Yes | | `Tax Code` | `{{ $json.taxable ? 'TAX' : 'NON' }}` | `Line.SalesItemLineDetail.TaxCodeRef` | No | | `Sheet Row URL` | `"Source: " + $json["row_url"]` | `PrivateNote` (Memo) | No |

• **Rate Limits**: QuickBooks Online API has strict rate limits. If processing >100 rows, use the 'Split in Batches' node in n8n (set batch size to 10-20) to stay within the 500 requests per minute threshold. • **Data Types**: QuickBooks expects numbers for amounts; Google Sheets often provides strings. Always wrap numeric mappings in `parseFloat()` or use the n8n 'Set' node to enforce Number types. • **Multiple Items**: If one sheet row represents one invoice, no aggregation is needed. If multiple rows apply to one invoice, the 'Aggregate' node is mandatory to avoid creating multiple separate invoices for the same person. • **Draft Mode**: n8n 'Test Step' results stay in the execution log. Always use a 'Test Sheet' before pointing the workflow to your live accounting ledger.

- [ ] **Credential Check**: Connection status shows 'Connected' for both Google and QuickBooks. - [ ] **Search Logic**: Test with a customer email that *doesn't* exist to ensure the 'Find or Create' branch works. - [ ] **Data Transformation**: View the 'Output' of the Aggregate node to ensure line items are formatted as a valid JSON array. - [ ] **Sandbox Test**: Run n8n in 'Test Workflow' mode using a QuickBooks Sandbox account first. - [ ] **Loop Prevention**: Verify the final 'Update Row' step successfully changes the status in Sheets to prevent the trigger from firing on the same data again.