Errors and Solutions PnP PowerShell SharePoint SharePoint Online 

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

Salaudeen Rajack 2 Comments

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 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 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 the PageSize switch doesn’t help when you use the “Query” parameter to get list items from large lists. So I ended up getting all list items first and then apply the filter with the “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

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

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

  • July 14, 2021 at 6:18 PM

    Can you please help, it doesnt work

    Reply
  • January 28, 2021 at 11:33 AM

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

    Reply

Leave a Reply