请求编写SharePoint Online中项目数超5000的列表PowerShell脚本

Hey there! Here's a practical PowerShell script for SharePoint Online that will help you track down all lists with more than 5000 items. I’ve included comments and best practices to make it easy to adapt to your environment:

Prerequisites #

• First, make sure you have the PnP PowerShell module installed. If not, run this command in an elevated PowerShell window:

  Install-Module -Name PnP.PowerShell -Force -AllowClobber
  

• You’ll need SharePoint Online site collection admin permissions (or sufficient access to view site contents and list item counts across your target sites).

The Script #

# Replace this with your tenant root site URL (or a specific site collection you want to scan)
$tenantRootUrl = "https://yourtenant.sharepoint.com"

# Connect to your SharePoint Online tenant
Connect-PnPOnline -Url $tenantRootUrl -Interactive

# Get all sites in the tenant (filters out redirect sites to avoid unnecessary scans)
$sites = Get-PnPTenantSite | Where-Object { $_.Template -notlike "SPS*RedirectSite*" }

# Initialize an array to store our results
$largeLists = @()

foreach ($site in $sites) {
    Write-Host "Scanning site: $($site.Url)" -ForegroundColor Cyan

    try {
        # Connect to the current site (skips tenant admin check for faster processing)
        Connect-PnPOnline -Url $site.Url -Interactive -SkipTenantAdminCheck

        # Fetch all non-hidden, non-system lists (includes document libraries and generic lists)
        $lists = Get-PnPList | Where-Object { 
            !$_.Hidden -and 
            !$_.IsSystemList -and 
            ($_.BaseType -eq "GenericList" -or $_.BaseType -eq "DocumentLibrary")
        }

        foreach ($list in $lists) {
            # Safely retrieve the item count to avoid throttling issues
            $listItemCount = (Get-PnPProperty -ClientObject $list -Property ItemCount).ItemCount

            if ($listItemCount -gt 5000) {
                # Add list details to our results array
                $largeLists += [PSCustomObject]@{
                    SiteUrl = $site.Url
                    ListTitle = $list.Title
                    ListUrl = "$($site.Url)/Lists/$($list.Title)"
                    ItemCount = $listItemCount
                    ListType = if ($list.BaseType -eq "DocumentLibrary") { "Document Library" } else { "Custom List" }
                }
                Write-Host "Found large list: $($list.Title) with $listItemCount items" -ForegroundColor Green
            }
        }
    }
    catch {
        Write-Host "Error scanning site $($site.Url): $_" -ForegroundColor Red
    }
}

# Display results in the console
Write-Host "`n=== Summary of Large Lists (Over 5000 Items) ===" -ForegroundColor Yellow
$largeLists | Format-Table -AutoSize

# Optional: Export results to a CSV file for easy sharing/analysis
$exportPath = "C:\Temp\SPOLargeLists.csv"
$largeLists | Export-Csv -Path $exportPath -NoTypeInformation
Write-Host "Results exported to: $exportPath" -ForegroundColor Green

Quick Tips for Using the Script #

• Avoid Throttling: If you have hundreds of sites/lists, add a Start-Sleep -Seconds 2 inside the list loop to stay under SharePoint's throttling limits.
• Targeted Scanning: If you don’t need to scan every site, replace the Get-PnPTenantSite line with an array of specific site URLs (e.g., $sites = @("https://yourtenant.sharepoint.com/sites/sales", "https://yourtenant.sharepoint.com/sites/marketing")).
• Adjust Filters: Modify the Where-Object clause for lists if you need to include hidden/system lists (though these are rarely relevant for this check).
• Permissions: If you hit access denied errors, double-check that your account has the necessary permissions on the sites you’re scanning.

内容的提问来源于stack exchange，提问作者joshua maniquiz