The article below is using SharePoint Module for managing lists, items and files as an extension to SharePoint Online Management Shell. Install the module to proceed.




Items 


Items are elements of SharePoint Online lists. Depending on the list type, they can be Announcements, Tasks, Contacts, Meetings, or custom ones, like Support Requests or Delivery Orders. The items can be added, modified, and deleted using User Interface, or programmatically, e.g. using C#. This article focuses on PowerShell approach.



Create Items


An item can be added using either a new item button or a Spreadsheet View:
  1. Navigate to the site containing the list for which you want to add an item.

  2. Select Settings SharePoint Online Public Website Settings button > Site contents, and then in the appropriate list section, select the name of the list.

    NOTE    A site can be significantly modified in appearance and navigation. If you cannot locate an option, such as a command, button, or link, contact your administrator.

  3. Select the Items tab, and then in the New group select New Item.

 
  1. Select edit at the top of the list. This is the same thing as selecting the Quick Edit command on the List tab.

    Adding an item to a list inline

    NOTE    You can easily switch from list view to spreadsheet view by selecting the View and Quick Edit commands respectively.

Source: https://support.office.com/en-sg/article/Add-edit-or-delete-list-items-a4b31f53-f044-470e-9823-4526594bacde


It is all very fine until we need to work with thousands of items and manual data entering and management is out of question.

Source: https://encrypted-tbn2.gstatic.com/images?q=tbn:ANd9GcSduLrp7N-zxOk1av58dKs4xmPKX1W4Zn-V1wCzbanzO2CpX0Yz


Having connected to the site with our lists (it can be a site collection or a subsite) using Connect-SPOCSOM cmdlet, we can create items using New-SPOListItem cmdlet.
Connect-SPOCSOM -Username 2190@tenant.onmicrosoft.com -AdminPassword *********** -Url https://tenant.sharepoint.com/sites/powie1
 
New-SPOList MultipleItems
 
New-SPOListItem -ListTitle MultipleItems -ItemTitle MyItem


Creating multiple items for test purposes


One of the common scenarios where we need lists with a lot of items is to reproduce the issues original lists are experiencing. If we cannot Open with Access a list of 2000 items, and a newly-created list opens without issues, is it item limitation or just my list went bad? Well, the list with multiple items for test purposes can be created with just one line:

for($i=0;$i -lt 2000;$i++){ New-SPOListItem -ListTitle MultipleItems -ItemTitle ("title"+$i)}



  


Items consist of more than a title


What if we want to add something more than a title?
New-SPOListItem -ListTitle multipleitems -ItemTitle Itemek -AdditionalField MyChoiceColumn -AdditionalValue Choice1


For multiple items:
for($i=0;$i -lt 2000; $i++){New-SPOListItem -ListTitle multipleitems -ItemTitle Itemek -AdditionalField MyChoiceColumn -AdditionalValue Choice1}



Import from CSV


 
Because it is PowerShell, we can use Import-CSV and combine it with New-SPOListItem in order to transfer a large number of items from Excel file to a SharePoint list.
First start with a CSV file that may look like this:
     

One line from PowerShell:
import-csv c:\users\ivo\Desktop\map1.csv | ForEach-Object {New-SPOListItem -ListTitle MultipleItems -ItemTitle ($_.title+$_.number) -AdditionalField $_.column -AdditionalValue $_.value}

 




Update Items


Just as easily as we were creating items, we can update them with one-line commands.

Single Item


Update-SPOListItem -ListTitle MultipleItems -ItemID 906 -FieldToUpdate MyField -ValueToUpdate "Value from Powershell"




Multiple Items


Here we have just created a new Boolean ($true/$false) column and want to set it to $true for all existing items.


  
$itemIDs=(Get-SPOListItems -ListTitle MultipleItems).ID
foreach($idd in $itemIDs){Update-SPOListItem -ListTitle MultipleItems -ItemID $idd -FieldToUpdate MyField -ValueToUpdate $true}




From CSV



The previous example worked fine if you wanted to set one field to one value. However, what if there is a whole bunch of different fields waiting to be updated? E.g. a company decided to change their domain and all your contacts need now to be updated with the new domain. You can use .csv file similar to this:



$itemki=Get-SPOListItems -ListTitle MultipleItems
$ImportedUpdates=import-csv c:\users\ivo\Desktop\map1.csv
foreach($impu in $ImportedUpdates) { Update-SPOListItem -ListTitle MultipleItems -ItemID (($itemki | where {$_.Title -eq ($impu.title+$impu.number)}).ID) -FieldToUpdate $impu.column -ValueToUpdate $impu.value }







Remove Items



Single item


