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, 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 Query filter. As the error message states, This error is caused by a large with with more than 5000 items when you query non-indexed columns! Here is the PowerShell script caused the above error message:
Solution:
It seems using PageSize switch doesn't help when you use "Query" parameter to get list items from large lists. So I ended up getting all list items first and then apply the filter with "where" clause.
Add an Indexed column to List and Query
You can also by-pass the issue by adding "Indexed Column" ( How to Add Indexed Column in SharePoint Online using PowerShell ) and then add the indexed column to the query. E.g. ID column is indexed by default and here is how you can <RowLimit> attribute with CAML query:
Root Cause:
The requirement was to get all list items created 90 days ago. So, I was trying to fetch list items by applying Query filter. As the error message states, This error is caused by a large with with more than 5000 items when you query non-indexed columns! Here is the PowerShell script caused the above error message:
#Parameter $SiteURL= "https://crescent.sharepoint.com/sites/Projects" $ListName = "Meetings" #Connect to PnP Online Connect-PnPOnline -Url $SiteURL -UseWebLogin #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 PageSize switch doesn't help when you use "Query" parameter to get list items from large lists. So I ended up getting all list items first and then apply the filter with "where" clause.
$SiteURL= "https://crescent.sharepoint.com/sites/Projects" $ListName = "Meetings" #Connect to PnP Online Connect-PnPOnline -Url $SiteURL -UseWebLogin #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 by-pass the issue by adding "Indexed Column" ( How to Add Indexed Column in SharePoint Online using PowerShell ) and then add the indexed column to the query. E.g. 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 -UseWebLogin #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.countInstead of RowLimit attribute, you can use "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
This doesn't work for large list items. See https://github.com/pnp/PnP-PowerShell/issues/879
ReplyDelete