Today I’m looking at querying libraries using flow. Flow is making this easy as it has an action called Get Files (properties only)
You specify the site, library and off you go. Easy isn’t it?
I hear you say: Where is the pain in that? This is SharePains! We want pain!
Well pain you are going to get!
First of all have you noticed the default = all in the above action? It is not true! by default this action returns 100 items. Wen you push the results into an apply to each you will see just 100 items returned.
I’m going to start by counting the number of items in my Flow. using the Apply to each step:
When you set the limit to 5000 then you will see 5000 items returned
What if your library has more than 5000 documents in it? We all know that the REST API requires indexes on columns to return more than 5000 items.
I set the number of items to be returned to 6000.
In my flow run I was quite quickly seeing messages like this:
after 4 retries Flow gives up and I’m starting to get worried that I’m hitting issues similar to the REST API handling 5000 items.
Time to use my indexes. My document library needs some indices. I tried of could to create an index on the ID column but it isn’t possible to create indices once you have more than 5000 documents.
So setting the Filter Query to filter by modification date should sort this out:
Ok, I’m really pushing Flow now. I’m still getting 5000 items back but I’m able to query all by 8000 documents. In my case I don’t need all 8000 document to be returned I just need to be able to query all 8000 document and just get a couple of documents returned.
So in short, if you didn’t plan the for the number of document to exceed 5000, make sure that you look at the automatically created column indices on your libraries. it might give you an acceptable escape route.
Note: When you create an index on a column on a large list you will not know when the index is created, however the index will be created. You just don’t know when. So when you run into issue, create the index and just wait!
Nice,
I hope some OData query examples for SharePoint documents about each property, for query quick.
Another pain I’ve found with the Get Items action is that there’s a maximum 12 lookup columns allowed in the library (this includes workflow status columns, we’ve got reuseable workflows so there are a few).
So I’m working on using the new (as of May 2018) action “Send an http request to SharePoint” and “Parse JSON” to get similar results to the Get Items action.