Troubleshoot Office Scripts running in Power Automate
Power Automate lets you take your Office Script automation to the next level. However, because Power Automate runs scripts on your behalf in independent Excel sessions, there are a few important things to note.
Tip
If you're just starting to use Office Scripts with Power Automate, please start with Run Office Scripts with Power Automate to learn about the platforms.
Avoid relative references
Power Automate runs your script in the chosen Excel workbook on your behalf. The workbook might be closed when this happens. Any API that relies on the user's current state, such as Workbook.getActiveWorksheet, may behave differently in Power Automate. This is because the APIs are based on a relative position of the user's view or cursor and that reference doesn't exist in a Power Automate flow.
Some relative reference APIs throw errors in Power Automate. Others have a default behavior that implies a user's state. When designing your scripts, be sure to use absolute references for worksheets and ranges. This makes your Power Automate flow consistent, even if worksheets are rearranged.
Script methods that fail when run in Power Automate flows
The following methods throw an error and fail when called from a script in a Power Automate flow.
| Class | Method |
|---|---|
| Chart | activate |
| Range | select |
| Workbook | getActiveCell |
| Workbook | getActiveChart |
| Workbook | getActiveSlicer |
| Workbook | getSelectedRange |
| Workbook | getSelectedRanges |
Script methods with a default behavior in Power Automate flows
The following methods use a default behavior, in lieu of any user's current state.
| Class | Method | Power Automate behavior |
|---|---|---|
| Workbook | getActiveWorksheet |
Returns either the first worksheet in the workbook or the worksheet currently activated by the Worksheet.activate method. |
| Worksheet | activate |
Marks the worksheet as the active worksheet for purposes of Workbook.getActiveWorksheet. |
Data refresh not supported in Power Automate
Office Scripts can't refresh data when run in Power Automate. Methods such as PivotTable.refresh do nothing when called in a flow. Additionally, Power Automate doesn't trigger a data refresh for formulas that use workbook links.
Script methods that do nothing when run in Power Automate flows
The following methods do nothing in a script when called through Power Automate. They still return successfully and don't throw any errors.
| Class | Method |
|---|---|
| PivotTable | refresh |
| Workbook | refreshAllDataConnections |
| Workbook | refreshAllPivotTables |
| Worksheet | refreshAllPivotTables |
Select workbooks with the file browser control
When building the Run script step of a Power Automate flow, you need to select which workbook is part of the flow. Use the file browser to select your workbook, instead of manually typing the workbook's name.
For more context on the Power Automate limitation and a discussion of potential workarounds for the dynamic selection of workbooks, see this thread in the Microsoft Power Automate Community.
Pass entire arrays as script parameters
Power Automate allows users to pass arrays to connectors as a variable or as single elements in the array. The default is to pass single elements, which builds the array in the flow. For scripts or other connectors that take entire arrays as arguments, you need to select the Switch to input entire array button to pass the array as one complete object. This button is in the upper-right corner of each array parameter input field.
Time zone differences
Excel files don't have an inherent location or timezone. Every time a user opens the workbook, their session uses that user's local timezone for date calculations. Power Automate always uses UTC.
If your script uses dates or times, there may be behavioral differences when the script is tested locally versus when it is run through Power Automate. Power Automate allows you to convert, format, and adjust times. See Working with Dates and Times inside of your flows for instructions on how to use those functions in Power Automate and main Parameters: Pass data to a script to learn how to provide that time information for the script.
Script parameter fields or returned output not appearing in Power Automate
There are two reasons that the parameters or returned data of a script are not accurately reflected in the Power Automate flow builder.
- The script signature (the parameters or return value) has changed since the Excel Business (Online) connector was added.
- The script signature uses unsupported types. Verify your types against the lists under the parameters and returns sections of Run Office Scripts with Power Automate article.
The signature of a script is stored with the Excel Business (Online) connector when it is created. Remove the old connector and create a new one to get the latest parameters and return values for the Run script action.
See also
Feedback
Submit and view feedback for