Run Office Scripts with Power Automate
Power Automate lets you add Office Scripts to a larger, automated workflow. You can use Power Automate to do things like add the contents of an email to a worksheet's table or create actions in your project management tools based on workbook comments.
Important
To use Office Scripts in Power Automate, you must have a business license of Microsoft 365. The Office 365 Enterprise E1 and Office 365 F3 licenses can use Scripts with Power Automate, but don't have Power Automate integrations directly in Excel.
Tip
Looking to automatically run a script without connecting to other apps? Use the Script scheduling feature.
Get started
If you are new to Power Automate, we recommend visiting Get started with Power Automate. There, you can learn more about all the automation possibilities available to you. The documents here focus on how Office Scripts work with Power Automate and how that can help improve your Excel experience.
Step-by-step tutorials
There are three step-by-step tutorials for Power Automate and Office Scripts. These show how to combine the automate services and pass data between a workbook and a flow.
- Tutorial: Update a spreadsheet from a Power Automate flow
- Tutorial: Automatically save content from emails in a workbook
- Tutorial: Send weekly email reminders based on spreadsheet data
Create a flow from Excel
You can get started with Power Automate in Excel with a variety of flow templates. Under the Automate tab, select Automate a Task.
This opens a task pane with several options to begin connecting your Office Scripts to larger automated solutions. Select any option to begin. Your flow is supplied with the current workbook.
Tip
You can also start making a flow from the More options (…) menu on an individual script.
Excel connector
The Excel Online (Business) connector gives your flows access to Excel workbooks. There are two actions that call Office Scripts.
- Run script. This is the action to use with scripts stored in the default location of your OneDrive.
- Run script from SharePoint library. This is the action to use when scripts are stored in your team's SharePoint site.
For the Run script action, the script location is always in your OneDrive.
Data security in Office Scripts with Power Automate
The "Run script" action gives people who use the Excel connector significant access to your workbook and its data. Additionally, there are security risks with scripts that make external API calls, as explained in External calls from Power Automate. If your admin is concerned with the exposure of highly sensitive data, they can either turn off the Excel Online connector or restrict access to Office Scripts through the Office Scripts administrator controls.
For admins who have enabled Conditional Access policies for unmanaged devices in their tenant, it's a best practice to disable Power Automate on unmanaged devices. This process is detailed in the blog post Control Access to Power Apps and Power Automate with Azure AD Conditional Access Policies.
Data transfer in flows for scripts
Power Automate lets you pass pieces of data between flow actions. Scripts can be configured to accept the information you need and return what you want from your workbook to your flow. Data is passed to scripts as static values, expressions, or dynamic content. Details on an individual service's connector can be found in the Power Automate Connector documentation.
Learn the details of how to pass data to and from your scripts with the following documentation.
- Learn by doing with Tutorial: Automatically save content from emails in a workbook and Tutorial: Send weekly email reminders based on spreadsheet data.
- Try the Automated task reminders sample scenario to see everything in action.
- Read Pass data to and from scripts in Power Automate for more usage scenarios and the technical TypeScript details.
Example
The following screenshot shows a Power Automate flow that's triggered whenever a new response to a Form is submitted. The flow runs a script that adds the satisfaction value from the Form to a table. The current average satisfaction is returned and sent as an email.
The main function of the script specifies the new satisfaction value as an input parameter (newData). The script returns the average satisfaction value from the table.
function main(workbook: ExcelScript.Workbook, newData: string): number {
// Add the new data to the table.
const table = workbook.getTable("SurveyTable");
table.addRow(-1, [newData]);
// Get the current satisfaction total.
const satisfactionColumn = table.getColumnByName("Current Satisfaction");
const values = satisfactionColumn.getRangeBetweenHeaderAndTotal().getValues();
let total = 0.0;
values.forEach((value) => {
total += value[0] as number;
});
// Return the average satisfaction.
return total / values.length;
}
Note
The script parameter is of type string because that is the type the Forms action returns for all values.
Find full examples with step-by-step walkthroughs in Office Scripts samples and scenarios. Many use Office Scripts in the center of Power Automate flows.