This article is contributed. See the original author and article here.
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.
Run Office Scripts via Power Automate
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
Share scripts with others in your organization
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.
Please feel encouraged to reach out to us! Office Scripts is still in preview, and your input is critical to the team’s ability to offer something truly great.
- If you have any feedback or suggestions for the new Office Scripts feature, please let us know via User Voice.
- If you get stuck, or have any questions about how to accomplish something, please ask questions on StackOverflow under the “office-scripts” tag.
- If you run into problems with the Script Recorder or one of the APIs, please send us feedback via the Help > Feedback button in Excel for the Web.
Happy Office Scripting!
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.