Integrated POS & Inventory Management System
CARO Management Inventory is a portfolio template that create fully by Google Sheet and Appscript suitable for small business, warehouse and related businesses.
Services
Inventory Management, POS System, Automations Data, Data Management, Data Entry Tools
Tools
Google Sheets, Apps Script, Advanced Formula
Value
Automations Data Entry, Diference DB Sheets
Timeline
5 weeks
Project Overview
This project is a comprehensive Point of Sale (POS) and Inventory Management System built entirely within the Google Sheets ecosystem. By leveraging Google Apps Script and advanced array formulas, I transformed a standard spreadsheet into a dynamic, automated business tool. This system serves as a lightweight, cost-effective alternative to expensive ERP software, offering real-time stock tracking, automated transaction entries, and a user-friendly interface.
Key Features:
Dynamic Product Management: Seamlessly add, edit, or delete products with a custom-built UI.
Automated POS Interface: A "Smart Cart" system that fetches product details and calculates margins instantly.
Real-time Inventory Sync: Automatic stock level updates (In/Out/Current) upon every transaction.
Custom Scripting: A bespoke "Control" menu integrated into the Google Sheets toolbar for professional-grade workflow management.
A complete retail sales system combining Point of Sale (POS) with 100-SKU inventory management, per-product margin analysis, and cashier performance reporting — built on Google Sheets and Apps Script, processed by Python, stored in SQLite, and visualized on a publicly accessible GitHub Pages dashboard.
Google Sheets | Apps Script | Python 3.13 | SQLite | SQL | GitHub Pages | LockService |
01. Background & Problem Statement
Retail Operational Complexity Without a Unified System
A retail business with hundreds of SKUs faces unique challenges: stock changes with every transaction, margin per product varies, and cashier performance is impossible to measure without centralized data. Commercial POS software is expensive and inflexible for custom analytics. This portfolio proves that enterprise-grade operational systems can be built with Google Sheets as the foundation.
Stockout Blind Spot Without real-time stock tracking, cashiers often sell products that are already out of stock — causing order cancellations and declining customer trust. Hidden Margin Erosion Discounts applied by cashiers without an oversight system can erode margin unnoticed. There is no report showing actual profit per transaction. | Zero Cashier Performance Data Without per-cashier recording, there is no way to know who is most productive, who gives the most discounts, or who needs coaching. No Inventory Valuation Total stock value (capital value vs selling value) is unknown — making reorder and pricing decisions guesswork rather than data-driven choices. |
02. Why Google Sheets + Apps Script?
This system is built on Google Sheets because the platform can function as a genuine POS terminal — with product dropdowns, stock automation, and PDF receipt printing, all without additional software. Apps Script becomes the engine powering all real-time operations.
01 | Entry New Product — Catalog Management Product CRUD form with automatic selling price calculation based on COGS plus markup in percentage or nominal format. Product modifications update data in the external ProductDB spreadsheet via URL. |
02 | onEdit() Trigger — Auto Mode POS When a cashier selects a SKU from the dropdown in the Transaction sheet, onEdit() immediately increments quantity or creates a new row. LockService.getScriptLock() prevents race conditions during rapid sequential transactions. |
03 | Receipt PDF Generation submitTransaction() builds a thermal receipt layout (32 characters wide, 58mm format) from HTML, converts it to PDF via Utilities, uploads to Google Drive, and opens it in the cashier's browser automatically. |
04 | Web App Export — Dual Dataset JSON doGet() exports two datasets at once: stockout_transactions (104 rows) and product_stock (100 SKUs). The ?sheet=stockout or ?sheet=products parameters are available for per-dataset debugging. |
03. Python Pipeline — Two Tables, One Database
Tool 09 is the only portfolio with two tables in a single SQLite database — an additional challenge requiring a more complex pipeline architecture. pipeline_09.py handles two different cleaning flows in parallel and loads both in a single atomic database transaction.
Composite Primary Key The stockout_transactions table uses row_key (invoice|sku) as its composite primary key — because one invoice can have multiple SKU rows. Derived Columns Python calculates margin_per_unit and margin_pct at load time so SQL does not need to recalculate on every query, improving dashboard performance. | Stock Consistency Validation The pipeline validates that in_stock minus out_stock equals qty_stock for every SKU. Discrepancies are logged as warnings with the conflicting values detailed. is_stockout Flag The is_stockout boolean column is auto-derived based on qty_stock less than or equal to zero. This makes SQL stock alert queries extremely simple and efficient. |
04. SQL with Cross-Table JOIN
What sets Tool 09 apart from the other two portfolios is the use of SQL JOIN between the transactions table and the stock table — a demonstration of more advanced relational querying capability that reflects real analytical work.
$3,457 | 19.8% | 32 | 100 |
Total Revenue | Margin | Invoices | SKU |
JOIN Query — Sales Performance + Stock Remaining:
SELECT s.sku, s.product_name,
ROUND(SUM(s.subtotal), 2) AS sold_revenue,
ROUND(SUM(s.margin), 2) AS total_profit,
p.qty_stock AS remaining_stock,
ROUND(p.qty_stock * p.value_selling, 2) AS remaining_value
FROM stockout_transactions s
LEFT JOIN product_stock p ON s.sku = p.sku
GROUP BY s.sku ORDER BY sold_revenue DESC;
This query combines historical sales data with the current stock snapshot, producing a single view showing which products sold best, how much profit was generated, and how much stock value remains. These insights are impossible to get from a single table.
05. Architecture & Special Dashboard Features
L1 | Google Sheets + Apps Script POS entry with onEdit() auto-mode, product management with COGS calculation, receipt PDF generation, and dual-dataset JSON export with URL parameters. |
L2 | Python ETL (pipeline_09.py) Clean two different datasets, derive margin columns, validate stock consistency, upsert to two SQLite tables, and log every run in the pipeline_runs table. |
L3 | SQLite with 2 Tables and JOIN 20 queries covering sales KPIs, monthly trend, top products, cashier performance, stock alerts, inventory valuation, and cross-table JOIN queries. |
L4 | GitHub Pages Dashboard (Unique Features) Raw Data tab has two sub-tabs for Stockout Transactions and Product Stock. The Payment/Cashier chart has an inner tab switch. Stock Alert displays a visual progress bar per SKU. |
L5 | GitHub Actions Daily automation at 00:01 WIB with cron schedule. Can be manually triggered with pipeline parameter from the GitHub repository Actions tab. |
06. Demonstration Value & What Makes This Portfolio Unique
Among the three portfolios, Tool 09 is the most technically complex — combining real-time event handling, race condition prevention, multi-table database design, relational SQL querying, and a richer UI with more components. It is the most comprehensive end-to-end demonstration of the full stack.
Event-Driven Architecture The onEdit() trigger is an example of event-driven architecture in a Google Sheets context. The use of LockService demonstrates understanding of concurrency and race conditions — concepts relevant to data pipelines in multi-user environments. Multi-Table Data Modeling Separating transactional data from reference data is a fundamental database normalization principle. JOIN queries then unite them for comprehensive analytics. | Inventory Analytics Skills The ability to calculate inventory turnover, margin per SKU, stock valuation (capital vs selling), and stockout rate is a sought-after skill for Supply Chain Analyst, Retail Data Analyst, and Operations BI roles. Full Operational Context This portfolio is not just about data — it is about understanding retail business operations end-to-end, from how cashiers work to how pricing and reorder decisions should be made based on data. |
