Automated Sales Invoicing from Google Sheets to Xero (Zapier)

1. **Create the Zap**: Log into Zapier and click 'Create Zap'. Name it 'Sheet Row to Xero Invoice'. 2. **Setup Trigger**: Choose **Google Sheets** and the event **Updated Spreadsheet Row**. Select your Spreadsheet and Worksheet. Set the 'Trigger Column' to your Status column (e.g., 'Status'). 3. **Add Filter**: Add a **Filter by Zapier** step. Configure it to only continue if 'Status' (from Step 2) exactly matches 'Approved'. 4. **Search/Create Contact**: Add a **Xero** action: **Find Contact**. Search by 'Contact Name' or 'Email'. Check the box 'Create Xero Contact if it doesn't exist yet' and map the Sheets name/email fields. 5. **Format Line Items (Optional)**: If your sheet doesn't have prices formatted as decimals, add **Formatter by Zapier** (Numbers > Currency) to ensure the value is Xero-compliant. 6. **Create Invoice**: Add another **Xero** action: **Create Type: Outbound Invoice**. - Map 'Contact ID' from Step 4. - Set 'Status' to 'Draft'. - Map 'Line Item Description', 'Quantity', and 'Unit Amount' from the Sheet. - Assign 'Account Code' (e.g., 200 for Sales). 7. **Write Back to Sheet**: Add a **Google Sheets** action: **Update Spreadsheet Row**. Use the Row ID from Step 2 and update a 'Xero Invoice ID' column with the ID generated in Step 6. This prevents duplicates.

| Google Sheets Field | Xero Field | Transformation / Required | | :--- | :--- | :--- | | Customer Name | Contact Name | **Required** (Exact match) | | Customer Email | Email Address | Optional (Used for Find Search) | | Status Column | (Filter Trigger) | **Condition**: Only if 'Approved' | | Unit Variable | UnitAmount | **Required** (Use Formatter if contains ' ) | | Qty Header | Quantity | **Required** (Number) | | Account Code | AccountCode | **Required** (Must match Xero COA, e.g., 200) | | Row ID | N/A | Used in Step 7 for internal tracking |

- **Account Code Mismatch**: Xero will error if the Account Code (e.g., 200) provided in the Sheet does not exist in your Xero Chart of Accounts. - **Rate Limiting**: Xero has a limit of 60 requests per minute. If you bulk-update 100 rows at once, Zapier might 'throttle' or error. Use a small delay for large batches. - **Contact Deduplication**: Always use the 'Find or Create' step. If you use 'Create' only, the Zap will fail every time a repeat customer buys from you. - **Draft vs. Authorised**: This Zap creates 'Draft' invoices. You must manually approve them in Xero unless you set the status to 'Authorised', which requires tax rates to be perfectly mapped.

- [ ] **Test Trigger**: Use the 'Test Trigger' button in Zapier to ensure it sees a row marked 'Approved'. - [ ] **Check Contact Loop**: Verify that the 'Find or Create' step successfully finds an existing contact without creating a duplicate. - [ ] **Verify Line Items**: Check the 'Draft' invoice in Xero to ensure the math (Qty x Price) matches your Sheet. - [ ] **History Logs**: Check 'Zap History' to confirm the 'Update Row' step correctly writes the Xero Invoice ID back to the Sheet. - [ ] **Audit Filter**: Intentionally change a row to 'Pending' and ensure the Zap history shows 'Filtered out' (Success).