SharePoint Online: Update List Items using PowerShell
Requirement: SharePoint Online PowerShell to Edit List Item
PowerShell CSOM Script to update List Items in SharePoint Online:
Here is the example for SharePoint Online PowerShell to update list item.
The above script updates a particular list item. What if you want to update all items in the list?
PowerShell to Update All List Items in SharePoint Online:
Here is the PowerShell to update list item in SharePoint Online
SharePoint Online: PnP PowerShell to Update List Item
Here is how to update list item using PnP PowerShell in SharePoint Online using Set-PnPListItem cmdlet.
Similarly, we can use the query parameter to filter and get a list item:
If you want to bulk update list items from a CSV file, use: SharePoint Online: Update List Items from a CSV File using PowerShell
How to Update Fields like Lookup, People Picker, Managed metadata, Hyperlink, etc?
The above scripts work just fine with simple field types like Single lines of text, choice, Yes/No, etc. However, for other field types such as Managed metadata, Hyperlink, Lookup, People Picker, etc. we've to handle them differently. Here are my posts to help:
PowerShell CSOM Script to update List Items in SharePoint Online:
Here is the example for SharePoint Online PowerShell to update list item.
#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" #Variables for Processing $SiteUrl = "https://crescent.sharepoint.com/" $ListName="Projects" $UserName="[email protected]" $Password ="Password goes here" #Setup Credentials to connect $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force)) #Set up the context $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) $Context.Credentials = $credentials try{ #Filter and Get the List Items using CAML $List = $Context.web.Lists.GetByTitle($ListName) #Get List Item by ID $ListItem = $List.GetItemById(1) #Update List Item title $ListItem["Title"] = "Project Darwin" $ListItem.Update() $Context.ExecuteQuery() write-host "Item Updated!" -foregroundcolor Green } catch{ write-host "$($_.Exception.Message)" -foregroundcolor red }This updates the Item with ID: 1 for the given list.
The above script updates a particular list item. What if you want to update all items in the list?
PowerShell to Update All List Items in SharePoint Online:
Here is the PowerShell to update list item in SharePoint Online
#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" #Variables $SiteURL="https://crescent.sharepoint.com" $ListName="Projects" Try { $Cred= Get-Credential $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password) #Setup the context $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL) $Ctx.Credentials = $Credentials $Web = $Ctx.web #Get the List $List = $Ctx.Web.Lists.GetByTitle($ListName) $Ctx.Load($List) $Ctx.ExecuteQuery() #Get All List items $ListItemsCAML = New-Object Microsoft.SharePoint.Client.CamlQuery $ListItemsCAML.ViewXml = "<View Scope='RecursiveAll'></View>" $ListItems = $List.GetItems($ListItemsCAML) $Ctx.Load($ListItems) $Ctx.ExecuteQuery() Write-host "Total Items Found:"$List.ItemCount #Iterate through each item and update Foreach ($ListItem in $ListItems) { #Set New value for List column $ListItem["Reference"] = $ListItem["ID"] $ListItem.Update() $Ctx.ExecuteQuery() } Write-host "All Items in the List: $ListName Updated Successfully!" -ForegroundColor Green } Catch { write-host -f Red "Error Updating List Items!" $_.Exception.Message }
SharePoint Online: PnP PowerShell to Update List Item
Here is how to update list item using PnP PowerShell in SharePoint Online using Set-PnPListItem cmdlet.
#Config Variables $SiteURL = "https://crescent.sharepoint.com" $ListName ="Projects" $ListItemID ="10" #Connect to PNP Online Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential) #Get List Item to Update $ListItem = Get-PnPListItem -List $ListName -Id $ListItemID -ErrorAction Stop #Update List Item - Internal Names of the columns : Value Set-PnPListItem -List $ListName -Identity $ListItem -Values @{"ProjectName" = "SharePoint 2016 Migration"; "ProjectID"="Abj-IT-3021"}
Similarly, we can use the query parameter to filter and get a list item:
#Config Variables $SiteURL = "https://crescenttech.sharepoint.com" $ListName ="Projects" $ListItemTitle ="SharePoint 2016 Migration" #Connect to PNP Online Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential) #Get List Item to Update by Query $ListItem = Get-PnPListItem -List $ListName -Query "<View><Query><Where><Eq><FieldRef Name='Title'/><Value Type='Text'>$ListItemTitle</Value></Eq></Where></Query></View>" -PageSize 1 -ErrorAction Stop #Update List Item - Internal Names of the columns : Value Set-PnPListItem -List $ListName -Identity $ListItem -Values @{"Title" = "SharePoint 2016 Migration V2"; "ProjectID"="Abj-IT-3025"}
If you want to bulk update list items from a CSV file, use: SharePoint Online: Update List Items from a CSV File using PowerShell
How to Update Fields like Lookup, People Picker, Managed metadata, Hyperlink, etc?
The above scripts work just fine with simple field types like Single lines of text, choice, Yes/No, etc. However, for other field types such as Managed metadata, Hyperlink, Lookup, People Picker, etc. we've to handle them differently. Here are my posts to help:
- SharePoint Online: PowerShell to Update Lookup Field Value
- SharePoint Online: Update Hyperlink Field Value using PowerShell
- SharePoint Online: PowerShell to Update Lookup Field Value
- SharePoint Online: Update Managed Metadata Field Value using PowerShell
- SharePoint Online: Update Person or Group Field Values using PowerShell
- SharePoint Online: Update Choice Field Value using PowerShell
- SharePoint Online: Update "Yes/No (Checkbox)" Field Value using PowerShell
- SharePoint Online: PowerShell to Update Date Field Value
can you show an example where you update the SP list if the ID in the csv is equal to the ID on the SP list?
ReplyDeleteSure, You can use this post: Update SharePoint List Items from CSV File using PowerShell
DeleteHow i can delete one Item ?
ReplyDeleteHere is how to delete list items using PowerShell How to Delete List Items in SharePoint Online using PowerShell
DeleteHi Salaudeen - I have a script that adds a an item list and it works. Are you able to help me attach an attachment when I create a new item?
ReplyDeleteI have a list item that will always contain an attachment and would like to create a list item and attach a file at the same time. Any idea how I can incorporate attaching a file in my script?
Sure, Here is how to add attachment to SharePoint Online List Item: SharePoint Online: How to Add Attachment to List Item using PowerShell
DeleteHi,
ReplyDeleteI want to update file meta data without changing mobified by / modified date in CSOM using powershell. Can anyone please help me??
Just store the current value of "modified by/ Modified Date" field values in a variable and then update them at end of your update. Here is how: How to update Created By / Modified At Metadata Values in SharePoint Online
DeleteI have been trying to access/manipulate a SharePoint Online list through PowerShell, and while I can connect to the site, and populate $ctx, it's failing on $Ctx.Web.Lists. The error I'm getting is "An error occurred while enumerating through a collection: The collection has not been initialized..."
ReplyDeleteAny thoughts?
Use:
Delete$Lists = $Ctx.Web.Lists
$Ctx.Load($lists)
$CtxExecuteQuery()