Office Scripts have been available for preview in Excel on the Web for over six months now. Today, we are excited to announce that Office Scripts have received several significant updates, including (1) a simplified API, (2) the ability to run Office Scripts via Power Automate, and (3) the ability to share scripts with others in your organization.
Office Scripts relies on Office JS, a set of APIs that allow developers to interface with Excel—usually to create Add-ins. With Office Scripts, we wanted to ensure that the immense benefits of automation would be available to all Excel users, not just those with highly advanced programming knowledge. In pursuit of that goal, we’ve taken significant steps to simplify these APIs and to make them more approachable and intuitive.
Office Scripts APIs can be invoked in a synchronous manner without having to worry about programming concepts such as promises or callbacks.
Easier API design that includes simple object collections, getter and setter methods for property access, better error handling options, etc.
Improved APIs to interact with Excel objects such as range cell, table rows and columns.
Development time static type checks offered by TypeScript language-based IntelliSense.
One of the things we’ve heard on countless occasions from our users is the desire to be able to schedule scripts, to trigger scripts based on a particular event, and to interface with other services and products than just Excel. As of today, all of that is now possible through Power Automate.
For those unfamiliar, Power Automate is a Microsoft product and platform which allows you to create automated workflows. Each of these “flows” contains a trigger and one or many connectors. Triggers define when a flow will execute—for example, every Monday at 10am—and connectors define what actions will occur. Connectors can perform actions across a whole host of different tools and services, from Microsoft offerings like sending an email in Outlook, to 3rd party platforms like fetching tweets from Twitter. All of this can be accomplished with little to no code.
For some time now, the Excel Online (Business) connector has allowed users to create flows that read and write data to tables in Excel workbooks. With the addition of the “Run script” action, you can now automate almost any task available in Excel on the Web—and set it up to run without you ever having to intervene manually. As of today, if you can record or write an Office Script that accomplishes a task you might otherwise do by hand, you can now accomplish that task regularly without ever having to open Excel.
Some examples of what you can accomplish:
Sending an email status report to one's broader team every Monday at 9am
Processing data submitted to Microsoft Forms and visualizing it using Excel's charting engine
Storing information about tweets which include a particular hashtag in a spreadsheet, then alerting a Teams channel about the update
Examining a team's status tracking spreadsheet for completion and sending a both email and teams reminders to colleagues who have yet to complete their assigned sections
Fetching issue data from a GitHub repository and summarizing and visualizing it for use in a report
Office Scripts can now be shared with other users of an Excel workbook. When you share a script in a workbook, the script is attached to the workbook and is visible to and runnable by anyone with whom the workbook is shared.
This is an immensely powerful collaboration tool for organizations; while not everyone may be motivated enough to learn to code and to automate their work with Office Scripts, now, team members can leverage the ingenuity and hard work of those that are.