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: As the error message says, we have 5000 as the hard limit for the list view threshold in SharePoint online. Unlike SharePoint on-premises, this threshold limit can't be increased!

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
$SiteURL="https://Crescent.SharePoint.com"
$ListName="Projects"
$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)
    $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
} 
Just make sure, your batch is lesser than 5000 to avoid the 5000 limit threshold issue on SharePoint Online!

7 comments:

  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

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

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

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

    ReplyDelete
  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: https://www.sharepointdiary.com/2017/07/sharepoint-online-powershell-to-bulk-check-in-all-documents.html

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

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.