Hitting an action limit add rows to table

(0) Share

Report

Posted on by rpinxt

Build a flow to add rows to an excel table.

But this is so extremely slow....

Yes there are a lot of rows but that is just the point why want to automate this.

The flow has to add over 25000 new rows to a table.

It works just fine but after running more then 16 hours (which also is just ridiculous) it fails due to hitting an action limit (throttling limit).

I already set in the apply to each, Degree of Parallelism to the max (50) so that I cannot improve further.

Did we just hit a hard limit here of power automate and can it just not be done or do I have still other options?

Why is there no way to just add and entire range of new excel files to a table/excel and can it be only done with 1 row (even cell by cell) at a time?

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (0)

takolota1 4,974 Moderator on at

Like (0)

Report

@rpinxt

For a long time, no we couldn’t add more than 1 row at a time. But check out these new batch action templates…

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/td-p/1634375

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (1)

Report
@rpinxt, yes, this can be in a few steps, using an action which will send all of the data at once through to Excel.

This will involve the use of the (free!) Send an HTTP request action that is inside the Office 365 Groups connector (see my follow up response below) which will basically send every single value all at once.

It's not going to be complete plain sailing, but if you can edit into your original question:

A screen shot of every step of your flow as it is right now.

Some sample data to show what is coming in at a given point to make all these thousands of entries.

Those two things will really assist making a step-by-step flow to handle this for you.

The only real difficulty will be in ensuring that the information is in the right format, but that *really* shouldn't be too hard to get there.

You don't *even* need to make it a table, but you might want to ... I dunno.

From what I can understand, though, thus far ... you have a number of separate excel sheets that you which to merge into one, that about the size of it?

edited to show a clear way forward rather than getting bogged down in our conversation

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@eliotcole @rpinxt

You could try it that way, but you will…
-Need a premium license for the HTTP action.
-Need to somehow get the correct range values for all your rows which probably makes the flow set-up much more complicated & makes updating rows in bulk very difficult (you’d probably just delete the table & start from scratch each time).
-Need to develop your own set of actions to format everything with the right values instead of using a pre-built template.

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report
Not so, @takolota!

The action is free and Microsoft made

