Automated Sales to Xero Invoicing via Google Sheets (n8n)

1. **Establish Credentials**: In n8n, go to 'Credentials' and set up 'Google Sheets OAuth2 API' and 'Xero OAuth2 API'. Ensure the Xero Scopes include `accounting.transactions` and `accounting.contacts`. 2. **Configure Trigger**: Add a **Google Sheets Trigger Node**. Set 'Poll Times' to every 5 minutes. Set the 'Document' and 'Sheet', and choose the event 'Row Updated'. 3. **Implement Data Filtering**: Add an **If Node** to check if the `Status` column equals `Closed-Won` and if the `Xero Invoice ID` column is empty (to prevent duplicates). 4. **Search/Create Contact**: Add a **Xero Node**. Set Resource to `Contact` and Operation to `Get All`. Use an expression in the 'Filter' field: `EmailAddress == "{{ $json.Email }}"`. 5. **Branch Logic for Contacts**: Add another **If Node**. If no contact is returned, use a Xero node to `Create` a contact using data from the sheet; if a contact exists, pass their `contactID` forward. 6. **Map Line Items**: Add a **Set Node** to format the invoice data. n8n requires the `LineItems` to be an array of objects. Use the expression: `{{ [ { "Description": $json.Service, "Quantity": $json.Qty, "UnitAmount": $json.Price, "AccountCode": "200" } ] }}`. 7. **Generate Invoice**: Add a **Xero Node**. Resource: `Invoice`, Operation: `Create`. Link the `ContactID` from the previous step and the `LineItems` array from the Set node. Set 'Status' to `DRAFT` for manual review. 8. **Write-back to Sheet**: Add a **Google Sheets Node**. Operation: `Update`. Map the `InvoiceID` and `InvoiceNumber` back to the original row using the `row_number` to ensure the automation doesn't re-process this record. 9. **Global Error Handling**: Create an 'Error Workflow' or add an **Error Trigger Node** that sends a Slack message if the Xero API returns a 400 error (usually due to invalid tax codes or closed periods).

| Google Sheets Field | Xero API Field | n8n Expression / Transformation | | :--- | :--- | :--- | | `Customer Name` | `ContactName` | `{{ $json["Customer Name"] }}` | | `Email` | `EmailAddress` | `{{ $json["Email"].toLowerCase() }}` | | `Total Price` | `UnitAmount` | `{{ parseFloat($json["Total"]) }}` (Required: Number) | | `Date` | `Date` | `{{ $now.toFormat('yyyy-MM-dd') }}` | | `Account Code` | `AccountCode` | Default: `200` (Sales) | | `Invoice ID` | `Status` | Hardcoded: `DRAFT` |

* **Data Type Strictness**: Xero will fail if numbers (Price/Quantity) are sent as strings. Use `{{ Number($json.field) }}` in n8n expressions. * **Rate Limiting**: Xero has a limit of 60 calls per minute. If processing bulk rows, use the **Split In Batches Node** in n8n with a 1-second delay between items. * **OAuth Refresh**: If self-hosting n8n, ensure your `WEBHOOK_URL` is configured correctly in your environment variables, or Xero's OAuth token will fail to refresh after 30 minutes. * **Tax Codes**: Ensure the 'TaxType' sent from Sheets matches Xero's internal IDs exactly (e.g., `OUTPUT` vs `Tax on Sales`).

- [ ] **Test Trigger**: Manually update a row in Google Sheets and click 'Execute Workflow' to see if n8n picks up the change. - [ ] **Contact Lookup**: Verify that a second run for the same customer uses the existing ContactID instead of creating a duplicate. - [ ] **Execution Log**: Check the 'Executions' tab in n8n to ensure the JSON sent to Xero contains a valid `LineItems` array. - [ ] **Xero Verification**: Open Xero > Business > Invoices > Draft to confirm the invoice appears with correct totals. - [ ] **Locking Mechanism**: Ensure the 'Xero Status' column in your sheet updates to avoid infinite loops.