SharePoint Basic List Item Operations Using PnP PowerShell

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.