SharePoint List Items CRUD Operations Using PnP PowerShell

Recently, I was working on a Power Apps leave management app where we wanted to create a few SharePoint Online lists. Before deploying this Power Apps application on the client machine, we need to create all these lists on SharePoint Online sites.

I used PnP PowerShell to create all these SharePoint list items. After doing this, I continued the research and did some CRUD operations for SharePoint list items using PnP PowerShell.

SharePoint List Items CRUD Operations Using PnP PowerShell

Here, we’ll see the below CRUD operations on SharePoint list items using PnP PowerShell.

• Create Single & Multiple SharePoint list items.
• Retrieve Single & Multiple SharePoint list items.
• Update Single & Multiple SharePoint list items.
• Delete Single & Multiple SharePoint list items.

Here is the SharePoint list named “Employee Leave Requests.”

Column Name	Data Type
Leave Title	Title(Single line of text)
Leave Type	Choice(Sick Leave,Annual Leave,Compassionate Leave,Maternity,Others)
Start Date	Date and Time
End Date	Date and Time
Half Day	Yes/No
Leave Days	Number
Department	Choice(IT,HR,Marketing,Sales,Research)
Manager Name	Single line of text

Create SharePoint List Items Using PnP PowerShell

We need to use the  Add-PnPListItem PnP PowerShell cmd to create a SharePoint list item. The syntax is:

Add-PnPListItem [-List] <ListPipeBind> [-ContentType <ContentTypePipeBind>] [-Values <Hashtable>][-Folder <String>] [-Label <String>] [-Connection <PnPConnection>]

The parameters of the above syntax:

• List = Provide SharePoint list name.
• ContentType= Specify the content type name or id.
• Values = Provide the values to the SharePoint list column names. Note: Always take the internal name of the column.
• Folder = Provide folder name if you’re storing items in the document library.
• Label = Provide the name of the retention label.
• Connection = Optional connection.

Connect-PnPOnline -Url "https://<tenant name >.sharepoint .com/sites/RetailManagement 
Site" -Interactive
$ListName = "Employee Leave Requests" 

# Add a new item to the list

Add-PnPListItem -List $ListName -Values @{"Title"= "Annual Leave";
"LeaveType"= "Vacation Leave";
"StartDate"= "2024-09-01";
"EndDate"="2024-09-10";
"HalfDay"= $false;
"LeaveDays"= 10;
"Department"="HR";
"ManagerName" = "Jane Doe"}

Write-Host "New item has been added successfully."

Here,

• $ListName variable contains the SharePoint list name.
• For the Values parameter in Add-PnPListItem cmd, I’m taking the column’s internal name and providing values for each column.

Run the above script in “Visual Studio Code Editor” or “Windows PowerShell ISE.” In the TERMINAL, you can see the created item ID along with the title and GUID.

Now check the SharePoint list once. You can see the newly created item.

Check out Customizing SharePoint List Fields with PnP PowerShell

Let’s see how to add multiple SharePoint list items using PnP PowerShell.

Run the code below after the execution is completed. You can see the details of the created items in TERMINAL.

# Config Variables
$SiteURL = "https://<tenant name >.sharepoint.com/sites/SalesDepartmentTeam"
$ListName = "Employee Leave Requests"  

# Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

# Array of items to be added
$items = @(
    @{"Title"= "Annual Leave";     "LeaveType"= "Vacation Leave";  "StartDate"= "2024-09-01";"EndDate"="2024-09-10";"HalfDay"= $false;"LeaveDays"= 10;"Department"="HR";        "ManagerName" = "Jane Doe"},

    @{"Title"= "Medical Emergency";"LeaveType"= "Sick Leave";      "StartDate"= "2024-08-15";"EndDate"="2024-09-17";"HalfDay"= $true; "LeaveDays" = 2;"Department"= "Sales";     "ManagerName" = "John Smith"},

    @{"Title"= "Going for trip";   "LeaveType"= "Annual Leave";    "StartDate"= "2024-07-15";"EndDate"="2024-09-17";"HalfDay"= $false;"LeaveDays" = 3;"Department"= "Marketing"; "ManagerName" = "Patti Fernanz"},

    @{"Title"= "Personal Day Off"; "LeaveType"= "Others";          "StartDate"= "2024-05-15";"EndDate"="2024-09-17";"HalfDay"= $true; "LeaveDays" = 2;"Department"= "IT";        "ManagerName" = "Lidia Holloway"},

    @{"Title"= "Trip Abroad";      "LeaveType"= "Annual Leave";    "StartDate"= "2024-06-15";"EndDate"="2024-08-17";"HalfDay"= $false;"LeaveDays" = 5;"Department"= "Sales";     "ManagerName" = "John Smith"},

    @{"Title"= "Bereavement Leave";"LeaveType"= "Vacation Leave";  "StartDate"= "2024-03-15";"EndDate"="2024-08-17";"HalfDay"= $true; "LeaveDays" = 7;"Department"= "Marketing"; "ManagerName" = "John Smith"},

    @{"Title"= "Annual Vacation";  "LeaveType"= "Annual Leave";    "StartDate"= "2024-05-15";"EndDate"="2024-07-17";"HalfDay"= $false;"LeaveDays" = 3;"Department"= "IT";        "ManagerName" = "John Doe"},

    @{"Title"= "Family Function";  "LeaveType"= "Others";          "StartDate"= "2024-09-15";"EndDate"="2024-09-17";"HalfDay"= $true; "LeaveDays" = 1;"Department"= "Sales";     "ManagerName" = "John Doe"},

    @{"Title"= "Home Renovation";  "LeaveType"= "Others";          "StartDate"= "2024-10-15";"EndDate"="2024-08-17";"HalfDay"= $false;"LeaveDays" = 6;"Department"= "Marketing"; "ManagerName" = "John Smith"},

    @{"Title"= "Jury Duty";        "LeaveType"= "Others";          "StartDate"= "2024-01-15";"EndDate"="2024-06-17";"HalfDay"= $true; "LeaveDays" = 4;"Department"= "Sales";     "ManagerName" = "John Doe"}
)

