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 tried 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 the 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 is 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 an “Indexed Column” to the list (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
Finally: You may get the “Attempted operation is prohibited because it exceeds the list view threshold” error when a list or document library grows beyond 5000 items in SharePoint Online or OneDrive in various situations like:
- Using CSOM script or PnP PowerShell cmdlets such as Add-PnPFile, Copy-PnPFile, Find-PnPFile, Get-PnPFolder, Get-PnPFile, Get-PnPListItem, executequery, etc.
- Trying to break permission inheritance, trying to rename a folder, etc.
- Using CAML query in CSOM PowerShell scripts
- When trying to delete a list or site
- Trying to get list items using Power Apps, etc.
You have to reduce the number of items in the list or library to solve this issue.
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