How to update taxonomy hidden field using PowerShell?

Continuation to How to use taxonomy hidden field? – (https://blog.sprider.org/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 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 – 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

 

 

Call Page button click event using PowerShell

Using the below PowerShell commands we can open a page and trigger the click event written inside the page. I am using ListItemCollectionPosition to run the while based on the ROW Limit configured in the CAML query.

Please customize this as per your requirements.

Add-PSSnapin Microsoft.SharePoint.Powershell
$pageURL = "http://server/sites/rnd/_layouts/15/SPTest/Pages/gridtest.aspx"
$url = "http://server/sites/rnd"
$listName = "List Name";

$web = Get-SPWeb $url
$list = $web.Lists[$listName]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = “Scope=’Recursive'”;
$spQuery.RowLimit = 2
$caml = ‘<OrderBy Override=”TRUE”><FieldRef Name=”ID”/></OrderBy>’
$spQuery.Query = $caml
$ie = New-Object -com internetexplorer.application;
$ie.visible = $true;
$ie.navigate($pageURL);

while ($ie.Busy -eq $true)
{
Start-Sleep -Milliseconds 1000;
}

Start-Sleep -Milliseconds 5000;

$doc = $ie.document

$viewmoreCNTRL = $doc.getElementByID(“ctrlclick”)

Start-Sleep -Milliseconds 5000;

do
{
$listItems = $list.GetItems($spQuery)
$spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition

foreach($item in $listItems)
{
Write-Output (“Item #” + $item.ID.ToString());
}

$viewmoreCNTRL.click()
Start-Sleep -Milliseconds 60000;
}
while ($spQuery.ListItemCollectionPosition -ne $null)

$web.Dispose();

Set Permission to List Items using PowerShell


Add-PSSnapin Microsoft.SharePoint.Powershell

$url = "http://weburl"
$listName = "List Name";
$permissionLevel = "Permission Level Name";
$groupName = "Enter Group Name";

#Grant permission on all uniquely secured list items to the specified group

$web = Get-SPWeb $url;
$list = $web.Lists[$listName];
$permission = $web.RoleDefinitions[$permissionLevel];
$principal = $web.SiteGroups[$groupName];

#Process each list item

foreach ($item in $list.Items) {
Write-Output ("Item #" + $item.ID.ToString());

#Check to see whether the item is uniquely secured

if ($item.HasUniqueRoleAssignments -eq $FALSE) {
Write-Output " No change, permissions are inherited.";
}

else {

#Find an existing role assignment for this principal

$assignments = $item.RoleAssignments;
$assignment = $assignments | where {$_.Member.Name -eq $principal.Name};
if ($assignment -eq $NULL) {

#Add a new role assignment for the principal

$assignment = new-object Microsoft.SharePoint.SPRoleAssignment($principal);
$assignment.RoleDefinitionBindings.Add($permission);
$assignments.Add($assignment);
Write-Output (" Granted " + $permissionLevel + " to " + $groupName);
}

elseif ($assignment.RoleDefinitionBindings.Contains($permission) -ne $TRUE) {

#Update the principal's role assignment to add the desired permission level

$assignment.RoleDefinitionBindings.Add($permission);
$assignment.Update();
Write-Output (" Updated " + $groupName + " permissions to " + $permissionLevel);
}
else {
Write-Output " No change.";
}
}

}
$web.Dispose();

Migrate data from Source to Destination Library without changing the audit trail column values– PowerShell

Add-PSSnapin "Microsoft.SharePoint.Powershell" -ErrorAction SilentlyContinue

$sourceWebURL = "http://srcurl"
$sourceListName = "Source"

$destinationWebURL = "http://desurl"
$destinationListName = "Destination"

$spSourceWeb = Get-SPWeb $sourceWebURL
$spDestinationWeb = Get-SPWeb $destinationWebURL

$spSourceList = $spSourceWeb.Lists[$sourceListName]
$spDestinationList = $spDestinationWeb.Lists[$destinationListName]

$RootFolder = $spDestinationList.RootFolder

$spSourceItems = $spSourceList.Items

ForEach ($item in $spSourceItems)
{

Try
{

$binary = $item.File.OpenBinary();

if ($binary -ne $null)
{
$sBytes = $item.File.OpenBinary()

[Microsoft.SharePoint.SPFile]$spFile = $RootFolder.Files.Add($item.Name, $sBytes, $true)
$theItem = $spFile.Item
write-host -f Green "...Success!"
$pos = $item["Author"].IndexOf("#")
$userAuthorLogin = "amat\"+$item["Author"].Substring($pos+1)

$pos1 = $item["Editor"].IndexOf("#")
$userEditorLogin = "amat\"+$item["Editor"].Substring($pos+1)

$dateCreatedToStore = Get-Date $item["Created"]
$dateModifiedToStore = Get-Date $item["Modified"]

$userAuthor = Get-SPUser -Web $spDestinationWeb | ? {$_.userlogin -eq $userAuthorLogin}
$userAuthorString = "{0};#{1}" -f $userAuthor.ID, $userAuthor.UserLogin.Tostring()

$userEditor = Get-SPUser -Web $spDestinationWeb | ? {$_.userlogin -eq $userEditorLogin}
$userEditorString = "{0};#{1}" -f $userEditor.ID, $userEditor.UserLogin.Tostring()

#Sets the created by field
$theItem["Author"] = $userAuthorString
$theItem["Created"] = $dateCreatedToStore

#Set the modified by values
$theItem["Editor"] = $userEditorString
$theItem["Modified"] = $dateModifiedToStore

#Store changes without overwriting the existing Modified details.
$theItem.UpdateOverwriteVersion()

write-host -f Green "...Success!"

}

}
Catch [system.exception]
{
write-host "Caught a system exception for " $item.ID $item.Title
}
Finally
{
$spSourceWeb.Dispose();
$spDestinationWeb.Dispose();
}

}