Remove-SPOListItem -ListTitle MultipleItems -ItemID 906







Remove Selected


Let's say that we want to remove items based on some sort of criteria. In the example below these will be the items with "UpdatedText" information in the MyTextColumn. The .Count cmdlet is not obligatory and serves to verify if the results retrieved under $ItemsForDeletion are what we expected and how long it may take to remove them all.
$ItemsForDeletion =(Get-SPOListItems -ListTitle MultipleItems -IncludeAllProperties $true | where {$_.MyTextColumn -eq "UpdatedText"})
$ItemsForDeletion.Count
$ItemsForDeletion | ForEach-Object {Remove-SPOListItem -ListTitle MultipleItems -ItemID $_.ID}



Remove Duplicates


The same cmdlets can serve to remove duplicate entries. In this situation, if a given item has the same title as another one, it will be deleted. The cmdlet can be modified and compare not titles, but other fields, numeric values or multiple columns.

$forduplicates=(Get-SPOListItems -ListTitle MultipleItems)
for($i=0;$i -lt $forduplicates.Count; $i++){ for ($j=$i; $j -lt $forduplicates.Count; $j++) { if(($forduplicates[$i].ID -ne $forduplicates[$j].ID) -and ($forduplicates[$i].Title -eq $forduplicates[$j].Title)) { $forduplicates[$i].ID; $forduplicates[$j].ID; "---"; Remove-SPOListItem -ListTitle MultipleItems -ItemID $forduplicates[$j].ID }}}






All


After the last two examples of removing items we are left with 11 items in the list. Let us remove them all using PowerShell.


Get-SPOListItems -ListTitle MultipleItems | ForEach-Object {Remove-SPOListItem -ListTitle MultipleItems -ItemID $_.ID}










Summary



   
for($i=0;$i -lt 2000;$i++){ New-SPOListItem -ListTitle MultipleItems -ItemTitle ("title"+$i)}   create multiple items called Title0,1,2
 for($i=0;$i -lt 2000$i++){New-SPOListItem -ListTitle multipleitems -ItemTitle Itemek -AdditionalField MyChoiceColumn -AdditionalValue Choice1}  create multiple items called Itemek and set the value of ChoiceColumn to Choice1
 import-csv c:\users\ivo\Desktop\map1.csv | ForEach-Object {New-SPOListItem -ListTitle MultipleItems -ItemTitle ($_.title+$_.number) -AdditionalField $_.column -AdditionalValue $_.value}  bulk-create items based on CSV
 
$itemIDs=(Get-SPOListItems -ListTitle MultipleItems).ID
foreach($idd in $itemIDs){Update-SPOListItem -ListTitle MultipleItems -ItemID $idd -FieldToUpdate MyField -ValueToUpdate $true}
 bulk-update multiple items 
 
$itemki=Get-SPOListItems -ListTitle MultipleItems
$ImportedUpdates=import-csv c:\users\ivo\Desktop\map1.csv
foreach($impu in $ImportedUpdates) { Update-SPOListItem -ListTitle MultipleItems -ItemID (($itemki | where {$_.Title -eq ($impu.title+$impu.number)}).ID) -FieldToUpdate $impu.column -ValueToUpdate $impu.value }
 bulk-update items based on CSV
 
$ItemsForDeletion=(Get-SPOListItems -ListTitle MultipleItems -IncludeAllProperties $true | where {$_.MyTextColumn -eq "UpdatedText"})
itemsForDeletion.Count
$itemsForDeletion | ForEach-Object {Remove-SPOListItem -ListTitle MultipleItems -ItemID $_.ID}
bulk-delete selected items
 
$forduplicates=(Get-SPOListItems -ListTitle MultipleItems)
for($i=0;$i -lt $forduplicates.Count$i++){ for ($j=$i$j -lt $forduplicates.Count$j++) { if(($forduplicates[$i].ID -ne$forduplicates[$j].ID) -and ($forduplicates[$i].Title -eq $forduplicates[$j].Title)) { $forduplicates[$i].ID; $forduplicates[$j].ID; "---"; Remove-SPOListItem -ListTitle MultipleItems -ItemID $forduplicates[$j].ID }}}
 delete duplicate items
 Get-SPOListItems -ListTitle MultipleItems | ForEach-Object {Remove-SPOListItem -ListTitle MultipleItems -ItemID $_.ID}  delete all items




Downloads




Related Articles

Manage SharePoint Online Access Requests using Powershell
SPOMod Cmdlets and Resources



Other Languages

The article is available in other languages:
Zarządzanie wieloma elementami listy SharePoint przy użyciu Powershell'a (pl-PL)



Other Languages



Other Languages

web
stats