Generating SharePoint List Item Count Report with PowerShell

SharePoint is a popular platform for business collaboration and content management. However, managing large SharePoint environments can be challenging. One common task is to get an inventory of all lists across all sites in a site collection, including the item count for each list. This can be useful for capacity planning, performance tuning, and migration planning.

In this blog post, we will discuss a PowerShell script that connects to a SharePoint site and generates a report of all lists and their item counts.

PowerShell Script for SharePoint List Item Count

function GetListItemCountForSite($siteURL, $csvFile)  
{
    Try {
        Connect-PnPOnline -Url $siteURL -Interactive
        $SubWebs = Get-PnPSubWeb -Recurse -IncludeRootWeb
        $ListItemCount = New-Object System.Collections.ArrayList
        Foreach ($Web in $SubWebs)
        {
            Write-host -f Yellow "Getting List Item count from site:" $Web.URL
            Connect-PnPOnline -Url $Web.URL -Interactive
            $ExcludedLists  = @("Reusable Content","Content and Structure Reports","Form Templates","Images","Pages","Workflow History","Workflow Tasks", "Preservation Hold Library")
            $Lists= Get-PnPList | Where {$_.Hidden -eq $False -and $ExcludedLists -notcontains $_.Title}
            foreach ($List in $Lists)
            {
                $Data = @{
                    "Site Name" = $Web.Title
                    "Site URL" = $Web.Url
                    "List Title" = $List.Title
                    "List URL" = $List.RootFolder.ServerRelativeUrl
                    "List Item Count" = $List.ItemCount
                }
                $ListItemCount.Add($Data) | Out-Null
            }
        }
        $ListItemCount | Out-GridView
        #$ListItemCount | Export-CSV $CSVFile -NoTypeInformation        
        Write-host -f Green "List Inventory Exported to Excel Successfully!"
    }
    Catch {
        write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
    }
}

The provided PowerShell script uses the SharePoint PnP (Patterns and Practices) cmdlets to connect to a SharePoint site and retrieve information about all lists in the site collection.

The script starts by defining a function GetListItemCountForSite that takes two parameters: the site URL and the CSV file path. The function first connects to the SharePoint site using the Connect-PnPOnline cmdlet. It then retrieves all subsites in the site collection using the Get-PnPSubWeb cmdlet.

The function then initializes an ArrayList $ListItemCount to store the list information. It loops through each subsite, connects to the subsite, and retrieves all lists in the subsite using the Get-PnPList cmdlet. It excludes certain system lists that are not typically of interest.

For each list, it creates a custom object with properties for the site name, site URL, list title, list URL, and list item count. It adds this object to the $ListItemCount ArrayList.

Finally, the function outputs the $ListItemCount ArrayList to the GridView for easy viewing. It also includes a commented-out line to export the data to a CSV file. You can uncomment the export-CSV function to export the output to CSV file

Running the Script

To run the script, you need to provide the site URL and the CSV file path. The script then calls the GetListItemCountForSite function with these parameters.

$SiteURL = "https://mstalk.sharepoint.com/sites/NewSite"
$CSVFile = "C:\reports\ListItemCount.csv"
GetListItemCountForSite $SiteURL $CSVFile

This PowerShell script allows you to quickly and easily get an inventory of all lists in a SharePoint site collection, including the item count for each list. This can be invaluable for capacity planning, performance tuning, and migration planning.