Managing SharePoint Online Large Libraries using PnP PowerShell

Large SharePoint Online document libraries (with 5,000+ items) can cause:

List View Threshold issues
Slow query performance
Inefficient metadata & indexing
Permission inheritance delays

Using PnP PowerShell, we can:
Identify large libraries
Optimize views & indexed columns
Automate document management
Improve performance & security

Step 1: Connect to SharePoint Online

$siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
Connect-PnPOnline -Url $siteUrl -Interactive

Write-Host " Connected to SharePoint Online"

✔ Ensures secure authentication.

Step 2: Identify Large Libraries

Find libraries exceeding 5,000 items:

$largeLibraries = @()

$lists = Get-PnPList

foreach ($list in $lists) {
    if ($list.ItemCount -gt 5000 -and $list.BaseType -eq "DocumentLibrary") {
        $largeLibraries += [PSCustomObject]@{
            LibraryName = $list.Title
            Items       = $list.ItemCount
            URL         = $list.RootFolder.ServerRelativeUrl
        }
    }
}

$largeLibraries | Format-Table -AutoSize

✔ Detects potential performance bottlenecks.

Step 3: Enable Indexed Columns

Indexed columns improve query speed and list view performance:

$libraryName = "Documents"
$columnsToIndex = @("Created", "Modified", "Title")

foreach ($column in $columnsToIndex) {
    Set-PnPList -Identity $libraryName -IndexedColumns $column
    Write-Host " Indexed column added: $column"
}

✔ Prevents threshold errors in large lists.

Step 4: Create Managed Views for Large Libraries

Split large lists into filtered views:

$viewName = "FilteredView"
$libraryName = "Documents"
$filterField = "Created"
$filterValue = "2023-01-01"

Add-PnPView -List $libraryName -Title $viewName -Fields "Title", "Modified", "Created" -Query "<Where><Gt><FieldRef Name='$filterField' /><Value Type='DateTime'>$filterValue</Value></Gt></Where>" -RowLimit 1000

Write-Host " Created a filtered view: $viewName"

✔ Reduces data load per query.

Step 5: Enable Column Formatting for Large Lists

Apply JSON-based color-coding & icons to improve user experience:

$columnName = "Status"
$libraryName = "Documents"
$jsonFormat = '{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "style": {
        "color": "=if([$columnName] == \'Completed\', \'green\', \'red\')"
    },
    "children": [
        {
            "elmType": "span",
            "txtContent": "[$columnName]"
        }
    ]
}'

Set-PnPField -List $libraryName -Identity $columnName -CustomFormatter $jsonFormat
Write-Host " Applied column formatting to: $columnName"

✔ Enhances visual clarity.

Step 6: Automate Large Library Cleanup

Move old files to an archive:

$sourceLibrary = "Documents"
$archiveLibrary = "Archive"
$thresholdDate = (Get-Date).AddMonths(-12).ToString("yyyy-MM-dd")

$filesToMove = Get-PnPListItem -List $sourceLibrary | Where-Object { $_["Created"] -lt $thresholdDate }

foreach ($file in $filesToMove) {
    Move-PnPFile -ServerRelativeUrl $file["FileRef"] -TargetUrl "/sites/YourSite/$archiveLibrary"
    Write-Host " Moved file: $($file['FileRef'])"
}

✔ Reduces library size & improves performance.

Step 7: Bulk Set Unique Permissions

Apply custom permissions to specific files:

$libraryName = "Documents"
$filePath = "/sites/YourSite/Shared Documents/Confidential.pdf"
$userEmail = "user@yourtenant.com"

Set-PnPListItemPermission -List $libraryName -Identity $filePath -User $userEmail -AddRole "Read"
Write-Host " Assigned read-only access to: $userEmail"

✔ Ensures secure document access.

Step 8: Export Library Insights Report

Generate a CSV report of large libraries:

$reportPath = "C:\Reports\LargeLibrariesReport.csv"
$largeLibraries | Export-Csv -Path $reportPath -NoTypeInformation

Write-Host " Large libraries report saved at: $reportPath"

✔ Provides detailed performance insights.

Step 9: Automate the Script (Task Scheduler)

Schedule the script weekly for continuous monitoring:

$taskAction = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-File C:\Scripts\ManageLargeLibraries.ps1"
$taskTrigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At 3AM
Register-ScheduledTask -TaskName "Manage Large SharePoint Libraries" -Action $taskAction -Trigger $taskTrigger -RunLevel Highest

✔ Automates library optimization.