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:
- synchronize a SharePoint list with the upstream store and then,
- synchornize that list with the term store.
Why break into two steps? A couple of reasons:
- The APIs for SharePoint lists are more modern and fully available in a REST form in the Graph API
- A list stores history, so one has an audit trail of what changed
- 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.
A couple of things to note:
- 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.
- 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).
- 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.
- 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.