Automated Excel Row to Xero Invoice Sync (n8n)

1. **Establish Credentials**: In n8n, go to 'Credentials' and create 'Microsoft Online' (OAuth2) and 'Xero' (OAuth2) connections. Ensure your Xero app has `acc.transactions` and `contact.read` scopes. 2. **Setup Excel Trigger**: Add the 'Microsoft Excel Online Trigger' node. Set the 'Resource' to 'Row' and 'Event' to 'Created'. Select your Spreadsheet and the specific **Table** name (not just the sheet name). 3. **Search for Contact**: Add a 'Xero' node. Action: 'Get Many' for 'Contact'. Use a filter expression: `{{ $json["Customer Name"] }}` to check if the customer already exists in Xero. 4. **Conditional Logic (IF Node)**: Insert an 'IF' node after the Search. If the Xero search returns an empty array, route to a Xero 'Create Contact' node; otherwise, proceed to invoice creation. 5. **Format Dates with Expressions**: n8n expressions are needed for date compatibility. Use `{{ $today.format('yyyy-MM-dd') }}` for the Invoice Date and `{{ $json["Due Date"].toDate().toISOString() }}` to ensure Xero accepts the Excel date format. 6. **Map Invoice Line Items**: Add a 'Xero' node with Resource: 'Invoice' and Action: 'Create'. Set 'Status' to 'DRAFT'. Map the 'Line Items' using an n8n expression to build the JSON array containing Description, Quantity, and UnitAmount. 7. **Include Account Codes**: Map the 'AccountCode' field. In n8n, use a fixed value like `200` (Sales) or a mapping expression if the code exists in your Excel column. 8. **Implement Error Handling**: Create an 'Error Trigger' workflow or use the 'On Error' setting on the Xero node to 'Route to another node' (e.g., Slack or Email) if the API returns a 400 error due to invalid account codes or missing data.

| Excel Field | Xero Field | Transformation / n8n Syntax | Req? | | :--- | :--- | :--- | :--- | | Customer Name | Contact Name | `{{ $json["Customer Name"] }}` | Yes | | Row ID | Reference | `Excel_ID_{{ $json["id"] }}` (Prevents Duplicates) | No | | Invoice Date | Date | `{{ $now.format('yyyy-MM-dd') }}` | Yes | | Due Date | DueDate | `{{ $json["Due Date"].format('yyyy-MM-dd') }}` | Yes | | Description | Line Item Description| `{{ $json["Item"] }}` | Yes | | Price | UnitAmount | `{{ parseFloat($json["Price"]) }}` | Yes | | Qty | Quantity | `{{ parseInt($json["Qty"]) }}` | Yes | | Account Code | AccountCode | `{{ $json["Code"] || '200' }}` (Default to 200) | Yes |

* **The Table Requirement**: Excel nodes only work with data inside a formal 'Format as Table' object. Standard cell ranges will be invisible to the trigger. * **Rate Limiting**: Xero has a limit of 5,000 API calls per day. If processing thousands of rows, use the 'Split In Batches' node to throttle requests. * **Date Serial Numbers**: Excel often sends dates as numbers (e.g., 45123). Use n8n's `DateTime` node or Luxon expressions to convert these to `YYYY-MM-DD` before hitting Xero. * **Duplicate Prevention**: Map the Excel 'Row ID' or a unique 'Order Number' to the Xero 'Reference' field. Before creating, use a Xero node to search for an invoice with that Reference to avoid double-billing.

- [ ] **Trigger Test**: Add a row to the Excel Table and use 'Fetch Test Event' in n8n to ensure data is received. - [ ] **Credential Check**: Ensure Xero Organization is correctly selected in the node (especially if you manage multiple companies). - [ ] **Data Type Validation**: Verify in the 'Execution Log' that Quantity is a Number and UnitAmount is a Decimal, not Strings. - [ ] **Draft Creation**: Confirm a 'Draft' invoice appears in Xero > Business > Invoices > Draft. - [ ] **Error Path**: Intentionally leave an Excel 'Customer Name' blank to trigger your error notification node.