How to update taxonomy hidden field using PowerShell?

Continuation to How to use taxonomy hidden field? – (https://sprider.blog/2018/09/12/how-to-use-taxonomy-hidden-field/) Article

Example PowerShell script to update taxonomy(MMD) hidden field

Add-PSSnapin Microsoft.Sharepoint.Powershell

$sourceWebURL = “http://siteurl”
$sourceListName = “<List Name>”

[Microsoft.SharePoint.SPSecurity]::RunWithElevatedPrivileges({

$spSourceWeb = Get-SPWeb $sourceWebURL
$spSourceList = $spSourceWeb.Lists[$sourceListName]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$caml = ‘<Where><Geq><FieldRef Name=”ID” /><Value Type=”Text”>0</Value></Geq></Where>’
$spQuery.Query = $caml
$spSourceItems = $spSourceList.GetItems($spQuery)

$spSourceWeb.AllowUnsafeUpdates = “true”;

foreach($item in $spSourceItems)
{
$item[taxonomyFieldObject.TextField] = “Term Value1|GUID1”;

# Based on how you have configured the MMD column, supply the input here. To check the hidden value, we can use sharepoint manager complex tool

$item.Update()
}
$spSourceWeb.AllowUnsafeUpdates = “false”;
$spSourceWeb.Dispose();

});

How to read SharePoint Managed Metadata Look-up (Taxonomy Field) value using Client Object Model?

The below Console Application code explains you how to read SharePoint Managed Metadata Look-up (Taxonomy Field) value using Client Object Model.

using System;
using System.Text;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Taxonomy;

namespace CSOMRnD
{
class Program
{
static void Main(string[] args)
{

using (ClientContext context = new ClientContext(“SiteURL”))
{
context.ExecutingWebRequest += new EventHandler(clientContext_ExecutingWebRequest);
context.AuthenticationMode = ClientAuthenticationMode.Default;
context.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

List list = context.Web.Lists.GetByTitle(“ListName”);

CamlQuery query = new CamlQuery();

query.ViewXml = “pass your query”;

ListItemCollection items = list.GetItems(query);
context.Load(items);
context.ExecuteQuery();

foreach (ListItem item in items)
{
StringBuilder sb_ProductFieldValue = new StringBuilder();

TaxonomyFieldValueCollection taxProductFieldValueColl = item[“Product”] as TaxonomyFieldValueCollection;

if (taxProductFieldValueColl != null)
{
foreach (TaxonomyFieldValue taxProductFieldValue in taxProductFieldValueColl)
{
if (taxProductFieldValue.Label.Trim() != null)
sb_ProductFieldValue.Append(taxProductFieldValue.Label + “|”);
else
sb_ProductFieldValue.Append(“Empty”);
}
}
Console.WriteLine(sb_ProductFieldValue.ToString());
}
Console.ReadLine();
}
}

static void clientContext_ExecutingWebRequest(object sender, WebRequestEventArgs e)
{
e.WebRequestExecutor.WebRequest.Headers.Add(“X-FORMS_BASED_AUTH_ACCEPTED”, “f”);
}

}
}

How to use taxonomy hidden field?

Problem:

In one of our SharePoint list we have more than 10 look-up columns with managed metadata type. One fine day our UAT SharePoint site went very low and the CPU utilization in DB Servers associated with the UAT farm started increasing up to 80%. At this point of time the total number of UAT users involved in testing are only 4 to 7. We have around 25k+ terms stored under different terms sets.

Cause:
When we started investigating we found that below key points

1) CAML query calls coming from the custom pages to get managed metadata column values are the most expensive queries on the DB server side.

2) This behavior is happening only when we access those particular custom pages for the first time after an IIS reset.

We all know SharePoint will take more time when we load anything after IIS Reset. But it should not consume more CPU and get into deadlock situation.

Imagine the situation if we move this code to production with 10 to 15 concurrent users? The whole UAT SharePoint farm users and shared resources will be affected because of the CAML Query Issue after any IIS reset.

Solution:

When we approached MS, they suggested “Every taxonomy field of the item should have a hidden field together; e.g. with the Category (taxonomy) field, there should also be a hidden field named something like Category_0 (in my farm), try to use this field in the view fields instead and see if it will improve the results. The reason is that the hidden field is not a lookup field that it shouldn’t need to do any follow up queries to SQL but 1 single query should do the job. Note that the hidden field doesn’t just contain the value that it will also contain the Taxonomy ID, so after getting the value of the hidden field, you will need to parse it to extract the field value. But this should minimize the queries sending to SQL.”

