Programatically managing the SharePoint Term Store

Posted

Public domain Spindle Diagram from Wikipedia.

The SharePoint Term Store is a lesser appreciated capability of Microsoft SharePoint: a seven deep taxonomy with ability to deprecate terms, add custom fields and more.

The store is typically used to provide managed metadata for SharePoint lists and document libraries: providing the user with a drop down of valid values rather than free text.

Frequently, the values your want to show are mastered in a reference data system external to SharePoint. The question then becomes: what’s the easiest way to synchronize the term store with the upstream reference data store? The answer to this question is complicated by the varying state of APIs and documentation out there. Here’s how I recently solved it:

Broadly, the solution breaks down into two parts:

Why break into two steps? A couple of reasons:

  1. The APIs for SharePoint lists are more modern and fully available in a REST form in the Graph API
  2. A list stores history, so one has an audit trail of what changed
  3. Most end-users are familiar with lists

To synchronize the list to the term store, we used the PnP.Powershell library which wraps the .NET Taxonomy API.

Here’s an example script that syncs based on matching via an external ID.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

# Sync is based on matching on an external ID that is stored as a column 
# in the SharePoint list and custom property in the Term Store
$sIDCol = "ExtID"
$destIDProperty = "ExtID"

# -WarningAction Ignore silences the warning that this is legacy 
# Authentication. We'd love to use something better but, Term Store 
# writes aren't supported by app registration yet.
Connect-PnPOnline -ClientId $clientID -ClientSecret $secret `
	 -Url "https://yoursite.sharepoint.com" -WarningAction Ignore

# get all terms in a termset
$terms = Get-PnPTerm -TermSet $destTermSet -TermGroup "GroupName" `
	-Includes CustomProperties

$termsByID = @{}
# Index by external ID
foreach($term in $terms){
	$termsByID.add($term.CustomProperties[destIDProperty],$term)
}

# get all items in SP list referenced by GUID
$items = get-pnplistitem -list <SourceListGUID> ` 
	-Fields $sIDCol,Title,Modified

# Use a time window to only look for recent modifications. Change this 
# based on how frequently your script runs
$window = (Get-Date).AddHours(-6)

foreach($item in $items){
	# If source item is recently modified
	if($item.FieldValues.Modified -gt $window){
		# if item is already in the term store
		if($termsByID.ContainsKey($item.FieldValues[$sIDCol])){
			$termGUID = $termsByID[$item.FieldValues[$sIDCol]].Id.Guid
			# then update it
			$t = Set-PnPTerm -Identity $termGUID `
				-name $item.FieldValues.Title `
				-TermSet <GUID of term set> -TermGroup "GroupName"
			Write-Verbose -Message "Updated Term $($item.FieldValues.Title)"
		} else {
			# else create it
			$t = New-PnPTerm -TermSet <GUID of term set> `
				-TermGroup "GroupName" -Name $item.FieldValues.Title `
				-CustomProperties @{$destIDAttribute = `
					$item.FieldValues[$sIDCol]}
			Write-Verbose -Message "Created Term $($item.FieldValues.Title)"
		}
	} else {
		Write-Verbose "Not processing $($item.FieldValues.Title) - no change"
	}
}

A couple of things to note:

  1. At the time of writing, the clientID and secret must be generated by SharePoint, not Azure Active Directory as Term Store writes aren’t available from AAD.
  2. You need a ‘magical’ app@SharePoint user. Kudos to this blog for noting that and describing how to create it (note only the legacy UI will work for creating the user).
  3. PnP.PowerShell doesn’t permit term deprecation yet. I have a pull request in for this, hopefully the maintainers will take pity on me and permit it.
  4. Finally, the SharePoint app registration needs to have the following permission bundle
<AppPermissionRequests AllowAppOnlyPolicy="true" >
	<AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="Read" />
	<AppPermissionRequest Scope="http://sharepoint/taxonomy" Right="Write" />
</AppPermissionRequests>

As to where to run this? Azure Automation works great but that’s a post for another day.

Featured image of Spindle Diagram by Petter Bøckman courtesy Wikipedia.