ICAEW.com works better with JavaScript enabled.

Excel Tips & Tricks

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

Author: Bani Lamba

Published: 28 Apr 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello and welcome back to Excel Tips and Tricks! This week we have a Developer level post where provide a high-level overview of Office Scripts in Excel, a potential replacement for VBA to help automate tasks.

What is Office Scripts and where can I find it 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.

excel image

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. 

How can I create a script?

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

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.

excel image

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.   

excel image

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.

excel image
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.
excel image

Where can I see the scripts I have created?

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.
excel image
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.
excel image

How do I run a saved script on a new spreadsheet?

 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’.
excel image

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!

gif 1

If I share my spreadsheet, will my colleagues be able to see my scripts?

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.

excel image
I can then use the button to run the script directly.
gif 2

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.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.