When we modified our CAML queries with the hidden column, we found that the CPU utilization is not increasing and the site performance also very decent.

I am not sure is there any better way to improve this. If you have any ideas / feedback please let me know.

Important Note:
The timer job which updates the taxonomy look-up column will not update this hidden column until there is a change in the item where it is looked-up. We were informed by MS that they are working on this issue.

To validate the hidden column names, I am using SharePoint 2013 Client Browser tool

https://spcb.codeplex.com/releases/view/119035

Sample JavaScript Code

var siteUrl = ‘/’;

var listfld_Product;
var listfld_Release;

var listfld_Product_str;
var listfld_Release_str;

var Inputstr = ‘Product A’;

function retrieveInternalNames()
{
try
{
var clientContext = new SP.ClientContext(siteUrl);
var oList = clientContext.get_web().get_lists().getByTitle(‘CA_Product_Master_List’);
clientContext.load(oList);

listfld_Product = oList.get_fields().getByTitle(“Product_0”);
listfld_Release = oList.get_fields().getByTitle(“Release_0”);

clientContext.load(listfld_Product);
clientContext.load(listfld_Release);

clientContext.executeQueryAsync(function ()
{
listfld_Product_str = listfld_Product.get_internalName();
listfld_Release_str = listfld_Release.get_internalName();
retrieveListItems();

},
function (sender, args)
{
alert(‘Request failed. ‘ + args.get_message() + ‘\n’ + args.get_stackTrace());
});
}
catch (err)
{
alert(err.message);
}
}

function retrieveListItems()
{
try
{
var clientContext = new SP.ClientContext(siteUrl);
var oList = clientContext.get_web().get_lists().getByTitle(‘CA_Product_Master_List’);

var camlQuery = new SP.CamlQuery();

camlQuery.set_viewXml(“<View><ViewFields><FieldRef Name=’” + listfld_Product_str + “‘ Type=’Notes’/><FieldRef Name=’” + listfld_Release_str + “‘ Type=’Notes’/></ViewFields><Query> <Where> <BeginsWith> <FieldRef Name=’” + listfld_Product_str + “‘ /><Value Type=’Notes’>” + Inputstr + “|” + “</Value> </BeginsWith> </Where> </Query></View>”)

this.collListItem = oList.getItems(camlQuery);
clientContext.load(collListItem);
clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
}
catch (err)
{
alert(err.message);
}
}

function onQuerySucceeded(sender, args)
{
var listItemInfo = ”;
var listItemEnumerator = collListItem.getEnumerator();
while (listItemEnumerator.moveNext())
{
var oListItem = listItemEnumerator.get_current();

var releaseArray = oListItem.get_item(listfld_Release.get_internalName()).split(‘;’);
var rleaseInfo = ”;
for (var i = 0; i < releaseArray.length; i++)
{
rleaseInfo += releaseArray[i].split(“|”)[0] + “;”
}

listItemInfo += oListItem.get_item(listfld_Product.get_internalName()).split(“|”)[0] + ‘
‘ + rleaseInfo + ‘

‘;
}
document.getElementById(‘div_Prod_Data’).innerHTML = listItemInfo;
}

function onQueryFailed(sender, args)
{
alert(‘Request failed. ‘ + args.get_message() + ‘\n’ + args.get_stackTrace());
}

_spBodyOnLoadFunctionNames.push(“retrieveInternalNames”);

O365 / SharePoint Online – Load And Install App using CSOM & PowerShell

cls

try
{
Write-Host “Load XML config file” -foregroundcolor black -backgroundcolor yellow

$xdoc = [xml] (get-content “C:\config.xml”)

$url = $xdoc.Tenant.Admin.site
$username = $xdoc.Tenant.Admin.username
$password = $xdoc.Tenant.Admin.password
$appfilepath = $xdoc.Tenant.appfilepath
$Password = $password |ConvertTo-SecureString -AsPlainText -force

Write-Host “Global variables loaded succeefully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Problem in loading the XML or parsing the variables : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

try
{
Write-Host “Load CSOM DLLs” -foregroundcolor black -backgroundcolor yellow Set-Location

$loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client”)
$loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client.Runtime”)

