Office Scripts

For years, Microsoft has been trying to replace VBA (Visual Basic for Applications), the programming language that “lives” in the background of Excel. As they say, the reasons are of a security nature. This is not entirely without merit, but it seems to me that the motive of the Excel development team, to a greater extent, was to create scripts that would fit better into the Microsoft 365 and Power Platform ecosystem. Whether they succeeded in this is up to you to try. 

Office scripts allow you to automate frequently repeated tasks, similar to recording macros. They are written in the TypeScript programming language, which is actually an improved version of the JavaScript language, with more readable code. Those who used to deal with web programming will find themselves on familiar ground! In many cases, you will generate the scripts automatically. If necessary, you can modify the code and adapt it to your needs.

After updating Excel, the Automate ribbon will appear, which contains options for working with Office scripts. If it’s not there, try adding it manually using the Customize the Ribbon option (accessed after right-clicking on the File tab).

You can create a new script by clicking the New Script button. The Code Editor will open on the right side of the screen, where you can write code in the TypeScript language. When you create a new script, it will be given the generic name Script1, which you can change to something more acceptable. Click on Run to start the script, Save Script is used for saving, and if you open the menu marked with “…” you will see some additional options.

Another way to create scripts is using the Record Actions option, which detects actions in Excel and generates TypeScript code based on them. After you start it, the Record Actions panel will open on the right. The Recording label means that action detection is in progress. Every time you perform an action, its description will appear in the list. By clicking on Restart, you start the process from the beginning, and by clicking on Stop, you stop the process and record the script.

In the Office Scripts gallery you can see some suggested and previously recorded scripts. By clicking on All Scripts, you will see all the scripts, those that you have generated yourself and templates for certain types of jobs. Automate a Task makes it possible to connect Excel with the Power Automate tool using one of the created scripts.

Are Office Scripts the right tool for you? All you have to do is try it and decide for yourself!