📦 Inventory Management System (IMS) - Google Sheets Automation
🚀 Overview
This is a fully automated Inventory Management System built using Google Apps Script + Google Sheets.
It helps businesses track stock, calculate inventory in real time, manage reorder levels, and analyze daily consumption — all automatically.
✨ Key Features
📊 Inventory Automation
- Real-time stock calculation (In/Out tracking)
- Custom date range inventory reports
- Default 30-day stock analysis
📦 Stock Intelligence
- Latest closing stock auto-update
- Color-coded stock levels:
- 🔴 Low Stock
- 🟡 Medium Stock
- 🟢 Healthy Stock
- 🟣 Overstock
🔁 Smart Reorder System
- Auto reorder quantity calculation
- Max level based stock control
- Approval-based ordering system
📈 Analytics
- Average daily consumption calculation
- SKU-wise demand tracking
- Data-driven forecasting support
📥 Indent Automation
- Select items for purchase
- Auto timestamp logging
- Direct transfer to Indent Sheet
⚙️ Automation
- Time-driven triggers (3H / 6H updates)
- On-edit timestamp tracking
- Fully hands-free system
🧠 Tech Stack
- Google Apps Script (JavaScript)
- Google Sheets API
- Spreadsheet Automation
- Time-based Triggers
🗂️ Sheet Architecture
| Sheet Name | Purpose |
|---|---|
| IMS | Main dashboard |
| In/Out (Manual) | Manual stock entry |
| In/Out (Form) | Google Form input |
| Item List | SKU master data |
| Reorder Sheet | Reorder control panel |
| Indent Link | Purchase order tracking |
⚙️ Setup Guide
- Open Google Sheets
- Go to Extensions → Apps Script
- Paste script code
- Save project
- Run
onOpen()once for authorization - Reload sheet → Menu appears: Inventory System Pro
📊 Screenshots
Replace these images with your actual Google Sheet screenshots
📌 Dashboard View
📌 Stock Analysis
📌 Reorder System
📌 Core Functions
| Function | Purpose |
|---|---|
| runInventoryDateRange | Custom date inventory |
| runInventoryDefault | Last 30 days inventory |
| runLatestStock | Live stock update |
| runReorderUpdate | Reorder calculation |
| runAvgConsumption | Daily usage analysis |
| pushIndentRows | Purchase indent export |
💡 Business Use Case
This system is ideal for:
- 🏭 Manufacturing units
- 🏬 Warehouses
- 🛒 Retail stores
- 📦 Distribution businesses
It replaces manual Excel tracking with fully automated inventory intelligence.
🔒 Important Notes
- Sheet names must match script exactly
- Date format must be
dd/mm/yyyy - Do not rename columns without updating script
- First run requires authorization
📈 Future Enhancements
- 📊 Power BI Dashboard Integration
- 📧 Email stock alerts
- 📱 WhatsApp notifications
- 🧾 Barcode scanning system
- ☁️ Multi-location inventory
⚠️ Note
This project uses dummy/modified data for portfolio purposes. No confidential company data is shared.
📫 Contact
- Name: Ashish Ranjan
- 📧 Email: [email protected]
- 🔗 LinkedIn: linkedin.com/in/ashishranjanji09
⭐ If you like this project, give it a star!