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."
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:
#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.count
Instead 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

1 comment:

  1. This doesn't work for large list items. See https://github.com/pnp/PnP-PowerShell/issues/879

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.