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

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

No comments

 201 total views,  11 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:

Buy the premium version of SharePoint Online & Office 365 administration eBook from here:


Buy SharePoint Online & Office 365 Administration eBook

 

About Post Author

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