SharePoint Diary » SharePoint Online » 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

January 31, 2023 Salaudeen Rajack 3 Comments the attempted operation is prohibited because it exceeds the list view threshold

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.”

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.

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

3 thoughts on “Fix “Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” Error”

Alex
July 14, 2021 at 6:18 PM
Permalink

Can you please help, it doesnt work
Reply
- Salaudeen Rajack
  January 29, 2022 at 9:45 AM
  Permalink
  
  Make sure the “Query” parameter doesn’t have “Where” class in it!
  Reply
AJ
January 28, 2021 at 11:33 AM
Permalink

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

SharePoint Diary