Fix “Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” Error
Problem: When trying to get list items from a SharePoint Online list using Get-PnPListItem cmdlet with a query parameter, I got an error message “Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
Root Cause:
The requirement was to get all list items created 90 days ago. So, I was trying to fetch list items by applying the Query filter. As the error message states, This error is caused by a large with more than 5000 items when you query non-indexed columns! Here is the PowerShell script that caused the above error message:
#Parameter
$SiteURL= "https://crescent.sharepoint.com/sites/Projects"
$ListName = "Meetings"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Define Query to Filter items created 90 days ago
$Query= "<View>
<Query>
<Where>
<Lt>
<FieldRef Name='Created' Type='DateTime'/>
<Value Type='DateTime' IncludeTimeValue='TRUE'>
<Today OffsetDays='-90'/>
</Value>
</Lt>
</Where>
</Query>
</View>"
#Get All Items from the List in batches
$ListItems = Get-PnPListItem -List $ListName -PageSize 500 -Query $Query
Write-host "Total Number of Items Found:"$ListItems.count
#Get Each Item's Created Date
$ListItems | ForEach-Object { Write-host ("List Item:{0} was Created on {1}" -f $_["FileLeafRef"],$_["Created"]) }
Solution:
It seems using the PageSize switch doesn’t help when you use the “Query” parameter to get list items from large lists. So I got all list items first and then applied the filter with the “where” clause.
$SiteURL= "https://crescent.sharepoint.com/sites/Projects"
$ListName = "Meetings"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get All Items from the List in batches
$ListItems = Get-PnPListItem -List $ListName -PageSize 500
Write-host "Total Number of Items Found:"$ListItems.count
#Filter List Items
$TimeStamp = (Get-Date).AddDays(-90)
$FilteredItems = $ListItems | Where {$_["Created"] -Lt $TimeStamp}
#Get Each Item's Created Date
$FilteredItems | ForEach-Object { Write-host ("List Item:{0} was Created on {1}" -f $_["FileLeafRef"],$_["Created"]) }
This issue is already raised in PnP GitHub and still open: https://github.com/MicrosoftDocs/office-docs-powershell/issues/3457
Add an Indexed column to List and Query
You can also bypass the issue by adding “Indexed Column” (How to Add Indexed Column in SharePoint Online using PowerShell ) and adding the indexed column to the query. E.g., the ID column is indexed by default, and here is how you can <RowLimit> attribute with CAML query:
#Parameter
$SiteURL= "https://crescent.sharepoint.com/sites/pmo"
$ListName = "projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Define Query to get list items
$Query= "<View Scope='RecursiveAll'>
<Query>
<OrderBy> <FieldRef Name='ID' Ascending='TRUE' /></OrderBy>
</Query>
<RowLimit>1000</RowLimit>
</View>"
#Get All Items from the List in batches
$ListItems = Get-PnPListItem -List $ListName -Query $Query
Write-host "Total Number of Items Found:"$ListItems.count
Instead of the RowLimit attribute, you can use the “PageSize” Parameter to the Get-PnPListItem cmdlet as well:
#Define Query to get list items
$Query= "<View Scope='RecursiveAll'>
<Query>
<OrderBy> <FieldRef Name='ID' Ascending='TRUE' /></OrderBy>
</Query>
</View>"
#Get All Items from the List in batches
$ListItems = Get-PnPListItem -List $ListName -Query $Query -PageSize 500
Can you please help, it doesnt work
Make sure the “Query” parameter doesn’t have “Where” class in it!
This doesn’t work for large list items. See https://github.com/pnp/PnP-PowerShell/issues/879