Introduction

Sometimes we need to create multiple list items at a time in SharePoint list due to a requirement - it could be while architecting the SharePoint application or doing SharePoint application testing. So when it comes to adding thousands of records in a list - it is not possible to add those records manually. Hence, the below script will be very helpful while handling this kind of requirement.
  1. CLS
  2. #Load SharePoint CSOM Assemblies
  3. #Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
  4. #Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6. $fileName = "Adding_Multiple_Items_Report"
  7. $enddate = (Get-Date).tostring("yyyyMMddhhmmss")
  8. $logFileName = $fileName +"_"+ $enddate+"_Log.txt"
  9. $invocation = (Get-Variable MyInvocation).Value
  10. $directoryPath = Split-Path $invocation.MyCommand.Path
  12. $directoryPathForLog=$directoryPath+"\"+"LogFiles"
  13. if(!(Test-Path -path $directoryPathForLog))
  14. {
  15. New-Item -ItemType directory -Path $directoryPathForLog
  17. }
  19. $logPath = $directoryPathForLog + "\" + $logFileName
  20. $isLogFileCreated = $False
  22. #DLL location
  23. $directoryPathForDLL=$directoryPath+"\"+"Dependency Files"
  24. if(!(Test-Path -path $directoryPathForDLL))
  25. {
  26. New-Item -ItemType directory -Path $directoryPathForDLL
  28. }
  30. #DLL location
  31. $clientDLL=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"
  32. $clientDLLRuntime=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"
  34. Add-Type -Path $clientDLL
  35. Add-Type -Path $clientDLLRuntime
  37. function Write-Log([string]$logMsg)
  38. {
  39. if(!$isLogFileCreated){
  40. Write-Host "Creating Log File..."
  41. if(!(Test-Path -path $directoryPath))
  42. {
  43. Write-Host "Please Provide Proper Log Path" -ForegroundColor Red
  44. }
  45. else
  46. {
  47. $script:isLogFileCreated = $True
  48. Write-Host "Log File ($logFileName) Created..."
  49. [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)
  50. Add-Content -Path $logPath -Value $logMessage
  51. }
  52. }
  53. else
  54. {
  55. [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)
  56. Add-Content -Path $logPath -Value $logMessage
  57. }
  58. }
  60. #variables region.
  61. $siteURL="https://globalsharepoint.sharepoint.com/sites/TestSite/" #Your Site URL
  62. $spUserName="YourUserName@<yourtenantname>.com"
  63. $password = "YourPassword"
  64. $spListName="Test List"
  65. $numberOfItemsToCreate="5001"
  66. #variables region end.
  67. $securePassword= $Password | ConvertTo-SecureString -AsPlainText -Force
  69. #Setup the Context
  70. try
  71. {
  72. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
  73. $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spUserName, $securePassword)
  75. #Get the list
  76. $spList = $ctx.Web.Lists.GetByTitle($spListName)
  77. $ctx.Load($spList)
  78. $ctx.ExecuteQuery()
  80. # Loop to create list items
  81. for($i=1; $i -le $numberOfItemsToCreate; $i++)
  82. {
  83. $listItemCreationInformationInSPOnline = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
  84. $newListItemInSPOnline = $spList.AddItem($listItemCreationInformationInSPOnline)
  85. $newListItemInSPOnline["Title"] = "CustomItemNumberAddedThruCode_$($i)"
  86. $newListItemInSPOnline["CustomItemNumber"] = $i;
  87. $newListItemInSPOnline.Update()
  88. $ctx.ExecuteQuery()
  90. write-host "Item created: CustomItemNumberAddedThruCode_$($i)"
  91. }
  93. }
  94. catch
  95. {
  97. $errorMessage = $_.Exception.Message +"in adding mulitple items in SP Online list using CSOM PowerShell script";
  98. Write-Host $errorMessage -BackgroundColor Red
  99. Write-Log $errorMessage
  100. }
  101. Write-Host "##########################################################" -ForegroundColor Green
  102. Write-Host "The script execution has been completed!" -ForegroundColor Green
  103. Write-Host "##########################################################"

How to execute the above script?

Place the below two DLLs in your script directory "Dependency Files" folder as like below:
Change the value of the variable in the variables section like below:
  1. #variables region.
  2. $siteURL="https://globalsharepoint.sharepoint.com/sites/TestSite/" #Your Site URL
  3. $spUserName="YourUserName@<yourtenantname>.com"
  4. $password = "YourPassword"
  5. $spListName="Your List Name"
  6. $numberOfItemsToCreate="Number of times you want to create(Number like - 1000,2000,3000 etc)"
  7. #variables region end.
Execute the above script like below:
After execution - output:
We can see that n($numberOfItemsToCreate) number items are added to the SharePoint list automatically.

Summary

Hence, we have learned here how to create multiples items in SharePoint online in a batch using CSOM PowerShell.