2 Ways to Bulk update SharePoint list items using PowerShell

In this SharePoint Online PowerShell tutorial, we will learn how to perform bulk update SharePoint list items using PowerShell. Here you will learn the below two methods to bulk update large list items in SharePoint Online:

• Bulk update SharePoint Online list items using CSOM PowerShell.
• Bulk update SharePoint Online list items using PnP PowerShell

Introduction: What is PowerShell?

PowerShell is a task automation and configuration management framework from Microsoft, consisting of a command-line shell and a scripting language. It is designed to automate system tasks, such as batch processing, and to create system management tools for commonly implemented processes. PowerShell was developed on the .NET framework and provides a robust environment for administrators and developers to perform a variety of functions.

Key Features of PowerShell

1. Command-Line Interface
   • A powerful CLI that allows users to interact with the system using commands known as cmdlets.
   • Provides immediate feedback, making it easier to test and execute commands on the fly.
2. Scripting Language
   • A versatile scripting language that enables the automation of administrative tasks.
   • Scripts, called PowerShell scripts, are written in plain text and saved with a .ps1 file extension.
3. Cmdlets
   • Specialized .NET classes that perform specific operations. Each cmdlet is designed to perform a single task (e.g., copying files, modifying the registry).
   • Example cmdlets include Get-Command, Get-Help, Get-Process, and Set-Item.
4. Pipeline
   • Allows the output of one cmdlet to be passed as input to another cmdlet, enabling complex operations to be composed of simple commands.
   • Example: Get-Process | Where-Object { $_.CPU -gt 100 } | Stop-Process
5. Modules
   • Collections of cmdlets, functions, workflows, variables, and aliases that are packaged together for distribution.
   • Example: Importing a module like Import-Module ActiveDirectory to manage Active Directory.
6. Remote Management
   • PowerShell remoting enables administrators to run commands on remote systems.
   • Uses WS-Management Protocol for secure remote connections.
7. Integrated Scripting Environment (ISE)
   • A graphical user interface that provides a more convenient way to write, test, and debug scripts.
8. Cross-Platform Compatibility
   • Originally Windows-only, but now available on Linux and macOS as PowerShell Core (based on .NET Core), allowing for cross-platform scripting and automation.

Common Uses of PowerShell

1. System Administration
   • Automate administrative tasks like user management, system configurations, and software deployment.
   • Example: Creating new user accounts, configuring network settings, and managing system updates.
2. Configuration Management
   • Manage system configurations, including Windows services, processes, and registry settings.
   • Example: Checking and modifying service statuses or registry entries.
3. Automation
   • Automate repetitive tasks and processes to improve efficiency and reduce human error.
   • Example: Automating backup processes, monitoring system health, and generating reports.
4. Data Management
   • Handle and manipulate various types of data, including files, directories, and structured data (e.g., JSON, XML).
   • Example: Parsing log files, converting data formats, and exporting data to CSV.
5. Cloud and Hybrid Management
   • Manage cloud services and hybrid environments using PowerShell modules for Azure, Office 365, and other cloud platforms.
   • Example: Automating Azure resource management, Office 365 user management, and hybrid cloud configurations.

Bulk update SharePoint list items using PowerShell CSOM

For bulk updating list items in SharePoint Online, we can use the below PowerShell CSOM script:

####The below script is used to perform bulk SharePoint list item updates 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 = "BulkListItemsUpdateSPOLog"

#'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 used to 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 many items you want to query—and 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 a query to get list items in batch; here we are just passing the row limit parameter; however, you can pass the where clause and specific field details.
$MyQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$MyQuery.ViewXml = "For the query XML refer to the below section."

#Get list items 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

#If the list item collection returns 0 items, exit the loop.
If ($MyListItems.Count -eq 0)
{
Break
}

#You can even add a filter condition here inside the loop to update the list item as needed.
ForEach($MyOneListItem in $MyListItems)
{
#Update List Item Title as needed; in this example, we are only updating the Title column; however, you can pass any columns that need to be updated.
$MyOneListItem["Title"]= "New Title Column Value updated by CSOM PowerShell"
$MyOneListItem.Update()
}
$Ctx.ExecuteQuery()

}
While($MyQuery.ListItemCollectionPosition -ne $null)

}
Catch
{

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

####Updating bulk list items ends here. ###################

PowerShell CAML query recursive all

Use the below CAML query in the above script:

 $MyQuery.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
</Query>
<RowLimit>$BatchSize</RowLimit>
</View>"

Bulk update SharePoint list items using PnP PowerShell

For bulk updating list items in SharePoint Online, we can use the below PnP PowerShell script as well:

#############Update bulk list items using PnP PowerShell in SharePoint Online. ###########################

#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"]

#Update List Item Title as needed; in this example, we are only updating the Title column; however, you can pass any columns that need to be updated.
Set-PnPListItem -List $ListOrDocumentLibraryName -Identity $OneItem.Id -Values @{"Title"= "New Title Column Value updated by PnP PowerShell"} | Out-Null
Write-host "The item has been successfully updated."

}

}

Catch
{

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

#############Update bulk list items using PnP PowerShell in SharePoint Online - ends here. ################

Notes:

• In the above script, you just need to update your site URL and list or document library name.
• If you have hundreds of thousands of items in your list or document library, don’t add the where condition in the camel query XML; otherwise, you will get a list view threshold error.

Summary: Bulk update SharePoint list items using PowerShell

Thus, in this article, we have learned the following with respect to updating thousands of items in the SharePoint Online document library or list:

• How to perform bulk updates on SharePoint Online list items using the CSOM PowerShell
• How to use PnP PowerShell to perform bulk updates on SharePoint Online list items

PowerShell is a powerful tool for system administrators and developers, providing a rich set of features for task automation, configuration management, and system administration. Its versatility, combined with its ability to integrate with various Microsoft and third-party products, makes it an essential skill for IT professionals.

See Also: SharePoint Online PowerShell Tutorials

You may like this article – SharePoint Online: Best way to get more than 5000 list items using PowerShell script.

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 Maven

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