PortfolioAbout This Topic
For small and medium-sized businesses, spreadsheets are the most familiar tool. In this project, we built a system that uses spreadsheets as the "center of order management" and automatically sends data to invoicing services.
When introducing new systems, resistance from the workplace is often the biggest barrier. That's why we chose to leverage spreadsheets that the team is already comfortable with.
Why This Design Matters
New Input Screens Have High Learning Costs
A common failure pattern in new system implementations is "building a feature-rich input screen that nobody uses." If the system isn't adopted, the investment is wasted.
| Approach | Learning Cost | Workplace Response |
|---|---|---|
| Dedicated System | High | Strong resistance, tendency to revert to old methods |
| Spreadsheet-based | Near Zero | Get automation benefits with familiar operations |
Strengths of Spreadsheets
- Everyone can use them: Excel/Spreadsheet skills are basic PC literacy
- Highly flexible: Freely add or rearrange columns
- Great visibility: See the whole picture in a list view
- Collaborative editing: Multiple people can work simultaneously
System Overview
Data entered in the spreadsheet is read by Google Apps Script (GAS) and automatically sent to the invoicing service.
1 row = 1 product. Multiple rows grouped by invoice number
Get column names from header → Read selection → Group by invoice number → Convert to JSON and POST
Call invoicing service API → Return results
Combining Multiple Rows into One Invoice
Order data is managed with one row per product, but actual invoices need to combine multiple products into one document.
To solve this, we implemented grouping by invoice number.
User selects rows to process
Rows with the same invoice number are combined into one group
Each group becomes one invoice data object
Even 10 line items become one invoice
Result Feedback
Whether the invoice was successfully created or resulted in an error is automatically written back to a designated column in the spreadsheet.
| Status | Meaning | Next Action |
|---|---|---|
| OK | Successfully created | Invoice number is also recorded |
| Error | Creation failed | Check error details and fix |
| Duplicate | Already created | Prevents duplicate creation (normal behavior) |
Safe Processing with Allow Lists
Spreadsheets may contain columns like internal notes that shouldn't be included in invoices. We introduced a mechanism to only process columns in the allow list.
Column names are read from the header row (row 1) and matched against the allow list. Columns not allowed are automatically ignored, so extraneous data is never sent.
Allow List Examples
invoice_number- Invoice number (grouping key)partner_code- Partner codeissue_date- Issue datedescription- Product namequantity- Quantityunit_price- Unit price
What This Design Achieves
For Operations
- Minimize adoption cost: No need to learn new systems
- Avoid migration risk: Maintain existing workflows
- Flexible response: Freely customize spreadsheet columns
For Field Staff
- Same old operations: Just enter data in the spreadsheet
- Immediate result confirmation: Status appears on the spot
- Easy error handling: Problematic rows are visible at a glance
Related Topics
Building on this design, we've also implemented:
- Bidirectional Data Utilization - Retrieve and analyze created data
- Error Recovery Mechanisms - Duplicate prevention and auto-retry