Admin Reports Folder Library List PnP PowerShell PowerShell SharePoint SharePoint Online 

SharePoint Online: Find All Folders Exceeding List View Threshold Limit of 5000

Salaudeen Rajack 0 Comments

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.”
    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”.
sharepoint online Something Went Wrong

So, we wanted to find all folders (including the root folder of a list or library, and sub-folders) with 5000 items in it, to proactively avoid end-user issues.

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 -NoTypeInformation

My related post on List view threshold in SharePoint Online

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply