I received an email recently stating one of my Microsoft Power Automate cloud flows had been throttled because it was making too many calls to the connector API. Since this flow is used for automating some run-the-business information transfer, I was concerned the information would not be available when needed.
The flow which had been throttled was set up as a scheduled flow to run every 15 minutes, take information from an Excel online file, and move it to a SharePoint list. However, the file is not updated every 15 minutes. Often, it is updated anywhere from 2-3 times per week. This is a very inefficient flow from the beginning! This flow runs 96 times per day!! Let’s do something different.
 |
| Old flow running 96 times per day. |
Why worry about this?
Bottom line up front: resources.
Many API calls can slow down the rest of your network. If you are triggering too many of these for critical resources such as an Enterprise Resource Planning (ERP) system (think SAP), that system can also be slowed due to the overwhelming number of requests it receives.
Additionally, some Power Automate plans are billed based on how many times an automation runs. You can save money by limiting how often your flows run if your business or enterprise has this approach to paying for Power Automate licensing.
Let’s optimize!
The Excel online file is stored on a Microsoft Team, so we can use SharePoint connectors as the trigger for the flow rather than a scheduled run every 15 minutes. Setting this up takes some creative thinking of the trigger and action options we have available.
The trigger we used is the SharePoint trigger “When an item or a file is modified” and the trigger is directed to the specific folder where we have our Excel online file saved. This will trigger the flow any time any item or file is modified in that specific folder, which is not exactly what we want. Therefore, we must collect some information on the file which triggered the flow.
We can do this by using the SharePoint action “Get file properties” and directing it to capture the properties of the ID of the item which triggered the flow.
The next step is the last of the changes I made to my flow before the rest of the flow remained “business as usual.” I created a condition control to only run the rest of the steps of the flow if the name of the file contains certain text. Of course, I could have used the condition which looks precisely for the specific filename, but I was not sure at the time of adjusting my flow if I should leave the “.xlsx” file extension within the scope of the condition or if I need to leave it off. Again, this is a run-the-business flow, so I need to adjust the flow to reduce API calls but not disrupt the flow of information.
 |
| New flow running as needed. |
At this point, we are done with modifying the flow for efficiency, and the flow will only run when a file or item in the folder it “listens” to triggers it to run. Furthermore, it will only run through all flow actions and facilitate information flow if the specific file in question is the file which triggered the flow to run.
Assuming this flow will only be triggered by the file which gets updated about 3 times per week, this is nearly a 97% reduction in API calls!
