SharePoint Online: Best way to get more than 5000 list items using PowerShell script

In this tutorial, we will learn how to get more than 5000 list items using PowerShell script in SharePoint Online. As we know generally, if we have a list or document library with more than 5000 items in SharePoint, we will not be able to retrieve those items due to the list view threshold error. When we try to execute the query to get items using a PowerShell CSOM script, we will get the below error:

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

In order to avoid this threshold limit error, we need to execute the query by passing batches until all items are retrieved from the list or document library.

Get more than 5000 list items using PowerShell CSOM script without the list view threshold limit error in SharePoint Online

Use the below PowerShell CSOM script to get all list items (more than 5000 list items) without getting the list view threshold limit error in SharePoint Online:

####The below script is used to get all document details from a given document library in SharePoint Online #############

cls

$PSshell = Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorVariable err -ErrorAction SilentlyContinue
if($PSshell -eq $null)
{
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$fileName = "GetAllFileDetailsFromSPOLog"

#'yyyyMMddhhmm yyyyMMdd
$enddate = (Get-Date).tostring("yyyyMMddhhmmss")
#$filename = $enddate + '_VMReport.doc'
$logFileName = $fileName +"_"+ $enddate+"_Log.txt"
$invocation = (Get-Variable MyInvocation).Value
$directoryPath = Split-Path $invocation.MyCommand.Path

$directoryPathForLog=$directoryPath+"\"+"LogFiles"
if(!(Test-Path -path $directoryPathForLog))
{
New-Item -ItemType directory -Path $directoryPathForLog
#Write-Host "Please Provide Proper Log Path" -ForegroundColor Red
}
#$logPath = $directoryPath + "\" + $logFileName

$logPath = $directoryPathForLog + "\" + $logFileName

$isLogFileCreated = $False

function Write-Log([string]$logMsg)
{
if(!$isLogFileCreated){
Write-Host "Creating Log File..."
if(!(Test-Path -path $directoryPath))
{
Write-Host "Please Provide Proper Log Path" -ForegroundColor Red
}
else
{
$script:isLogFileCreated = $True
Write-Host "Log File ($logFileName) Created..."
[string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)
Add-Content -Path $logPath -Value $logMessage
}
}
else
{
[string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)
Add-Content -Path $logPath -Value $logMessage
}
}

#This is to t load SharePoint CSOM Assemblies - you should have the below dlls in the given location.
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"

#Configuration Parameters
$SiteURL="https://globalsharepoint2020.sharepoint.com/sites/GSDRnD" #This is your site URL
$ListName="DocumentLibraryWithMoreThan5KItems" #This is your document library name
$BatchSize= 2000 #This is batch size, for a single query iteration how much items you want to query, the maximum threshold is 5000.

Try
{
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

#Setup the context for the given site.
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials

#Get the List details
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()

#Define Query to get List Items in batch, here we are just passing the row limit parameter, however, you can pass where clause and specific field details.
$MyQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$MyQuery.ViewXml = "<View Scope='RecursiveAll'><RowLimit>$BatchSize</RowLimit></View>"

#Get list items in batch wise based on the batch configuration - here each iteration will bring 2000 items.
Do
{
$MyListItems = $List.GetItems($MyQuery)
$Ctx.Load($MyListItems)
$Ctx.ExecuteQuery()
$MyListItems.count
$MyQuery.ListItemCollectionPosition = $MyListItems.ListItemCollectionPosition
}
While($MyQuery.ListItemCollectionPosition -ne $null)

}
Catch
{

$ErrorMessage = $_.Exception.Message +"in while getting list items!:"
Write-Host $ErrorMessage -BackgroundColor Red
Write-Log $ErrorMessage
}

Get more than 5000 list items using PnP PowerShell

Another way of getting more than 5000 list items from a larger list (or document library) is using PnP PowerShell. Below is the PnP PowerShell script we can use for this.

#Region: Get more than 5000 list Items from bigger list using PnP PowerShell

#Parameter
$SiteURL = "https://globalsharepoint2020.sharepoint.com/sites/GSDRnD" #This is your site URL
$ListOrDocumentLibraryName= "DocumentLibraryWithMoreThan5KItems" #This is your document library name

Try
{

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

#Get all list items or documents from list or document library in batches
$MyListItems = Get-PnPListItem -List $ListOrDocumentLibraryName -PageSize 2000

Write-host "Total Number of List Items:" $($MyListItems.Count)

#Loop through each Item in the items collection.
ForEach($OneItem in $MyListItems)
{
Write-Host "Id :" $OneItem["ID"]
Write-Host "Title :" $OneItem["Title"]
Write-Host "Title :" $OneListItem["DocumentStatus"]
}

}

Catch
{

$ErrorMessage = $_.Exception.Message +"in while getting list items!:"
Write-Host $ErrorMessage -BackgroundColor Red
Write-Log $ErrorMessage
}

#Region Ends: Get list Items from bigger list using PnP PowerShell

Notes:

• Because MFA (multi-factor authentication) may cause an error when connecting to SharePoint Online using the PowerShell CSOM method, you should use a non-MFA enabled admin account or use the PnP PowerShell method.
• If you have a very large list or document library, do not use any where clauses in the camel query XML because in a larger document library or list, we cannot add filter parameters with the where condition. If we add a where clause to the query with certain field conditions and retrieve below 5000 items in batches, we will still get the list view threshold limit error. In this case, do not add any where clause to the query; read all items in batches, and inside the item collection for each loop, add the if condition for the filter of the data.
• If you have a million-item document library or list, the PowerShell CSOM method will not work; the connection will be lost after a certain number of times, so PnP PowerShell is the recommended method to handle heavy data, and it is also faster.

Once we have retrieved more list items, we can perform the update and delete operations using the list item collection object.

Update SharePoint list item using PowerShell

Let’s say we have a choice column named “DocumentStatus.”, We want to update this column for all items if it is empty, then we can use the below PowerShell code:

ForEach ($oneListItem in $MyListItems)   #This $MyListItems reference taken from the above script
{
$varDocumentStatus=$oneListItem["DocumentStatus"] #This is a choice column internal name.
if ([string]::IsNullOrEmpty($varDocumentStatus)) #Checking whether the document status column value for the current item is empty.
{

#Set the new value for the list column
$oneListItem["varDocumentStatus"] = "Draft" #Just passed the new value as "Draft", if values are missing and this "Draft" must be one of the choice values.
$oneListItem.Update()
$Ctx.ExecuteQuery()

}

}

Update SharePoint list item using PnP PowerShell

Using the below PnP PowerShell script, we can update the SharePoint list item dynamically:

#Loop through each Item in the items collection.
ForEach($OneListItem in $MyListItems) #This $MyListItems reference taken from the above PnP PowerShell Get Items script
{
Write-Host "Id :" $OneListItem["ID"]
Write-Host "Title :" $OneListItem["Title"]
Write-Host "Title :" $OneListItem["DocumentStatus"]

$varDocumentStatus=$oneListItem["DocumentStatus"] #This is a choice column internal name.

if ([string]::IsNullOrEmpty($varDocumentStatus)) #Checking whether the document status column value for the current item is empty.
{

#Syntax to update list item - Internal Names of the columns : Value
Set-PnPListItem -List $ListName -Identity $OneListItem -Values @{"Title" = "New Title Column Value"; "DocumentStatus"="Draft"}

}

 

}

Delete SharePoint list item using PowerShell

Using the below PowerShell script, we can delete list items from SharePoint Online dynamically:

ForEach ($oneListItem in $MyListItems)   #This $MyListItems reference taken from the above script
{
$varDocumentStatus=$oneListItem["DocumentStatus"] #This is a choice column internal name.
if ($varDocumentStatus -eq "Expired") #Checking whether the document status column value for the current item is expired.
{

#Delete item dynamically

$oneListItem.DeleteObject()
$Ctx.ExecuteQuery()

Write-Host "An expired list item has been deleted successfully!" -ForegroundColor Green

}

}

Delete SharePoint list item using PnP PowerShell

Using the below PnP PowerShell script, we can delete list items dynamically from the SharePoint Online document library or list:

ForEach ($oneListItem in $MyListItems)   #This $MyListItems reference taken from the above PnP PowerShell script
{
$varDocumentStatus=$oneListItem["DocumentStatus"] #This is a choice column internal name.
if ($varDocumentStatus -eq "Expired") #Checking whether the document status column value for the current item is expired.
{

#Delete item dynamically

Remove-PnPListItem -List $YourListName -Identity $oneListItem -Force

#Remove-PnPListItem -List $YourListName #This removes all list items from the given list.

Write-Host "A list Item has been deleted successfully!" -ForegroundColor Green

}

}

Summary: Get more than 5000 list items using PowerShell CSOM script without the list view threshold limit error in SharePoint Online

Thus, in this PowerShell article, we have learned the following with respect to handling the larger list items (more than 5000 items) in SharePoint Online:

• How to get more than 5000 list items without the list view threshold error using the PowerShell CSOM script.
• How to get more than 5000 list items without getting the list view threshold error when using the PnP PowerShell script.
• How to update list items dynamically in SharePoint Online using the PowerShell CSOM script
• How to update list items dynamically in SharePoint Online using the PnP PowerShell script
• How to delete list items dynamically in SharePoint Online using the PowerShell CSOM script
• How to delete list items dynamically in SharePoint Online using the PnP PowerShell script

See Also: SharePoint Online PowerShell script (articles hub)

You may also like the following SharePoint PowerShell tutorials:

• How to replace root site in SharePoint Online from Microsoft 365?
• How to create hub site in SharePoint Online using PnP PowerShell?
• [Verified]: Find all InfoPath forms in SharePoint using PowerShell
• [Verified]: Remove recycle bin items in SharePoint using PowerShell
• [Verified]: Cancel SharePoint workflows using PowerShell
• SharePoint Online: Remove custom app using PnP PowerShell
• Office 365: How to create content type in SharePoint Online using PowerShell?
• Office 365: How to create document library in SharePoint Online using PowerShell?
• Export SharePoint user information list to CSV(Excel) file using PowerShell
• How to fix “The term ‘Get-MsolUser’ is not recognized as the name of a cmdlet”
• How to fix the “The term ‘Get-SPWeb’ is not recognized as the name of a cmdlet, function” PowerShell error
• How to hide quick launch menu in SharePoint online using PnP PowerShell
• Edit user Permission is greyed Out SharePoint Online
• Get workflow inventory from SharePoint online using PowerShell CSOM
• Create a modern team site using PnP PowerShell in SharePoint
• In 2 steps convert a classic SharePoint page to modern using PnP
• SharePoint Online: Delete All Files from document library for the given date – PowerShell CSOM
• Create SharePoint online list using PnP provisioning template
• SharePoint Automation: PowerShell script to get remote server information
• Office 365: Retrieve hub sites and associated sites using PnP Powershell
• SharePoint Online Automation – O365 – Upload files to document library using PowerShell CSOM
• SharePoint Online Automation – O365 – Create multiple items in a list using PowerShell CSOM
• SharePoint Online Automation – O365 – Update document library metadata using PowerShell CSOM
• SharePoint Online: List operations using PnP PowerShell
• Get started with SharePoint Online Management Shell

About Post Author

SP Doctor

I am your SharePoint and Power Platform doctor and guide. Sharing knowledge with my fellow colleagues, friends, and the tech community makes me utmost happy in my job.

See author's posts