PortfolioAbout This Topic
In invoice integration apps, the ability to "retrieve" data is just as important as "sending" it. To address needs like "I want to check this month's sales" or "I need a list of delivery slips for a specific partner," we implemented data retrieval functionality.
Why Bidirectional Matters
A system that only creates invoices is one-directional data integration. However, real business operations frequently encounter needs like:
| Need | Example |
|---|---|
| Monthly aggregation | Total sales this month, sales by partner |
| Verification work | How many delivery slips were sent to this partner |
| Analysis | Which products are selling well |
| Report creation | Performance by department or representative |
Manually checking these one by one in the invoicing service's admin panel is inefficient.
Two-Stage Retrieval Method
To efficiently retrieve large amounts of data, we adopted a two-stage retrieval method.
Retrieve invoice list by date range. Skip details for speed
Filter by partner name, department, tags, etc.
Retrieve details only for filtered invoices
Add brand information from JAN codes
Why Two Stages?
Retrieving all details at once results in massive data volumes and takes time. By first getting "just the overview" to filter, then retrieving details for only what's needed, waiting time is dramatically reduced.
| Method | With 100 items | With 1000 items |
|---|---|---|
| Bulk retrieval | Reasonably fast | Very slow (timeout risk) |
| Two-stage retrieval | Still fast | Still fast if filtered to 10 items |
Date Range Data Retrieval
Simply specify a date range from the GAS menu to retrieve relevant data to the spreadsheet.
| Preset | Behavior |
|---|---|
| This Month | Retrieve data from 1st of this month to today |
| Last Month | Retrieve data from 1st to end of last month |
| Custom | Freely specify start and end dates |
Frequently used periods (this month, last month) can be executed with one click, greatly streamlining month-end aggregation work.
Diverse Filtering Options
To extract only what's needed from large data sets, we prepared multiple filtering conditions.
Retrieve all invoices for the specified period
Partial match search
Filter by department code
Filter by memo tags
Display only matching data
Filtering Use Cases
- Partner name: View only invoices for "Company XYZ"
- Department code: Aggregate only sales department revenue
- Tags: List all invoices tagged "needs review"
Product Master Integration
Extract product codes (JAN) from delivery slip details and match against a product master in a separate sheet to automatically add supplementary information like brand names.
Create JAN → Brand map from ItemList sheet
Extract JAN codes from details
Match against master and add brand name to each detail
Sales aggregation by brand now possible
What This Integration Enables
- Sales by brand: Which brands are selling well
- Category analysis: Sales trends by product category
- Inventory integration: Predict order quantities from sales data
Real-World Use Cases
Month-End Sales Report
- Run "Get This Month's Transactions" from GAS menu
- Filter by partner name
- Match with product master to add brand info
- Aggregate with pivot table
Checking Invoices by Partner
- Retrieve data for specified period
- Partial match search by partner name
- Review matching invoice list
- Retrieve details as needed
What This Design Achieves
For Operations
- Streamlined analysis work: No manual data collection
- Flexible filtering: Multiple filter conditions available
- Added value: Product master integration for analysis-ready data
For Accounting Staff
- One-click retrieval: Presets for common periods
- Clear overview: See everything in spreadsheet
- Ready for aggregation: Use existing pivot table skills