Best 2 ways to Bulk update SharePoint list items using PowerShell in SharePoint Online

 611 total views,  1 views today

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

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

#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

See Also: SharePoint Online PowerShell Get List Items

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

Global SharePoint

SharePoint Specialist (2019 | 2016 | 2013 | 2010 | 2007 | SharePoint Online | Microsoft 365)

See author's posts