SharePoint Online - best way to get more than 5000 list items using PowerShell script

Best Way To Get More Than 5000 List Items using PowerShell

By Microsoft Techy in PnP PowerShell, PowerShell, SharePoint on
No comments

Loading

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.

Introduction: What is list view threshold limit in SharePoint Online?

In SharePoint Online, the list view threshold limit is a performance-related setting that restricts the number of items that can be retrieved or operated upon in a single query. This limit is put in place to ensure the optimal performance and stability of SharePoint Online by preventing large queries that could degrade the service’s performance.

Key Characteristics of the List View Threshold Limit

  1. Threshold Value
    • The default list view threshold limit in SharePoint Online is 5,000 items.
    • This means that any query or operation retrieving more than 5,000 items will be blocked to protect system performance.
  2. Performance Optimization
    • The limit helps to prevent scenarios where large queries could significantly slow down the system or impact the performance for other users.
    • Ensures that SharePoint Online can handle multiple requests efficiently and maintain overall service quality.
  3. Impact on Users and Developers
    • Users may encounter errors or performance issues when trying to view or operate on large lists that exceed the threshold.
    • Developers need to design solutions and queries that work within this limit, using best practices to optimize performance.

Strategies to Work Within the List View Threshold

  1. Indexing Columns
    • Indexing columns used in filters and queries can improve performance and help avoid threshold issues.
    • SharePoint automatically creates some indexes, but custom indexes can be created for additional columns.
  2. Using Filters and Views
    • Applying filters to views can reduce the number of items returned in a query.
    • Custom views can be created to display subsets of data, ensuring the query stays within the threshold limit.
  3. Content Organizer
    • Use the Content Organizer feature to automatically route documents to different libraries based on metadata.
    • Helps distribute content across multiple lists or libraries, preventing any single list from becoming too large.
  4. Folder Structure
    • Organizing items into folders can help manage large lists and libraries.
    • Each folder’s contents are considered separately, so navigating into a folder effectively reduces the number of items viewed.
  5. Indexed Views
    • Creating views with indexed columns can optimize performance and ensure queries remain efficient.
    • For example, creating a view that filters items based on a date range using an indexed date column.
  6. Batching and Pagination
    • Implement batching and pagination in custom solutions to retrieve items in smaller chunks rather than a single large query.
    • APIs and custom scripts should handle data in manageable sets to avoid exceeding the threshold.

Example Use Cases and Solutions

Scenario 1: Large Document Library

A document library contains 20,000 documents. Users frequently need to find documents by their creation date.

Solution:

  • Index the “Created” Column: Create an index on the “Created” column.
  • Filtered Views: Create views that filter documents by specific date ranges, ensuring each view returns fewer than 5,000 items.

Scenario 2: Large List of Customer Records

A list of customer records exceeds 5,000 items, and users need to filter by customer region.

Solution:

  • Index the “Region” Column: Index the “Region” column to speed up queries.
  • Folder Organization: Organize customer records into folders based on region or another logical grouping.
  • Custom Views: Create custom views that filter by region to keep the number of items in view under the threshold.

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="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 = "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

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

The list view threshold limit in SharePoint Online is a crucial performance measure designed to ensure the service remains efficient and responsive. While it may present challenges when working with large lists or libraries, understanding and implementing best practices such as indexing, filtering, and organizing content can help manage and mitigate these challenges. By optimizing the way data is queried and displayed, users and developers can work effectively within the threshold limits while maintaining optimal performance and usability.

See Also: SharePoint Online PowerShell script (articles hub)

You may also like the following SharePoint PowerShell tutorials:

 

About Post Author

a61309e2126612fbe89449c19b64adc3ed138092261cdfddd685493d7cbe28ab?s=150&d=identicon&r=g

Microsoft Techy

As a SharePoint and Power Platform Maven, my greatest joy comes from sharing my expertise with colleagues, friends, and the tech community, helping them navigate the ever-evolving world of technology and guiding them through the dynamic landscape of modern technology.

See author's posts

Tags: , ,

Do you have a better solution or question on this topic? Please leave a comment