SharePoint Online: Find All Folders Exceeding List View Threshold Limit of 5000
Problem: Any SharePoint List, Library, or Folder contains more than 5000 items at the same level results list view threshold exceeded error messages in SharePoint Online. While the maximum number of items or files that can be stored in the SharePoint Online list or library is 30 million, more than 5000 items can’t be stored at the same level. They must be classified into sub-folders! If not,
- When you try to open in the “File Explorer” view, you’ll see “This folder is empty” message, Although the folder has content in it.
- If you try to navigate to these containers using SharePoint designer, you’ll get “Server error: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
- If you browse these libraries or folders, you’ll get one of the below error messages “Something went wrong. Please try again or refresh the page”.
So, to avoid end-user issues proactively, we wanted to find all folders (including the root folder of a list or library and sub-folders) with 5000 items in them.
PowerShell to Find All Folders that contains more than 5000 Items in SharePoint Online:
This PowerShell script scans the given site collection for folders with > 5000 items stored at the same level.
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Funds"
$ListviewThreshold = 5000
$CSVPath = "C:\Temp\ListviewThreshold.csv"
$Global:ListviewInventory = @()
$Pagesize = 2000
#Function to audit list view threshold
Function Audit-ListviewThreshold
{
[cmdletbinding()]
param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
Write-host "Scanning Large Lists on Site '$($Web.URL)'"
#Get All Large Lists from the Web - Exclude Hidden and certain lists
$ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
"Site Collection Documents", "Site Collection Images","Style Library")
$Lists= Get-PnPProperty -ClientObject $Web -Property Lists
$LargeLists = $Lists | Where-Object {$_.Hidden -eq $False -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt $ListviewThreshold}
#Process all large lists
$LargeLists | ForEach-Object {
$Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
Write-host "`tAuditing Items at List '$($Folder.ServerRelativeUrl)'" -f Yellow
#Check the Root Folder
If($Folder.ItemCount -gt $ListviewThreshold)
{
Write-host "`t`tFound a Folder with '$($Folder.ItemCount)' Items at '$($Folder.ServerRelativeURL)'" -f Green
#Collect data
$global:ListviewInventory += New-Object PSObject -Property ([ordered]@{
SiteURL = $Web.URL
FolderName = $Folder.Name
URL = $Folder.ServerRelativeUrl
ItemCount = $Folder.ItemCount
})
}
#Get Folders from List
$global:counter = 0;
$LargeFolders = Get-PnPListItem -List $_ -PageSize $Pagesize -Fields ItemChildCount,FolderChildCount -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting List Items of '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";} | Where {$_.FileSystemObjectType -eq "Folder"}
#Process all large folders in the list
$LargeFolders | ForEach-Object {
#Get the Item count in Folder
$ItemsCount = [int]$_.FieldValues.ItemChildCount
$SubfoldersCount = [int]$_.FieldValues.FolderChildCount
$ItemCount = $ItemsCount + $SubfoldersCount
If($ItemCount -gt $ListviewThreshold)
{
Write-host "`t`tFound a Folder with '$ItemCount' Items at '$($_.FieldValues.FileRef)'" -f Green
#Collect data
$global:ListviewInventory += New-Object PSObject -Property ([ordered]@{
SiteURL = $Web.URL
FolderName = $_.FieldValues.FileLeafRef
URL = $_.FieldValues.FileRef
ItemCount = $ItemCount
})
}
}
}
}
#Connect to Site collection
Connect-PnPOnline -Url $SiteURL -Interactive
#Iterate through all webs in the site collections and call the function
Get-PnPSubWeb -Recurse -IncludeRootWeb | ForEach-Object { Audit-ListviewThreshold $_ }
#Export Documents Inventory to CSV
$Global:ListviewInventory | Export-Csv $CSVPath -NoTypeInformation
Audit Folder Item Count at Tenant Level using PnP PowerShell:
This time, let’s audit all folders from site collections in the entire tenant.
#Parameters
$Domain = "CrescentIntranet" #Domain Name in SharePoint Online. E.g. https://Crescent.sharepoint.com
$ListviewThreshold = 4000
$CSVPath = "C:\Temp\ListviewThreshold.csv"
$Global:ListviewInventory = @()
$Pagesize = 2000
#Frame Tenant URL and Tenant Admin URL
$TenantURL = "https://$Domain.SharePoint.com"
$TenantAdminURL = "https://$Domain-Admin.SharePoint.com"
#Function to audit list view threshold
Function Audit-ListviewThreshold
{
[cmdletbinding()]
param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
Write-host "Scanning Large Lists on Site '$($Web.URL)'"
#Get All Large Lists from the Web - Exclude Hidden and certain lists
$ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
"Site Collection Documents", "Site Collection Images","Style Library")
$Lists= Get-PnPProperty -ClientObject $Web -Property Lists
$LargeLists = $Lists | Where-Object {$_.Hidden -eq $False -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt $ListviewThreshold}
#Process all large lists
$LargeLists | ForEach-Object {
$Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
Write-host "`tAuditing Items at List '$($Folder.ServerRelativeUrl)'" -f Yellow
#Check the Root Folder
If($Folder.ItemCount -gt $ListviewThreshold)
{
Write-host "`t`tFound a Folder with '$($Folder.ItemCount)' Items at '$($Folder.ServerRelativeURL)'" -f Green
#Collect data
$global:ListviewInventory += New-Object PSObject -Property ([ordered]@{
SiteURL = $Web.URL
FolderName = $Folder.Name
URL = $Folder.ServerRelativeUrl
ItemCount = $Folder.ItemCount
})
}
#Get Folders from List
$global:counter = 0;
$LargeFolders = Get-PnPListItem -List $_ -PageSize $Pagesize -Fields ItemChildCount,FolderChildCount -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting List Items of '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";} | Where {$_.FileSystemObjectType -eq "Folder"}
Write-Progress -Activity "Completed Retrieving Items from List $($Folder.ServerRelativeUrl)" -Completed
#Process all large folders in the list
$LargeFolders | ForEach-Object {
#Get the Item count in Folder
$ItemsCount = [int]$_.FieldValues.ItemChildCount
$SubfoldersCount = [int]$_.FieldValues.FolderChildCount
$ItemCount = $ItemsCount + $SubfoldersCount
If($ItemCount -gt $ListviewThreshold)
{
Write-host "`t`tFound a Folder with '$ItemCount' Items at '$($_.FieldValues.FileRef)'" -f Green
#Collect data
$global:ListviewInventory += New-Object PSObject -Property ([ordered]@{
SiteURL = $Web.URL
FolderName = $_.FieldValues.FileLeafRef
URL = $_.FieldValues.FileRef
ItemCount = $ItemCount
})
}
}
}
}
#Connect to Admin Center
Connect-PnPOnline -Url $TenantAdminURL -Interactive
#Get All Site collections - Filter BOT and MySite Host
$Sites = Get-PnPTenantSite -Filter "Url -like '$TenantURL'"
#Iterate through all site collections
$Sites | ForEach-Object {
#Connect to each site collection
Connect-PnPOnline -Url $_.URL -Interactive
#Iterate through all webs in the site collections and call the function
Get-PnPSubWeb -Recurse -IncludeRootWeb | ForEach-Object { Audit-ListviewThreshold $_ }
}
#Export Documents Inventory to CSV
$Global:ListviewInventory | Export-Csv $CSVPath -NoTypeInformation
My related post on List view threshold in SharePoint Online – FAQs
Don’t normally leave comments on this, but you just save the day for a support case I’m working on.
Just fyi not sure if this was a command that was misspelled or the name has changed, but:
Get-PnPSubWeb didn’t run, I had to change it to Get-PnPSubWebs. Worked a treat after.