The newest form of automation in Excel is Office Script; this uses the power of TypeScript and the web to automate spreadsheets! In this post, we’ll be recording an Office Script using the script recorder.
This recorder provides an easy to use experience for getting started in automating your Excel Online workbooks!
Getting Started
You need to have the following before getting started in Office Script and Power Apps:
- An educational, business or enterprise license of Office 365
This sort of license is required in order to have Power Apps and to run Office Scripts. - Office Scripts must be enabled by your administrator
This can be done in the Microsoft 365 admin console under Settings > Org Settings.
- All recordings must be done in Excel Online
At this time, Office Script can only be done in Excel Online.
It is not recommended to use Script Lab, which is another way to automate Excel. This add-in is primarily used for building and testing Office Add-Ins which are much more complex than Office Scripts.
Let’s Start Recording an Office Script!
If you have Office Script enabled, you will see the Automate tab available in your spreadsheet.
The automate tab is comprised of 3 elements:
- Record Actions – This allows you to record a script based on steps that you take in Excel Online.
- Code Editor – This allows you to edit a recorded script, or to record a new script.
- Recently Used Scripts – This section allows you to quickly select scripts that have been recently run.
We’ll start by clicking on Record Actions.
Recording an Office Script using Record Actions
After clicking on Record Actions, the Record Actions pane appears on the right side of your spreadsheet.
At this point, the record actions section is blank. However; there is a message below Record Actions showing Recording.
This means that all your steps in Excel Online are being recorded!
Let’s see what happens when we enter something on the spreadsheet.
Editing a Spreadsheet when Recording Actions
The first thing to note is that simply selecting cells will not record an action.
This is different from recording a macro in desktop Excel (VBA); in that case, selecting cells would be recorded using the macro recorder!
When we add text, such as “I am recording a script using Excel Online”, it creates a record in the Record Actions window that a cell has been edited.
If adjustments need to be made to the cell, such as bolding or increasing font size, these will be recorded as well.
Now that a very basic script has been recorded, we can click on the Stop button to stop recording our actions.
The Recording Finished! (Now what?)
After a brief wait, the code editor will show the steps that you have successfully recorded.
As a default, the script has recorded with the name Script 1. This is also the name that appears in the recently used scripts.
Renaming an Office Script
By clicking on the name of the script in the editor, you can bring up a dialog box to rename the current script.
For more advanced options, such as sharing the script or adding comments to the script, you can enter into the Script Details for additional actions that can be performed.
Running an Office Script
Running a script is done by pushing the Run button on the Code Editor Details screen. This will run the code that has been written or recorded.
The script will run as soon as the button is pressed.
And the code has been recorded!
Ways to get to the Code Editor Details
If the script was one of the most recently recorded scripts, clicking on one of these scripts will bring up the code editor details for that script.
Alternatively, if you need a script that is not one of your 3 most recently recorded or edited scripts, you can click the drop down menu to select additional scripts.
Finally, clicking All My Scripts will bring up a list of all the scripts that you have recorded.
Conclusion
The script recorder provides a simple way to get started with Office Script in Excel Online, similar to the VBA recorder in Excel desktop.
However; like VBA, there is a lot more power within Office Script than just the script recorder!
This new method for automation will allow for integration with the Power Platform, and provides a starting block for advanced automation using Office Add-Ins in ways that VBA never could!