Filtering a table in a spreadsheet using a SharePoint list

[seedorfjwork]

Documents/Files overview:

Master spreadsheet containing 3000+ tasks for various projects

Sharepoint list of 40 different project numbers that are important to my team

Refined spreadsheet containing only tasks assigned to the relevant project numbers

Current Operations:

1. Wipe the Refined spreadsheet

2. Get the most recent project number list from the SharePoint

3. List all of the rows in the Master spreadsheet (unable to perform OData filtering using the values from the sharepoint list apparently)

4. On a row by row basis (apply to each) compare that row's Project number to the SharePoint list by looping through an if statement.

5. If the current row's project number = a project number in the SharePoint list then add the current rows values as a new row to the refined sheet

Problem:

After 20 minutes it's on row 60/3637 and is obviously far too slow to be practical.  How can I refine/replace this process to speed it up?

Edit:

My best guess is that every time it adds a row it opens, updates, saves and closes the spreadsheet (as evidenced by the fact that I can open the spreadsheet and see that it has been updated throughout the process). A solution would be finding a way to store the rows and add them later all at once.

[efialttes]

Hi!

I think you can improve performance from step 4: "On a row by row basis (apply to each) compare that row's Project number to the SharePoint list by looping through an if statement".

My suggestion is

1) to add an array withSharepoint project number. Select is much more efficient than 'Apply to each for such purpose'

2) to add an 'Filter array' assigning as its input your Master spreadsheet, then:

-assign to the left side of your condition rule 'Select' output

-assign to the right side of your condition rule current Master spreadsheet row 'Project id'

-operator 'contains'

3) to add an 'Apply to each' using as its input 'Filter array''s output, so you just iterate over your target projects, and not all the ones in the Master spreadsheet.

IN order to implement step 1), I would suggest the approach described here. The trick is to switch to Text Mode:

https://powerusers.microsoft.com/t5/Building-Flows/Create-a-variable-that-extracts-an-email-address-...

Hope this helps

[v-litu-msft · Accepted solution]

Hi @seedorfjwork,

I think you can optimize it in step 5.

"If the current row's project number = a project number in the SharePoint list then add the current rows values as a new row to the refined sheet"

Before the loop, initialize an array variable to store these rows. If the current row's project number = a project number in the SharePoint list, append current rows into the array variable.  After the loop through is over, loop through the array and add the rows to the target Excel file.

If so, there is no need to consider the conflict with add multiple rows in the same line, a tip could make the loop through run faster is that you could open the Concurrency control of the Apply to each action:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.