Working with SharePoint metadata columns in PowerAutomate

In some SharePoint designs the metadata managed columns are used. Managed columns need a different approach when it comes to integration to upload or update your items or documents. In this post I am using Power Automate but the same principles can be applied for when using the standard Sharepoint API’s.

Here is blog post of Enterprise Knowledge which explains how to use and create metadata managed column in Sharepoint.

When working with managed columns in an integration, the API call will not accept the Term value but instead it requires the Term Id to be passed. For example, the Term list is a list of projects and in our example the integration must update the properties of a file from Project ‘NA’ to Project: ‘X010’. The value ‘X010’ cannot be passed but instead the integration needs to retrieve the Term Id associated with project ‘X010’. The retrieved ID ‘a3f71668-d910-4d5e-8390-20d7287d0b6e’ can only be used as column value when updating the file properties.

In our example the Columns and Term group are both called Project. First, we need to retrieve the TermSetId and the SspId of the Term group. These are the identifiers for the taxonomy group in which our project values and Ids are stored.

_api/web/lists/getByTitle('Documents')/fields/getbytitle('Project')?$select=SspId,TermSetId

Now that we have the group identifiers, the ID of the project value can be retrieved.

Start requires the name / value of the taxonomy. In our example 'X010'.

SSPList and TermSetList are retrieved from the previous step.

_vti_bin/TaxonomyInternalService.json/GetSuggestions

{"start":"@{outputs('Compose_-_remove_spaces')}",

"lcid":1033,

"sspList":"@{body('Parse_JSON_-_term_store_info_Project')?['d']?['SspId']}",

"termSetList":"@{body('Parse_JSON_-_term_store_info_Project')?['d']?['TermSetId']}",

"anchorId":"00000000-0000-0000-0000-000000000000",

"isSpanTermStores":false,

"isSpanTermSets":false,

"isIncludeUnavailable":false,

"isIncludeDeprecated":false,

"isAddTerms":false,

"isIncludePathData":false,

"excludeKeyword":false,

"excludedTermset":"00000000-0000-0000-0000-000000000000"}

The returned ID can be used to update the file properties to the correct project.

I used the following compose function to get the ID from the parsed JSON.

first(first(body('Parse_JSON_-_term_store_suggestions_Project')?['d']?['Groups'])?['Suggestions'])?['Id']