SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items)
Problem:
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."
Root cause: As the error message says, we have 5000 as the hard limit for list view threshold in SharePoint online. Unlike SharePoint on-premises, this threshold limit can't be increased!
Solution: 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:
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."
Root cause: As the error message says, we have 5000 as the hard limit for list view threshold in SharePoint online. Unlike SharePoint on-premises, this threshold limit can't be increased!
Solution: 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:
#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!
SharePoint Online: How to Get All List Items from Large Lists ( >5000 Items)
Reviewed by Salaudeen Rajack
on
December 03, 2016
Rating:

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.
ReplyDeleteThanks for the assistance
Usually on the same directory where your script is saved. Or in your Default Home directory (Start >> Run >> type "." without quote and Enter)
DeleteAny wayto export the list ina csv file using this logic?
ReplyDeleteAny way to pull all the list columns in a csv file using this logic?
ReplyDeleteUse: SharePoint Online: Export List Items to CSV using PowerShell
DeleteWould 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