Recently we had a request to find fields/columns in all lists across the tenancy which have a specific Taxonomy term because we needed to report on field usage across all site collections. However, we found that getting a report of all Taxonomy fields in your SharePoint tenancy that is linked to a specific Term Set can get quite daunting because there is no direct SharePoint Query to fetch the associations.
The technical challenge is that using PnP PowerShell, the Taxonomy fields are returned as a generic SP.Field type and not of type SP.TaxonomyField. Hence the Taxonomy field metadata values such as Group ID and Termset ID are absent.
To resolve the above limitation, we used the Field $field.SchemaXml to find the specified values.
Note: Querying the Term store while searching for a specific termset by using Get-PnPTerm can add a lot of latency time. Hence to decrease the additional time we could download the entire term store to an Excel file and use that Excel file as the master data for matching. Below is the command to get an export of all Taxonomy values as a CSV file.
Export-PnPTaxonomy -Path "[path]\taxonomyreport.csv" -Delimiter "," -IncludeID
The steps to retrieve and check for taxonomy fields can be found below.
- Get all lists in a web site
- Get the Taxonomy fields for a List
- Read the schema.xml and search for a TermsetID and AnchorID (Thanks to @Colin Philips (http://itgroove.net/mmman/) for finding the correct xpath parsing for xml)
- Match the data with the above Taxonomy report for Group ID, Termset ID, and Anchor ID with Term ID that is column is linked to
- In case of a match, save the values into the report.
The below code uses PnP PowerShell. For a quick set up of PnP PowerShell, please refer to this blog.
Hence with the above approach, we can retrieve the taxonomy fields for all site collections. Be aware that the above process can take about half a day or more to run depending on the number of site collections and taxonomy fields in your tenancy. Be sure to give it enough time to run before prematurely cancelling it.
Happy Coding!! 😊