Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : AgeefYzsxPvuixV4Wmufnv
Power Platform Community / Forums / Power Automate - Using Connectors / Using an HTTP call for...
Power Automate - Using Connectors
Answered

Using an HTTP call for 2500 excel rows

Like (0) ShareShare
ReportReport
Posted on 13 Mar 2024 13:03:27 by Misa 246

Hi.

 

I have an excel doc that has 2500 rows.  The data will be used to create a word template and then uploaded to our human resources system.  

 

What is the best way for the flow so that the HTTP Post does not timeout or handle more than 1 upload?  Below is what the flow looks like:

 

Misa_0-1710334878309.pngMisa_1-1710334909281.png

 

I am able to upload the document when i do 1 but am having issues when doing more than one.

 

BTW i also tried moving the Access Token and Parse JSON nodes outside of the Foreach loop and same result.

 

Any help will be greatly appreciated.  Thank you.

 

  • Misa Profile Picture
    Misa 246 on 13 Mar 2024 at 18:55:57
    Re: Using an HTTP call for 2500 excel rows

    @barret Thank you.   I changed the concurrent setting to 1 and the files did post in the OneDrive after about a minute.  So that seemed to help.  I'll need to check and experiment like you said with the flow because its still running.  Not a fan of Foreach/ForAll/Apply to Each loops because you cant see whats going on in the step until the flow either passes or fails unless you add notifications after each step.

     

    Thank you again for you suggestion.  I hope you have a wonderful rest of week.

  • bblake8480 Profile Picture
    bblake8480 642 Most Valuable Professional on 13 Mar 2024 at 18:42:04
    Re: Using an HTTP call for 2500 excel rows

    If you can re-use the same access token for each call, then yes, I would move it outside the for loop. And anything else that is common across all the loops. That will reduce overhead. As for the 23 minutes, it can depend on a great many factors and might be a little misleading. For example, when PA edits or opens an Office document (such as your template perhaps), it will put a lock on the file and could potentially take a while to release that lock, causing delays or errors between the iterations. So you might add a step for each loop to use a OneDrive file copy command to make a copy of the template, and then populate it. That's one of the things I've found can help sometimes. Unfortunately, it's one of those things that doesn't seem to be consistent and requires experimentation with different approaches to see what works best in each situation. There can also be a lot of external factors. For instance, various systems might be throttling your actions, slowing things down. 

  • Misa Profile Picture
    Misa 246 on 13 Mar 2024 at 18:21:06
    Re: Using an HTTP call for 2500 excel rows

    @barret That is correct.  It will produce and upload 2500 files.  Do you think that having the Access Token and Parse JSON node outside of Foreach loop will be necessary?  

     

    Thank you for your response.  I will try what you suggested.   I recent did another test and it looked like (according to when the docs were created in OneDrive) 23 mins per upload.  Like what?!?  

  • Verified answer
    bblake8480 Profile Picture
    bblake8480 642 Most Valuable Professional on 13 Mar 2024 at 18:07:16
    Re: Using an HTTP call for 2500 excel rows

    Just want to make sure I understand: If there are 2500 rows, then it will populate and upload 2500 different files? That's the impression I get from the flow template you posted. If so, the biggest potential issue I see is that a foreach loop in Power Automate, by default, will run a bunch of the loops in parallel (running groups at the same time). This could be hitting up against throughput limits with the file interaction populating the template and/or the HTTP calls. One thing to try: Go in to the configuration settings of the ForEach loop and find a setting called concurrency. Set it's value to 1 to start with. This will force the loop to run one at a time. See if that resolves the issue. If that works, you can experiment with increasing the concurrency until you find a balance that lets the flow run, but doesn't take forever to do so (running 2500 rows, one at a time, creating and posting a file will take a long, long time I would guess).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,342 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans