How to Add SharePoint Online List Items Using PowerShell?

In this PowerShell article, I will explain how to add SharePoint list items using PowerShell with examples.

How to Add SharePoint Online List Items Using PowerShell

In this post, I will show you how to add SharePoint list items using PnP PowerShell.

NOTE:

Only SharePoint site administrators or the members with edit permissions to the list can add the SharePoint list items using PowerShell.

To execute the PowerShell commands, we can use Visual Studio Code or Windows PowerShell ISE.

Add SharePoint Online List Items Using PnP PowerShell

In the PnP PowerShell, the Add-PnPListItem command is used to add the SharePoint list items. You can easily add different kinds of data to the different columns using this command.

Syntax:

To add a single SharePoint list item, we can use the script below:

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

To add a multiple SharePoint list item, follow the script below:

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

Where;

  • List: It specifies the list’s ID, Title, or Url.
  • ContentType: It specifies the name, ID, or actual content type.
  • Values: It specifies the values for the list columns. You should provide the internal column name here.
  • Folder: It specifies the folders and sub-folders present in that list. If the specified folders are unavailable in the list, new folders will be created, and the list items will be added.
  • Label: It specifies the name of the retention label.
  • Connection: You may optionally connect to a different site by executing the Get-PnPConnection or Connect-PnPOnline command.
  • Batch: It specifies adding multiple list items in a batched manner. Using this batch object, users can group multiple items and add them all at once instead of adding each item individually.

NOTE:

The Values parameter format must be “@{“Column1″= “Value1”; “Column2″= “Value2″; ….}.”
You should always pass the internal column name while providing the values.

For example, I have a list [Customer Orders] with different columns. Such as:

Column NameColumn Data Type
Customer_IDSingle line of text
Customer_NameSingle line of text
Product_LinkHyperlink
Product_DescriptionMultiple lines of text
Product_NumberNumber
Oder_DateDate and Time
Total_PriceCurrency
Payment_MethodChoice
Delivery_LocationLocation
Delivery_PersonPerson or Group
Order_StatusYes/No
Product_IdLookup

Now, I will add items for individual columns.

Example 1: [SharePoint Single line of text Column]

Follow the PowerShell command below to add SharePoint list items for the single line of the text column.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS1"; "Customer_Name"="John Smith"}

Execute the above command by clicking the RUN button. The added list of item details will be displayed in the TERMINAL after execution.

add sharepoint list items using powershell

If you want to check whether an item has been added, open your SharePoint list and check it there. List items will be added, as shown in the screenshot below.

add sharepoint list items for single line of text column using powershell

Example 2: [SharePoint Hyperlink Column]

To add a SharePoint list item for the hyperlink column, follow the below PowerShell script. Here, pass the URL in the form of a string.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS2"; "Customer_Name"="John" ; "Product_Link"=
"https://media.istockphoto.com/id/1136422297/photo/face-cream-serum-lotion-moisturizer-and-sea-salt-among-bamboo-leaves.webp?s=2048x2048&w=is&k=20&c=z6udAz2awGU8VK_5LKKuM6e6lKOniWiX3dMSB8gdRB0="}

To execute the code, Click on the RUN button. You can view the output of the list item details in the TERMINAL.

add sharepoint list items using pnp powershell

The added hyperlink is displayed on the SharePoint list page, as shown below.

how to add sharepoint list items for hyperlink column using powershell

Example 3: [SharePoint Multiple lines of text Column]

Use this PowerShell command to add the SharePoint list item for the multiple lines of text column. Pass the multi-line text in string format.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS3"; "Customer_Name"="John" ; "Product_Description"=  "This momoisturiser product protects your skin,
Also you get 20% offer on this product.. "}

Execute the above command, and you will get the output in the TERMINAL, as shown in the figure below.

how to add sharepoint list items using pnp powershell

You can see that the list item will be added for multiple lines of text columns in your SharePoint list.

add sharepoint list items for multiple lines of text column using powershell

Example 4: [Add SharePoint multiples list items at a time in batched mannere]

To add multiple list items in a batched manner, use the “Batch” parameter within the for loop. Follow the PowerShell command to add batched SharePoint list items to your list.

batch = New-PnPBatch
for($i=0;$i -lt 10;$i++)
{
    Add-PnPListItem -List "Customer Orders" -Values @{"Title"="SharePoint $i"} -  Batch $batch
}
Invoke-PnPBatch -Batch $batch

Click the RUN button to execute the above command. Later, you will get the list of item details in the TERMINAL.

how to add sharepoint list items using powershell

The list items are added to your SharePoint list, as shown in the figure below.

how to add the sharepoint list items using pnp powershell

Example 5: [SharePoint Date and Time Column]

If you want to add list items for the date and time column, follow the below PowerShell command.

Pass the “MM/DD/YYYY” format to add the date item to the SharePoint List.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS4"; "Oder_Date"="03/15/2024" }

If you want to pass the time and time, follow the “MM/DD/YYYY HH: MM” format.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS4"; "Oder_Date"="03/15/2024 14:16" }

Execute any one of the above commands that suits your needs. After execution, the added list item details will be given in the TERMINAL.

how to add sharepoint list items for date and time column using powershell

If you enable the time setting when creating the column but do not pass the time when executing the PowerShell command, the time will be provided randomly, as shown in the figure below.

add sharepoint list items for date and time column using powershell

Example 6: [SharePoint Currency Column]

Pass the currency value in a string format to add the SharePoint list item for the currency column. To do this, follow the PowerShell command below.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS5"; "Total_Price"= "400"}

To execute the above command, click the RUN button and check the output in the TERMINAL, as shown below.

how to add sharepoint list items for currency column using powershell

You can observe that the list item will be added to your SharePoint list.

add the sharepoint list items for currency column using powershell

Example 7: [SharePoint Choice Column]

Follow the PowerShell commands below to add choices to the SharePoint list choice column.

To add a single choice, follow the “choice1” format, as in the example below.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS6"; "Payment_Method"="Gpay"}

To add multiple choices, follow the “choice1, choice2,….” format, as in the example below.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS6"; "Payment_Method"="Gpay,Card"}

You can execute any one of the above commands based on your needs. After execution, you will see the output of the PowerShell script on the terminal.

how to add sharepoint list items for choice column using powershell

The list item will be added to a choice column in your SharePoint list, as shown in the screenshot below.

add sharepoint list items for choice column using powershell

Example 8: [SharePoint Location Column]

Follow the PowerShell script below to add a list item for the location column. Replace all your details of the location with mine.

$Address = @{
        "Street" = "Westmang"
        "City" = "Bergkamen"
        "State" =  "Nordrhein-Westfalen"
        "CountryOrRegion" = "Germany"
        "PostalCode" = "59192"
        }
$Coordinates = @{
        "Longitude" = [double]"7.6405439376831055"
        "Latitude" = [double]"51.646881103515625"
        }
$DisplayNameLocation = $Adress["Street"]+ ", " + $Adress["Postalcode"] + "," +$Adress["City"] 
$Location = @{
        "DisplayName" = $DisplayNameLocation
        "Address"= $Address
        "Coordinates" = $Coordinates
        }
        $Location = $Location | ConvertTo-Json
    
Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS6"; "Delivery_Location"=$Location}

After executing the above command, you will get your added list item details output in the TERMINAL.

how to add sharepoint list items for location column using powershell

You can view the added location in your SharePoint list column, as shown in the screenshot below.

add sharepoint list items for location column using powershell

Example 9: [SharePoint Person or Group Column]

Suppose you want to add a list item for the person or group column to your SharePoint list. Follow the below PowerShell commands.

To add one person to the column, pass the user’s email address in string format. Follow the example below to do this.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS7"; "Delivery_Person"="JohannaL@szg52.onmicrosoft.com"}

If you want to add multiple users, pass the users’ mail address in the format of @(“User1@<tenant>.onmicrosoft.com”, “User1@<tenant>.onmicrosoft.com”).

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS7"; "Delivery_Person"=@("JohannaL@szg52.onmicrosoft.com", "PattiF@szg52.onmicrosoft.com")}

After executing any of the commands, you will get added item details in the TERMINAL.

add sharepoint list items for person or group column using powershell

You can view the added list items for the person or group column on your SharePoint list page, as shown in the figure below.

how to add the sharepoint list items for person or group column using powershell

Example 10: [SharePoint Yes/No Column]

Follow this PowerShell command to add the list item for the Yes/No column. Pass the $false for No and $true for Yes.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS8"; "Order_Status"=$false}

Execute the above command by clicking the RUN button. Later, you will get the list of item-related outputs in your TERMINAL.

how to add sharepoint list items for yes or no column using powershell

You can view the items added on your SharePoint list page, as shown below.

add sharepoint list items for yes or no column using powershell

Example 11: [SharePoint Lookup Column]

This PowerShell example script adds the list item for the lookup column. First, you should get the connected SharePoint list items using the Get-PnPListItem command. Then, you can add the list item using the Add-PnPListItem command.

# Fist you need to get the connected table
$Product= Get-PnPListItem -List "Product Details" | Where-Object {$_.Fieldvalues.Title -eq "PD3"}

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS13";"Product_Id"=$Product.id}

Execute the above script by clicking the RUN button. Then, you will get the added list item details in the TERMINAL.

how to add sharepoint list items for lookup column using powershell

The SharePoint list item will be added to the lookup column on your SharePoint list page, as shown below.

add sharepoint list items for lookup column using powershell

Example 12: [SharePoint Number Column]

To add the Number of data items to the SharePoint list, follow the below PowerShell command. Pass the value in Number format.

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS10"; "Product_Count"=12345}

After executing the above command, you will see the details of the added items in the TERMINAL.

Add the sharepoint list items for the number column using PowerShell

The added list item is on the SharePoint list page, as shown in the screenshot below.

how to add sharepoint list items for the number column using PowerShell

Add SharePoint list items using PnP PowerShell

Follow the PowerShell example script below to add all the data types of values to one SharePoint list item.


$Address = @{
    "Street" = "Westmang"
    "City" = "Bergkamen"
    "State" =  "Nordrhein-Westfalen"
    "CountryOrRegion" = "Germany"
    "PostalCode" = "59192"
    }
$Coordinates = @{
    "Longitude" = [double]"7.6405439376831055"
    "Latitude" = [double]"51.646881103515625"
    }
$DisplayNameLocation = $Adress["Street"]+ ", " + $Adress["Postalcode"] + "," +$Adress["City"] 
$Location = @{
    "DisplayName" = $DisplayNameLocation
    "Address"= $Address
    "Coordinates" = $Coordinates
    }
    $Location = $Location | ConvertTo-Json

    $Product= Get-PnPListItem -List "Product Details" | Where-Object {$_.Fieldvalues.Title -eq "PD3"}

Add-PnPListItem -List "Customer Orders" -Values @{"Title"= "CTS15";`
                                    "Customer_Name"="Miller";`
                                    "Product_Link"=
                                    "https://media.istockphoto.com/id/1136422297/photo/face-cream-serum-lotion-moisturizer-and-sea-salt-among-bamboo-leaves.webp?s=2048x2048&w=is&k=20&c=z6udAz2awGU8VK_5LKKuM6e6lKOniWiX3dMSB8gdRB0=";
                                    "Product_Description"=  "This momoisturiser product protects your skin,
                                                                Also you get 20% offer on this product.. ";
                                    "Oder_Date"="04/15/2024";
                                    "Total_Price"= "500";
                                    "Payment_Method"="Gpay";
                                    "Delivery_Location"=$Location;
                                    "Order_Status"=$false;
                                    "Product_Id"=$Product.id;
                                    "Delivery_Person"="JohannaL@szg52.onmicrosoft.com"}

To execute the above code, click the RUN button. Then, you will get the list item details in the TERMINAL.

add the sharepoint list items using powershell

All the data values are added at a time to the SharePoint list, as shown in the screenshot below.

how to add the sharepoint list items using powershell

This is how we can add the SharePoint list item using PowerShell.

In this tutorial, I explained to you how to add the SharePoint Online list items using PnP PowerShell with various examples.

Also, you may like: