Build an instant flow which iterate over a SharePoint library that have 2 million documents and folders

Question

We have a SharePoint document library which contain around 3,000 customers subfolders inside the root folder, and total of 2 million files and sub-folders inside the 3,000 customer sub-folders.

Now we added a custom column named "CustomerFilesLastModifed" , which should be equal to the latest date any file inside the related customer sub-folder was modified.. for example let say we have this case:-

1. Customer sub-folder named "CustomerA"

2. Inside the "CustomerA" sub-folder we have 2 files:-

• Expenses.xlsx was modified on 12 Feb 2022

• Profile.docx was modified on 10 March 2023

3. then the "CustomerFilesLastModifed" date for "CustomerA" sub-folder should be equal to 10 March 2023 (the latest modified date for the files inside the sub-folder).. and so on for the other customers sub-folders.

so for this to work i will need to create an instant flow which can loop through all the 2 million documents.. so does Power automate support this type of operations? or i need to look into creating an azure function or power shell script?

Thanks

Answer 1

As @Derek Gusoff mentioned, doing such a large bulk operation is tricky and will likely run into throttling issues, and while I would agree with the comment to push back on the idea of doing it all at once, you can still do something towards reaching the requirement.

You could set up a flow like this:

• Trigger on item updated
• If the item is a file:
• Find the parent folder and set the CustomerFilesLastModified field
• (It isn't clear from your question if you only need to set the field on the immediate parent subfolder, but you could also have the flow continue to climb up the folder hierarchy, setting the field on each parent folder, until you reach the first level of the original 3000 customer folders, if that is your requirement)

While this wouldn't have all folders immediately populated with the correct data, it would allow for the data to build up over time without any intense resource usage or heavy bulk operations.

This is a step in the right direction, and probably what you want as a long term solution anyway. If you still have a requirement to get the data populated on all folders ASAP, you at least have something working for you making incremental progress while you take the time to figure out how to do the bulk operation.

Answer 2

You are going to run into throttling issues no matter what approach you try. In a database, updating two million records is no problem, but updating that much data in SharePoint is a problem. It just isn't designed for that kind of volume.

What's worked for me in the past for bulk operation is: Azure Queue backed by an Azure Function with queue trigger. The reason I like this is failure mitigation is baked into the architecture. When you hit throttling errors the items are re-queued and can be processed when the throttling has passed.