Office Scripts: Announcing a simplified API, Power Automate support, and sharing

[PM_DAN]

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.

Simplified API

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.

Some highlights:

• 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.

Read more about the newly simplified APIs for Office Scripts

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 3^rd 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

Read more about using Office Scripts with Power Automate

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.

Read more about sharing Office Scripts in Excel on the Web

Next Steps

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!

[Faraz Shaikh]

I'm just loving it ..!!! 

[OOO13377771610]

1. Can the script in one workbook use this apparently-special  API to access another workbook in the same tenant ? (as Power Automate does)

2. Does running the script directly or via-power-automate willl lock the file properties metadata from being edited? (as Power automate does)

[Cristiano Galvão]

Microsoft's initiatives to bring JavaScript to Excel, as well as its evolution, were already a good thing to see... but the possibility of connecting the scripts with Power Automate is really amazing. This thing brings a lot of possibilities for business.

[PM_DAN]

@OOO13377771610 great questions—

1. Can the script in one workbook use this apparently-special  API to access another workbook in the same tenant ? (as Power Automate does)
   • Yes—scripts are actually workbook-agnostic at the moment. You can use any script on any workbook, and you can create flows involving multiple workbooks. You can also pass data between scripts and workbooks by including parameters and return values in your scripts.
2. Does running the script directly or via-power-automate willl lock the file properties metadata from being edited? (as Power automate does)
   
   • That's actually a great question—as far as I know, a file's metadata shouldn't be locked while a script is running. Scripts run as if the user who owns them were directly performing the actions manually.
     • Edit: I've confirmed that files' metadata are no longer locked for editing in the Excel Online Business connector—this applies to both Office Scripts' "Run script" action and all other Excel connector actions.

[Ngô Quang Đạo AskSock]

So awesome! I'll try 

[Sergei Baklan]

@PM_DAN , as I understood Flow could run the script if only workbook is closed. If so it could be the showstopper for using it in some scenarios. Will it work in "co-authoring" mode?

[PM_DAN]

@Sergei Baklan great question—yes, Power Automate can run scripts against workbooks even while they are open.

Office Scripts edit a workbook as if they were the user themselves performing an action manually. Power Automate-run scripts perform the same way in co-authoring scenarios as "real" users.

[Sergei Baklan]

@PM_DAN , thank you, that's great. 

[OOO13377771610]

@PM_DAN Good Job.

Ref: Microsoft Word Host and Office Scripts and Power Automate. When can we expect a version of "Run Script" action but for the WORD host ?  I mean, is it more difficult than a copy-replace ?

Very important use case: being able to read/write information in the word document using the Javascript API through power automate, for example to cite/quote a particular paragraph from a Word document. (Reading the file content using the Sharepoint connector would only give you the Zipped XML,  right ?)

URGENT, THANKS, HEHE :>

[PM_DAN]

@OOO13377771610

Great question!

You can likely expect such a feature in the other Office connectors someday—I can't say for sure when, but I'm personally excited by the prospect of it as well. Unfortunately, it's quite a bit more difficult than a copy-replace.

There may actually be some ways to accomplish what you're looking for today—I don't know exactly how, but I'll reply here if I'm able to find an approach that would work.

[OOO13377771610]

@PM_DAN  OK, the OneNote API is in the graph https://docs.microsoft.com/en-us/graph/api/resources/onenote-api-overview?view=graph-rest-1.0

Also the Excel Workbook API is in the graph https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

But Word has neither graph REST API nor its Javascript API  is runnable from Power Automate ... At the present time I don't see any (Microsoft-supported-)ways to search and read a paragraph in any Sharepoint Word file from Power Automate or Word add-in.

[PM_DAN]

@OOO13377771610 

Here's one potential way, though I haven't yet validated whether you can perform actions with the downloaded file from within Power Automate—you may be able to use the OneDrive connector to download the file and read the XML. Clunky, but would perhaps allow you to accomplish what you're going for.

See:

https://stackoverflow.com/questions/51774488/accessing-a-word-docx-files-content-with-microsoft-grap...