The range is a new one, and even if it's not, matching columns up isn't going to be taxing (and I'm dumb!)

The formatting would have to be setup somewhere either way

So, don't worry, @rpinxt ... The Send an HTTP request action is in the Office 365 Groups connector and directly allows you to run pretty much ANY Graph call that your user has the licence to perform. It is NOT a premium connector. I can make this work for you, don't worry.

Here is how you get it:

1

2

3

So ... now that you know this, you can use a Select action to build the JSON data super easily, and BOOM, instant (ish) Graph input to an excel sheet.

It'll still take a little while for so much data, and it won't be "entry level" stuff, but I'll help you understand it. Plus we'll make sure that you are leaving annotations for everything.

It's just a matter of ensuring that the data is represented in the right way in the JSON, there.

If you send me a private message with sample data, or put up (in the original question) an example of some, then I'll happily show you how this can be done.

One question ... as it will make life a lot easier ... there's no formula that need to go in the excel sheet, are there?

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@eliotcole

SHOOT! That works on any MS Graph call?!!
The 1st time I worked with that action it was only in the context of group calendars. I must have gotten it conflated with the idea it was just for groups stuff in MS Graph, like the HTTP call specific to SharePoint. Because a couple calls I tried to make didn’t work on that the same way they worked on the standard premium HTTP action. That’s wonderful news!

Now how do you expect to get the row range for the range call though? Or do some of those calls only require the column range if you also provide the id?
Because that’s my main concern with that is the rows not the columns. If you have to get row numbers for the ranges then creating a batch of items requires enough extra set-up getting the last row index or full length of the table (assuming you want to add to an existing table with existing rows). But batch updates or batch delete would just be terribly more difficult.
Again, maybe you’re thinking to just recreate the table each time, which is likely possible, but depending on the table size, that may still take quite a while, & it’s wasteful if you only need to add like 1k rows to a 100k table. Plus you may have to find additional resources for actions to size & format the new table. Unless you really don’t need it accessible through power automate.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@eliotcole

I hope you have some additional solution, but there is a reason I spent a week developing these templates. It’s because I looked & didn’t find anything as good & I knew it would be really useful.

If it had already existed & could be easily found, I have other projects I could spend my time on.

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report
Regarding the range, etc, it's really not going to be too troublesome, mate.

Basically all you'd do is:

Identify incoming data
(as agnostically as possible - ie. not prescriptively looking for specific things)

Use a Select to build an object for each row

Place that as the value for the 'values' key in the excel call

Send the call

With any luck there's no formatting and/or formulas needed, but that needn't be a big deal, either.

I'll demo with some of @rpinxt's data if they give us something nice to work with ... but you're brainier than me, so I'm sure you'll get it. It's just logic stuff, really.

Like I say, though, what's key is to annotate everything, here. I would suggest (to rpinxt) that it all be in a separate Compose or two in key areas, for future users of the flow.

Also, since it might have complexities, it should maybe have some very basic error handling, but that's super simple.

----

There are a LOT of functions available in the excel Graph stuff, many of which aren't in Flow as actions.

For example, if you wanted to get the entire used area of a worksheet then there's the getusedrange Graph call (which I've mentioned before but can't remember if you were in thread) which works whether or not a table is there at all.

I've used that to get data when there's no table around, or (if desired) to find the area of which to make one.

Obviously I'm not saying that as a specifically useful thing here, necessarily, just that there's a lot that can be done. I'm very confident that I'll have this pushing the data across without too much hassle. But it will still be a lot to process, and should be handled properly (ie. using Select/Filter/etc.) to ensure that it's managed correctly.

----

Oh, and hehehehe ... I know how you feel about touching some of these actions, I do it all the time ... like ... I *constantly* forget how useful the OneDrive Convert action is, or that it exists.

Also ... just like the SharePoint API call, it occasionally gets fussy about HTTP encoding when there's more than one or two GUIDs in there.

----

Finally ... I'd really like to offer a HUGE piece of advice here, over and above everything that's being discussed ... and it really and truly is stated without any 'poking' at anything:

Reports and Excel, in general, are not the best way way to display data anymore.

There's far better ways to impart the information which mean that none of this need ever be done.

By far the easiest of these is bespoke SharePoint pages and/or list views, which can ALWAYS be up to date information which shows exactly what the person viewing it needs to see. These can be tailored to groups, public, private, individual, etc, and even be tabs on Teams channels.

If you need an excel sheet for some legacy system to process it, that's understandable, however producing a report should rarely (if ever) be required for anything other than one-off occasions.

However, if I *am* automating something, or placing it in a more responsive format (like SharePoint) then I'll always build in a 'I don't like it' button for people that refuse to move from legacy ways of doing things.

This means that they can still have their cake, and you can eat it. 😉

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@eliotcole

“For example, if you wanted to get the entire used area of a worksheet then there's the getusedrange Graph call (which I've mentioned before but can't remember if you were in thread) which works whether or not a table is there at all.”

Alright so there is a way to get the current data range address in a different call if you need to input the end of that range for the new call to insert new range values after it. That’s good to hear.
I still think it would be difficult to do any update or delete where all the rows will have different ranges throughout a table. Thus I think the best approach there is to just recreate the entire range once you have the new table version with the updates & deletions. Which is not simpler than anything I proposed & will likely not achieve the best performance. But this specific person asked only for adding, not updating or deleting, so I digress.

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report
Updating is possible, too, @takolota ... and like you say, it's not simple, but it's not really hard, either.

It will just take a little onerous effort to set it up right.

Once it's done once, and annotated properly, it'll be very simple to maintain for future usage.

Here's the Range section of the docs (really quite useful), plus, like I said ... you can ALWAYS make the range into a table, and even add a reference column to 'databasify' it. Then either add/update/modify using the normal tools. However that brings it back down to slower processing.

Like I said (without insult) in one of my sections above, though ... I highly suspect (like with 90% of all non-financial/scientific data) that there's a better way to store and display this information than an excel sheet.

---

If I were to just ball-park this and guss, I'd think maybe the most efficient way to do it would be:

Two initial parrallel flow lines:

Process the data; to the right format for the Graph call

This will maybe have more than one step, but hopefully not

Process sheet; for readiness:
Doesn't need too agnostic an approach, it's known there's a table

Start a session with the Create a Workbook Session call in the Graph connector

Get the table id/name
(Flow action/Graph call)

Use the Create TableRow Graph POST call which can add multiple rows in one shot.Ensure it has the headers

Prefer: respond-async

Workbook-Session-Id: {Workbook-Session-Id}

Once the asynchronous update has responded as done (potentially not simple), close the session (graph call) and stop the flow

Like I say, that's off the top of my head, but it's doable, and feels like it's even simpler than some of the stuff that I was talking about previously. It won't be in and out, but there's a rough path to follow.

I only mentioned a lot of that stuff to be as agnostic as possible. The fact that the user has a table there already simplifies this greately with the Create TableRow call which is more powerful than the single row flow action.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@eliotcole
Thanks I’ll keep that on file, especially for the use-cases I didn’t design for like handling ranges that are not tables.

But to go back to my proposal, the steps for what I’m saying are.

1. Download the batch create template.
2. Copy the script code into any online Excel instance. Change the standard actions to pull from your desired source, select the columns & values you want to update, and the destination Excel file & script you are using.

Then enjoy the 3000 per minute row creations with like 2% the traditional number of API calls.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

I really tried to make this almost as simple as using a couple standard Excel connector actions.

Was this reply helpful? Yes No