Bulk Invoice Generation from Excel to QuickBooks Online (Make.com)

1. **Create Scenario**: Log into your Make.com dashboard and click 'Create a new scenario'. 2. **Module 1 - Excel Online**: Add the 'Watch Table Rows' module. Connect your Microsoft account, select your Workbook (stored in OneDrive/SharePoint), and select the designated Table. Ensure your Excel data is formatted as an 'Official Table' (Ctrl+T). 3. **Module 2 - QuickBooks Online (Search)**: Add 'Search Customers'. Map the 'Customer Name' or 'Email' from the Excel module to the search filter. This prevents duplicate contact creation. 4. **Module 3 - Router**: Add a Router. If the 'Search' result is empty, route to 'Create Customer'. If a contact exists, proceed to 'Create Invoice'. 5. **Module 4 - QuickBooks Online (Create Invoice)**: Use the ID from the Search module (if found) or the Create Customer module. Map 'Invoice Date', 'Due Date', and 'Line Items'. For Line Items, use the 'Add Item' function within the QuickBooks module. 6. **Data Transformation**: Use the `formatDate()` function for date fields to ensure they match QuickBooks' required format `YYYY-MM-DD` (e.g., `{{formatDate(1.Date; "YYYY-MM-DD")}}`). 7. **Error Handling**: Right-click the QuickBooks Create Invoice module and select 'Add error handler'. Choose the 'Break' or 'Rollback' directive to ensure you are notified of failures (like invalid Tax Codes) without stopping the scenario. 8. **Update Status**: Add a final Excel 'Update a Row' module. Map the 'Row ID' from the trigger and change a 'Sync Status' column to 'Finalized' or 'Invoiced' to prevent re-processing.

| QuickBooks Field | Excel Source Field | Transformation / Logic | | :--- | :--- | :--- | | **Customer Ref** | Customer Name/ID | Use `map()` to find ID from Search module | | **Transaction Date** | Invoice Date | `{{formatDate(now; "YYYY-MM-DD")}}` | | **Line: Amount** | Total Price | `{{parseNumber(Total)}}` (Ensure numeric) | | **Line: Description** | Description | Direct mapping | | **Private Note** | Project ID | `{{1.Project}} - From Excel` | | **Currency** | Currency Code | Default to 'USD' if empty |

• **Table Limitations**: Make.com cannot read Excel files unless the data is inside a named 'Table'. Formatting the sheet as a table is mandatory. • **Operation Usage**: If you have 100 rows, Make.com will use 100+ operations. Use a 'Filter' after the trigger to only process rows where 'Status' is not 'Complete'. • **Connection Refresh**: Microsoft IDs for files can change if you move the file between folders, which will break the Make.com module. Keep the file in a static directory. • **The Price/Product Gap**: Ensure the 'Product/Service' names in Excel match your QuickBooks 'Item' list exactly, or use the Internal ID for the mapping.

- [ ] **Run Once**: Click 'Run Once' and check if the Excel 'Status' column updates successfully. - [ ] **Data Check**: Verify in QuickBooks that the Invoice 'Total' matches the Excel 'Total' exactly. - [ ] **Duplicate Test**: Run the scenario twice with the same data to ensure a second customer record isn't created. - [ ] **Formatting**: Check the 'History' tab in Make to ensure dates were parsed correctly without 'Invalid Date' errors.