Excel table to email - keeps timing out!

[marymascari]

I've been working on a Flow that keeps timing out. I've spent two full days on it, and I'm ready to jump out the window.

I have a spreadsheet with a table. The table is populated by a query.

The table has two columns with employee IDs: a main and a backup.

I need to send an email to each employee with the rows of the table that relate to them (either as the main or the backup).

Oversimplified, it looks like this:

Employee No	Backup Employee No	Other columns

The way I'm doing this now is:

1. I have a list of 200 employee IDs in SharePoint. I use a Get Items to retrieve the ID number and Email address columns. This usually takes 2-5 minutes.

(it always pulls all 200, even though I put the Order By and Top Count in)

2. Then I iterate through each of these rows (I've tried putting the data into an array and iterating through that, but it hasn't made any difference that I could tell).

• I put the ID number and the Email addresses into variables
• I pull the rows from the Excel table, filtered by the ID number
• I populate an array with the relevant values from the Excel rows.

This takes 6 minutes, if it doesn't time out.

Then I do the whole thing again, this time filtering the rows where the Backup ID = the current ID number.

This always times out.

3. The last step is to take the array of data, turn it into an HTML table, and then email it out to the person.

I have tried putting all the data from the Excel table into an array and then filtering that instead of the Excel rows each time but I couldn't get that to work.

It's also a killer because every time I try to test it, it takes 10 minutes, even if it works.

Please tell me if there's a better way to do this.

[Paulie78]

Quite a lot going on here, so couple of things worthy of mention:

Step 1. I have a list of 200 employee IDs in SharePoint. I use a Get Items to retrieve the ID number and Email address columns. This usually takes 2-5 minutes. Is the Get Items action in a loop or is something else going on? A simple Get Items action with a list of 200 people should be more or less instant to retrieve. Have you gone over your allocation of flow runs?

Step 2. You don't need to put the values from the SharePoint list into variables inside your apply...loop. You can reference them with something like item()['emailAddress']. Removing these steps will speed things up, but nothing like the amount you need.

Here is how I would modify it:

1) Get Items from SharePoint

2) List rows present in a table (don't filter with oData query)

3) Loop around SharePoint list items

4) Within the loop use a filter action to filter the results of your Excel data

I guess your problem may be that you are performing so many "List rows present in a table" actions and that is causing things to slow down a great deal. You really only need to do it once and you can re-use the output many times, by using the filter array action.

[marymascari]

What do you mean by "allocation of Flow runs"?

[JulienOlivier]

@marymascari 
Agreeing with @Paulie78 , having an Excel action in your "Apply to each" will throttle (i.e reaching the Excel connector throttling limit which is quite low). So it will cause some waiting time until the limit is relieved.

Also, when testing a flow, a timeout message might show up after 10 minutes.
However it does not mean that it did time out.
You can check this from the flow run history (your test run should show as "still running").

Another strange point to me, is your SharePoint "Get items" action that is returning all 200 items instead of 3.
I made a quick test in my tenant as you did and it's returning me 3 items, as it should.
You may want to double check this action.
Hope it helps!

Julien