Discover Office Scripts, the new way to automate Office

There’s a new way to program or automate actions in Microsoft Office called Office Scripts.  We’ll explain what it is, how it’s different from Javascript or VBA and who can get it.

Office Scripts is available for some commercial users in Excel but it’ll spread to other Office apps and possibly/hopefully non-Business users.  Even if you’re not interested now, they’ll likely become relevant in the future.

According to Microsoft, Office Scripts:

“allow you to record and replay your Excel actions …”

Which doesn’t seem like a big deal because VBA can already do that. The change is in the following phrase:

… on different workbooks and worksheets”

Scripts are stored in the Microsoft cloud store for your organization unlike VBA which is saved with a workbook or file linked to a workbook.

Action Recorder

Like VBA, Scripts can be made by recording steps done manually.

Source: Microsoft

The interface is a lot clearer than the VBA ‘Record Macros’.

Record Actions makes an Office Script that you can use or, more likely, adapt.

Script code

Office Script code might come as a shock to anyone accustomed to VBA. Once you look past the style differences, a lot of the concepts in VBA continue in Office Script, such as objects, ranges and collections.

Source: Microsoft

Office Scripts use the Typescript language, a Javascript-like system, hence the semi-colons at the end of each command.

Sharing Scripts

Office Scripts are stored in the cloud and then linked to a workbook to allow anyone with edit access to the workbook permission to run the script.

Available scripts appear in the Automate tab

Source: Microsoft

Office Scripts with Power Automate

Because Office Scripts can be saved in the cloud, they can be used independently of workbooks.    Users could open a workbook then choose from a library of centralised scripts.

A good example of this is Power Automate, Microsoft’s IFTTT system for commercial users.

For example, Power Automate can be triggered when a new file is added to an online folder. A newly arrived file can be manipulated automatically by a Script.  That’s something VBA can’t do.

What about VBA?

VBA isn’t going anywhere. Existing and new VBA code will continue to work for the foreseeable future.

Who gets Office Scripts

Right now, Office Scripts have very limited access, you need:

  • Only Excel on the web has Script access for now.
  • The workbook saved to OneDrive for Business
  • A Microsoft 365 commercial or education licence which includes access to Office 365 desktop apps.  That means plans like Business Basic do NOT qualify but Business Standard does.

Microsoft’s plan is to extend Office Scripts to at least Word and PowerPoint plus, of course, the desktop apps.

Hopefully, Office Scripts will be expanded to allow all Microsoft Office 365 users access.  If Microsoft wants Scripts to succeed, it needs to be available to all.

More info

Microsoft has published a lot of information about Office Scripts.

Curious about how Scripts are shared?

Microsoft 365 Admins can control access to Office Scripts.

Being Microsoft documentation, there’s no place for bugs or limitations.  There’s sure to be problems with this first public release of Office Scripts plus things that are possible in VBA but not (yet) in Scripts.  That’s left for paying customer to waste time discover on their own.

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address