Wednesday, 11 May 2016

List Item Operations using CSOM with PowerShell for SharePoint Online / o365 / on-premise platforms

Views

In this article, you will learn how we can retrieve, create, delete or update list items using CSOM with PowerShell. This is mainly focused on using PowerShell scripts for any SharePoint sites.

Note: I have explained all the logic with respect to SharePoint online sites. If you want to do same operation for SharePoint on premise sites, you need to remove credentials parameter from the context and run it on the respective on premise servers. 


Get List Items:


First we will see how we can get the existing list items available for a list on the SharePoint site. The steps followed here are very similar to the steps following CSOM or JSOM programming.
  1. Initialize context object with the site URL parameterThen initialize the SP Online Credentials with the above parameters and set it to the context.
  2. Then access the list using the context, initialize the query and use GetItems method to get items
  3. Load the objects and execute the query.
  4. Loop through the result object and get the necessary field values
    1. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
    2. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
    3.   
    4. $siteURL = ""  
    5. $userId = ""  
    6. $pwd = Read-Host -Prompt "Enter password" -AsSecureString  
    7. $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)  
    8. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
    9. $ctx.credentials = $creds  
    10. try{  
    11.     $lists = $ctx.web.Lists  
    12.     $list = $lists.GetByTitle("TestList")  
    13.     $listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())  
    14.     $ctx.load($listItems)  
    15.       
    16.     $ctx.executeQuery()  
    17.     foreach($listItem in $listItems)  
    18.     {  
    19.         Write-Host "ID - " $listItem["ID""Title - " $listItem["Title"]  
    20.     }  
    21. }  
    22. catch{  
    23.     write-host "$($_.Exception.Message)" -foregroundcolor red  
    24. }  
This will get all the list items of the list on the site.
Next, we will see how we can create list items.


Create List Item:


Here we will see how we can create item in a list. The following steps depict the flow.
  1. Initialize context object with the site URL parameterThen initialize the SP Online Credentials with the above parameters and set it to the context.
  2. Initialize the ListItemCreationInformation object.
  3. Set the required parameters for new list item.
  4. Add new list item creation object to the list item collection. Then Load and execute the query.
    1. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
    2. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
    3.   
    4. $siteURL = ""  
    5. $userId = ""  
    6. $pwd = Read-Host -Prompt "Enter password" -AsSecureString  
    7. $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)  
    8. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
    9. $ctx.credentials = $creds  
    10. try{  
    11.     $lists = $ctx.web.Lists  
    12.     $list = $lists.GetByTitle("TestList")  
    13.     $listItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
    14.     $listItem = $list.AddItem($listItemInfo)  
    15.     $listItem["Title"] = "c"  
    16.     $listItem.Update()      
    17.     $ctx.load($list)      
    18.     $ctx.executeQuery()  
    19.     Write-Host "Item Added with ID - " $listItem.Id      
    20. }  
    21. catch{  
    22.     write-host "$($_.Exception.Message)" -foregroundcolor red  
    23. }  
The list item will be added. Next, we will see how to delete list.


Delete List Item:



Here we will see how we can delete the list item. The following steps depict you the flow.
  1. Initialize context object with the site URL parameterThen initialize the SP Online Credentials with the above parameters and set it to the context.
  2. Get the list item using GetItemById method.
  3. Then, remove the list item using the delete object method.
  4. Using the context, execute the query.
    1. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
    2. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
    3.   
    4. $siteURL = ""  
    5. $userId = ""  
    6. $pwd = Read-Host -Prompt "Enter password" -AsSecureString  
    7. $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)  
    8. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
    9. $ctx.credentials = $creds  
    10. try{  
    11.     $lists = $ctx.web.Lists  
    12.     $list = $lists.GetByTitle("TestList")  
    13.     $listItem = $list.GetItemById(1)  
    14.     $listItem.DeleteObject()  
    15.     $ctx.executeQuery()      
    16. }  
    17. catch{  
    18.     write-host "$($_.Exception.Message)" -foregroundcolor red  
    19. }  
The list item will be deleted from the list.



Update List Item:



Here we will see how we can update the list item. The following steps depict you the flow.
  1. Initialize context object with the site URL parameterThen initialize the SP Online Credentials with the above parameters and set it to the context.
  2. Get the list using GetItemById method.
  3. You can do your own custom operation with your custom logic here.
  4. Then, update the list item using the update method.
  5. Using the context, execute the query.
    1. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"  
    2. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
    3.   
    4. $siteURL = ""  
    5. $userId = ""  
    6. $pwd = Read-Host -Prompt "Enter password" -AsSecureString  
    7. $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)  
    8. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
    9. $ctx.credentials = $creds  
    10. try{  
    11.     $lists = $ctx.web.Lists  
    12.     $list = $lists.GetByTitle("TestList")  
    13.     $listItem = $list.GetItemById(1)  
    14.     $listItem["Title"] = "aa"  
    15.     $listItem.Update()  
    16.     $ctx.load($listItem)      
    17.     $ctx.executeQuery()  
    18. }  
    19. catch{  
    20.     write-host "$($_.Exception.Message)" -foregroundcolor red  
    21. }  
The list item will be updated. Likewise you can do your own update operations.