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! 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
Fix "Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." Error Fix "Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." Error Reviewed by Salaudeen Rajack on March 03, 2019 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.