Gestion des Stock: Google Sheets Automation Addon
Enterprise Google Apps Script addon automating stock management with 64-column calculations, multi-source data integration, and advanced filtering.
Project Overview
The Challenge
Organizations managing inventory across multiple data sources (stock files, delivery frequencies, service rates, offers) face time-consuming manual data collection and consolidation processes, leading to delays and human errors.
Calculating stock coverage targets, safety stock levels, reactivity gaps, and reorder points for thousands of SKUs requires sophisticated formulas and data matching across multiple dimensions (suppliers, depots, acquisition codes).
Keeping inventory data synchronized with external Google Sheets files and CSV sources while tracking file modification dates and ensuring data freshness presents significant coordination challenges.
Categorizing inventory items based on multiple criteria (velocity, value, lifecycle stage) and providing instant filtered views for different stock categories (A/B/C classification, dead stock) requires advanced filtering logic.
Tracking data processing operations, logging errors, and maintaining an audit trail for compliance and troubleshooting in a spreadsheet environment lacks built-in enterprise-grade capabilities.
Developed enterprise Google Sheets addon for automated stock management and inventory optimization. Built complete data processing workflow integrating 4+ external data sources (Google Sheets, CSV files) with automatic validation, file modification tracking, and parameter-driven configuration.
Engineered sophisticated 64-column calculation engine performing complex inventory metrics for thousands of SKUs, including stock coverage targets, safety stock levels, reorder points, ABC classification, and supplier-level aggregations. Implemented modular formula functions with matrix-based lookups for optimal performance.
Created advanced filtering system with multi-criteria matching, dynamic parameter extraction, and specialized filter functions for A/B/C classification and dead stock identification. Built template-based architecture with automatic sheet duplication, positioning, and comprehensive error handling with audit trail logging.
Technical Architecture
Addon Menu System: Custom Google Sheets menu with initialization and update commands, onInstall/onOpen triggers for automatic menu creation, and user-friendly French interface
Initialization Module: Template-based sheet duplication from master spreadsheet, parameter configuration with date tracking, and automated sheet positioning and naming
MAJ (Update) Module: Orchestrates complete data refresh workflow, validates required parameters, extracts data from 4+ external sources, performs 64-column calculations, and updates 10+ internal sheets
Data Extraction Layer: URL-based Google Sheets access with Drive API, CSV parsing with automatic delimiter detection, file modification date tracking, and dynamic range detection
Advanced Filtering System: Multi-criteria filtering based on depot/acquisition code/appro, dynamic filter list population from parameters, and ABC classification with dead stock identification
Calculate Module: 64 calculated columns for inventory metrics, stock coverage and safety stock formulas, supplier-level aggregations, and week-based service rate calculations
Helper Utilities: Comprehensive error handling with stack traces, logging system with timestamps and categorization, sheet management (duplicate/replace/move), and data transformation functions
Formula Functions: Modular calculation functions (getAC through calculateAu), number validation and type checking, and matrix-based lookup logic
Key Challenges & Solutions
Multi-Source Data Integration with Validation
Built data extraction layer with URL parsing and Drive API integration, implemented file modification date tracking for each source, created parameter validation system requiring creation dates (E8, E12, E16, E20), and added comprehensive error messages for missing or invalid data sources.
Complex 64-Column Calculation Engine
Developed modular calculation system with dedicated formula functions (getAC through calculateAu), implemented data matching with object-based lookups for performance, created matrix-based parameter tables for dynamic configuration, and optimized calculations with array operations and duplicate detection.
Dynamic Filtering & Classification Logic
Built parameter parsing system extracting filter criteria from Param outil sheet, implemented advancedFilter function with multi-criteria matching, created specialized filter functions (filtre_a, filtre_b, filtre_c, filtre_mort) with sorting, and added automatic filter removal and recreation for clean state.
Template-Based Sheet Management
Implemented getOrduplicateSheetFromTemplate function for conditional duplication, created replaceSheet function for complete sheet refresh, added automatic sheet positioning with moveActiveSheet, and built sheet activation and navigation logic.
Enterprise-Grade Error Handling & Logging
Developed handleError function with stack trace logging and toast notifications, created handleLog function with timestamp and categorization (Info/Alert/Error), implemented LOGS sheet with automatic row insertion and formatting, and added try-catch blocks throughout with descriptive error messages.
Impact & Results
Automated data processing workflow reducing manual data entry by 90%, processing 4+ external data sources with automatic validation
64-column calculation engine for sophisticated inventory metrics including stock coverage, safety stock, reorder points, and ABC classification
Dynamic filtering system with parameter-driven configuration, instant filtered views for A/B/C classification and dead stock
Template-based architecture ensuring consistent sheet structure across workbooks with master template synchronization
Comprehensive logging with full audit trail, timestamps, categorization, and user-friendly toast notifications
Key Features
- Automated data processing from 4+ external sources
- 64-column calculation engine for inventory metrics
- Advanced filtering with ABC classification
- Template-based sheet management system
- Comprehensive error handling and logging
- Multi-criteria dynamic filtering
Technologies Used
Project Gallery
Project Details
Client
Tanitlab
Timeline
2024
Role
Google Apps Script Developer
© 2025 Firas Jday. All rights reserved.