Write-Host “CSOM DLLs loaded succeefully” -foregroundcolor black -backgroundcolor Green

}
catch
{
Write-Host “Problem in loading CSOM DLLs : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

try
{
Write-Host “Authenticate tenant site $url and get ClientContext object” -foregroundcolor black -backgroundcolor yellow

$context = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$context.Credentials = $credentials

$web = $context.Web
$site = $context.Site
$context.Load($web)
$context.Load($site)
$context.ExecuteQuery()

Write-Host “Authentication to online site $url and get ClientContext DLLS succeeful” -foregroundcolor black -backgroundcolor Green

}
catch
{
Write-Host “Unable to authenticate to online site. Error : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

try
{
$appIoStream = New-Object IO.FileStream(“C:\TestApp.app” ,[System.IO.FileMode]::Open)
$appInstance = $web.LoadAndInstallApp($appIoStream) | Out-Null
$context.ExecuteQuery()
Write-Host $appInstance.Id

}
catch
{
Write-Host “Unable to Install App Error : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

O365 Custom JavaScript Injection using PowerShell

Custom action fetches an external script and includes it in the page. So at run time the script will self execute as an anonymous function.

Following are few scenarios you may need this functionality in your SharePoint Projects:

1) Override the new sub site link (https://tenant.sharepoint.com/sites/dev/_layouts/15/newsbweb.aspx) under Site Contents  with your site provisioning app URL

2) Redirect the user to specific page

3) Inject third-party JavaScript libraries such as Jquery, Knockout, AngularJs, etc.,

4) Add dynamic html (global navigation / company copy rights information on the bottom of your page) content on your sharepoint page at run time without even modifying the actual master page.

In the below example, I have explained how to inject Jquery file to one of my web. The source files can be placed in CDN / File Share / Common Location such as App Catalog.

Please feel free to change the logic as per your requirement.

Note: The logic remains same for JSOM code also.

inject_action.png

O365 / SharePoint Online – Import Term Store Terms using CSOM & PowerShell

Looking for a solution to import Term Store Terms using CSOM & PowerShell in O365 / SharePoint Online?

Here you go….

Note: Modify the XML term attributes and PS script logic as per your requirement

XML Config File Structure

config-xml1.png

PowerShell Script

cls

