Handling Large SharePoint Online Lists Efficiently using PnP PowerShell

Managing large lists in SharePoint Online can be challenging due to list view thresholds (5000 items). Using PnP PowerShell, we can efficiently:

Query large lists without hitting thresholds
Use indexing and filters for better performance
Implement batch processing to handle bulk operations

Step 1: Connect to SharePoint Online

Connect to your SharePoint Online site:

$siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
Connect-PnPOnline -Url $siteUrl -Interactive
Write-Host " Connected to SharePoint Online"

✔ Establishes a secure connection.

Step 2: Enable List Indexing

For large lists, index key columns to improve performance:

$listName = "LargeDocuments"

Set-PnPList -Identity $listName -EnableThrottling $false
Add-PnPFieldIndex -List $listName -Field "Created"
Add-PnPFieldIndex -List $listName -Field "Title"
Write-Host " Indexed 'Created' and 'Title' columns in $listName"

✔ Prevents query throttling.

Step 3: Use CAML Queries to Fetch Data Efficiently

Avoid using Get-PnPListItem without filters. Instead, use CAML queries:

$camlQuery = @"
<View>
    <Query>
        <Where>
            <Geq>
                <FieldRef Name='Created' />
                <Value Type='DateTime'>$(Get-Date -Format "yyyy-MM-dd")T00:00:00Z</Value>
            </Geq>
        </Where>
    </Query>
    <RowLimit Paged="TRUE">2000</RowLimit>
</View>
"@

$items = Get-PnPListItem -List $listName -Query $camlQuery
Write-Host " Retrieved $($items.Count) items efficiently"

✔ Fetches only required data.
✔ Supports pagination to handle large lists.

Step 4: Bulk Processing Using Batching

Process large lists efficiently using batch requests:

$items = Get-PnPListItem -List $listName -PageSize 2000
$batch = New-PnPBatch

foreach ($item in $items) {
    Set-PnPListItem -List $listName -Identity $item.Id -Values @{"Status"="Archived"} -Batch $batch
}

Invoke-PnPBatch -Batch $batch
Write-Host " Bulk updated items using batch processing"

✔ Reduces API calls.
✔ Improves performance.

Step 5: Delete Old Items in Batches

For cleanup, delete items in chunks:

$items = Get-PnPListItem -List $listName -Query $camlQuery
foreach ($item in $items) {
    Remove-PnPListItem -List $listName -Identity $item.Id -Force
}
Write-Host " Deleted old records efficiently"

✔ Prevents timeouts.

Step 6: Automate Cleanup Using Power Automate (Optional)

Use PnP PowerShell & Power Automate to:
🔹 Identify old records
🔹 Trigger deletion/archive workflows
🔹 Move data to external storage