How to automate a spreadsheet with scripts, and run them safely

What scripts are good for

Scripts pay off on work that is the same every time and dull by hand. Some typical jobs:

• Formatting a report that arrives in the same shape each week: bold headings, a fill on the top row, money shown as money and dates as dates.
• Clearing a template for a new month and setting its headings and dates up again.
• Going down a list and writing a note beside each row that breaks a rule, such as an invoice past its due date with no payment against it.
• Adding a summary sheet that totals what is on the others.

A calculation that should update as you type belongs in a formula. Bringing in data from the same file each month is a job for Power Query, which records its steps without any code.

Office Scripts, VBA and the rest

VBA macros are the older way, in Excel since Excel 5.0 in 1993. A macro is kept inside the workbook, in an .xlsm, .xlsb or older .xls file, and runs in desktop Excel with everything your user account can do. It can read and write files anywhere on the computer and start other programs. That reach is why macros have long been a favourite way to spread malware, and why Microsoft now blocks them by default in files that came from the internet.

Office Scripts arrived in Excel on the web in 2020 and have since come to Excel for Windows and Mac. They are written in TypeScript, which is JavaScript with types added, and each one has a main function that Excel hands the workbook to: function main(workbook: ExcelScript.Workbook). Scripts are saved as .osts files in your OneDrive, apart from any workbook, and they need a work or school Microsoft 365 plan. A script works on the workbook it is run in. It cannot open files on your computer or start programs, though it can call web services, and Power Automate can run one on a schedule or when something happens elsewhere.

Google Sheets has Apps Script, also JavaScript, which asks for permissions the first time it runs. Those permissions can reach well beyond the spreadsheet, as far as your Gmail and Drive. LibreOffice runs macros written in its own Basic or in Python, kept in the document or in your own macro library.

Recording a script, and writing one

In Excel, Automate → Record Actions starts the recorder. Do the job once, press Stop, and the Code Editor shows the script it wrote. Recorded scripts use fixed addresses. If you set a number format on B2:B40, the script sets it on B2:B40 next time, even when next week’s report runs to row 60.

That is usually the first thing to change by hand, and the object model is easier to read than it looks. A workbook holds worksheets and each worksheet holds ranges, the blocks of cells a script reads and writes. sheet.getUsedRange() gives the block of cells actually in use, so a script built on it follows the data wherever it ends. range.getValues() returns the cells as rows to loop over, and range.setValues() writes rows back in one go, which is far quicker than one cell at a time.

Run it from the Code Editor first, on a copy of a real file. A script can change thousands of cells in a moment, and a mistake in a loop changes every one of them. Once it works, Excel can add a button for it to the workbook, so that other people who share the file run it with one click.

What a script may touch

Read a script before you run it, even roughly. You are looking for what it writes and where it sends things:

• Writes to whole columns or the whole used range, and any call that clears or deletes. Check that the addresses are the ones you expect.
• Loops that add sheets or rows with no clear end.
• In an Office Script, any fetch call. That is the script sending a request to a web address, and what it sends can include the contents of your cells.
• In a VBA macro, anything that opens files or starts something outside Excel, such as the Shell function or a WScript.Shell object. Those lines reach beyond the workbook.

How far a script can reach depends on what runs it. An Office Script is limited to the open workbook and the web. A VBA macro can do anything your account can do on the computer, which is why the next section matters most for macros.

Keep a copy of anything that matters before running a script on it. A macro’s changes cannot be undone with Ctrl+Z, because running a macro clears Excel’s undo list.

Opening other people’s files

Opening a workbook never runs an Office Script, because scripts are not kept in the file. A workbook shared on OneDrive or SharePoint can carry a button linked to a script, and pressing it runs that script as you, so look at the script first.

Macros are kept in the file, and one called Workbook_Open or Auto_Open runs as the workbook opens if macros are enabled. That is the route most harmful spreadsheets take. Excel opens files from the internet and email attachments in Protected View, and for a file marked as downloaded it now shows a red bar saying that macros have been blocked because the source is untrusted. Leave it blocked for any file you were not expecting.

If you need the data from such a file and not its code, save a copy as .xlsx. That format cannot hold a VBA project, and Excel warns that it will leave the macros out. A workbook that arrives as .xlsx cannot carry macros at all, so an attachment that insists on being opened as .xlsm, or tells you to enable content to see it properly, deserves suspicion.