Map Term Set to Site Column on SharePoint using CSOM PowerShell

We will see how to map SharePoint taxonomy term set to a Site column using CSOM with PoweShell on SharePoint 2013 / SharePoint 2016 / SharePoint Online sites.

The basic idea of the article is to show how we can automate the mapping process of term set to site columns.

Steps Involved:

The following prerequisites need to be executed before going for any operations using CSOM PowerShell on SharePoint sites. 

1. Add the references using the Add-Type command with necessary reference paths. The necessary references are Microsoft.SharePoint.Client.dll, Microsoft.SharePoint.Client.Runtime.dll and Taxonomy dll.
   
   1. Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"  
   2. Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
   3. Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"  
2. Initialize client context object with the site URL.
   
   1. $siteURL = ""  
   2. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
3. If you are trying to access SharePoint Online site then you need to setup the site credentials with credentials parameter and load it to the client context.
   
   1. #Not required for on premise site - Start    
   2. $userId = "abc@abc.onmicrosoft.com"    
   3. $pwd = Read-Host -Prompt "Enter password" -AsSecureString    
   4. $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userId, $pwd)    
   5. $ctx.credentials = $creds    
   6. #Not required for on premise site - End    
4. If you are trying to access the SharePoint on premise site then the credentials parameter is not required to be set to the context but you need to run the code on the respective SharePoint Server or you need to pass the network credentials and set the context.
   
   1. #Credentials for on premise site - Start    
   2. $pwd = Read-Host -Prompt "Enter password" -AsSecureString    
   3. $creds = New-Object System.Net.NetworkCredential("domain\userid", $pwd)    
   4. $ctx.Credentials = $creds    
   5. #Credentials for on premise site - End    

Before executing below steps, the site column’s Term Setting will look like the image below.

Now we will see how we can retrieve term sets from the term store and assign it to the site column.

Retrieve Term Sets:

The below logic retrieves term set called "Test" from the taxonomy term store. 

