Introduction
In this article, you will learn how we can create, retrieve, update and delete the items on SharePoint lists using PnP PowerShell. The Client Side Object Model is used internally for these operations.
Prerequisite
You need to have PowerShell 3.0 available on a Windows machine. You need to install or import PnP PowerShell packages. You can download the installers or view more documentations on the official site. The installers are available here.
The following operations will be compatible with SharePoint 2013 on-premise or Office 365 versions.
Connect To Site
Connect to the site, using the snippet given below. The PnP PowerShell code, given below, helps in getting the current context of the site, using the Client Side Object Model (CSOM).Ā
$siteurlĀ =Ā "https://abc.sharepoint.com"
Connect-SPOnlineĀ -UrlĀ $siteurlĀ Ā
$ctxĀ =Ā Get-SPOContextĀ Ā
Once connected, you can carry out any of the operations mentioned below, based on the requirement.Ā
Create List Item
The list items can be added to the root site or sub site lists by setting the context, using the site URL. PnP CSOM PowerShell can be used to complete the operation.
Add-SPOListItem command is used to create the items on SharePoint list. The new values can be passed as hash tables through the values parameter. In my example, I have added a new item with two custom column values. When the hash table is not passed as a parameter, the new item will be created with the empty field values.
The following command snippet helps in adding the new items to a custom list.
functionĀ AddListItem(){Ā Ā Ā Ā Ā Ā
#Ā CreatesĀ itemĀ withĀ emptyĀ fields Ā Ā Ā Ā
Add-SPOListItemĀ -ListĀ "PnPList" Ā Ā Ā Ā
#Ā CreatesĀ itemĀ withĀ values Ā Ā Ā Ā
Add-SPOListItemĀ -ListĀ "PnPList"Ā -
ValuesĀ @{"Title"="6";"ProductId"="6"}Ā Ā }Ā Ā
AddListItemĀ #Ā AddsĀ ListĀ item Ā
Retrieve List Items
The list items, available on the root site collections or sub site lists, can be retrieved by setting the context, using PnP CSOM PowerShell. We will see how we can retrieve the items from SharePoint lists.
Get-SPOListItem is used to retrieve the items from the lists. Along with the command, the list name and the required fields are passed. The column names are passed, using a field parameter. The list item collection will be retrieved. Using the Ā for each loop, we can get all the item details. When the field names are not passed, all the field values for items are retrieved.
The following code snippet shows how we can retrieve all the items from SharePoint list:
functionĀ RetrieveListItems(){Ā Ā Ā Ā Ā Ā
$listItemsĀ =Ā Get-SPOListItemĀ -ListĀ "PnPList"Ā -FieldsĀ "Title","ProductId" Ā Ā Ā Ā
foreach($listItemĀ inĀ $listItems){Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā
Write-HostĀ "TitleĀ :Ā "Ā $listItem["Title"]Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā
Write-HostĀ "IdĀ :Ā "Ā $listItem["ID"]Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā
Write-HostĀ "ProductIdĀ :Ā "Ā $listItem["ProductId"]Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā
Write-HostĀ "------------------------------------" Ā Ā Ā Ā }Ā Ā }Ā Ā
RetrieveListItemsĀ #Ā GetĀ ItemsĀ Ā
Using Query
Queries can be used to filter SharePoint list items, based on the requirements. The query can be sent with Query parameters for filtering data.
The following code snippet shows how we can retrieve all items from the SharePoint list using queries.
functionĀ RetrieveListItemByQuery(){Ā Ā Ā Ā Ā
Ā $listItemĀ =Ā Get-SPOListItemĀ -ListĀ PnPListĀ -QueryĀ "<View><Query><Where><Eq>
<FieldRefĀ Name='ProductId'/><ValueĀ Type='Text'>2</Value></Eq></Where></Query></View>" Ā Ā Ā Ā
Write-HostĀ "TitleĀ :Ā "Ā $listItem["Title"]Ā Ā Ā Ā Ā Ā
Write-HostĀ "IdĀ :Ā "Ā $listItem["ID"]Ā Ā Ā Ā Ā Ā
Write-HostĀ "ProductIdĀ :Ā "Ā $listItem["ProductId"]Ā Ā Ā Ā Ā Ā
Write-HostĀ "------------------------------------" }Ā Ā
RetrieveListItemByQueryĀ #Ā RetrieveĀ itemsĀ byĀ query Ā
Update List Item
SharePoint list items available on the root site collections or sub sites can be updated, using PnP CSOM Powershell.Ā
Set-SPOListItem is used to update the items, using PnP PowerShell. The necessary parameter is used to update the item regarded as the list name and identity (list item id or item variable). The values are passed as hash tables through values parameter.
The following code snippet shows how we can update the items on the list, using PnP PowerShell:
functionĀ UpdateListItem(){Ā Ā Ā Ā Ā Ā
Set-SPOListItemĀ -ListĀ "PnPList"Ā -IdentityĀ 6Ā -
ValuesĀ @{"Title"="6A";"ProductId"="product6"}Ā Ā }Ā Ā
UpdateListItemĀ #Ā UpdateĀ itemĀ byĀ identityĀ Ā
Using query
The items can be updated, using query, as well. Retrieve the items, using query (refer to the above section). The retrieved item is then passed as an identity with the update command.
The following code snippet shows how we can update the items on the list with a query, using PnP PowerShell:
functionĀ UpdateListItemByQuery(){Ā Ā Ā Ā Ā
#Ā UpdateĀ usingĀ query Ā Ā Ā Ā
$itemĀ =Ā Get-SPOListItemĀ -ListĀ "PnPList"Ā -QueryĀ "<View><Query><Where><Eq>
<FieldRefĀ Name='ProductId'/><ValueĀ Type='Text'>product6</Value></Eq></Where></Query></View>"
if($itemĀ -neĀ $null){Ā Ā Ā Ā Ā Ā Ā Ā Ā
Ā Set-SPOListItemĀ -ListĀ "PnPList"Ā -IdentityĀ $itemĀ -ValuesĀ @{"Title"="6";"ProductId"="6"}Ā Ā Ā Ā Ā Ā
}Ā Ā }Ā Ā
UpdateListItemByQueryĀ #Ā UpdateĀ itemĀ byĀ query Ā
Delete List Item
The list items can be deleted using PnP CSOM PowerShell.
Remove-SPOListItem command is used to delete a list item. The required parameters for deleting the list items are list name and identity value. Once the command is executed, the alert box will be prompted for the confirmation. The force attribute is used to delete the list items; i.e., without a confirmation prompt.
The following code snippet shows how we can delete the items in a SharePoint list.
functionĀ DeleteListItem(){Ā Ā Ā Ā Ā Ā
Remove-SPOListItemĀ -ListĀ "PnPList"Ā -IdentityĀ 5Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā
Remove-SPOListItemĀ -ListĀ "PnPList"Ā -IdentityĀ 4Ā -
ForceĀ #Ā DeleteĀ byĀ UsingĀ forceĀ attributeĀ Ā }Ā Ā
DeleteListItemĀ #Ā DeleteĀ itemĀ byĀ identityĀ Ā
Using Query
The items can be deleted using query as well. Retrieve the items, using query (refer above section). The retrieved item is passed as an identity with the delete command.
The following code snippet shows how we can delete the items on the list with the query, using PnP PowerShell.
functionĀ DeleteListItemByQuery(){Ā Ā Ā Ā Ā Ā
#Ā DeleteĀ usingĀ query Ā Ā Ā Ā
$itemĀ =Ā Get-SPOListItemĀ -ListĀ "PnPList"Ā -QueryĀ "<View><Query><Where><Eq>
<FieldRefĀ Name='ProductId'/><ValueĀ Type='Text'>6</Value></Eq></Where></Query></View>"
if($itemĀ -neĀ $null){Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā
Remove-SPOListItemĀ -ListĀ "PnPList"Ā -IdentityĀ $itemĀ Ā Ā Ā Ā Ā
}Ā Ā }Ā Ā
DeleteListItemByQueryĀ #Ā DeleteĀ itemĀ byĀ query Ā
Summary
Thus, you have learned how to create, retrieve, update or delete the SharePoint list items, using PnP PowerShell on SharePoint 2013 / SharePoint online sites.
Comments