*** Project Overview
This project is a comprehensive, automated system designed to manage over 1,000 rows of retail inventory and analyze profitability within Google Sheets. The primary objective was to transform raw inventory data into a dynamic, executive-ready decision-making tool without relying on heavy or expensive external software.
*** Key Features & Technical Implementation
• Optimized Data Architecture: Developed a clean 3-sheet structure (Raw Data, Calculation Backend, and Dashboard) featuring 14 critical supply chain columns, including Closing Stock, Reorder Point, and Stock Status.
• Dynamic Backend Computations: Utilized advanced SUBTOTAL formulas within a structured Pivot Table to guarantee real-time, accurate financial reporting that updates flawlessly with user filters.
• Executive Minimalist Dashboard (Clean UI/UX): Stripped away background gridlines to create a modern visual interface. Integrated an interactive Combo Chart analyzing Total Revenue (Dark Navy columns) against Total Profit (Coral line) on a secondary axis, controlled by a rounded, pastel-themed Category Slicer.
• Smart Insights Section: Implemented dynamic INDEX/MATCH/MAX formulas to instantly extract the Top Performing Category and Total Profit Margin based on active filters.
***Apps Script & Automated Email Alert System
Developed a production-ready, custom Google Apps Script paired with a daily time-driven trigger. The system autonomously scans the inventory every morning and shoots an automated email notification to the manager whenever items hit "Reorder" or "Out of Stock" status, reducing human error and preventing stockouts.
*** Technical Script Architecture:
• Dynamic Data Scanning: Iterates through spreadsheet datasets efficiently while bypassing header layouts.
• Smart User Resolution: Programmatically resolves the active manager's identity via Google Session protocols to direct notifications dynamically.
• Time-Driven Triggers: Built to fire reliably within an optimized morning maintenance window (9:00 AM – 10:00 AM).
• Scalable Data Schema: Prepared with clean execution variables, allowing smooth integration into custom developer libraries.
*** Try It Yourself (Live Demo)
You can test the automated email feature live without changing any data or editing the code:
- Open the Live Demo Sheet:
https://docs.google.com/spreadsheets/d/1ERffAjT3rS1PHKHKR7maAeGMJR07OIoJQ2y13OreKaU/edit?usp=sharing
- Open Apps Script: From the top menu, go to Extensions -> Apps Script.
- Run the Code: Make sure checkInventoryAndSendEmail is selected in the dropdown at the top, then click the Run button.
- Grant Permission: Click Review Permissions, select your Google account, click Advanced, choose Go to Untitled project (unsafe), and click Allow.
Result: Check your Gmail Inbox! You will instantly receive a real-time, low-stock report email generated dynamically from the sheet.
