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.

Adnan, a distinguished professional, boasts an impressive track record as a Microsoft MVP, having achieved this prestigious recognition for the eighth consecutive year since 2015. With an extensive career spanning over 18 years, Adnan has honed his expertise in various domains, notably excelling in SharePoint, Microsoft 365, Microsoft Teams, the .Net Platform, and Microsoft BI. Presently, he holds the esteemed position of Senior Microsoft Consultant at Olive + Goose. Notably, Adnan served as the MCT Regional Lead for the Pakistan Chapter from 2012 to 2017, showcasing his leadership and commitment to fostering growth within the tech community. His journey in the realm of SharePoint spans 14 years, during which he has undertaken diverse projects involving both intranet and internet solutions for both private and government sectors. His impact has transcended geographical boundaries, leaving a mark on projects in the United States and the Gulf region, often collaborating with Fortune 500 companies. Beyond his roles, Adnan is a dedicated educator, sharing his insights and knowledge as a trainer. He also passionately advocates for technology, frequently engaging with the community through speaking engagements in various forums. His multifaceted contributions exemplify his dedication to the tech field and his role in driving its evolution.

Leave a Reply