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

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/pnp/powershell/issues/651

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 - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

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

  • -Query and -PageSize don’t work together (Although the documentation says they do. See https://github.com/pnp/powershell/issues/651

    Reply
  • Can you please help, it doesnt work

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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *