A complete ERP system for Mandi Commission Agents (Aarthi/Broker) built with Google Apps Script, Google Sheets, and React 18 — zero hosting cost.
A production-ready Commission Agent ERP that manages the full business cycle of a wholesale commodity trading operation:
Supplier → Shipment → Godown (Warehouse) → Sale → Buyer Payment → Profit Distribution
Built entirely on Google's free ecosystem — Google Sheets as database, Apps Script as backend, React 18 as frontend. No server, no hosting fees, no monthly subscriptions.
| Layer | Technology |
|---|---|
| Backend | Google Apps Script (Code.gs) |
| Database | Google Sheets (18 tables) |
| Frontend | React 18 (CDN + Babel) |
| Charts | Chart.js 4.4.0 |
| Tables | jQuery DataTables 1.13.7 |
| Icons | Font Awesome 6.5.1 |
| Alerts | SweetAlert2 |
| Hosting | Google HTML Service (free) |
- Phone + Password login
- Email OTP password reset (3-step flow)
- 3-role RBAC — Admin, Staff, Partner
- Session management with 24-hour TTL
| Module | What It Does |
|---|---|
| Dashboard | 3 role-specific dashboards with KPI cards and Chart.js visualizations |
| Accounts (Khata) | Manage Suppliers, Buyers, Saraf with auto-balance recalculation |
| Shipments | Track incoming goods with KG → Man → Kharwar unit conversion |
| Inventory (Godown) | Auto-created on shipment arrival, tracks stock in/out |
| Sales | Stock validation, credit limit check, bardana/weight deduction |
| Sale Payments | Record buyer payments, auto-update sale status and balance |
| Supplier Payments | Pay suppliers, auto-sync shipment payment status |
| Roznamcha (Ledger) | Daily ledger with auto-entries from all 8 transaction types |
| Partners | Profit sharing (time-bounded), investment tracking |
| Partner Withdrawals | Overdraw protection, profit share validation |
| Loans (Qarz) | Given/Taken loans with auto-EMI schedule generation |
| Loan Payments | Auto-recalc balance, auto-settle when fully paid |
| Business Expenses | Category-based expenses affecting P&L and partner shares |
| Expense Categories | Manage expense categories |
| Profit & Loss | Revenue, COGS, expenses, net profit, partner share breakdown |
| Net Worth | Balance sheet — receivable + loans + inventory - payable |
| Activity Logs | Full audit trail with user/action/module/date filters |
| Settings | App name, currency symbol, logo upload |
| Trigger | What Happens Automatically |
|---|---|
| Shipment created | Ledger entry recorded, freight auto-added as expense |
| Shipment arrives | Inventory record auto-created in godown |
| Sale created | Stock checked, credit limit validated, inventory updated, shipment P&L recalculated, buyer balance updated, ledger entry recorded |
| Payment received | Sale balance and status updated, account balance recalculated, ledger entry recorded |
| Supplier paid | Supplier balance updated, shipment pay-status synced, ledger entry recorded |
| Expense added | Shipment P&L recalculated, ledger entry recorded |
| Withdrawal created | Validated against available profit share, ledger entry recorded |
| Loan payment made | Loan balance recalculated, auto-settled if zero, ledger entry recorded |
- SWR v2 caching with focus revalidation and deduplication
- Cross-view invalidation (mutating sales auto-refreshes 7 related views)
- Searchable dropdowns (custom component)
- DataTables with CSV, PDF, Print export
- Row conditional coloring (paid/partial/overdue)
- Summary footer totals on financial tables
- Date range filters across all modules
- Skeleton loading states
- Mobile responsive with bottom navigation
- "On save" info banners explaining auto-actions
- Force-refresh button (clears SWR cache globally)
- Cascade delete protection (checks child records)
- LockService for concurrent write safety
- ISO 8601 date standardization
- O(1) lookup maps for all joins (no nested loops)
- Partner profit % validated to sum ≤ 100%
- Buyer credit limit enforcement
18 Google Sheets tables:
Users → Authentication & roles
Partners → Profit sharing config
Accounts → Supplier / Buyer / Saraf khata
Commodities → Tradeable goods
Shipments → Incoming purchases
Shipment_Expenses → Per-shipment costs
Inventory → Godown stock tracking
Sales → Outgoing sales
Sale_Payments → Buyer payment records
Supplier_Payments → Supplier payment records
Ledger → Roznamcha (daily journal)
Partner_Withdrawals→ Partner payouts
Loans → Qarz given/taken
Loan_Payments → Loan repayments
Expense_Categories → Business expense types
Business_Expenses → General business costs
Settings → App configuration
Activity_Logs → Audit trail
Screenshots from the live application
- 12 KPI cards with light pastel colors
- Net Worth balance sheet section
- 3 Chart.js visualizations (Users by Role, Accounts by Type, Monthly Registrations)
- Recent activity feed
- DataTable with row coloring (green=paid, yellow=partial, red=overdue)
- Record Payment button in action column
- Date range filter
- Summary footer (Total Sales, Paid, Balance Due, Commission)
- Weight conversion display (KG → Man)
- Inline expenses table
- P&L summary (Sold - Purchase - Expenses)
- Payment status tracking
- Create a new Google Sheet
- Open Extensions → Apps Script
- Copy
Code.gscontent into the script editor - Create
index.htmlfile and paste content - Click Deploy → New Deployment → Web App
- Set: Execute as Me, Access Anyone
- Open the deployed URL — run
setupDemoData()from script editor to populate demo data
| Role | Phone | Password |
|---|---|---|
| Admin | 03001000001 | admin123 |
| Partner | 03001000002 | partner123 |
| Staff | 03001000004 | staff123 |
| Metric | Count |
|---|---|
| Backend functions | 116 |
| Frontend components | 64 |
| Database tables | 18 |
| Total lines of code | 8,887 |
| Chart.js visualizations | 9 |
| DataTables | 13 |
| Modal forms | 23 |
| SWR cache hooks | 19 |
Full stack Google Apps Script tutorials, ERP systems, dashboards, CRM apps, and business automation — all built on Google's free ecosystem.
- 21K+ subscribers
- 300+ client projects delivered worldwide
- Weekly tutorials
This project is provided as a learning resource. Source code is available for educational purposes.
For custom business projects: WhatsApp