SharePoint listdata generator

Testing your application with large numbers of data is important but sadly often forgotten. SharePoint is no exception to this rule.

The problem

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.

Time to hunt for this hidden timebomb

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.

Preview

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.

shadow small

Usage

Output of the PowerShell should look like this.

shadow medium

Code Example 1 - Custom lists

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

open raw file

Code Example 2 - Document libraries

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

open raw file

Make sure you have a dummy text-file in the same directory named ’testfile1.txt’