Run Office Scripts with Power Automate

Article
02/08/2024

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.

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.

A task pane showing flow template options such as 'Schedule an Office Script to run in Excel and then send an email' and 'Run an Office Script in Excel when a Microsoft Forms response is received'.

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.

Run script
Run script from SharePoint library

For the Run script action, the script location is always in your OneDrive.

The Run script action with completed fields that show the location is 'OneDrive for Business', the document library is 'OneDrive', the file is 'daily-readings.xlsx', and the script is named 'Format Table'.

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 Power Automate flow editor showing the example flow.

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.

Share via