try
{
Write-Host “Load XML config file” -foregroundcolor black -backgroundcolor yellow Set-Location

$xdoc = [xml] (get-content “C:\Joseph\Code\SPRIDER\PS\TermStore\config.xml”)

$Url = $xdoc.Tenant.Admin.site
$Admin = $xdoc.Tenant.Admin.username
$Pwd = $xdoc.Tenant.Admin.password

$SecurePwd = $Pwd |ConvertTo-SecureString -AsPlainText -force

Write-Host “Global variables loaded succeefully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

try
{
Write-Host “Load CSOM DLLs” -foregroundcolor black -backgroundcolor yellow Set-Location

Add-Type -Path “c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll
Add-Type -Path “c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll
Add-Type -Path “c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll

Write-Host “CSOM DLLs loaded succeefully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

function Recursive($inputerm,$inputNode)
{

Foreach ($InnerTermNode in $inputNode.term)
{
$childterms = $inputerm.Terms
$Ctx.Load($childterms)
$Ctx.ExecuteQuery()
$childterm = $childterms | Where-Object {$_.Name -eq $InnerTermNode.name}
if($childterm)
{
Write-Host “Term” $InnerTermNode.name “already exists.” -foregroundcolor black -backgroundcolor yellow
}
else
{
Write-Host “Creating term ” $InnerTermNode.name -foregroundcolor black -backgroundcolor yellow

$childterm = $inputerm.CreateTerm($InnerTermNode.name, 1033, [System.Guid]::NewGuid())
try
{
$Ctx.ExecuteQuery()

Write-Host “Term” $childterm.name “Created successfully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error while creating Term” $InnerTermNode.name $_.Exception.Message -foregroundcolor black -backgroundcolor Red
return
}
}
if($InnerTermNode.ChildNodes.Count -gt 0)
{
Recursive $childterm $InnerTermNode
}
}
}
try
{
Write-Host “Authenticate tenant site $url and get ClientContext object” -foregroundcolor black -backgroundcolor yellow

$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Admin, $SecurePwd)
$Ctx.Credentials = $Credentials
if (!$Ctx.ServerObjectIsNull.Value)
{
Write-Host “Connected to SharePoint Online site: ” $Ctx.Url “” -foregroundcolor black -backgroundcolor Green

$TaxonomySession = [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($Ctx)
$TaxonomySession.UpdateCache()
$Ctx.Load($TaxonomySession)
$Ctx.ExecuteQuery()

Write-Host “Taxonomy session initiated: ” $TaxonomySession.Path.Identity “” -foregroundcolor black -backgroundcolor Green

$termStore = $TaxonomySession.GetDefaultSiteCollectionTermStore()
$Ctx.Load($termStore)
$Ctx.ExecuteQuery()
Foreach ($GroupName in $xdoc.Tenant.TermStore.group)
{
Write-Host “Getting list of groups from term store” -foregroundcolor black -backgroundcolor yellow

$groups = $termStore.Groups
$Ctx.Load($groups)
$Ctx.ExecuteQuery()

try
{
$Ctx.ExecuteQuery()

Write-Host “List of groups from term store loaded successfully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error while getting list of groups from term store” $_.Exception.Message -foregroundcolor black -backgroundcolor Red
return
}

Write-Host “Check whether the group is exist in the term store” -foregroundcolor black -backgroundcolor yellow

$group = $groups | Where-Object {$_.Name -eq $GroupName.name}

if ($group)
{
Write-Host “Group” $GroupName.name “already exists.” -foregroundcolor black -backgroundcolor yellow

$group = $termStore.Groups.GetByName($GroupName.name)
$Ctx.Load($group)
$Ctx.ExecuteQuery()
}
else
{
Write-Host “Creating group” $GroupName.name -foregroundcolor black -backgroundcolor yellow

$group = $termStore.CreateGroup($GroupName.name,[System.Guid]::NewGuid().toString())

try
{
$Ctx.ExecuteQuery()

Write-Host “Group” $GroupName.name “Created successfully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error while creating Group” $GroupName.name $_.Exception.Message -foregroundcolor black -backgroundcolor Red
return
}
}

Foreach ($TermSetNode in $GroupName.termset)
{
$termSets = $group.TermSets
$Ctx.Load($termSets)
$Ctx.ExecuteQuery()

$termSet = $termSets | Where-Object {$_.Name -eq $TermSetNode.name}

if($termSet)
{
Write-Host “Termset” $TermSetNode.name “already exists.” -foregroundcolor black -backgroundcolor yellow

$termSet = $group.TermSets.GetByName($TermSetNode.name)
$Ctx.Load($termSet)
$Ctx.ExecuteQuery()
}
else
{
Write-Host “Creating term set” $TermSetNode.name -foregroundcolor black -backgroundcolor yellow

$termSet = $group.CreateTermSet($TermSetNode.name,[System.Guid]::NewGuid().toString(),1033)

try
{
$Ctx.ExecuteQuery()

Write-Host “Term set ” $TermSetNode.name “Created successfully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error while creating Term set” $TermSetNode.name $_.Exception.Message -foregroundcolor black -backgroundcolor Red
return
}
}

Foreach ($TermNode in $TermSetNode.term)
{
$terms = $termSet.Terms
$Ctx.Load($terms)
$Ctx.ExecuteQuery()
$term = $terms | Where-Object {$_.Name -eq $TermNode.name}

if($term)
{
Write-Host “Term” $TermNode.name “already exists.” -foregroundcolor black -backgroundcolor yellow
}
else
{
Write-Host “Creating term ” $TermNode.name -foregroundcolor black -backgroundcolor yellow

$term = $termSet.CreateTerm($TermNode.name, 1033, [System.Guid]::NewGuid())

try
{
$Ctx.ExecuteQuery()

Write-Host “Term” $TermNode.name “Created successfully” -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host “Error while creating Term” $TermNode.name $_.Exception.Message -foregroundcolor black -backgroundcolor Red
return
}
}

if($TermNode.ChildNodes.Count -gt 0)
{

Recursive $term $TermNode
}
}
}
}
}
}
catch
{
Write-Host “Error : $_.Exception.Message” -foregroundcolor black -backgroundcolor Red
return
}

O365 / SharePoint Online – Apply Theme – CSOM & PowerShell

As you know, Web.ApplyTheme method applies a theme with the specified components to this site.

For more information refer – https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.web.applytheme.aspx

Here fontSchemeUrl and backgroundImageUrl parameters can accept null values. But when I tried to pass $null or null or Empty String to these two parameters via PowerShell, I got the following error:

“Exception calling “ApplyTheme” with “4” argument(s): “The ‘fontSchemeUrl’ argument is invalid.”.Exception.Message”

Then i tried with Out-Null instead of $null or null or Empty String. Trust me it worked like a charm.

Actually the Out-Null cmdlet deletes output instead of sending it down the pipeline.

This is my final code.

Hope this helps.

fixed.png

 

 

O365 – SharePoint Hosted App – Custom Global Navigation using Term Store

Hope you are all aware of the limitations of Managed Navigation in SharePoint. If not go through this article http://nikpatel.net/2014/06/09/limitations-of-managed-navigation-in-sharepoint-2013

As explained in the above article, each term set can be associated with only one site collection at given time. If you have 5 site collections, you must have 5 copies of same term set to use for 5 site collections navigation data.

To overcome this issue I have created a SharePoint Hosted app which can read the term store and dynamically build the navigation. This app can be embedded in to webpart zone or master page based on your requirement.

O365 Patterns and Practice code helped me a lot to build this app – (Path – PnP-master\PnP-master\Samples\Core.TaxonomyPicker\Core.TaxonomyPickerWeb\Scripts\taxonomypickercontrol.js)

Step 1 – Create Term Set with your desired menu items and a custom property to configure the redirect url as shown below :

termstore.png

Step 2 – Create a SharePoint Hosted App using Napa / Visual Studio

Step 3 – Configure Permission as per your requirement

permission.png

Step 4 – Add the necessary script files and a DIV tag to load the dynamic html from App.js file as shown below

default-aspx.png

Step 5 – Build the dynamic menu list and push the html inside the DIV from App.js as shown below:

Note: Replace the term store and termset Guid with your environment values.

‘use strict’;

var terms;
var sb = “”;

var tree = {
term: terms,
children: []
};

$(document).ready(function() {
fn_Build_Navigation();

});

function fn_Build_Navigation() {
var start = new Date().getTime();
var context = SP.ClientContext.get_current();
var taxSession = SP.Taxonomy.TaxonomySession.getTaxonomySession(context);
var termStores = taxSession.get_termStores();
//Name of the Term Store from which to get the Terms.
var termStore = termStores.getByName(“Taxonomy_kFyvauM0tqfeNGG7M0bRmw==“);
//GUID of Term Set from which to get the Terms.
var termSet = termStore.getTermSet(“fa6a6655-4db6-4568-aa41-588b9420aec9“);
terms = termSet.getAllTerms();
context.load(terms);

context.executeQueryAsync(function() {
var termEnumerator = terms.getEnumerator();

while (termEnumerator.moveNext()) {
var currentTerm = termEnumerator.get_current();

var currentTermPath = currentTerm.get_pathOfTerm().split(‘;’);
var children = tree.children;

// Loop through each part of the path
for (var i = 0; i < currentTermPath.length; i++) {
var foundNode = false;
for (var j = 0; j < children.length; j++) {
if (children[j].name === currentTermPath[i]) {
foundNode = true;
break;
}
}

// Select the node, otherwise create a new one
var term = foundNode ? children[j] : {
name: currentTermPath[i],
children: []
};

// If we’re a child element, add the term properties
if (i === currentTermPath.length – 1) {

var navurl = currentTerm.get_customProperties().redirectTo;
if (!navurl) {
navurl = “#”;
}

term.term = currentTerm;
term.title = currentTerm.get_name();
term.guid = currentTerm.get_id().toString();
term.url = navurl;
}

// If the node did exist, let’s look there next iteration
if (foundNode) {
children = term.children;
}
// If the segment of path does not exist, create it
else {
children.push(term);

// Reset the children pointer to add there next iteration
if (i !== currentTermPath.length – 1) {
children = term.children;
}
}
}
}

if (tree.children.length > 0) {
sb = “<ul id=’globalMenu’ class=’menu’>”;

for (var prarentindex = 0; prarentindex < tree.children.length; prarentindex++) {
if (tree.children[prarentindex].children.length > 0) {
buildTermSetTreeLevel(tree.children[prarentindex].children, tree.children[prarentindex].name, tree.children[prarentindex].url);

} else {
sb += “<li><a href=’” + tree.children[prarentindex].url + “‘>” + tree.children[prarentindex].name + “</a></li>”;
}
}

sb += “</ul>”;

$(“#message”).html(sb);
}

var end = new Date().getTime();
var time = end – start;
console.log(‘Execution time: ‘ + time);
}, function(sender, args) {
console.log(args.get_message());
});
}

function buildTermSetTreeLevel(termList, name, url) {

sb += “<li>”;
sb += “<a href=’” + url + “‘>” + name + “</a>”;
sb += “<ul>”;

for (var i = 0; i < termList.length; i++) {
var term = termList[i];

if (term.children.length > 0) {
buildTermSetTreeLevel(term.children, term.nameterm.url);
} else {
sb += ” <li><a href=’” + term.url + “‘>” + term.name + “</a></li>”;
}
}

sb += “</ul>”;
sb += “</li>”;
}

Hope you liked this post. If you have any questions on this topic, please let me know…