Automated Excel Sales Data to Xero Invoice Sync (Make.com)

1. **Establish Connections**: In Make.com, add the **Microsoft Excel** module and sign into your Office 365/OneDrive account. Repeat for **Xero**, ensuring you authorize access to the correct Organization via OAuth2. 2. **Configure Spreadsheet Trigger**: Add the 'Watch Table Rows' module. Select your Workbook and the specific Table. Ensure your Excel data is formatted as a 'Table' (Ctrl+T) or Make won't detect the headers. 3. **Search for Contact**: Add a Xero 'Search Contacts' module. Use the mapping `Name = {{row.CustomerName}}`. This prevents creating duplicate contact records for every invoice. 4. **Add Router Logic**: Use a Router to check if the Contact ID exists. If not, add a Xero 'Create a Contact' module using data from the Excel row. 5. **Format Dates for Xero**: In the Xero 'Create an Invoice' module, use the Make.com built-in function `formatDate(row.Date; "YYYY-MM-DD")`. Xero is strict about the date format; passing a standard Excel serial number will cause a 400 error. 6. **Map Line Items**: Map the 'Line Amount', 'Account Code' (e.g., 200), and 'Tax Type'. Use the `ifempty()` function for optional fields to provide default values. 7. **Implement Error Handling**: Right-click the Xero module and select 'Add error handler'. Use the **Breakthrough** or **Ignore** directive to prevent the entire scenario from stopping if one row has an invalid account code. 8. **Update Source Status**: Add an Excel 'Update a Row' module at the end. Map the `Row ID` from the trigger and update a 'Status' column to 'Synced' and a 'Xero ID' column with the newly created Invoice ID for traceability. 9. **Schedule the Run**: Set the scenario to run 'Immediately' (if using Webhooks) or at a specific interval (e.g., every 15 minutes) depending on your data volume.

| Excel Field | Xero Field | Transformation / Notes | | :--- | :--- | :--- | | Customer Name | Contact Name | Required. Use `trim()` to remove accidental spaces. | | Invoice Date | Date | `formatDate(value; "YYYY-MM-DD")` | | Due Date | Due Date | `formatDate(value; "YYYY-MM-DD")` | | Description | Line Item Description | Default: "Sales Entry" if empty. | | Total Price | Unit Amount | Ensure data type is 'Number' in Make. | | Account Code | Account Code | **Critical**: Must match Xero Chart of Accounts (e.g., 200). | | Tax Type | Tax Type | Use Xero's internal strings (e.g., `OUTPUT`, `NONE`). |

* **The Serial Date Trap**: Excel often sends dates as numbers (e.g., 45123). Use the `addDays(1899-12-30; DateValue)` formula in Make if `formatDate` fails on raw Excel numbers. * **Xero Rate Limits**: Xero has a limit of 5,000 API calls per day. If syncing thousands of rows, use an **Array Aggregator** to batch updates or schedule the scenario to run during off-peak hours. * **Table Requirement**: Make.com cannot see data in Excel unless it is inside a named 'Table'. Floating data in cells will result in an 'Empty Bundle' error. * **Account Code Validation**: If the Excel sheet contains an Account Code not present in Xero, the module will fail. Use a **Data Store** in Make to map 'Excel Categories' to 'Xero Codes' for safer routing.

- [ ] Run 'Run Once' with a single test row in Excel. - [ ] Check the 'Execution Log' to ensure the Date transformation resulted in YYYY-MM-DD. - [ ] Verify the Contact was either found or created in Xero. - [ ] Log into Xero and confirm the Draft Invoice appears in the 'Business > Invoices' section with correct tax totals. - [ ] Confirm the Excel row was updated with a 'Synced' status to prevent duplicate processing on the next run.