How to create Excel records more efficently

Question

I have managed to create a logic app that goes through all the available subscriptions (via the List Subscriptions action), then collects their tag IDs and tag values (via a For_each loop with the List subscription resource tags action), and then stores them in an Excel file in Sharepoint. However, my approach to storing the values of the different tags has been at best agricultural: I have a For_each loop with a switch inside which loops through the different tag names and assigns their values to variables. Then, I use the Add row into table Excel action to compose a row for each subscription:

My problem is this approach is extremely slow when compared for instance to a PowerShell script: it takes the Logic App between 45 and 55 minutes to go through ~150 subscriptions, when the script running on my laptop would create the same Excel sheet in less than 4 minutes.

I was wondering if there would be a more efficient way to gather the tag names and values and pass their values to the Add row into a table action.

Answer 1

@Luis O Thanks for reaching out.

As you have business logic in your logic app and individual execution of action takes time and if you have more actions in your workflow then it will take more time for the execution and if you need more performance then you can review the below optimization in your flow:

• Wherever possible use the function reference in your action to minimize the number of actions that your workflow executes. For example, you can use if condition as an inline function with multiple cases rather than using the switch statement.
• Run your foreach in parallel rather than sequential wherever possible.
• I will suggest you to leverage the inline query action to do the calculation as it would be way more faster for these type of object modification/calculation but inline query does have the limitation on run duration, data size etc so please refer to the limitation before following this approach.
• Alternatively, the best approach would be offloading this functionality/calculation to azure function and call azure function from logic app with your custom data and then loop in though the return object to insert it to excel.

Feel free to get back to me if you have any queries or concerns.

Please click on 'Yes' if it helped so that it can help others in the community looking for help on similar topics.