Get-PnPListItem errors “The attempted operation is prohibited because it exceeds the list view threshold.” for over 5,000 items.

Summary

I have written Site Provisioning Engine in Azure Function using PnP.PowerShell. There is request list name “Master Site Inventory”. This list grew in size over time. The provisioning engine was working fine but it stopped working with above error.

After doing the analysis the error was clearly for the request list size grew over 5,000 items.

The issues was using the CAML query to get items from the list using Get-PnPListItem. I used the CAML Query so I can only get items where RequestStatus is equal to Approved.

$strQuery = "
  <View>
    <Query>
      <Where>
        <Eq>
          <FieldRef Name='RequestStatus' />
          <Value Type='Text'>"Approved"</Value>
        </Eq>
      </Where>
    </Query>
    <ViewFields>
      <FieldRef Name='RequestStatus' />
      <FieldRef Name='Title' />
    </ViewFields>
  </View>"

$HashArguments = @{
  Url            = $webUrl
  ClientId         = $env:GRAPH_APP_ID
  CertificateBase64Encoded = $certificateBase64Encode
  Tenant           = $("{0}.onmicrosoft.com" -f  $tenant)
}

# use splatting to pass parameter.
$conn = Connect-PnPOnline @HashArguments  -ReturnConnection

$HashArguments = @{
  Connection    = $conn
  List      = "MasterSiteInventoryā€¯ 
  # Query       = $strQuery <---- This is an issue, NO CAML QUERY
}

[array]$items = Get-PnPListItem @HashArguments  -PageSize 1000 | `
		%{New-Object psobject -Property `
		@{ 
		  Id           = $_.Id; 
		  RequestStatus    = $_["RequestStatus"];
		  Title        = $_["Title"]; 
		 }} | `
         	 select ID, 
	        	RequestStatus,
			Title,
$FilteredItems  = $items | 
                  Where-Object {$_.RequestStatus -contains 'Approved'}  | 
                  Sort-Object -Property Created

How was it resolved?

Basically, the CAML query for the large list such as over 5,000 items the Get-PnPListItem will fail with the above error.

The resolution is, user the PageSize of some number and get “ALL” items from the list. After getting all items, apply filter to the returned items.

Conclusion

There are number information on the web for this error. I want to put this resolution just too the point without any noise. Basically, stop using CAML query to get items for large list. Get everything and filter it.

NOTE: Since you are getting all items from the list, make sure you are getting only required columns in my case I just need Id and RequestStatus Column. Once I filter the items from all items I then get rest of the field for the filtered array. This will help in the performance.

About Pankaj

I am a Developer and my linked profile is https://www.linkedin.com/in/pankajsurti/
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s