Office Scripts in Excel – Power Automate your Spreadsheet

Office Scripts in Excel is a new feature in Office365 that allows your to Power Automate complex spreadsheet calculations that normally would require a VB script.

Robert Sep, one of the founders of The Qoders Community, shared an update in his LinkedIn feed on Office Scripts that caught my attention.

Office Scripts in Excel Online

“Office Scripts in Excel on the web let you automate your day-to-day tasks. You can record your Excel actions with the Action Recorder, which creates a script. You can also create and edit scripts with the Code Editor. Your scripts can then be shared across your organization so your coworkers can also automate their workflows.”

If you are experienced in Microsoft Excel, than you have touched the topic of VBA code. Office Scripts in Excel Online is an online version of the Macro Recorder that you can find in the Excel client for Windows 10. It also reminds me of Microsoft Windows Steps Recorder (that is used to record steps for example to reproduce errors) but then for VBA code.

So basically you record the steps, called actions, you would like to do and a script is created. The other option is to use the Code Editor, so you can manually script to your needs. So if you are a walking VBA library than the Code Editor may be the preferred way of crafting you scripts. Please check out the review of Visual Studio Code as well, the free open source code editor of Microsoft.

Office 365 subscription requirements

Office Scripts in Excel Online is not available for all subscriptions.

For example I’m using an Office 365 E1 subscription for a long time now, simply because it gives me what I need and has a super interesting price point. Unfortunately Office Scripts in Excel Online is -in preview- not available for E1. So hopefully it will become available when Office Scripts will get out of the preview phase.

On the requirements page for Office Scripts in Excel Online you can see that most subscriptions are supported: “Any commercial or educational Microsoft 365 license with access to the Microsoft 365 Office desktop apps”.

When Office Scripts is available in your tenant you simply need to activate it from the admin center. Spreadsheet Guru has published a how to guide for enabling Office Scripts for Excel Online. Fairly easy 🙂

Connecting Office Scripts to Power Automate

Microsoft has always had the integration power of different products in their portfolio. This is now also applicable for Office Scripts for Excel Online and Power Automate. In terms of platforms you see the Office platform and the Power platform working hand-in-hand.

“Learn how Office Scripts integrates with Power Automate to increase the capabilities beyond the power of Excel to help businesses easily automate tasks and run businesses efficiently anytime, anywhere.”

Robert Sepp summarized the scenario steps in his LinkedIn post. Daniel Wilson, Program Manager at Microsoft, gives you a good idea of the capabilities of the integration with Power Automate in the YouTube video above, which will cost you about 22 minutes of time.

The following articles on the  give an excellent idea of what’s possible:

• Power Automate Blog:  Announcing support for Office Scripts in the Excel Online (Business) connector;
• Excel Blog of Microsoft Tech Community:  Office Scripts: Announcing a simplified API, Power Automate support, and sharing;

Concluding thoughts and wrap-up

Robert Sep and Marc Priem are former Microsoft colleagues that I’ve worked with at Microsoft Consultancy Services. They have founded The Qoders Community, so please check out their website over here. Robert thanks for sharing the Office Scripts information in your Linkedin feed. 🙂

Honestly it is cool to see that the Marco Recorder for Excel has been taken to the cloud. When you combine Office Scripts in Excel with the Power Automate and Power Apps integration it becomes a very powerful combination with endless possibilities. And if you have smart BI specialists working in your enterprise backend, the topic of governance on the scripts could become an area you need to look at.

I like this development, and hopefully we will see some more improvements before Office Scripts will go out of preview.

Furthermore I’ve published a couple of other articles with add-ins for Microsoft Excel, and a bunch on Power BI related articles.

• Power BI Publisher for Microsoft Excel;
• Transform Data by Example for Microsoft Excel;

What do you think about Office Scripts for Excel in combination with Power Automate ?