Bulk Managing Large Lists with Indexed Columns using PnP PowerShell

Managing large SharePoint lists can be challenging due to list view threshold limits (5000 items). Using indexed columns and PnP PowerShell, you can efficiently:

Add or remove indexed columns
Query large lists without threshold errors
Improve SharePoint performance
Automate bulk operations

Step 1: Connect to SharePoint Online

Before managing large lists, connect to your SharePoint Online site:

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

✔ Ensures secure authentication.

Step 2: Check Existing Indexed Columns

To see which columns are already indexed:

$listName = "LargeList"

$indexedColumns = Get-PnPList -Identity $listName | Select -ExpandProperty Fields | Where-Object { $_.Indexed }

Write-Host " Indexed Columns in $listName:"
$indexedColumns | Select Title, InternalName, Indexed

✔ Identifies existing indexed columns.

Step 3: Add an Indexed Column

To index a new column (e.g., “Status”):

$columnName = "Status"

Set-PnPField -List $listName -Identity $columnName -Values @{Indexed=$true}

Write-Host " Indexed column added: $columnName"

✔ Helps optimize queries on large lists.

Step 4: Bulk Query a Large List Without Threshold Errors

Use indexed columns to fetch items efficiently:

$filteredItems = Get-PnPListItem -List $listName -PageSize 1000 -Query "<View><Query><Where><Eq><FieldRef Name='Status' /><Value Type='Text'>Active</Value></Eq></Where></Query></View>"

Write-Host " Retrieved $($filteredItems.Count) active items"

✔ Uses paging & indexed filtering.

Step 5: Bulk Update Large Lists Efficiently

To update a specific column for thousands of records:

$itemsToUpdate = Get-PnPListItem -List $listName -PageSize 1000

foreach ($item in $itemsToUpdate) {
    Set-PnPListItem -List $listName -Identity $item.Id -Values @{"Status"="Completed"}
    Write-Host "✏ Updated Item ID: $($item.Id)"
}

✔ Prevents threshold errors.

Step 6: Bulk Delete Old Records

Delete items older than 1 year:

$thresholdDate = (Get-Date).AddYears(-1)

$itemsToDelete = Get-PnPListItem -List $listName -PageSize 1000 | Where-Object {
    $_["Created"] -lt $thresholdDate
}

foreach ($item in $itemsToDelete) {
    Remove-PnPListItem -List $listName -Identity $item.Id -Force
    Write-Host "🗑 Deleted Item ID: $($item.Id)"
}

✔ Frees up storage space.

Step 7: Automate the Script Using a Scheduled Task

To run the script daily, follow these steps:

1️⃣ Save the script as ManageLargeList.ps1
2️⃣ Open Task Scheduler → Create Task
3️⃣ Trigger → Daily
4️⃣ Action → Start program → powershell.exe -File C:\Scripts\ManageLargeList.ps1

✔ Keeps SharePoint lists optimized automatically.