A fully automated workflow where updating a Google Sheet row triggers a search for the customer in Xero, creates them if missing, and generates a professionally formatted invoice with correct tax codes and line items linked back to the source data.
The Problem
Manual entry of sales data from spreadsheets into Xero is time-consuming and prone to human error, such as typos in amounts or duplicate contact creation. Businesses often struggle to sync multi-line items originating from a single row or form submission into a structured accounting format.
The Outcome
A fully automated workflow where updating a Google Sheet row triggers a search for the customer in Xero, creates them if missing, and generates a professionally formatted invoice with correct tax codes and line items linked back to the source data.
Step-by-Step Guide
1. **Create a New Scenario**: Log into your Make.com dashboard and click '+ Create a new scenario'.
2. **Configure Google Sheets Trigger**: Add the 'Google Sheets > Watch Changes' module (requires the Make Chrome Extension) or 'Watch Rows'. Select your Spreadsheet and Sheet Name. Set the 'Limit' to 1 to process one invoice at a time during testing.
3. **Establish Connections**: Click 'Add' on the Google Sheets module to authorize your Google account. Do the same for the Xero modules using your Xero developer credentials or the standard OAuth flow.
4. **Add a Filter**: Click the line between the Sheets module and the next step. Label it 'Check Status'. Set the condition to only allow rows where the 'Status' column (e.g., Column E) exactly matches 'Send to Xero'.
5. **Search/Create Contact Logic**: Add the 'Xero > Search for a Contact' module. Search by 'Name' or 'Email' using the value from your Google Sheet.
6. **Insert a Router**: Add a Router module.
- **Route 1**: If the search results are empty, add 'Xero > Create a Contact'.
- **Route 2**: If the search results exist, proceed to the invoice step.
7. **Map the Invoice Module**: Add 'Xero > Create an Invoice'. Map the Contact ID from the previous steps. Use `formatDate(row_date; YYYY-MM-DD)` for the date fields to ensure Xero compatibility.
8. **Handle Line Items**: If your sheet has multiple item columns, use the 'Add item' button in the Xero module. Map 'Description', 'Quantity', 'Unit Amount', and importantly, the 'Account Code' (e.g., 200 for Sales).
9. **Update Source Row**: Add a final 'Google Sheets > Update a Row' module. Map the 'Invoice Number' or 'Invoice ID' from the Xero module back to a 'Xero Ref' column in your sheet. Set the status to 'Synced' to prevent loops.
10. **Error Handling**: Right-click the Xero Invoice module and select 'Add error handler'. Use a 'Break' or 'Rollback' directive, or simply an 'Update Row' module to log the error message back to the Google Sheet for manual review.