From: Dana Okafor, finance operations lead, Harbor & Pine Outfitters (fictional)
Every month someone spends an afternoon on the orders workbook: working out each order's amount, building a summary by region with a total, marking orders of 500 or more for the credit check, and noting the run in the log. Next month we want Power Automate to run a script on the 1st and email the total to the managers.
The data: the Orders sheet (one row per order: region, units, unit price; Amount is empty) and the Log sheet with a table called RunLog. The number of orders changes every month and new regions appear.
Done looks like: one script that, run on any month's workbook,
- fills Amount for every order,
- creates the Summary sheet if it's missing, with headers,
- lists every region A to Z with its total,
- adds a Total row with a live SUM formula,
- highlights orders of 500 or more in light yellow (#FFF2CC),
- adds a row to RunLog and returns the grand total for the flow.
Build it one step at a time: each step adds to the script you already have. All names and amounts are fictional practice data.
You'll grow one Office Script over six steps, and each step is checked by running the whole script on this month's workbook and on a changed copy with more orders and a new region. So every part has to read the data rather than assume its size.
Keep the first read of the data in data and the amounts you work out in amounts. Later steps reuse them: the summary adds amounts, not column E of data, because data was read before the amounts were written. That detail catches people out.
A script you'll run every month must also be safe to run twice. Getting the Summary sheet before adding it, and sizing every write from the data, are what make that true.
Get the sheet, and add it only when it is missing, so the script runs on any month's workbook.