R&D Requirements Form – Automation Project
· Web-based Internal Tool · Bandung, West Java, Indonesia
Developed a web-based R&D Requirements Form to help the sales team request product samples according to potential customers’ needs.
The system automates data flow from submission to visualization using Google Apps Script, Google Sheets, and Google Sites,
eliminating manual tracking and improving communication between departments.
Development Overview
- Built the form using HTML, CSS, and JavaScript with validation rules for user input control.
- Deployed the form via Google Apps Script Web App to enable free online hosting.
- Connected form submissions to a Google Spreadsheet that acts as the central database.
- Configured automatic time stamping, division categorization, and sample type grouping upon submission.
Data Processing & Visualization
- Implemented dynamic formulas in Google Sheets to calculate total kg, total samples per division, and sample status.
- Created real-time dashboards showing pending samples, most active sales orders, and other performance metrics.
- Linked processed data visualizations directly to Google Sites for transparent public access.
- Used ArrayFormula and Query functions to automate summary generation.
Data Tracking Layer
- Created a second data layer using QUERY to import master data into a separate, non-editable view for sales tracking.
- Allowed salespeople to track the status of their requested samples in real time without risking data modification.
- Enhanced transparency between Sales and R&D teams through structured, read-only access.
Outcome & Impact
- Reduced manual reporting and form processing time by over 60%.
- Improved coordination between Sales and R&D divisions with centralized data.
- Provided cost-free hosting and maintenance through Google ecosystem integration.
- Enabled transparent and accessible sample tracking through live dashboards on Google Sites.