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

Question

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

Answer 1

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

Answer 2

Hi @ADM Roman Korchak ,

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.

Answer 3

Hello @ADM Roman Korchak

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