A seamless, real-time workflow where updating a status in Google Sheets triggers the creation of a Xero invoice. The system automatically verifies if the customer exists, creates them if missing, and ensures all financial data is formatted correctly for accounting compliance.
The Problem
Manual entry of sales data from spreadsheets into Xero is time-consuming and prone to human error, such as typos in currency or incorrect account coding. Without automation, there is often a delay between a sale being closed in a sheet and the invoice being sent, impacting cash flow.
The Outcome
A seamless, real-time workflow where updating a status in Google Sheets triggers the creation of a Xero invoice. The system automatically verifies if the customer exists, creates them if missing, and ensures all financial data is formatted correctly for accounting compliance.
Step-by-Step Guide
1. **Create the Trigger**: Log into Zapier and click 'Create Zap'. Select **Google Sheets** as the trigger app and choose **Updated Spreadsheet Row**. Select your specific Spreadsheet and Worksheet.
2. **Set the Trigger Column**: In the trigger settings, set 'Trigger Column' to your Status column (e.g., 'Status') to ensure the Zap only runs when that specific value changes.
3. **Add a Filter**: Add a 'Filter by Zapier' step. Set it to only continue if the Status column text exactly matches 'Ready' or 'Send to Xero'.
4. **Search for Existing Customer**: Add an action for **Xero** and choose **Find Contact**. Search by the customer's email address from Google Sheets. **Crucial**: Check the box 'Create Xero Contact if it doesn't exist yet' to handle new customers automatically.
5. **Format Dates (Optional)**: If your sheet date format isn't YYYY-MM-DD, add a **Formatter by Zapier** step (Date/Time) to convert your spreadsheet date into the ISO-8601 format required by Xero.
6. **Create the Invoice**: Add a **Xero** action for **Create Invoice**. Map the Contact ID from the 'Find/Create Contact' step. Map the Line Items from your sheet rows. Ensure 'Status' is set to 'Draft' or 'Submitted' for review.
7. **Map Account Codes**: In the Line Item section of the Invoice step, manually enter or map your Xero Account Code (e.g., '200' for Sales) to ensure the revenue hits the correct Ledger.
8. **Update Source Row**: Add a final **Google Sheets** action for **Update Spreadsheet Row**. Use the 'Row ID' from step 1 and map the 'Invoice Number' or 'Xero URL' back to a 'Xero Link' column in your sheet for easy reference.
9. **Rename and Turn On**: Name your Zap and hit 'Publish'.