Testing your application with large numbers of data is important but sadly often forgotten. SharePoint is no exception to this rule.
Not having tested your application for large volume of data almost always has big consequences. Problems more often than not arise only after a certain period of time. Your application can run flawless for months or even years. Suddenly a limit is reached and your applications starts to fail.
If you have worked with large lists in SharePoint, you have probably received errors due to the threshold limit. SharePoint is configured with a default threshold limit of 5000 items in a list or library. This means that if the items in a list or library exceed that number, any views, grouping and queries will will no longer work.
Fill it up! Yes, although another valid approach to simulate this SharePoint limit would be to lower the listview-treshhold to a low number like 50 it will not always reveal all the problems having large lists.
So we need to fill the list manually. But wait… filling SharePoint lists with more than 5000 items is laborious and repetitive work, even when using Excel. PowerShell is a perfect tool for this. Like most of my SharePoint PowerShell scripts, I like to use the CSOM (Client-side object model) Using CSOM allows me to use this script without being on the SharePoint server itself.
The PowerShell listed below creates a list and generates the specified number of list items. The number of list items to be created can be configured and is really fast. Generating 5000 list items generally only takes a number of seconds.
Output of the PowerShell should look like this.
In order to get this to work, you will first need to copy the ‘Microsoft.SharePoint.Client.dll’ and ‘Microsoft.SharePoint.Client.Runtime.dll’ to the same folder of this script. The script listed here should be seen as a starting point for your own specific SharePoint list. If no $listName list is found it will create a list for you to fiddle around with. In your situation you will probably already have a SharePoint list with some content-types or list fields added to it. In this example I only use a field called “QuestionType”. Of course you can- and probably will add fields to this to suit your situation.
1# Copyright (C) www.jurjan.info - All Rights Reserved (MIT License)
2
3# change the following three lines to your likings
4$url = "http://yourdevelopmentsitecollection/"
5$listName = "sometestlist"
6$itemsToCreate = 5010
7
8$ScriptDir = Split-Path $script:MyInvocation.MyCommand.Path
9Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
10Add-Type -Path "$ScriptDir\Microsoft.SharePoint.Client.dll"
11Add-Type -Path "$ScriptDir\Microsoft.SharePoint.Client.Runtime.dll"
12
13# open the site
14$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($url);
15$web = $clientContext.Web;
16$clientContext.Load($web)
17$clientContext.ExecuteQuery()
18Write-Host $web.Title $web.ServerRelativeUrl -ForegroundColor Red -BackgroundColor Yellow
19
20# open the list, if no list is found it will create one
21$list = $web.Lists.GetByTitle($listName);
22$clientContext.Load($list)
23try {
24 $clientContext.ExecuteQuery()
25 Write-Host "Opened list" $list.Title -ForegroundColor Red -BackgroundColor Yellow
26} catch {
27 Write-Host "No list found, creating a new one"
28 $listCreateInfo = New-Object Microsoft.SharePoint.Client.ListCreationInformation;
29 $listCreateInfo.Title = $listName;
30 $listCreateInfo.TemplateType = [Microsoft.SharePoint.Client.ListTemplateType]::GenericList
31 $list = $web.Lists.Add($listCreateInfo)
32 $list.Update();
33 $clientContext.ExecuteQuery();
34
35 # add some listfields for example purposes
36 $a = $list.Fields.AddFieldAsXml("<Field Type='Choice' DisplayName='QuestionType'>
37 <CHOICES>
38 <CHOICE>Office 365</CHOICE>
39 <CHOICE>General</CHOICE>
40 <CHOICE>Email</CHOICE>
41 <CHOICE>OneDrive</CHOICE>
42 <CHOICE>SharePoint</CHOICE>
43 <CHOICE>Office Apps</CHOICE>
44 <CHOICE>Office Online</CHOICE>
45 <CHOICE>Other</CHOICE>
46 </CHOICES></Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
47}
48
49# random words used to create listitems
50$words = "Lorem","Ipsum","Donald","Duck","Wine","Chees","Fruit","Garlic","Weather","Together","Car","Pizza"
51$choices = "Office 365", "General", "Email", "SharePoint"
52
53for($index = 1; $index -lt $itemsToCreate+1; $index++) {
54 $randomText = $words | Get-Random -count 1
55 $titleText = "$index $randomText"
56 Write-Host "[$index/$itemsToCreate] Creating '$titleText'" -ForegroundColor Green -BackgroundColor Red
57
58 $newListItem = $list.AddItem($itemCreateInfo)
59 $newListItem["Title"] = $titleText
60 $newListItem["QuestionType"] = $choices | Get-Random -count 1
61 $newListItem.Update()
62 if($index % 100 -eq 0)
63 {
64 Write-Host $index
65 $clientContext.ExecuteQuery()
66 }
67}
68$clientContext.ExecuteQuery()
69Write-Host "Done!" -ForegroundColor Green
The second example makes use of the PnP PowerShell library and can be used for document-libraries
1# Copyright (C) www.jurjan.info - All Rights Reserved (MIT License)
2# This powerhell script will fill a SharePoint document library with random documents and random properties
3# You need to install https://github.com/pnp/PnP-PowerShell first to make it work.
4
5# Change these variables to your own needs
6$itemsToCreate = 10
7$credentials = "Name of your credential in the windows credentialstore" # credentials are stored in the windows credentialstore
8$words = "Airplay","Cyanide","Cyborgs","Cycloon","Dynamos","Gameboy","Hyacint","Hydraat","Hydrant","Moyenne","Penalty","Symbool","Typeren","Yoghurt","Cylinder","Cytosine","Monotype","Mysterie","Royement","Typenaam","Typering","Baxters","Complex","Excerpt","Exogeen","Exogene","Experts","Laxeren","Lexicon","Mailbox","Mentrix","Mixture","Sextant","Textiel","Xylitol","Xanthoom","Xenofiel","Xenofobe","Xenofoob","Xenomani","Cabaret","Centers","Cesuren","Chianti","Chinees","Clement","Collage","Collega","Curator","Decente","Lactose","Lichter","Locatie","Narcose","Scanner","Sociale","Calvados","Cassette","Ceintuur","Checkbox","Cynische","Delicaat","Scenario","Antiqua","Aquarel","Aquaria","Aquavit","Attaque","Cliques","Croquet","Enquete","Equator","Exquise","Jacquet","Kumquat","Liquide","Quanten","Queeste","Queueen","Quotums","Requiem","Tequila","Adequaat","Aquaduct","Aquanaut","Aquarium","Aquastop","Aquavion","Attaques","Calqueer","Choquant","Claqueur","Craquele","Frequent","Quartole","Quotejes","Quoteert","Quoteren"
9$user = "set your user user@domain.nl"
10$url = "url of the web"
11$listName = "name of the list"
12$contentTypeName = "name of the contenttype"
13$debug = $false
14
15function GetAllTermsRecursive($terms)
16{
17 $termIdList = @()
18 ForEach($item in $terms)
19 {
20 if($item.TermsCount -gt 0) {
21 GetAllTermsRecursive $item.Terms
22 } else {
23 $termIdList += $item.Id
24 if($debug) { Write-Host "$($item.Id)-$($item.Name)" }
25 }
26 }
27 return $termIdList
28}
29
30Connect-PnPOnline $url -Credentials $credentials -UseAdfs -NoTelemetry -ErrorAction Stop
31
32$list = Get-PnPList -Identity $listName
33if($null -ne $list) {
34 Write-Host "Opened list" $list.Title -ForegroundColor Red -BackgroundColor Yellow
35} else {
36 Write-Host "No list found"
37 break
38}
39
40$contenttype = Get-PnPContentType -List $listName | Where-Object {$_.Name -eq $contentTypeName}
41if($null -eq $contenttype) {
42 Write-Host "No $contenttype ContentType available on the list" -ForegroundColor Red
43 Get-PnPContentType -List $listName | ForEach-Object { Write-Host "Available contenttype: " $_.Name -ForegroundColor Green }
44 break;
45}
46
47Get-PnPproperty -clientobject $contenttype -property "Fields" | Out-Null
48
49Write-Host "Using ContentType: $($contenttype.Name)" -ForegroundColor Green
50if($debug) {
51 $contenttype.Fields | ForEach-Object {
52 Write-Host $_.Title -ForegroundColor Green -NoNewline
53 Write-Host " " $_.InternalName -ForegroundColor Yellow -NoNewline
54 Write-Host " " $_.TypeAsString -ForegroundColor Red
55 }
56 break
57}
58
59for($index = 1; $index -lt $itemsToCreate+1; $index++) {
60 $randomText = $words | Get-Random -count 1
61 $randomNr = Get-Random -Minimum 10000 -Maximum 99999
62 $titleText = "$randomNr-$randomText"
63 $fileName = "$($titleText).$($fileExtension)"
64
65 $spprops = @{}
66 $taxFields = @{}
67
68 $contenttype.Fields | Where-Object { -Not $_.InternalName.StartsWith("_") } | ForEach-Object {
69 if($_.InternalName -eq "Title") {
70
71 $spprops[$_.InternalName] = $titleText
72 }
73 if($_.InternalName -ne "Title" -and $_.TypeAsString -eq "Text" -or ($_.TypeAsString -eq "Note" -and $_.Hidden -eq $false)) {
74 # hidden - false -> make sure we do not set hidden taxfields
75 $spprops[$_.InternalName] = $randomText
76 }
77 if($_.TypeAsString -eq "User") {
78 $spprops[$_.InternalName] = $user
79 }
80 if($_.TypeAsString -eq "Choice") {
81 $spprops[$_.InternalName] = $_.Choices | Get-Random -Count 1
82 }
83 if($_.TypeAsString -eq "DateTime") {
84 $days = Get-Random -Minimum -100 -Maximum 100
85 $spprops[$_.InternalName] = [System.DateTime]::Now.AddDays($days)
86 }
87 if($_.TypeAsString -eq "TaxonomyFieldType") {
88 $terms = Get-PnPTerm -TermSet $_.TermSetId -TermGroup $_.Group -IncludeChildTerms -Recursive
89 $results = GetAllTermsRecursive $terms
90 $randomTerm = $results | Get-Random -Count 1
91 $taxFields[$_.InternalName] = $randomTerm
92 }
93 if($_.TypeAsString -eq "TaxonomyFieldTypeMulti" -and $_.InternalName -ne "TaxKeyword") {
94 $terms = Get-PnPTerm -TermSet $_.TermSetId -TermGroup $_.Group -IncludeChildTerms -Recursive -ErrorAction Continue
95 $results = GetAllTermsRecursive $terms
96 $randomTerm = $results | Get-Random -Count 1
97 if($randomTerm -ne $null) {
98 $taxFields[$_.InternalName] = $randomTerm
99 }
100 }
101
102 if($($spprops[$_.InternalName]) -ne $null) {
103 Write-Host "$($_.InternalName):" -NoNewline -ForegroundColor Red
104 Write-Host "$($spprops[$_.InternalName])" -ForegroundColor Yellow
105 }
106 }
107 $file = Add-PnPFile -Path $filePath -Folder $list.EntityTypeName -NewFileName $fileName -Values $spprops
108 if($null -ne $file) {
109 Write-Host "Added List item: $($file.ListItemAllFields.Id) - $($file.Title)" -ForegroundColor Green
110
111 # I was unable to set the taxonomy fields directly, so I do it here by using Set-PnpTaxonomyFieldValue
112 foreach ($h in $taxFields.GetEnumerator()) {
113 Write-Host "Setting Taxfield: " -ForegroundColor Green -NoNewline
114 Write-Host "$($h.Name) - $($h.Value)" -ForegroundColor Yellow
115 Set-PnPTaxonomyFieldValue -ListItem $file.ListItemAllFields -InternalFieldName $h.Name -TermId $h.Value
116 }
117 }
118}
119
120Write-Host "Done!" -ForegroundColor Green
Make sure you have a dummy text-file in the same directory named ’testfile1.txt’