6.2 Office Scripts

What you'll be able to do

Turn a repetitive monthly task into a button. Record it, read the code the recorder wrote, improve it, and then the real prize: schedule it to run with your laptop shut.

Before you start

Download the automation monthly workbook. It holds a month-end tidy-up made to be automated. You need a work or school Microsoft 365 plan, as the choosing lesson explained.

The work

1. No documented keyboard shortcut for this — use the menu: record first. Go to Automate → Record Actions, then do the monthly routine once by hand: format the header, fix the column widths, add the totals row. Stop recording, name the script, and press its play button. The routine happens again on its own. That alone is worth the trouble.
2. No documented keyboard shortcut for this — use the menu: now read what it wrote. Go to Automate → open your script in the Code Editor. What the recorder writes is wordy but readable TypeScript: getWorksheet, getRange, setValue. Each line is one action you performed. Reading recorded code is how office scripting is really learned, because you almost never start from an empty file.
3. Same either way: make one improvement each time you sit down. Replace a fixed range with the used range, using getUsedRange(). Wrap a repeated action in a loop over the rows. Add an if, so that the totals row is not added twice. Small edits to recorded code are the craft at this level, and the editor's autocomplete carries you along.
4. Same either way: mind what the recorder cannot see. It records the outcome on the cells you touched, not what you meant. Record on January's 200 rows and the script may fix row 201 in place for ever. Test it on a month of a different size before trusting it, and fix it with the used-range pattern from step 3.
5. No documented keyboard shortcut for this — use the menu: now the schedule, which is the point of all this. In Power Automate, at flow.microsoft.com, create a scheduled flow for the first of the month at 6 am. Give it the "Run script" action, pointing at your script and at your workbook, which must live on OneDrive or SharePoint. The script now runs in the cloud, with no laptop and no person, and the file is ready before anyone asks for it.
6. Same either way: learn the boundaries now, so that they are not surprises later. Scripts run where the modern engine exists, which is Excel on the web and current desktop Excel, and not in older bought-once versions. They cannot reach your own disk or other desktop apps, which is VBA's ground. A colleague running your script needs access to both the script and the workbook. It is a cloud tool, with cloud rules.

Try it

Record the practice workbook's month-end tidy-up. Read the code, make the used-range improvement, and test it on the second month sheet, which has a different number of rows. Then schedule it in Power Automate and confirm the 6 am run from its run history.

Check yourself