SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items)

Problem: When you try to get items from larger lists with more than 5000 items, You get list view threshold exceed 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!

Solution: How to overcome 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.
#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
$BatchSize= 1000

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)

    #Define Query to get List Items in batch
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml = @"
    <View Scope='RecursiveAll'>
            <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        <RowLimit Paged="TRUE">$BatchSize</RowLimit>

    #Get List Items in Batch
        $ListItems = $List.GetItems($Query)
        $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 "PageSize" switch to handles larger lists in batches:
$SiteURL = ""
$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"]


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

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

  2. Any wayto export the list ina csv file using this logic?

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

  4. 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:

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


Please Login and comment to get your questions answered!

Powered by Blogger.