Automate Excel Sales Rows to Xero Invoices (Zapier)

1. **Prepare Microsoft Excel**: Ensure your file is saved in OneDrive for Business or SharePoint. Highlight your data range (including headers) and press `Ctrl+T` to format it as a **Table**. Name the table (e.g., 'SalesInvoices'). 2. **Create the Zap**: Log into Zapier and click 'Create Zap'. Select **Microsoft Excel** as the Trigger app. 3. **Configure the Trigger**: Select 'New Row' as the Event. Connect your account and select the Folder, Spreadsheet, and Table created in Step 1. 4. **Add a Filter by Zapier (Optional)**: Add a Filter step to ensure the Zap only continues if a specific column (like 'Customer Name') is not empty. This prevents partial rows from triggering the Zap. 5. **Format Dates with Formatter**: Excel often sends dates as serial numbers. Add a **Formatter by Zapier** step (Date/Time -> Format). Map the Excel date to the 'Input' and set the 'To Format' as `YYYY-MM-DD` for Xero compatibility. 6. **Search for Xero Contact**: Add a **Xero** action step and select 'Find Contact'. Choose the 'Customer Name' from Excel. Check the box 'Create Xero Contact if it doesn't exist yet' to map the email and address fields as backups. 7. **Create the Invoice**: Add another Xero step: 'Create Sales Invoice'. Link the Contact ID from the previous 'Find Contact' step. 8. **Map Line Items**: Map 'Description', 'Quantity', and 'Unit Price' from the Excel trigger. Hardcode the 'Account Code' (e.g., 200 for Sales) or map it from an Excel column. 9. **Set Status**: Set the 'Status' to 'Draft'. This allows an accountant to review the invoice before it is sent to the customer. 10. **Test and Publish**: Click 'Test Step' to verify an invoice appears in Xero Drafts. Turn the Zap On.

| Excel Field (Source) | Xero Field (Destination) | Requirement | Transformation Sync/Notes | | :--- | :--- | :--- | :--- | | `Customer Name` | Contact | Required | Use 'Find Contact' step first to get ID | | `Invoice Date` | Date | Required | Use Formatter: `YYYY-MM-DD` | | `Due Date` | Due Date | Optional | Use Formatter: `YYYY-MM-DD` | | `Description` | Line Description | Required | Map directly | | `Price` | Unit Amount | Required | Map directly (ensure no currency symbols) | | `Quantity` | Quantity | Required | Default to `1` if empty | | `Account Code` | Account Code | Required | Must match Xero Chart of Accounts (e.g., 200) | | `Tax Rate` | Tax Type | Optional | Must match Xero Tax Name (e.g., 'Output') |

* **The Table Rule**: Zapier strictly requires data to be in a formatted Table. If you add data below the table range, the Zap will not trigger. * **Column Header Changes**: If you rename a column header in Excel, the Zap mapping will break. Always 'Update' the Zap after structural changes. * **Serial Date Error**: Excel often passes dates as integers (e.g., 44561). Use Zapier's Formatter to convert these to standard ISO-8601 dates before sending to Xero. * **Xero Rate Limits**: Xero allows 60 requests per minute. If you bulk-paste 100 rows into Excel, Zapier may encounter rate limits. Consider using a 'Delay' step if processing high volumes. * **Account Codes**: Xero will fail if the Account Code (e.g., 200) doesn't exactly match your Chart of Accounts. Use a dropdown in Excel to ensure data integrity.

- [ ] The Excel file is stored in a 'Business' account (Personal OneDrive has limited Zapier support). - [ ] The data is inside an actual 'Table' object in Excel. - [ ] The 'Find Contact' step in Zapier correctly identifies existing customers to avoid duplicates. - [ ] The Xero Invoice appears in 'Draft' status (prevents accidental billing during testing). - [ ] Date formats in Xero match the Excel input after being processed by Formatter. - [ ] Task History in Zapier shows 'Success' for a test run with a real row of data.