Excel-to-QuickBooks Automated Invoicing (Zapier)

1. **Prepare Excel**: Ensure your workbook is in OneDrive for Business. Format your data as an official 'Table' (Select data > Ctrl+T). Add a 'Status' column and a 'QBO_ID' column. 2. **Create Zap**: Log into Zapier and click 'Create Zap'. Name it 'Excel to QBO Invoice Sync'. 3. **Set Trigger**: Select **Microsoft Excel** as the App and **New Row** (within a Table) as the Event. Connect your account and select the Drive, Spreadsheet, and Table. 4. **Add Filter**: Add a 'Filter by Zapier' step. Set it to only continue if 'Status' (from step 1) exactly matches 'Ready'. 5. **Format Data (Optional but Recommended)**: Add a 'Formatter by Zapier' step (Date/Time) to ensure the Excel date string matches the ISO-8601 format required by QuickBooks. 6. **Find/Create Customer**: Add a **QuickBooks Online** step with the event **Find Customer**. Check the box 'Create QuickBooks Online Customer if it doesn't exist yet'. Map 'Name' and 'Email' from the Excel trigger. 7. **Map Invoice Action**: Add a QuickBooks Online step with the event **Create Invoice**. - **Customer**: Use the 'ID' from the previous Search step (Custom tab). - **Line Items**: Map Product, Description, Quantity, and Price from Excel. - **Invoice Date**: Map the output from the Formatter step. 8. **Update Source (Closed Loop)**: Add another Microsoft Excel step: **Update Row**. Use the Row ID from Trigger 1 and map the 'Invoice ID' from the QBO step to your 'QBO_ID' column. This prevents double-billing. 9. **Test & Publish**: Run a test with a sample row. Confirm the invoice appears in QBO Sandbox or active account, then turn the Zap on.

| Excel Field | QuickBooks Online Field | Required? | Zapier Logic/Transformation | | :--- | :--- | :--- | :--- | | Customer Name | Customer Display Name | Yes | Used in 'Find/Create' step | | Email | BillEmail/Address | No | Standard mapping | | Transaction Date | Invoice Date | Yes | Formatter: Date/Time (ISO-8601) | | Item Name | Line: Product/Service | Yes | Use 'Custom' value to match QBO Item Name | | Price | Line: Unit Price | Yes | Formatter: Numbers (Currency) | | QBO_ID | (Return Value) | No | Map QBO Invoice ID back to Excel | | Status | (Zap Logic) | Yes | Filter: Only proceed if 'Ready' |

- **Excel Table Requirement**: Zapier cannot 'see' cells unless they are inside a named Excel Table. If you add data outside the table boundaries, the Zap won't trigger. - **Rate Limiting**: QuickBooks Online API has a limit of 40 requests per second. For high-volume bulk imports, Zapier may 'throttle' or delay tasks. - **Oauth Session Expiry**: Excel Online connections in Zapier occasionally disconnect if the file is moved or renamed in OneDrive; always keep the file in its original folder. - **Duplicate Customers**: If you have two customers with the same name, the 'Find Customer' step might return the wrong one. Mapping a unique 'Email' is the safest search attribute.

- [ ] **Excel Check**: Is the spreadsheet saved in OneDrive for Business/SharePoint? - [ ] **Table Check**: Is the data strictly within a formatted Table range (Ctrl+T)? - [ ] **Search Logic**: Does the 'Find Customer' step include the 'Create if not found' checkbox enabled? - [ ] **Trigger Accuracy**: Use 'Test Trigger' in Zapier to ensure headers are correctly identified. - [ ] **Closed Loop**: Verify the 'Update Row' step successfully writes the QBO Invoice Number back to Excel.