Outcome and scope
Create an operational month-end summary from order rows in Google Sheets. The output should explain its date range, currencies and included order states, and allow staff to trace a total back to its source orders. It is not an automated accounting determination. Keep a version identifier so that a later correction does not silently replace an approved report.
Define the unit before counting. A row may be an order or an order line, and prepaid, unpaid and cancelled records may follow different reporting rules. Keep order and line identifiers when both levels are present.
Prepare the fields and destination
Prepare these fields: Order ID, order time, reporting date, currency, item amount, shipping, cancellation state, refund amount and report version. Keep source values separate from a reviewer’s confirmed values, and do not reuse an existing column for a different meaning. Create the first record with practice identifiers in a destination whose read and edit access matches the intended staff roles. Inspect the saved result from that role before using live information.
Returns and refunds need separate references and states. A returned item is not necessarily refunded yet, and a shipping adjustment is not the same as a product refund. Encode the approved reporting rule rather than inferring one during a scenario run.
Build the workflow in order
Define the period with an inclusive start and exclusive end, such as the start of one month in Japan time through the start of the next. Choose order date or payment date explicitly. Make the reporting month an input, so a failed month-end run can be repeated without changing the machine clock or selecting a new month accidentally.
Retrieve eligible rows, then separate duplicate order IDs and missing amounts before aggregation. Group by currency and preserve distinct components for merchandise, shipping and refunds. Validate separators and negative values before treating a cell as a number. Unknown amounts belong in an exception list rather than being silently converted to zero.
Save the summary and its source order IDs under the same report version. Include excluded cancellations, the treatment of refunds and the number of exceptions. Add a human finalization field. Notify recipients once that version has been approved, rather than describing a freshly calculated but unchecked sheet as a final financial report.
Work through a small example
Use a small practice set with ten ordinary orders, one cancellation and one refund processed in the following month. Decide which period owns that refund before calculating. Work out the expected counts independently. Keep Japanese yen and US dollars in separate summary rows; a sum of unlike currencies is not a meaningful total.
| State | Check | Action |
|---|---|---|
| Ordinary order | Inside the selected period and state | Aggregate within its currency |
| Cancellation | Excluded under the reporting rule | Record count and exclusion reason |
| Later refund | Reporting period not yet agreed | Hold for the report owner |
| Correction after approval | An approved report already exists | Create a new report version |
Handle changes during operation
Check retrieval limits and pagination before using the result as a monthly total. An interrupted fetch must leave the report incomplete, with its retrieval range and source count visible to the reviewer.
Estimate cost and usage
A monthly start does not mean a single billable action. Count retrieved rows, pagination, exceptions, report storage and correction runs. Distinguish orders from line items and inspect where the chosen modules create separate processing units. Include retained report storage and staff reconciliation time, and avoid rereading every historical month on each scheduled run.
Investigate failures
When totals disagree, compare source order IDs before retrying the scenario. Check the time boundary, cancellation rule and whether line items were mistaken for whole orders. Save a correction as a new version, retain the prior approval record and tell recipients which version replaces it. Repeating a wrong selection rule only reproduces the same discrepancy.
Review before release
Before release, test a normal record, one missing a required value, a repeated identifier and an edited record. Use order id to trace the saved result back to the source. The acceptance condition is: A usable report has a traceable period, currency, source IDs and version. Record the expected result before testing, compare it with the actual saved state, and resolve unexplained differences before increasing the scope.
Start a small workflow in Make
Try the required workflow with practice data, then choose a plan around its usage and features. Check current prices, promotions and eligibility in the official signup flow.
Sign up on MakeAffiliate linkEstimate costs for your workload
Enter monthly jobs, billable steps and retries to see how different billing units affect your estimate.
Open cost calculatorOfficial sources
Pricing, availability and feature limits can change. Check the linked official pages before subscribing.
- Make: Working with filesInformation checked: 2026-10-30
- Google Sheets modulesInformation checked: 2026-10-30
- Make: Incomplete executionsInformation checked: 2026-10-30
- Make pricingInformation checked: 2026-10-30
- Make pricing — Core at 10,000 monthly creditsInformation checked: 2026-10-28
- Make creditsInformation checked: 2026-10-26
- Make scenario settingsInformation checked: 2026-10-26
