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,
PowerShell to Find All Folders that Contains more than 5000 Items in SharePoint Online:
This PowerShell script scans given site collection for folders with > 5000 items stored at the same level.
Audit Folder Item Count at Tenant Level using PnP PowerShell:
This time, let's audit all folders from site collections in the entire tenant.
- 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".
PowerShell to Find All Folders that Contains more than 5000 Items in SharePoint Online:
This PowerShell script scans 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") $LargeLists = Get-PnPList -Web $Web | 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 List Root 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 $_ -web $Web -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 -UseWebLogin #Iterate through all webs in the site collections Get-PnPWeb | Audit-ListviewThreshold Get-PnPSubWebs -Recurse | ForEach-Object { Audit-ListviewThreshold $_ } Disconnect-PnPOnline #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://CrescentIntranet.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") $LargeLists = Get-PnPList -Web $Web -Connection $SiteConn | 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 List Root 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 $_ -Web $web -Connection $SiteConn -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 -UseWebLogin #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 $SiteConn = Connect-PnPOnline -Url $_.URL -UseWebLogin -ReturnConnection #Call the Function for Web & all Subwebs Get-PnPWeb -Connection $SiteConn | Audit-ListviewThreshold Get-PnPSubWebs -Recurse -Connection $SiteConn | ForEach-Object { Audit-ListviewThreshold $_ } Disconnect-PnPOnline -Connection $SiteConn } #Export Documents Inventory to CSV $Global:ListviewInventory | Export-Csv $CSVPath -NoTypeInformationMy related post on List view threshold in SharePoint Online
No comments:
Please Login and comment to get your questions answered!