A fully automated pipeline where updating a 'Status' in Google Sheets triggers an immediate search for contacts in Xero, creates missing records, and generates a draft invoice with precise line-items and tax codes, all while logging the Xero Invoice ID back to the sheet for auditability.
The Problem
Manual data entry from sales trackers to accounting software is prone to human error, leading to incorrect tax reporting and delayed billing. Businesses often struggle to sync line-item details and maintain a clean CRM contact list across both platforms.
The Outcome
A fully automated pipeline where updating a 'Status' in Google Sheets triggers an immediate search for contacts in Xero, creates missing records, and generates a draft invoice with precise line-items and tax codes, all while logging the Xero Invoice ID back to the sheet for auditability.
Step-by-Step Guide
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).