1. Get taxonomy session object by loading and executing the context.
   
   1. $taxStore = [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($ctx)  
   2. $ctx.Load($taxStore)  
   3. $ctx.ExecuteQuery()  
2. Get the term store by using term store name and term store collection object.
   
   1. # Retrieve Term Stores & Groups  
   2. $termStores = $taxStore.TermStores.GetByName("Managed Metadata Service")  
   3. $ctx.Load($termStores)  
   4. $ctx.ExecuteQuery()  
   5. $termStore = $termStores[0]  
   6. $Groups = $termStore.Groups  
   7. $ctx.Load($termStore)  
   8. $ctx.Load($Groups)  
   9. $ctx.ExecuteQuery()  
3. Get the appropriate group where term sets are available by using group name and group collection object. From the selected group, select the term set from the term set collection and term set name.
4. 1. # Retrieve Term Set & Update Site Column with TermSet  
   2. $Group = $Groups.GetByName("Testing")  
   3. $termSet = $Group.TermSets.GetByName("Test")  
   4. $ctx.Load($termSet)  
   5. $ctx.ExecuteQuery()  

We got the term set. Now we will see how we can map it to site column called "Test".

Mapping Term Set to Site Column:

1. From the context, get the root web object. Then get the fields. From the field collection, get the respective field with the field name.
   
   1. $web = $ctx.Site.RootWeb  
   2. $field = $web.Fields.GetByTitle("Test")  
2. We need to cast the field object to taxonomy field by invoking generic methods. For this we will use MakeGenericMethod along with GetMethod. Here we will use Taxonomy Field object for casting field object.
   
   1. $field = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($ctx, $field)  
3. Then we can assign the term set and store id of the term set identified from the above section to the field by following code snippet.
   
   1. $field.SspId = $termStore.Id   
   2. $field.TermSetId = $termSet.Id   
4. Then update field, load objects and execute the query to complete processing.
   
   1. $field.Update()  
   2. $ctx.Load($web)  
   3. $ctx.Load($field)  
   4. $ctx.ExecuteQuery()  

Here we have mapped the term set to corresponding site column. The below image shows the term set mapping to site column.

The above set of operations will help in mapping the term set of taxonomy to managed metadata site column. But this will not affect the columns already created at list level using the above site column. For updating the list level metadata columns with the same changes, we need to explicitly use an update operation, where it will push the changes from site level to list levels. Instead of giving $field.update() in the above code, we need to give $field.UpdateAndPushChanges($true). This will make changes available for list columns already created.

Retrieve Video Portal, Channels And Videos From Office 365 Using REST Search And List API

Retrieve Video Portal Detail:

First, we will see how we can retrieve the video portal information from Office 365 using SharePoint 2013 REST Search API.

The video portal is a part of the Point Publishing hub site. The site template used to create the portal is POINTPUBLISHINGHUB. The REST search API can be used along with the Web template ID to get the relevant information. Web template property should be used in query parameter to get the channels using search. Template ID should be passed as the Web template value.

The search API will be,

• https://siteurl/_api/search/query?QueryText='WebTemplate:POINTPUBLISHINGHUB'

The following code snippet shows the JQuery Ajax call to get the video portal information from Office 365 site, using search API:

1. $.ajax
2. ({  
3. "http://siteurl/_api/search/query?QueryText='WebTemplate:POINTPUBLISHINGHUB'",  
4.     type: "GET",  
5.     async: false,  
6.     headers: { "accept": "application/json;odata=verbose" },  
7.     success: function(data){  
8.         var portalInfo = data.d.query.PrimaryQueryResult.RelevantResults;  
9.           
10.             var portalResults = portalInfo.Table.Rows.results;  
11.         for(var i = 0; i< portalResults.length; i++){  
12.             console.log("Title : "+portalResults[i].Cells.results[3].Value);  
13.             console.log("Created By : "+portalResults[i].Cells.results[4].Value);  
14.             console.log("portal URL : "+portalResults[i].Cells.results[6].Value);  
15.         }  
16.         //Similarly other results can be retrieved and displayed  
17.     },  
18.     error: function(data){  
19.     }  
20. });  

Retrieve Channels:

The channels are separate site collections present on the portal. The site template used to create a channel (site collection) on the video portal is POINTPUBLISHINGTOPIC. Hence, we can use the template ID to query the portal to get the channels available. 

The search API will be,

• https://siteurl/_api/search/query?QueryText='WebTemplate:POINTPUBLISHINGTOPIC'

Some of the parameters that can be retrieved using the above URL are Channel Name, Created by, Channel URL, etc.

The following code snippet shows the JQuery Ajax call to get the video channels:

1. $.ajax
2. ({  
3.     url: "http://siteurl/_api/search/query?QueryText='WebTemplate:POINTPUBLISHINGTOPIC'",  
4.     type: "GET",  
5.     async: false,  
6.     headers: { "accept": "application/json;odata=verbose" },  
7.     success: function(data){  
8.         var channelInfo = data.d.query.PrimaryQueryResult.RelevantResults;  
9.           
10.         console.log("Available Channels Count : " + channelInfo.RowCount);  
11.         var channelResults = channelInfo.Table.Rows.results;  
12.         for(var i = 0; i< channelResults.length; i++){  
13.             console.log("Title : "+channelResults[i].Cells.results[3].Value);  
14.             console.log("Created By : "+channelResults[i].Cells.results[4].Value);  
15.             console.log("Channel URL : "+channelResults[i].Cells.results[6].Value);  
16.         }  
17.         //Similarly other results can be retrieved and displayed  
18.     },  
19.     error: function(data){  
20.     }  
21. });  

Retrieve Videos:

Next, we will see how we can retrieve the videos from the portal in different ways.

1. Using List REST API: From the channels identified, we will access the videos from the respective channels. REST API is used to query SharePoint list items that will be used. To access a video from one single channel, find the following REST List API URL, given below:

• https://channelurl/_api/web/lists/GetByTitle('Videos')/Items

Channel URL is the channel path name, found from the previous query.

To select necessary properties for the videos, use select parameter and specify the properties. Some of the properties displayed in my example are Title, Author Name, and Video URL. Here, to get the author name, an expanded query needs to be used.

The following code snippet shows the Jquery Ajax call to get the videos present on the specified channel, using List API:

1. $.ajax
2. ({  
3.     url: "http://videoportal_url/_api/web/lists/GetByTitle('Videos')/Items?$select=Title,FileRef,Author/Title&$expand=Author",  
4.     type: "GET",  
5.     async: false,  
6.     headers: { "accept": "application/json;odata=verbose" },  
7.     success: function(data){  
8.         if(data.d != null && data.d.results.length > 0){  
9.             var videoResults = data.d.results;  
10.               
11.             console.log("Available Videos Count : " + videoResults.length);  
12.         for(var i = 0; i< videoResults.length; i++){  
13.             console.log("Title : "+videoResults[i].Title);  
14.             console.log("Created By : "+videoResults[i].Author.Title);  
15.             console.log("Video URL : "+videoResults[i].FileRef);  
16.         }  
17.     }  
18.     //Similarly other results can be retrieved and displayed  
19.     },  
20.     error: function(data){  
21.     }  
22. });   

2. Using Search REST API Query: In the section above, we have seen how we can retrieve the videos from the channels using List REST API. Now, we will see how we can retrieve the videos (same set of information) from the channel using search REST API.

The content type used for video is cloud video. Navigate to SharePoint Video library from channel site and find out the content, type of list, cloud video, and content type. The search API will be,

• https://siteurl/_api/search/query?QueryText=%27contenttypeid:list_cloudvideo_contenttypeid%27 

The following code snippet shows the Jquery Ajax call to get the videos, present on the specified channel, using the list video content type.

1. $.ajax
2. ({  
3.     url: "http://siteurl/_api/search/query?QueryText='list_cloudvideo_contenttypeid'",  
4.     type: "GET",  
5.     async: false,  
6.     headers: { "accept": "application/json;odata=verbose" },  
7.     success: function(data){  
8.         var videosInfo = data.d.query.PrimaryQueryResult.RelevantResults;  
9.           
10.         console.log("Available Videos Count on Channel : " + videosInfo.RowCount);  
11.     var videoResults = videosInfo.Table.Rows.results;  
12.     for(var i = 0; i< videoResults.length; i++){  
13.         console.log("Title : "+videoResults[i].Cells.results[3].Value);  
14.         console.log("Created By : "+videoResults[i].Cells.results[4].Value);  
15.         console.log("Video URL : "+videoResults[i].Cells.results[6].Value);  
16.     }  
17.     //Similarly other results can be retrieved and displayed  
18.     },  
19.     error: function(data){  
20.     }  
21. });