Excel Tips & Tricks #470 – Introducing Office Scripts in Excel

Office Scripts is a feature that allows users to record and document actions in Excel so you can automate repetitive tasks in Excel. For example, you may run a monthly process where you download data from another system and then use Excel to format and clean the data. This may involve removing empty rows and formatting the data into a table. You can use Office Scripts to record the steps in this process. The next time you download the data, you can then run the script to perform the same tasks.

This feature was initially available in Excel Online but can now be found in the ‘Automate’ tab in the desktop version.

It’s important to note that Office Scripts is only available in enterprise and educational licenses and in some cases an administrator may need to enable this feature before you can use this.

You may already be familiar with creating automated templates using VBA and the macro recorder. While Office Scripts is similar to VBA in many ways, it offers compatibility with online and desktop platforms over VBA. VBA also cannot be used with Power Automate.

Office Scripts uses TypeScript which is a JavaScript based programming language to code actions in Excel, which some suggest is more user-friendly than VBA. There is a similar feature in Google Sheets which is called App Scripts.

Office Scripts is an important tool to understand how to use and can be used in conjunction with Power Automate in Excel. 

In the ‘Automate’ tab in Excel, there are two scripting tool options which include ‘Record Actions’ and ‘New Script’.

The ‘Record Actions’ button allows you to record the actions you have taken to edit your spreadsheet and save them in Office Script format. The script can then be used to run the same actions again on different spreadsheets.

To start creating a new Office Script, click the ‘Record Actions’ button. A new pane will open up on the right-hand side where you will see a list of the actions you’re taking.

In the example above, I have formatted the column width of data and used custom sort to sort my data into an order that is useful for me. 

When I’m done with all the steps, I can press the Stop button to stop recording my actions. This will open up the ‘Code Editor’ pane which will show me my script.   

In this pane, I can edit the details of my script and save it. I can also add a description, view script access and activity here to monitor who has access and any changes made to the script.

New Script

With the ‘New Script’ button, more experienced users can access the ‘Code Editor’ in Excel to use their own code to build a new script from scratch.

You can also edit existing scripts. This is useful when you need to add in additional functionality, such as conditional statements (if/else) and loops, that isn’t supported by the action’s recorder.

Once you’ve created and saved a script, you will be able to see it when you click on ‘All scripts’ in the ‘Automate’ tab.

This will open the ‘Code Editor’ pane on the right-hand side where you can see all your recently accessed and saved scripts, scripts used in the workbook as well as a collection of sample scripts built into Excel.

 Let’s demonstrate this with a simple example. I can use a script I created to transform my data into a table, and format and sort it so the data is ordered by ‘Location’ and ‘Item’.

To run the script, I navigate to the ‘Automate’ tab and then click on ‘All Scripts’. This will open up the ‘Code Editor’ pane. From here, I select the script I want to use and then click on the 3-dot menu and select ‘Run’.

The script then runs, and my data is transformed!

Yes. Anyone with access to the spreadsheet will be able to view the scripts used in the spreadsheet. However, only those with spreadsheet edit permissions will be able to run them.

A recommended method to flag scripts and make it easier for others to run them, is to create a button in your worksheet – functionality that is very similar to the ability to add buttons to trigger VBA macros.

To do this, from the ‘Code Editor’ pane I can click on the 3-dot menu of the script I’d like to create a button for. From here, if I click on ‘Add button’ this will add a button to my spreadsheet in my selected cell.

I can then use the button to run the script directly.

You can also share just the scripts you have created with others within your organisation from the ‘Code Editor’ pane as shown earlier.

It’s important to remember that Office Scripts currently only works in Excel and not all actions are supported at the moment. While Microsoft is constantly updating this, if you record an unsupported action, this will be noted in the ‘Record Actions’ pane and will not be added to the script. 

You will also need to use Power Automate to automatically trigger scripts to run based on certain actions or schedules. Power Automate is also needed to run scripts that require access across multiple workbooks and interaction with your desktop and other applications.

In future tips we’ll be exploring Office Scripts in a bit more detail, to look at how to really make the most of them to support your automations! If you’ve got any great examples where you’ve used Office Scripts, do get in touch at excel@icaew.com, we’d love to hear from you.