End-to-End Inventory Management Automation Setup

Automate Inventory Sync: FoxPro → Excel → Google Sheets → Shopify
Overview
We need to fully automate our inventory sync process. Our Excel file is auto-refreshed from FoxPro; Google Sheets holds the updated product and inventory data; Shopify must be updated accordingly. We want a reliable, twice-daily automation that can run via Windows Task Scheduler.
Task Outline
1. 1. Excel Auto-Refresh:
• - Open via VBScript
• - Wait 5 minutes for FoxPro data refresh
• - Save and close Excel
2. 2. Google Sheets Sync:
• - Read Excel via Python
• - Overwrite entire Sheet with new data via Google Sheets API
3. 3. Shopify Inventory Update:
• - Read updated Google Sheet
• - Use Shopify Admin API to update inventory quantity per product variant
4. 4. Scheduler Setup:
• - Configure Task Scheduler to run all automation twice daily (e.g. 9 AM & 3 PM)
5. 5. Inventory Adjustment Before Shopify Update:
• - Deduct 2 units from each item's QTY before updating Shopify
Deliverables
- VBScript to launch and refresh Excel (`open_excel.vbs`)
- Python script to upload Excel to Google Sheets and sync to Shopify (`sync_all.py`)
- Logic to deduct 2 units from each SKU before pushing to Shopify
- Instructions for Google Sheets and Shopify API setup
- Logging or notification feature on completion (optional)
- Error handling with clear messaging
Onboarding Checklist
- [ ] Path to Excel file (e.g. C:\Inventory\Inventory.xlsx)
- [ ] Sample data from the Excel file
- [ ] Shared Google Sheet (edit access)
- [ ] `credentials.json` for Google Sheets API
- [ ] Shopify store domain and private app access token
- [ ] Sample SKUs and known inventory_item_id / location_id
Technical Requirements
- Python 3.x, running on Windows machine
- Familiarity with Google Sheets API and Shopify Admin API
- Experience with Windows Task Scheduler or other automation tools
- Solid understanding of Excel file manipulation using Python
- Ability to preprocess inventory data (e.g., auto-deduct damaged/lost items)
Communication
- Share work via GitHub, Bitbucket, or direct delivery
- Provide updates at each milestone
- Offer basic documentation for setup and maintenance