SharePoint Online: Get List Items from Large Lists ( >5000 Items) using PowerShell without List View Threshold Exceeded Error

Problem: When you try to get items from larger lists with more than 5000 items through PowerShell, You get the list view threshold exceeded issue in SharePoint Online!

A typical PowerShell CSOM script to get list items from a list resulted in an error:
“Exception calling “ExecuteQuery” with “0” argument(s): “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”

Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

Root Cause: 5000 Item list view threshold in SharePoint Online!

As the error message says, we have 5000 as the hard limit for the list view threshold in SharePoint Online. Any attempt to perform bulk operations such as read/write more than 5000 items in SharePoint Online invokes this issue. Unlike SharePoint on-premises, this threshold limit can’t be increased, unfortunately! So, Let’s see how to use CAML to query large lists in SharePoint Online.

How to get Items from a large list using PowerShell without List view threshold exceeded Error?

How to overcome the list view threshold in SharePoint Online? There are various solutions to deal with this issue. Here is mine: Get list items in batch!

PowerShell to Get List Items from Large Lists:

Here is how to get more than 5000 records using PowerShell in SharePoint Online List without List view threshold exceeded Error.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#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'><RowLimit>$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
} 

Just make sure your batch is lesser than 5000 to avoid the 5000 limit threshold issue on SharePoint Online!

Get List Items from Larger lists using PnP PowerShell

The PnP PowerShell module provides a “PageSize” switch to handle larger lists in batches:

#Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#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"]
}

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

22 thoughts on “SharePoint Online: Get List Items from Large Lists ( >5000 Items) using PowerShell without List View Threshold Exceeded Error

  • No matter what I try SharePoint Online. I still getting the same error. The only way I can delete large folder is if I Sync the folder with OneDrive and use File Explorer.

    This script works great on small folders only
    #Config Variables
    $SiteURL = “https://mysite.sharepoint.com”
    $ListName =”Test”
    $FolderServerRelativeURL = “/Test/folder/Folder1/Folder2”
    Try {
    >> #Connect to PnP Online
    >> Connect-PnPOnline -Url $SiteURL -Interactive
    >>
    >> #Get All Items from Folder in Batch
    >> $ListItems = Get-PnPListItem -List $ListName -FolderServerRelativeUrl $FolderServerRelativeURL -PageSize 500
    >>
    >> Write-host “Total Number of Items Found:”$(ListItems.count)
    >>
    >> #Get List Items from the folder
    >> $ItemsFromFolder = $ListItems | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }
    >>
    >> #Powershell to delete all files from a folder
    >> ForEach ($Item in $ListItems)
    >> {
    >> Write-Host “Id :” $Item[“ID”]
    >> Write-Host “Title :” $Item[“Title”]
    >>
    >> Remove-PnPListItem -List $ListName -Identity $Item.Id -Recycle -Force
    >> Write-host “Removed File:”$Item.FieldValues.FileRef
    >> }
    >> }
    >> Catch {
    >> write-host “Error: $($_.Exception.Message)” -foregroundcolor Red
    >> }
    Error: The attempted operation is prohibited because it exceeds the list view threshold.

    Reply
    • The Get-PnPListItem cmdlet with FolderServerRelativeUrl parameter will fail on larger lists and libraries – a known issue. Instead, use: Get-PnPListItem -List $ListName -PageSize 500 | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }

      Reply
      • I changed it and now I get this error
        Error: The term ‘ListItems.count’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

        Try {
        #Connect to PnP Online
        Connect-PnPOnline -Url $SiteURL -Interactive

        #Get All Items from Folder in Batch
        $ListItems = Get-PnPListItem -List $ListName -PageSize 500 | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }

        Write-host “Total Number of Items Found:”$(ListItems.count)

        #Get List Items from the folder
        $ItemsFromFolder = $ListItems | Where {$_.FieldValues.FileDirRef -like $FolderServerRelativeURL }

        #Powershell to delete all files from a folder
        ForEach ($Item in $ListItems)
        {
        Write-Host “Id :” $Item[“ID”]
        Write-Host “Title :” $Item[“Title”]

        Remove-PnPListItem -List $ListName -Identity $Item.Id -Recycle -Force
        Write-host “Removed File:”$Item.FieldValues.FileRef
        }
        }
        Catch {
        write-host “Error: $($_.Exception.Message)” -foregroundcolor Red
        }

        Reply
  • Hi! I am getting error “Error Getting List Items: Exception calling “ExecuteQuery” with “0” argument(s): “The sign-in name or password does not match one in the Microsoft account system.” after authentication. Confirmed that my username and password is correct. Can you please help? Thank you!

    Reply
      • Hi Salaudeen,
        You are right. Figured out that it was caused by MFA. The PnP PowerShell method worked for me. Thank you very much for all scripts!

        Reply
  • Is there a way to get all the ListNames from a Site? We were originally using $dstList = Get-List -Name $destLib.Name -Site $dstSite but this is returning the threshold limit error as we can have thousands of lists. All the work arounds seem to be for the next level down and get the items within the list

    Reply
  • Hello, can you pleae help me, how can i filter( search) in list, which have more than 5000 records? Because if i try to add Where, im ending on Threshold error. Thanks much

    Reply
    • Unfortunately, You can’t apply where condition directly on larger lists. However, you can filter list items after retrieving them in batches. E.g.,

      #Filter List Items
      $TimeStamp = (Get-Date).AddDays(-90)
      $FilteredItems  = $ListItems | Where {$_["Created"] -Lt $TimeStamp}
      
      Reply
  • How would l do the same for SP 2013. I tried it but get the same message again “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”

    Reply
  • How to retreive all items with a specific content type from a large list. If I CAML query , it throws an list threshold error.

    Reply
  • I could swear this worked for me the first time though. I’m stepping through it manually. During the loop, the only thing returned is $listitems.count. The data is never returned and the end $listitems.count is what ever was left after the last full 1000. Entering $ListItems to see what it contains, I receive this message:
    format-default : The collection has not been initialized. It has not been requested or the request has not been executed. It may need
    to be explicitly requested.
    + CategoryInfo : NotSpecified: (:) [format-default], CollectionNotInitializedException
    + FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException,Microsoft.PowerShell.Commands.FormatDefaul
    tCommand

    Reply
  • Would you be able to post a version of this where it also checks out the files as well? I am trying to combine this script with the one posted here: https://www.sharepointdiary.com/2017/07/sharepoint-online-powershell-to-bulk-check-in-all-documents.html

    Reply
  • Any way to pull all the list columns in a csv file using this logic?

    Reply
  • Any wayto export the list ina csv file using this logic?

    Reply
  • Hi! I’m using this to try and export a large list I have. It ran successfully, but I would like to know where this export went? I read through the code, but I’m not sure how this data is exported.

    Thanks for the assistance

    Reply
    • Usually on the same directory where your script is saved. Or in your Default Home directory (Start >> Run >> type “.” without quote and Enter)

      Reply

Leave a Reply

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