Today’s post is all about the scalability of Office Scripting in Microsoft Excel using the Run Script action in Power Automate. Do you know the limitations for solutions based on Office Scripting?
My setup
Table of Contents
Recently, I’ve worked on a solution that is based on Excel, Power Automate and SharePoint. nearly 2000 Excel files live in SharePoint and on a regular basis and Office script is run on all of these Excel files. When I first looked at this solution there were some scalability and stability issues as I mentioned recently in How to make your Excel connector retry in Power Automate.
Running an Office script from Power Automate
Today I’ll look at the Office Scripting part of the solution. To present the issues here I created an office script that updates a cell in Excel. Nothing too complicated, The script is just reading the current value and incrementing the value.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range A2 on selectedSheet
selectedSheet.getRange("A2").setValue(Number(selectedSheet.getRange("A2").getValue()) + 1 );
}My flow that now calls the script 2000 times is simple. My apply to each will run 2000 times the Run script action from the Excel connector.
Limitations of the Excel Connector
There are quite a few limitations of the Excel connector. The full list of limitations you can find below.
| Short description | Operations | Long description |
|---|---|---|
| Outdated data | List rows present in a table | The data may not be up to date (there may be some delay) when filtering or sorting is used in the List rows present in a table action. |
| Filtering capabilities | List rows present in a table | The List rows present in a table action supports basic filtering and sorting: – Supports the following filter functions: eq, ne, contains, startswith, endswith.– Only 1 filter function can be applied on a column. – Only 1 column can be used for sorting. |
| Stacked Filters | List rows present in a table | When a filter is applied using the action List rows present in a table, it will be added on top of the existing filters. |
| Non-Stacked Filters | List rows present in a table | If a filter is not applied using the List rows present in a table action, any filters already set in the Excel file will be ignored. The action retrieves all rows regardless of any pre-existing filters in excel. |
| Pagination | List rows present in a table | The List rows present in a table action returns up to 256 rows by default. In order to get all rows, turn on pagination. |
| Select nonsequential rows | List rows present in a table | The connector retrieves rows for 500 columns maximum in the List rows present in a table action. Rows for the first 500 columns are returned by default. You can provide a comma-separated list of specific columns to retrieve in a Select Query parameter. |
| Key column field name | Add a key column to a table Delete a row Get a row Update a row | The key column field is case-sensitive. |
| Update/delete multiple rows | Delete a row Update a row | In the case of multiple matches in operations such as Update a row and Delete a row, only the first row will be updated/deleted. |
| Microsoft Excel Macro-Enabled Spreadsheet support | Run script | The Run script action additionally supports the Microsoft Excel Macro-Enabled Spreadsheet (*.xlsm) format. To learn more, go to How to use macro files in Power Automate flows. |
| Table column headers limitation | Add a row Update a row Delete a row | Changes committed by operations such as Add a row, Update a row, Delete a row do not always take affect immediately after successful response from a corresponding Power Automate, LogicApps or Power Apps actions. Delays up to 30 seconds are expected due to underlying backend service limitations. |
| Run script operation limitations | Run script | Run script operation allows up to 3 calls per 10 seconds and up to 1600 calls per day. |
| Run script operation sovereign clouds support | Run script | Run script operation is NOT available in sovereign clouds (including Gcc, GccHigh, DoD and other higher clouds). |
Now the one limitation that I ran into is:
Run script operation allows up to 3 calls per 10 seconds and up to 1600 calls per day.
This means that we can only run 1600 calls per day and 18 calls per minute. Yes, I hear you say if you need to run scripts in Excel, is Excel the right solution? Well, in this case I didn’t have the option to move away from Excel (yet).
But what happens when you exceed the 1600 calls to scripts.
Testing the Run Script action
I created a basic flow that makes many calls and I found that after 1314 calls things went wrong
When investigating the failure, I saw Out of call volume quota. Quota will be replenished in 05:01:55 returned by every failed action.
So what is the solution here?
There isn’t any, as far as I’m aware. Other than making sure that you don’t call Office scripts this often. So if you are considering using Office Scripting in Power Automate for a solution that may grow over time then please feel free to contact me. I’m always happy to discuss alternative solutions on the chat on SharePains.com.
Discover more from SharePains
Subscribe to get the latest posts sent to your email.