How to find files over certain limit in SharePoint Library with hundreds of folders

Hi luvsql 

The 5000 limit is really a legacy issue because SharePoint used to be a web front end to a SQL database using on premise servers. 

This legacy 5000 item issue still exists, I believe because code from the  good old days, was used for SharePoint online.

You're right libraries can hold 20million files (last time I checked the limits).

What the 5000 item limit means in practice is that when you have VERY big libraries you have to use PowerShell and PAGINATE  or PAGE the results, hence the code mentioned in this conversation has the 

Get-PnPListItem -List $List -PageSize 500

What that tells PowerShell is get the first 500 items, then follow the generated link to the next 500 items until there are no more items to get. If there are 10,000 items in the library the script will run (10,000 / 500 = 20) 20 times and get all the items.

If the script didn't have -PageSize 500 then it would get the most it can by default, which I think is 100.

I know this seems weird but Microsoft does this to reduce the cost (processor time and electricity) of us running scripts.

As users we just have to work with this foible to get what we need.

I'm trying the powershell, however since we use MFA, I had to first use Connect-SPOService -Url https://mytenant-admin.sharepoint.com which then prompted me to login and authenticate, but then using this script asked me to login again and my admin account won't work:

#Config Variables
$SiteURL = "https://mytenant.sharepoint.com/Estimating"
$CSVFilePath = "C:\Temp\LargeFiles.csv"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

#Get all document libraries
$FileData = @()
$DocumentLibraries = Get-PnPList | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $False}

#Iterate through document libraries
ForEach ($List in $DocumentLibraries)
{
Write-host "Processing Library:"$List.Title -f Yellow

#Get All Files of the library with size > 100MB
$Files = Get-PnPListItem -List $List -PageSize 500 | Where {($_.FieldValues.FileLeafRef -like "*.*") -and ($_.FieldValues.SMTotalFileStreamSize/1MB -gt 100)}

#Collect data from each files
ForEach ($File in $Files)
{
$FileData += [PSCustomObject][ordered]@{
Library = $List.Title
FileName = $File.FieldValues.FileLeafRef
URL = $File.FieldValues.FileRef
Size = [math]::Round(($File.FieldValues.SMTotalFileStreamSize/1MB),2)
}
}
}
#Export Files data to CSV File
$FileData | Sort-object Size -Descending
$FileData | Export-Csv -Path $CSVFilePath -NoTypeInformation