Using an HTTP call for 2500 excel rows

(0) Share

Report

Posted on by Misa

248

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:

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.

Categories:

Using connectors

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

Barret 652 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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).

Was this reply helpful? Yes No
Misa 248 on at

Like (0)

Report
Copy link

Link copied!

@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?!?

Was this reply helpful? Yes No
Barret 652 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Misa 248 on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No