PortfolioBackground
When building a multi-location compatible integration app, we were also required to improve efficiency in data analysis and inventory management, not just order integration and shipping notifications.
Wanting to aggregate and analyze NextEngine data in spreadsheets, and conversely, wanting to reflect inventory managed in spreadsheets to NextEngine—to meet these requirements, we implemented bidirectional integration with Google Sheets.
Feature Overview
Automatic Order Data Export
We built a system that periodically retrieves order data from NextEngine and automatically records it in spreadsheets.
Daily sales aggregation and monthly report creation are completed just by opening the spreadsheet.
Automatic Inventory Updates
We also implemented a system where editing inventory numbers in the spreadsheet automatically reflects those changes to NextEngine.
Even when sharing inventory across multiple stores, updating in one place reflects to all stores.
Multiple Sync Configurations
We made it possible to create multiple sync configurations for different purposes. For example:
- Daily sales report - Retrieve previous day's sales at 9 AM daily
- Inventory updates - Sync inventory every hour
- Monthly report - Retrieve all data from previous month on the 1st
Data Flow (Order Data Export)
Order Management
Transform & format
Spreadsheet
Data Flow (Inventory Sync)
Edit inventory data
2-stage sync
Inventory reflected
3 Types of Data Output Presets
Three presets are available depending on use case:
| Preset | Data Source | Use Case | Columns |
|---|---|---|---|
| Preset 1 | Confirmed shipments | Daily shipping reports, sales aggregation | 12 columns |
| Preset 2 | All order data | Detailed analysis, customer info management | 32 columns |
| Preset 3 | Cancelled orders | Cancellation analysis, returns management | 12 columns |
Preset 1 (Shipping Data) Output Items
Outputs basic items needed for daily sales aggregation:
- Order number, Store ID, Product code, Ship date
- Email address, Quantity, Tax rate, Unit price
- JAN code, Payment method, Product name, Department
Preset 2 (Detailed Data) Output Items
Outputs all items needed for customer analysis and detailed sales analysis:
- Above basic items plus
- Delivery info (address, name, phone number)
- Customer info (member number, customer rank, etc.)
- Delivery time slot, Internal ID, Fees, Points
Preset 3 (Cancellation Data) Output Items
Used for cancellation reason analysis and returns processing:
- Cancellation datetime, Cancellation reason
- Original order info, Refund amount
2-Stage Inventory Sync Method
Simply "sending spreadsheet values" could cause inventory inconsistencies. Therefore, we adopted a 2-stage method for reliable synchronization.
Step 1: Reset
Check existing data
Create clean state
Ready
Step 2: Apply
Get inventory data
uppercase→lowercase, /→-, etc.
Safety stock, etc.
Sync complete
Benefits of This Method
- No cumulative errors - Syncing with "absolute values" not "differences"
- Single source of truth - Spreadsheet becomes the only correct inventory source
- Transparency - Looking at spreadsheet shows current inventory
Sync Configuration Structure
Each sync configuration has the following items:
| Setting | Description | Example |
|---|---|---|
| Config name | Management name | "Daily Shipping Data" |
| Spreadsheet ID | Google Sheets ID | "1ABC_XYZ_123" |
| Sheet name | Target sheet | "Shipping Data" |
| Start cell | Write start position | "A2" |
| Mapping type | Output format | preset1/preset2/preset3 |
| Sync mode | Overwrite or append | overwrite/append |
| Clear range | Range to clear on overwrite | "A2:L" |
| Schedule | Auto-execution frequency | "0 0 * * *" (daily at 0:00) |
| Target stores | Filter targets | ["1", "2", "3"] |
Deduplication Feature (Append Mode)
In append mode, the following logic eliminates duplicates to prevent writing the same data multiple times:
Get from NextEngine
Get from spreadsheet
Check for duplicates
No duplicates
Use Cases
Automatic Daily Sales Report Generation
Automatically fetches previous day's shipping data at 9 AM every morning. Spreadsheet functions calculate sales totals and product-wise aggregations, completing the daily report automatically.
Bulk Inventory Updates
When shipments arrive, update inventory numbers in the spreadsheet. With one button, all store inventory in NextEngine is updated.
Automatic Monthly Report Creation
Retrieves all data from previous month at the beginning of each month. Pivot tables can auto-generate month-over-month trends and product-wise analysis.
Benefits
This feature provides the following benefits:
- Time savings - No need for data download and paste operations
- Real-time updates - Latest data always reflected in spreadsheet
- Flexible analysis - Free analysis using spreadsheet capabilities
- Improved inventory accuracy - Single source of truth prevents inventory inconsistencies
Notes
Spreadsheet Capacity Limits
Google Sheets has row count and capacity limits. When accumulating large amounts of data, we recommend periodically archiving old data.
Concurrent Editing Caution
Editing the spreadsheet during inventory sync may cause data inconsistencies. We recommend avoiding edits during sync execution or editing after sync completion.