# Add each item to the list
foreach ($item in $items) {
    # Add item to the SharePoint list
    Add-PnPListItem -List $ListName -Values @{
        "Title"                        = $item["Title"]
        "LeaveType"           = $item["LeaveType"]
        "StartDate"             = $item["StartDate"]
        "EndDate"               = $item["EndDate"]
        "HalfDay"                = $item["HalfDay"]
        "LeaveDays"          = $item["LeaveDays"]
        "Department"       = $item["Department"]
        "ManagerName" = $item["ManagerName"]
    }
}
Write-Host "Items have been added successfully."

Here,

• $items is an array that contains multiple SharePoint list items column values.
• With the foreach loop, I’m iterating over each item on the array and adding those items to the SharePoint list using Add-PnPListItem cmd.

Check the SharePoint list once. You’ll be able to see newly added multiple items in the SharePoint list.

This way, we can add single and multiple SharePoint list items using PnP PowerShell.

Check out PowerShell Substring() Example

Retrieve SharePoint List Items Using PnP PowerShell

In PnP PowerShell, the Get-PnPListItem command helps us retrieve both individuals and all items from the SharePoint list.

Here is the syntax for the Get-PnPListItem command.

Get-PnPListItem [-List] <ListPipeBind> [-Id <Int32>] 

• List = Provide SharePoint list name.
• Id = Provide the SharePoint list item ID.

Run the below cmdlets to retrieve the single SharePoint list item.

Connect-PnPOnline -Url "https://<tenant name >.sharepoint .com/sites/RetailManagement Site" -Interactive

$ListName = "Employee Leave Requests" 
Get-PnPListItem -List $ListName -Id 1

Here, $ListName contains the SharePoint list name. Inplace of <tenant name >, provide your tenant name.

In the TERMINAL pane, you can see the details of the retrieved item.

Now, we’ll see how to retrieve all SharePoint list items using PnP PowerShell.

Run the below cmd on VSCode. In the TERMINAL pane, you can see the details of all SharePoint list items.

Connect-PnPOnline -Url "https://<tenant name >.sharepoint .com/sites/RetailManagement Site" -Interactive

$ListName = "Employee Leave Requests" 
Get-PnPListItem -List $ListName

I didn’t take the ID parameter here, so it displays all the items in the SharePoint list.

This way, we can perform retrieve operations on SharePoint list items using PnP PowerShell.

Read Get Microsoft 365 Group Members Using PowerShell

Update SharePoint List Items Using PnP PowerShell

The Set-PnPListItem command in PnP PowerShell will help us to update the SharePoint list items.

The syntax for this command is:

Set-PnPListItem [-List <ListPipeBind>] -Identity <ListItemPipeBind> [-Values <Hashtable>] 

Here,

• List = Provide SharePoint list name.
• Identity = Provide the SharePoint list item ID.
• Values = Provide the values you want to update for the columns present in the list.

Run the code below to update a single item in the SharePoint list.

Connect-PnPOnline -Url "https://<tenant name >.sharepoint .com/sites/RetailManagement Site" -Interactive

$ListName = "Employee Leave Requests" 
$ItemId = 1
# Update item in the list

