Automate tasks in Excel with Office Scripts
VBA doesn’t play well online, but this tool can get you back on script.Not too long ago, Microsoft announced an easy way to create scripts for Excel online. That’s great if you happen to know what a “script” is — and, no, we aren’t talking about Aaron Sorkin penning one for Jennifer Lawrence.
A script is a piece of code (a routine) that can automate a task, such as creating a chart in Excel. That’s particularly useful for repetitive tasks.
Over the years, Microsoft has developed many tools to facilitate the creation of solutions and applications (apps) powered by data. The Office Scripts feature is designed to automate and simplify mundane, repetitive tasks. As accountants, we regularly seek out tools for automating such tasks — so it’s important to take notice of this tool, even if the phrase “IT development tools” may send a shiver down your spine and it is only available with Excel on the web at this time.
Current programmability options in Office include COM add-ins, Visual Basic for Applications (VBA), web add-ins, and Office Scripts. The first two options focus on providing solutions for desktop software and individuals, while the last two focus on solutions for the internet and collaboration — which Microsoft views as the growth of the future.
Like VBA, Office Scripts offers similar functionality to record and share actions performed by the end user or modeller alike. When recording a macro, the Excel macro recorder records all the steps in VBA code. These steps include different types of behaviour performed by the end users such as typing text or numbers; clicking cells or commands on the Ribbon or on menus; formatting cells, rows, or columns; or worksheet events and workbook events.
The problem with VBA is that the internet and VBA do not play nicely together. This is where Office Scripts comes in: With this new feature, end users may record all actions performed such as renaming the worksheet, inputting the data, and changing the format, etc., inside an online Excel workbook, and save the steps as a script. The script generated may then be used with Power Automate (formerly known as Microsoft Flow) or be integrated with another workflow. Furthermore, once the script is created, users may share it with others and a central management system, which can facilitate the sharing of all created scripts.
The new feature is incorporated in the Automate tab on the Ribbon for online Excel. This Ribbon offers the functionality to record actions as shown in the screenshot below:
Users may start recording the script in a similar way to VBA. The Record Actions pane will show all the actions performed and generate titles for each individual step. Once the script is created, users can save the script locally or upload it to a cloud server with customised names and detailed descriptions so that others may download the script and apply it elsewhere (you can share the script with designated others or the entire world). You don’t need to be a programmer — you simply “point and click” and create a library to promote collaboration with others and reuse of resources.
Allow me to demonstrate with a generic example. Suppose we have a raw data table with monthly Total Revenue and Expected Revenue, as shown in the screenshot below:
If we highlight this data (preferably in a table, Ctrl+T), we can insert a line chart showing the comparison between Total Revenue and Expected Revenue (eg, Alt+F1), as shown in the screenshot below.
All the steps performed would be shown in the Record Actions pane:
Once the recording finishes, the process can be saved and viewed in the Code Editor pane. It is here that people may choose to run or edit the script before sharing it with others:
Let’s have a look at how to edit the script. Clicking on Edit, a user can access the detailed code section displayed below:
In this case, the Code Editor records all the steps performed in what is known as TypeScript, including comments and intelligent code coalescing (ie, it helps improve performance and reduces storage requirements). Similar to VBA macro recording, all the steps recorded are written in a logical order, and the comments generated will help users to understand the purpose of the specific steps and provide an indication of which actions have been made. If necessary, users can also amend the parameters or built-in functions in the coding area to customise their requirements.
Users can also write self-defined functions in the Code Editor, similar to user-defined functions (UDFs) in VBA. For example, if the users want to add comments, the syntax can be formed with intelligent code coalescing to create a function to add comments to workbook context by defining the Item and Range objects.
The script generated can be further shared with others in the workbook, as shown below:
If the script is shared with others, when other users are working on the same workbook, they can access the shared script by referring to the Code Editor interface and run the shared code as required.
As mentioned previously, the scripts generated can also be integrated with Power Automate, which Microsoft improved not too long ago with the addition of user interface (UI) flows, which facilitate automation across the broader IT ecosystem, not just Excel or even Office. This new UI flows connector enables users to record click steps on an application UI, which facilitates making changes to specific steps in parts of the flow. The generated script can then be assigned to different flows.
Therefore, Office Scripts offers a way for users to discover how routines may be used in the workbook without a Ph.D. in coding. It is a convenient method for nonprogrammers. From the perspective of an accountant, Office Scripts effectively reduces unnecessary repetitive tasks by enabling users of all capabilities to automate their workflow and processes without deep understanding of the underlying code (JavaScript). In essence, it empowers accountants to generate reports and run data analysis readily and easily.
For example, imagine you wanted to generate charts for multiple data tables with the same data structure, imported from different databases or sources. Here, you may use Office Scripts to record an extract, transform, and load (ETL) process for a single data table and create the chart based on the structured data as a script. This may then be applied to each different source data to produce the required charts automatically.
Given that Office Scripts also allows any code recorded to be shared with others, it helps the team to create and maintain a library of operational codes. The Code Editor can also facilitate reviewing, tracking, and customising the code as required. This way, Office Scripts can streamline normal accounting processes, such as generating journal entries and business intelligence reports, and reduce time spent on repetitive tasks going forward.
Word to the wise
It’s easy to confuse Office Scripts with Script Lab, especially as the latter is now available for Excel on the web. Over three years ago, Microsoft released Script Lab, a little-known add-in for Excel (and other applications).
Script Lab was created as a tool for anyone who wanted to learn about writing add-ins for Excel, Word, or PowerPoint. Microsoft was — and still is — keen to push the JavaScript application program interface (API), as it sees it as the technology needed for building Office add-ins that run across platforms.
Script Lab has three main features:
- Code in a pane beside your spreadsheet.
- Run the code in another pane beside the editor.
- Share your snippets elsewhere.
The problem was that it was really aimed at expert users and, quite frankly, didn’t really take off. This add-in is mostly used by developers, especially for what is known as object-oriented programming (OOP):
When running the code, the end user simply clicks the button and obtains the results required.
The code uses TypeScript as well as Office Scripts, but it neither has the functionality of recording the steps nor is particularly friendly for nonprogrammers. Anyone wanting to use this add-in is likely to possess some understanding of JavaScript, CSS, HTML, API programming, and/or OOP.
It is powerful, but probably not for your typical accountant. Professional developers may find this add-in useful, since they can create projects directly in the pane, run the code, and share it with others, but it is not as user-friendly as Office Scripts.
— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.