Remember, Office Scripts are only available when using Excel (or other Office 365 applications) on the Web.
Login to the Office.com website with your Microsoft 365 account, launch Excel for the Web, and start a blank workbook.
Select the Automate tab.
From here, you can record your actions, view your scripts, or try out some pre-recorded scripts.
NOTE: If you do not have the Automate table it is because either your network administrator has deactivated this feature via a policy, or you don’t have a Microsoft 365 Business or Enterprise account.
Recording an Office Scripts “Macro”
Just like in Excel and VBA, we can record a “macro”, or in this case, a script.
This tool will automatically generate the necessary code based on your actions.
The “Record Actions” feature isn’t as sensitive as the VBA Recorder; some actions are not recorded using this tool. Admittedly, the VBA Recorder doesn’t catch every action either, but it is a bit more robust than the “Record Actions” feature.
Keep in mind, Office Scripts is still in its infancy compared to VBA which has been around for decades and has seen dozens of development cycles.
The fact that we have a script recorder at all makes it easier for business users to begin learning the underlying code.
Recording a Script
We can record a simple Office Script by pressing the “Record Actions” button, then perform a series of common tasks, such as:
- Enter text in cells, like titles and subtitles.
- Make the text bold.
- Change the text color.
- Change the font size.
- Add a border to the title.
The above actions are recorded by the script recorder and listed on the right of the window.
We stop the recorder and give the script the name “Formatting”.
Clicking the Edit button will display the underlying script code.
A fantastic feature of the script recorder is the inclusion of automatic comments to document the code in a more natural language format.
“Where are Scripts Saved?”
Scripts are saved in a file that is separate from the Excel file from which it was created. The scripts are saved in a file named “OfficeScripts” in a folder on your OneDrive site.
This means that the script is easily repurposed for use in any other Excel file. This is like storing VBA Macros in the Personal Macro Workbook.
The scripts can be connected to a file, so it travels with the file when shared with other users.
I can go to any other Excel file and run this script from the Automate ribbon.
“What about looping, IF statements, & more complex code?”
More complex code can be hand-crafted in the Code Editor, but there are many differences between VBA and TypeScript.
One difference is object selection. In VBA, if you perform an action, like selecting a cell, and you do not tell VBA which sheet holds the target cell, VBA assumes you want the cell on the currently selected sheet (i.e., the Active Sheet).
In TypeScript, you are required to tell the code which sheet you wish to manipulate.
Another HUGE difference between VBA and Office Scripts is the method by which target ranges are selected.
If we wish to get the cell address of the first cell on the sheet (the cell in the first column and the first row), we could write a statement like the following.
let MyAddress = selectedSheet.getCell(1,1).getAddress()
We will display the results using the Console Log feature. This will display the results on the screen in the Code Editor panel.
If we ran our code on a sheet named “Sheet3” we will see the following result.
Notice that TypeScript thinks that the cell on Row 1 and Column 1 is cell B3. This appears to be incorrect.
The issue is that Office Scripts is a zero-based language; it starts counting at 0 (zero) instead of 1 like VBA.
We need to adjust our brains to remember to offset the position value by one, like in the following example.
let MyAddress = selectedSheet.getCell(0,0).getAddress()