Set-PnPListItem -List $ListName -Identity $ItemId -Values @{"Title"= "Medical Emergency";"LeaveType"= "Sick Leave";"StartDate"= "2024-08-15";"EndDate"="2024-09-17";"HalfDay"= $true;"LeaveDays" = 2;"Department"= "Sales";"ManagerName" = "John Smith"}

Write-Host "Item updated successfully."

In the Set-PnPListItem command within the Values parameter, I took all the column’s internal names I wanted to update.

• $ListName contains the Sharepoint list name.
• $ItemId contains the id value.

Once the code is executed, check the SharePoint list once. You can see the SharePoint list item has been updated.

Now, we’ll see how to update multiple SharePoint list items using PnP PowerShell.

Run the below code, and in TERMINAL, you can see the details of the updated SharePoint list items.

# Config Variables
$SiteURL = "https://<tenant name>.sharepoint.com/sites/RetailManagementSite"
$ListName = "Employee Leave Requests"

# Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

# Retrieve all items from the list
$items = Get-PnPListItem -List $ListName

# Check if there are items to update
if ($items.Count -eq 0) {
    Write-Host "No items found in the list."
} else {
    # Iterate over each item and update the Title field
    foreach ($item in $items) {
        $newTitle = "Annual Leave" 

        # Update the item
        Set-PnPListItem -List $ListName -Identity $item.Id -Values @{"Title" = $newTitle}
        Write-Host "Updated item with ID $($item.Id) to Title: $newTitle"
    }

    Write-Host "All items have been updated successfully."
}

In the above code, I’m trying to update all items’ Leave title value to “Annual Leave.” Here,

• $items contain all the SharePoint list items.
• With the foreach loop, we’re iterating through each item present in the $items array and updating each item Leave Title column value as an “Annual Leave.” using Set-PnPListItem cmd.
• If no items are found in the SharePoint list, a message will display, such as “No items found in the list.”

Let’s check the SharePoint list once. Each item’s Leave Title field value has been changed.

Check out Delete All SharePoint List Items using PowerShell

Delete SharePoint List Items Using PnP PowerShell

We need to use this Remove-PnPListItem PnP Power Shell command to delete the SharePoint list item. The syntax for this command is :

Remove-PnPListItem [-List] <ListPipeBind> -Identity <ListItemPipeBind> [-Recycle] [-Force] 

• List = Provide SharePoint list name.
• Identity = Provide the SharePoint list item ID.
• Recycle = By providing this parameter, the deleted item will be moved to the Recycle bin.
• Force = It will skip the confirm message when deleting the SharePoint list item.

Run the code below to delete a single SharePoint list item using PnP PowerShell.

Connect-PnPOnline -Url "https://<tenant name >.sharepoint .com/sites/RetailManagement Site" -Interactive
$ListName = "Employee Leave Requests" 
$ItemId = 1

# Delete the item from the list
Remove-PnPListItem -List $ListName -Identity $ItemId -Force

Here

• $ListName contains the SharePoint list name.
• $ItemId contains SharePoint list item ID.

You can check the SharePoint list and Recycle bin. It will be deleted permanently.

Let’s see how to delete multiple SharePoint list items using PnP PowerShell.

$SiteURL = "https://<tenant name>.sharepoint.com/sites/RetailManagementSite"
$ListName = "Employee Leave Requests"

Connect-PnPOnline -Url $SiteURL -Interactive

# Retrieve all items from the list
$items = Get-PnPListItem -List $ListName

# Check if there are items to delete
if ($items.Count -eq 0) {
    Write-Host "No items found in the list."
} else {
    # Iterate over each item and delete
    foreach ($item in $items) {
        Remove-PnPListItem -List $ListName -Identity $item.Id -Force
        Write-Host "Deleted item with ID $($item.Id)"
    }

    Write-Host "All items have been deleted successfully."
}

Here,

• $items contain all the SharePoint list items.
• A message will display if no items are found in the SharePoint list, such as “No items found in the list.”
• If items are found, it will delete each item by iterating over the items in the $items array with a foreach loop.

Once it is executed, check the sharePoint list once. You won’t find any items. Also, you can search in Recycle Bin on the site there, but items are not found, which means they are deleted permanently.

This way, we can delete single and multiple SharePoint list items using PnP PowerShell.

I hope you understand how to perform CRUD operation on SharePoint list items using PnP PowerShell.

You can use these PnP PowerShell commands whenever you’re required to perform CRUD operation on bulk items because it saves your time and energy, and we can easily avoid making mistakes while adding bulk data to the SharePoint list.

You may like the following PowerShell tutorials:

• Foreach vs Foreach-Object in PowerShell
• Create and Delete SharePoint Document Library Using PowerShell
• PowerShell Foreach With Examples
• Remove Duplicate Objects from an Array in PowerShell