PS., I tried the following solution, is there a better way to achieve the goal to improve the performance of the flow? Thank you.
1. Create an Office Script that you want to run on the Excel files. Make sure the script is stored in your OneDrive.
2. In Power Automate, create a new flow and add a trigger, such as "Manually trigger a flow" or any other trigger that suits your use case.
3. Add a "Send an HTTP request to SharePoint" action. Configure the action with the following settings:
- Site Address: The SharePoint site URL where your folder is located.
- Method: GET
- Uri:
'_api/web/GetFolderByServerRelativeUrl('<your_folder_relative_path>')/Files?$filter=substringof('.xlsx', Name) or substringof('.xls', Name)'
- Headers: Accept with value application/json;odata=verbose
4. Add a "Parse JSON" action to parse the response from the previous action. Use the following schema:
{
"type": "object",
"properties": {
"d": {
"type": "object",
"properties": {
"results": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Name": {
"type": "string"
},
"ServerRelativeUrl": {
"type": "string"
}
},
"required": [
"Name",
"ServerRelativeUrl"
]
}
}
},
"required": [
"results"
]
}
},
"required": [
"d"
]
}
5. Add an "Apply to each" action to loop through the Excel files. Use the "results" output from the "Parse JSON" action as the input for the "Apply to each" action.
6. Inside the "Apply to each" loop, add a "Run script" action from the Excel Online (Business) connector. Use the "ServerRelativeUrl" dynamic content from the "Parse JSON" action as the input for the "Location" field, and specify the script you want to run.
