Client Side Object Model (CSOM) List PnP PowerShell PowerShell SharePoint SharePoint Online 

SharePoint Online: Bulk Update All Items in Large List using PowerShell

Salaudeen Rajack 6 Comments , ,

Requirement: Bulk Update SharePoint Online List Items using PowerShell

PowerShell to Bulk Update All Items in Large List in SharePoint Online

How to update a Large Number of List Items in Bulk using PowerShell?

Are you looking for a way to quickly update all items in a SharePoint Online list? Maybe a column value needs to be updated for all items in the list. This article will show you how to use the PowerShell script to quickly bulk update all list items in SharePoint Online.

While lists with < 5000 items can be updated using the approach in my other post, Update SharePoint Online List using PowerShell, How about updating large lists with more than 5000 list items and avoid “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” issue in PowerShell?

Well, large lists must be updated in batches. Here is my PowerShell script to update large lists in bulk: This PowerShell script updates the “Title” field value from “File Name”. 

#Load SharePoint CSOM Assemblies
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"
  
#Parameters
$SiteURL = "https://Crescent.sharepoint.com"
$ListName = "Documents"
$UserName = "[email protected]"
$Password = "Password123456"
$SecurePassword= $Password | ConvertTo-SecureString -AsPlainText -Force
$BatchSize =100
 
#Setup the Context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
 
#Get the List
$List = $Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()

#sharepoint online powershell caml batch update
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
    <View Scope='RecursiveAll'> 
        <Query> 
             <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        </Query>
        <RowLimit>$BatchSize</RowLimit>
    </View>"

#Get List Items in Batches
Do
{
    #Get List Items 
    $ListItems = $List.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()
    $ListItems.Count

    #Update Postion of the ListItemCollectionPosition
    $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

    If ($ListItems.Count -eq 0) { Break }
    
    #Update List Item
    ForEach($Item in $ListItems)
    {
        #Update List Item Title as File Name
        $Item["Title"]= $Item["FileLeafRef"]
        $Item.Update()
    }
    $Ctx.ExecuteQuery()
}While ($Query.ListItemCollectionPosition -ne $null)

If you want to bulk update multiple items from a CSV file, use: Update SharePoint Online List from a CSV using PowerShell

PnP PowerShell to Update All Items in a SharePoint Online List

Updating multiple list items using PnP PowerShell is relatively easier than the above CSOM script (That’s the Power of PnP!). 

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName = "Projects"

#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive

#Get all Items from the list
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000

#Update all list Items
ForEach($Item in $ListItems)
{
    #Update List Item Title as its ID value
    Set-PnPListItem -List $ListName -Identity $Item.Id -Values @{"Title"= $Item.Id} | Out-Null
    Write-host "Updated Item:"$Item.ID    
}

You can also use the PnP PowerShell’s New-PnPBatch method to bulk update list items faster! Here is the reference: SharePoint Online: Perform Bulk Operations with New-PnPBatch in PowerShell

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

6 thoughts on “SharePoint Online: Bulk Update All Items in Large List using PowerShell”

  • Could you plz let me know how to insert items in SharePoint list in batches so that i can minimize the number of calls to server?

    Reply

  • While going for pnp method, it will take longer time to execute the “Set-PnPListItem” command, right?
    How can we improve it’s performance

    Reply

  • I haven’t been able to get this to work on lists over 5000. On lists under 5000 items, I see that it processes items in batches of 100 and have verified that the items have been updated. On a list over 5000, I get the dreaded “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”

    Reply

    • This now works. Thank you so much!

      I’m not very strong in the Power Shell world yet, I truly appreciate your help.

      Reply

Leave a Reply