Is there API equivalent for SharePoint.Files function

[romankorchak]

Hi All

I am still trying to find the best way to get a list of old files, older then certain date, from sharepoint sites

And the quickiest way so far is to use SharePoint.Files function in the power query to build power bi report - it gets all the files in one go and doesnt complain about 5000 limit threshould 

But we have a few thousанds sites and need to get that report for all of them, so I am wandering if there are an API equivalent for SharePoint.Files power query function so I can use it in a flow or powershell script 

I have tried SharePoint "_api/web/lists/GetByTitle('Documents')/items?$filter" but it complains about list view threshold

Any ideas?

Thanks

[christinepayton]

IMO, if your end goal is to help users clean out old content, you might be better off to use PowerShell to create a view on all the libraries that shows items outside of folders, sorted aescending by modified date. That would give people a view to directly clean up old files inside their site, instead of referencing a report, then cross-checking on filename in the libraries. This is assuming you are asking users to clean it up and not programmatically removing files. 

If you hit the list view threshold with this, you can set a date filter on it, like last modified < [Today] - 1000 to show only things that are super old - that usually cuts down the size substantially. 

[romankorchak]

Main goal is to get a report of how many/total size of old files in each site. I do have a powershell script that loops through eatch site to gather that info, but it takes weeks to complete (as its going through each site folder to check for old files ).
   Regarding the view threshould - that API link gives "The attempted operation is prohibited because it exceeds the list view threshold" error even if the site has more than 5000 files in total but less than 5000 old files, so its checking for a threshold limit before filtering  

[Greg_Deckler]

@romankorchak Can you just wrap it in a custom function with a single parameter, import a list of your sites and then just add a column that calls the function with the site URL? Then just expand that column.

[romankorchak]

I did think of that, but we have around 4000 sites with few k to few 10k old files per site, so if I load all of it into one pbi report its will just crash. unles there is a way (which I dont know of) to save old files list into csv/db per each site in a looping through sites, so do something like
 

foreach(site in sites){
   save list of old files in the site into csv or database 
} 

in a PBI DataSet (we have a Fabric so could do it in the dataflow)