question

ADMRomanKorchak-7139 avatar image
0 Votes"
ADMRomanKorchak-7139 asked CaseyYangMSFT-4714 commented

Getting "The attempted operation is prohibited because it exceeds the list view threshold" even on indexed list

Hi All
I am trying to get some items from SP list with more then 5000 items with following script:

 Connect-PnPOnline -Url $SiteUrl -ClientId $clientId -ClientSecret $secret
 $query = "<View><Query>
     <Where> <And>
       <Eq>  <FieldRef Name='Period'/>  
           <Value Type='Number'>1</Value>
       </Eq>
       <Eq>  <FieldRef Name='Week'/>  
           <Value Type='Number'>1</Value>
       </Eq>
     </And> </Where>
     <OrderBy> <FieldRef Name='Created' Ascending='TRUE' /></OrderBy> 
    </Query> <RowLimit>1000</RowLimit> </View>";
 $a_Files2 = (Get-PnPListitem -List AllFileActions2122 -Query $query -PageSize 500  ).FieldValues

and getting "The attempted operation is prohibited because it exceeds the list view threshold" although "Created" column is indexed
I have a similar list with more then 5000 items in another site and it works, but not in this particular site :/
Any Ideas?
Thanks

office-sharepoint-onlinewindows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sharatha avatar image
0 Votes"
sharatha answered

you could use below same script to get all items from large lists.

 #Parameter
 $SiteURL = "https://crescent.sharepoint.com/sites/PMO"
 $ListName= "Projects"
      
 #Connect to PnP Online
 Connect-PnPOnline -Url $SiteURL -UseWebLogin
     
 #Get all list items from list in batches
 $ListItems = Get-PnPListItem -List $ListName -PageSize 500
     
 Write-host "Total Number of List Items:" $($ListItems.Count)
     
 #Loop through each Item
 ForEach($Item in $ListItems)
 { 
     Write-Host "Id :" $Item["ID"]
     Write-Host "Title :" $Item["Title"]
 }

Ref: #Read more: https://www.sharepointdiary.com/2016/12/sharepoint-online-get-all-items-from-large-lists-powershell-csom.html#ixzz78KntI0lZ

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CaseyYangMSFT-4714 avatar image
0 Votes"
CaseyYangMSFT-4714 answered CaseyYangMSFT-4714 commented

Hi @ADMRomanKorchak-7139,

You could use following PowerShell commands to define Query to get List Items in batch.

 #Config Parameters
 $SiteURL="https://Crescent.SharePoint.com"
 $ListName="Projects"
 $BatchSize= 2000
     
 Try {
     $Cred= Get-Credential
     $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
     
     #Setup the context
     $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
     $Ctx.Credentials = $Credentials
             
     #Get the List
     $List = $Ctx.Web.Lists.GetByTitle($ListName)
     $Ctx.Load($List)
     $Ctx.ExecuteQuery()
     
     #Define Query to get List Items in batch
     $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
     $Query.ViewXml = @"
     <View Scope='RecursiveAll'>
         <Query>
             <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
         </Query>
         <RowLimit Paged="TRUE">$BatchSize</RowLimit>
     </View>
 "@
     
     #Get List Items in Batch
     Do
     {
         $ListItems = $List.GetItems($Query)
         $Ctx.Load($ListItems)
         $Ctx.ExecuteQuery()
         $ListItems.count
         $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
     }
     While($Query.ListItemCollectionPosition -ne $null)
     
 }
 Catch {
     write-host -f Red "Error Getting List Items:" $_.Exception.Message
 }

For Reference: SharePoint Online: Get List Items from Large Lists ( >5000 Items) using PowerShell without List View Threshold Exceeded Error
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.


If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ADMRomanKorchak-7139,

I'm checking how the things are going on about this issue. Is there any progress on this issue?

0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello @ADMRomanKorchak-7139

When you exceed this limit, it will cause some unexpected behaviors and performance issues. So, what is adviced for users is to manage large list referring to the article below:

Manage large lists and libraries
https://support.microsoft.com/en-us/office/manage-large-lists-and-libraries-b8588dae-9387-48c2-9248-c24122f07c59?redirectsourcepath=%252fen-us%252farticle%252fmanage-large-lists-and-libraries-in-office-365-b4038448-ec0e-49b7-b853-679d3d8fb784


--If the reply is helpful, please Upvote and Accept as answer--

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.