Recently I published an article to the LinkedIn about SharePoint data model. The full article link is here
https://www.linkedin.com/pulse/sharepoint-data-platform-senthamil-selvan
SharePoint as Data Platform
Leave a reply
Category Archives: CSOM with PS
Recursively update File metadata taxonomy field
In this blog you will find a CSOM PowerShell code to update Taxonomy field of a file. The script in the Github will recursively go through all the folders in a library and update the taxonomy field. The script update the field to the specific term you mention. To update the taxonomy field you will need the below details
$termValue.Label
$termValue.TermGuid
$termValue.WssId
To find the WSSID of the term you will need to navigate to the Site collection level Hidden Taxonomy List using the URL /Lists/TaxonomyHiddenList/AllItems.aspx. In the list you can find the term and get the Item ID.
In the above screenshot the wssid for the Presentation term is 2
The complete PowerShell below, you can also find in my github
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.dll"
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.Taxonomy.dll"
$global:CopiedCount = 0
$username = ""
$password = ""
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
$destUrl = ""
$destLibrary = "Documents"
$destinationFolder = "/sites//Shared Documents//"
$termName= "MyTerm"
$termGUID = "TERM GUID HERE"
$termWssId = wssid ## wssid of the term, you can find it from hiddentaxonomy list and the ID of the term in that list /Lists/TaxonomyHiddenList/AllItems.aspx
$libFieldColumnName = "ColumnName"
########
function UpdateFileColumns
{
param($sourceFileCopy, $fListItem)
try{
$modDate = $fListItem["Modified"]
$modUser = $fListItem["Editor"]
$termValue = New-Object Microsoft.SharePoint.Client.Taxonomy.TaxonomyFieldValue
$termValue.Label = $termName #provide the term label here to change
$termValue.TermGuid = $termGUID # term GUID here
$termValue.WssId = $termWssId
$taxField.SetFieldValueByValue($fListItem, $termValue)
$fListItem["Modified"] = $modDate
$fListItem["Editor"] = $modUser
$fListItem.Update()
$destContext.Load($fListItem)
$destContext.ExecuteQuery()
$global:CopiedCount = $global:CopiedCount +1
Write-Host "Updated the file. File Count: " $global:CopiedCount -ForegroundColor Cyan
}catch{
$statusRemark = $statusRemark + "Creation error: " + $_.Exception.Message
Write-Host $statusRemark -ForegroundColor Red
}
}
function CreateFolders
{
param (
[Parameter(Mandatory=$true)] $srcfolder
)
$fileCol = $srcfolder.Files
$destContext.Load($fileCol)
$destContext.ExecuteQuery()
foreach ($f in $fileCol)
{
$ListItem = $f.ListItemAllFields
$destContext.Load($f)
$destContext.Load($ListItem)
$destContext.ExecuteQuery()
Write-Host "Updating file : " $f.ServerRelativeUrl
UpdateFileColumns $f $ListItem
} ## end of for each file
$fL1FolderColl = $srcfolder.Folders
$destContext.Load($fL1FolderColl);
$destContext.ExecuteQuery();
foreach ($myFolder in $fL1FolderColl)
{
$destContext.Load($myFolder)
$destContext.ExecuteQuery()
CreateFolders $myFolder
}
}
$destContext = New-Object Microsoft.SharePoint.Client.ClientContext($destUrl)
$destContext.Credentials = $credentials
$destContext.RequestTimeout = 1000 * 60 * 10
try
{
$destWeb = $destContext.Web
$destList = $destWeb.Lists.GetByTitle($destLibrary)
$destContext.Load($destWeb)
$destContext.Load($destList)
$field = $destList.Fields.GetByInternalNameOrTitle($libFieldColumnName) # column Name here
$destContext.Load($field)
$destContext.ExecuteQuery()
}
catch{
Write-Host $_.Exception.Message -ForegroundColor Red
exit
}
$taxField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($destContext, $field)
$folder = $destWeb.GetFolderByServerRelativeUrl($destinationFolder)
$destContext.Load($folder)
$destContext.ExecuteQuery()
CreateFolders $folder
$destContext.Dispose()
$now=Get-Date -format "dd-MMM-yyyy HH:mm"
Write-Host "Script End : '$($now)'"
##############
Create termset & terms using CSOM PowerShell
CSOM has the rich API to create and manage the Termset and terms within SharePoint. The PowerShell code in the Github uses the sample XML here to create a Termset with hierarchy.
The full PowerShell code below
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.dll"
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.Taxonomy.dll"
$now=Get-Date -format "dd-MMM-yy,HH:mm:ss"
Write-Host "Script Start : '$($now)'" -ForegroundColor Yellow
$username = "user name"
$password = ""
$srcUrl = "SITE URL"
$xmlFilePath = "C:\Github\msisgreat\ps\ps\sample\CountriesTerm.xml" # change this to xml path with the termset defined
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
function CreateTerm($context, $termSet, $label, $lcid, $termXmlData)
{
$terms = $termSet.Terms
$context.Load($terms)
$context.ExecuteQuery()
$term = $terms | Where-Object {$_.Name -eq $label}
if($term)
{
Write-Host "Term" $label "already exists." -foregroundcolor Blue
$termXmlData.Term | ForEach-Object { CreateTerm $context $term $_.Name $lcid $_ }
}
elseif($label)
{
Write-Host "Creating term " $label -foregroundcolor yellow
$term = $termSet.CreateTerm($label, $lcid, [System.Guid]::NewGuid())
try
{
$context.ExecuteQuery()
Write-Host "Term" $label "Created successfully" -foregroundcolor Green
$termXmlData.Term | ForEach-Object { CreateTerm $context $term $_.Name $lcid $_ }
}
catch
{
Write-Host "Error while creating Term" $label $_.Exception.Message -foregroundcolor Red
return
}
}
}
function CreateTermSet($context, $group, $termSetXml, $lcid)
{
$termSets = $group.TermSets
$context.Load($termSets)
$context.ExecuteQuery()
$termSet = $termSets | Where-Object {$_.Name -eq $termSetXml.Name}
if($termSet)
{
Write-Host "Termset" $termSetXml.Name "already exists." -foregroundcolor Blue
$termSet = $group.TermSets.GetByName($termSetXml.Name)
$context.Load($termSet)
$context.ExecuteQuery()
}
else
{
Write-Host "Creating term set" $termSetXml.Name -foregroundcolor yellow
$termSet = $group.CreateTermSet($termSetXml.Name, [System.Guid]::NewGuid(), $lcid)
try
{
$context.ExecuteQuery()
Write-Host "Term set " $termSetXml.Name "Created successfully" -foregroundcolor Green
}
catch
{
Write-Host "Error while creating Term set" $termSetXml.Name $_.Exception.Message -foregroundcolor Red
return
}
}
$termSetXml.Term | ForEach-Object { CreateTerm $context $termSet $_.Name $lcid $_ }
}
function CreateTermSet($context, $group, $termSetXml, $lcid)
{
$termSets = $group.TermSets
$context.Load($termSets)
$context.ExecuteQuery()
$termSet = $termSets | Where-Object {$_.Name -eq $termSetXml.Name}
if($termSet)
{
Write-Host "Termset" $termSetXml.Name "already exists." -foregroundcolor Cyan
$termSet = $group.TermSets.GetByName($termSetXml.Name)
$context.Load($termSet)
$context.ExecuteQuery()
}
else
{
Write-Host "Creating term set" $termSetXml.Name -foregroundcolor yellow
$termSet = $group.CreateTermSet($termSetXml.Name, [System.Guid]::NewGuid(), $lcid)
try
{
$context.ExecuteQuery()
Write-Host "Term set " $termSetXml.Name "Created successfully" -foregroundcolor Green
}
catch
{
Write-Host "Error while creating Term set" $termSetXml.Name $_.Exception.Message -foregroundcolor Red
return
}
}
$termSetXml.Term | ForEach-Object { CreateTerm $context $termSet $_.Name $lcid $_ }
}
function Get-TermStoreInfo
{
Write-Host "Loading taxonomy session" -foregroundcolor yellow
$session = [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($context)
$session.UpdateCache();
$context.Load($session)
$context.ExecuteQuery()
Write-Host "Loading term stores" -foregroundcolor yellow
$termStores = $session.TermStores
$context.Load($termStores)
try
{
$context.ExecuteQuery()
$termStore = $termStores[0]
$context.Load($termStore)
Write-Host "Term store with the following id is loaded:" $termStore.Id -foregroundcolor Green
}
catch
{
Write-Host "Error detail while getting term store id" $_.Exception.Message -foregroundcolor Red
return
}
return $termStore
}
function CreateMetadata($context)
{
Write-Host "Loading the terset xml..." -foregroundcolor Green
[xml]$xmlContent = (Get-Content $xmlFilePath)
if (-not $xmlContent)
{
Write-Host "Error loading the xml." -foregroundcolor Red
return
}
$termStore = Get-TermStoreInfo $context
Write-Host "Create Taxonomy group if it is not available" -foregroundcolor yellow
$sitecollectiontaxonomyGroup = $termStore.GetSiteCollectionGroup($context.Site,$true)
$context.Load($sitecollectiontaxonomyGroup)
try
{
$context.ExecuteQuery()
Write-Host "Site collection $url taxonomy group "$sitecollectiontaxonomyGroup.Name" created or retrived successfully " -foregroundcolor Green
}
catch
{
Write-Host "Error while creating or getting site collection $url taxonomy group" $_.Exception.Message -foregroundcolor Red
return
}
$xmlContent.TermSets.TermSet |
ForEach-Object { CreateTermSet $context $sitecollectiontaxonomyGroup $_ $termStore.DefaultLanguage }
}
### The script starts here to run ####
Write-Host "Authenticating ..." -ForegroundColor White
$context = New-Object Microsoft.SharePoint.Client.ClientContext($srcUrl)
$context.Credentials = $credentials
$web = $context.Web
$site = $context.Site
$context.Load($web)
$context.Load($site)
try {
$context.ExecuteQuery()
}
catch {
Write-Host "Error" + $_.Exception.Message -ForegroundColor Red
exit
}
Write-Host "Connected to the web..." -ForegroundColor Cyan
#Provision Site collection taxonomy group, termset and terms based on SiteCollectionTermsets.xml configuration file
Write-Host "Provisioning Site collection terms started" -foregroundcolor yellow
CreateMetadata ($context)
Write-Host "Provisioning Site collection terms Completed" -foregroundcolor green
$context.dispose()
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
Sample XML file for input
Output of the PowerShell
Update Webpart view in a Page programmatically using PowerShell
For a SharePoint Administrator managing the changes in the view for a webpart is a very tiresome job. If a single view changes in the library or list then a page which uses the webpart view has to be manually changed. To change the view in the webpart the administrator has to edit the page, modify webpart and save the page. Imagine if you have 5 pages and each page have 5 webparts and a view is changed. You have to open each page in edit mode and the each webpart edit, change view and save one by one.
To effectively change the webpart view in a page, CSOM have an api to get the webpart in a page as a XML. Manipulate the XML and get the webpart name and view name from xml. Usually when a webpart is added to the page the view xml is cached to the page. So when the view is changed in the library / list the updated view will not be reflected in the page. The PowerShell code will get the webpart and update the view xml
The full code is here you can download
#### To change the webpart view
#### Get the webpart from the page. get the WebPart XmlDef and find the View ID
### List has hidden view for the web part added. So get the view from the List and change that view.
### Its all about that hidden View
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.dll"
Add-Type -Path ".\CSOM\Microsoft.SharePoint.Client.Runtime.dll"
$now=Get-Date -format "dd-MMM-yy,HH:mm:ss"
$fileFormat = Get-Date -format "dd-MMM-yy_HHmmss"
Write-Host "Script Start : '$($now)'" -ForegroundColor Yellow
$username = ""
$password = ""
$srcUrl = "" # full url like hhtp://my.sharepoint.com/
$sitePath = "/SitePages/"
$srcLibrary = "Documents"
$pagesList = @("Home.aspx") # you can specify many pages to be changed array here
$wepPartChangeList = @{"Documents" = "All Documents" ; } ### dictionary value Key = Web part Title seen on page, Value = View Name from the Library
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
Write-Host "Authenticating ..." -ForegroundColor White
$srcContext = New-Object Microsoft.SharePoint.Client.ClientContext($srcUrl)
$srcContext.Credentials = $credentials
$srcWeb = $srcContext.Web
$srcList = $srcWeb.Lists.GetByTitle($srcLibrary)
$srcContext.Load($srcWeb)
$srcContext.Load($srcList)
try{
$srcContext.ExecuteQuery()
}catch{
Write-Host $_.Exception.Message -ForegroundColor Red
exit
}
Write-Host "Connected to the web..." -ForegroundColor Green
foreach($page in $pagesList)
{
$filePath = $sitePath + $page
$pageObject = $srcWeb.GetFileByServerRelativeUrl($filePath)
$srcContext.Load($pageObject)
$srcContext.ExecuteQuery()
$WPM = $pageObject.GetLimitedWebPartManager("Shared")
$webParts = $WPM.WebParts
$srcContext.Load($WPM)
$srcContext.Load($webParts)
$srcContext.ExecuteQuery()
foreach($wp in $webParts)
{
$srcContext.Load($wp.WebPart)
$srcContext.Load($wp.WebPart.Properties)
$srcContext.ExecuteQuery()
if( $wepPartChangeList[$wp.WebPart.Title] -ne $null)
#if($wp.WebPart.Title.IndexOf("Documents") -gt 0)
{
Write-Host "---------- Processing Page = $($pageObject.Name) Webpart = $($wp.WebPart.Title) ------- " -ForegroundColor Yellow
$wpXmlContent = $wp.WebPart.Properties["XmlDefinition"]
$wpXml = New-Object -TypeName XML
$wpXml.LoadXml($wpXmlContent)
$viewGUID = $wpXml.View.Name
$viewName = $null
$viewName = $wepPartChangeList[$wp.WebPart.Title]
if($viewName)
{
$view = $srcList.Views.GetByTitle($viewName)
$wpView = $srcList.Views.GetById($viewGUID)
$srcContext.Load($wpView)
$srcContext.Load($view)
$srcContext.ExecuteQuery()
#$viewXml = New-Object -TypeName XML
#$viewXml.LoadXml($view.ListViewXml)
write-host "### WebPart Xml -------Before Xml Change -----" -foreground cyan
write-host "$($wpView.ListViewXml)"
$wpViewFields = $wpView.ViewFields
$viewFields = $view.ViewFields
$srcContext.Load($viewFields)
$srcContext.Load($wpViewFields)
$srcContext.ExecuteQuery()
$wpViewFields.RemoveAll()
foreach($vField in $viewFields)
{
$wpView.ViewFields.Add($vField)
}
$wpView.RowLimit = $view.RowLimit
$wpView.ViewQuery = $view.ViewQuery
$wpView.Update()
$srcContext.Load($wpView)
$srcContext.ExecuteQuery()
write-host "### WebPart Xml -------After Xml Change -----" -foreground cyan
write-host "$($wpView.ListViewXml)"
write-host "*******************************************************************" -foreground cyan
}
else
{
Write-Host "Unable to fing view for " $wp.WebPart.Title -ForegroundColor Cyan
}
#$wp.SaveWebPartChanges()
}
}
#Write-Host "Next Page "
}
#>
$srcContext.Dispose()
$now=Get-Date -format "dd-MMM-yy,HH:mm:ss"
Write-Host "END : '$($now)'" -ForegroundColor Yellow<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
Set SharePoint folder level permission using C# & PowerShell
The PowerShell script here will set the permission to the folder inside the document library. The PowerShell script uses the C# code for permission. The C# code will add mandatory Full Control access and break the inheritance. The code uses the lambda function to find the unique permission. Since PowerShell natively does not support lambda expression the C# code is embedded within the PowerShell
The full script is below
$cSharp = @”
using System;
using System.Security;
using System.Linq;
using System.Collections.Generic;
using Microsoft.SharePoint.Client;
namespace SPClient
{
public class Permission
{
private static string username = “”;
private static string password = “”;
private static string srcUrl = “”;
private static string[] groupNames = { “Team Site Members” };
private static string srcFolder = “folder path(/sites/dev/shared documents/folder1)”;
private static string permissionType = “Edit”;
private static string mandatoryGroupName = “Developer Owners”;
public static void ApplyPermission()
{
try
{
var securePassword = new SecureString();
foreach (char c in password)
{
securePassword.AppendChar(c);
}
using (var clientContext = new ClientContext(srcUrl))
{
Console.WriteLine(“Authenticating…” + srcUrl);
clientContext.Credentials = new SharePointOnlineCredentials(username, securePassword);
Web srcWeb = clientContext.Web;
Folder applyFolder = srcWeb.GetFolderByServerRelativeUrl(srcFolder);
clientContext.Load(srcWeb);
clientContext.Load(applyFolder,f=>f.ListItemAllFields.HasUniqueRoleAssignments);
clientContext.ExecuteQuery();
Console.WriteLine(“Connected.”);
Console.WriteLine(“Applying to folder : ” + srcFolder);
GroupCollection groups = srcWeb.SiteGroups;
clientContext.Load(groups);
clientContext.ExecuteQuery();
var myGroups = (from a in groupNames
from w in groups
where a == (w.Title)
select w).ToArray();
Console.WriteLine(“Groups…”);
var coordinator = (from a in groups where a.Title == mandatoryGroupName select a).FirstOrDefault();
RoleDefinitionCollection rdc = srcWeb.RoleDefinitions;
RoleDefinition myRole = rdc.GetByName(permissionType);
RoleDefinition coordinateRole = rdc.GetByName(“Full Control”);
clientContext.Load(rdc);
clientContext.Load(myRole);
clientContext.Load(coordinateRole);
clientContext.ExecuteQuery();
Console.WriteLine(“Role definitions…”);
if (!applyFolder.ListItemAllFields.HasUniqueRoleAssignments)
{
Console.WriteLine(“Breaking inheritance…”);
applyFolder.ListItemAllFields.BreakRoleInheritance(true, false);
}
else
{
Console.WriteLine(“Inheritance broken already…”);
}
var folderRoles = applyFolder.ListItemAllFields.RoleAssignments;
Console.WriteLine(“Applying the role assignments …”);
RoleDefinitionBindingCollection coordinateRdb = new RoleDefinitionBindingCollection(clientContext);
coordinateRdb.Add(coordinateRole);
RoleDefinitionBindingCollection collRdb = new RoleDefinitionBindingCollection(clientContext);
collRdb.Add(myRole);
//clientContext.Load(folderRoles);
//clientContext.ExecuteQuery();
folderRoles.Add(coordinator, coordinateRdb);
foreach (Group eachGroup in myGroups)
{
Console.WriteLine(“Applying Group: ” + eachGroup.Title);
folderRoles.Add(eachGroup, collRdb);
}
applyFolder.Update();
clientContext.ExecuteQuery();
Console.WriteLine(“Successfully applied”);
Console.Read();
}
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
//Console.Read();
}
}
}
}
“@
$assemblies = @(
“C:\Documents\PS\CSOM\Microsoft.SharePoint.Client.dll”,
“C:\Documents\PS\CSOM\Microsoft.SharePoint.Client.Runtime.dll”,
“System.Core”
)
Add-Type -TypeDefinition $cSharp -ReferencedAssemblies $assemblies
[SPClient.Permission]::ApplyPermission()
Export SharePoint Groups and users using CSOM PowerShell
CSOM library have SPGroups object to get all the groups within sites and site collection. The PowerShell script in this location will export all the groups and users within each group as a .csv file format.
Below is the sample output from the PowerShell window.
Below is the sample CSV format of the exported Group and Users.
Document Library View with Nested Condition
The OOB SharePoint List / Document library view allows only flat OR / AND conditions to be applied to the columns. Think of a scenario where you want to get all documents modified on last 10 days from a selected list of people. This condition is difficult to achieve in OOB view creation. In this scenario the custom view with CAML query comes to the rescue. We can create a normal view in SharePoint and using the below PowerShell script we can update the existing view to use the nested condition.
The CAML query used to filter the document is like below
<FieldRef Name="Modified” Ascending=”FALSE” />
<FieldRef Name="Editor” /><Value Type="User“>senthamil<FieldRef Name="Editor” /> <Value Type="User“>venkat<FieldRef Name="Editor” /><Value Type="User“>amanda<FieldRef Name="Editor” /><Value Type="User“>arwen<FieldRef Name="Editor” />
<Value Type="User“>matthew<FieldRef Name="Editor” /><Value Type="User“>mcdonnel<FieldRef Name="Editor” /><Value Type="User“>ravi<FieldRef Name="Modified” /><Value Type="DateTime“><Today OffsetDays="-6” />
To achieve this first create a View in the SharePoint document library like a normal view with the name “LatestDocsByUsers”. Once you finished creating the view then download the PowerShell from this location and change the below variables
$username, $password, $srcList, $srcUrl and $view
In this case the $view = “LatestDocsByUsers”
The PowerShell will update the existing view with the above CAML Query.
The condition tree looks like below
Classic Site – Create View Filter by Metadata, Using PowerShell
Previous blog discuss about creation of view filter for Modern SharePoint site. This part we discuss how to create View filter based on Metadata field for Classic SharePoint site.
Classic SharePoint Site – How to Create View Filter
In my scenario I have a document library “Documents” which has several type of documents with the category. The category is the Metadata / Taxonomy field. The Category has EU as top level TermSet, below is the screenshot of the termset and terms.
Step – 1:
Create Document Library with the Taxonomy / Metadata field. In my case Category column with EU termset with all the terms. The document library has several folders and documents inside tagged with the metadata needed.
Step – 2:
Create a view to show all the documents without folder. Name the view as “O365 Files” the name can be any meaningful name which you can create. While creating the view make sure you select “Show all items without Folders” under Folders. In my case I want to see all documents uploaded to library without any folders. This view filter will be modified later by the PowerShell CSOM script.
Below is the view I created with the name O365 Files. This view will be changed using the PowerShell script to update the filtering metadata.
Step – 3
Identify the Metadata tag ID you want to filter. The metadata tag id is the hidden id which is created under the site collection to store the data. There will be a hidden list under site collection. Make sure you have atleast one document uploaded and tagged with the metadata you wanted to filter. The Metadata will have a unique Id in the hidden list. Navigate to https:///Lists/TaxonomyHiddenList/AllItems.aspx to get the list. I have created a new view for this list to see the Id column easily called ShowId. Below is the screenshot of the List with the Id column made visible. To filter the documents by O365 and all its children metadata note down all the Ids. In my case below are the ids
Step – 4:
Prepare to run the PowerShell CSOM script to update the View CAML. Download the CSOM dll from Nuget.org https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM/16.1.7414.1200 Use the Manual download link at the right side of the page. The downloaded file will be microsoft.sharepointonline.csom.16.1.7414.1200.nupkg based on the version you download. Unzip the file to the folder. You can change the file extension to .zip and unzip all files. Use the .net45 folder libraries for the CSOM PowerShell.
Step – 5:
Use the PowerShell below to update the View to filter by metadata. The LookUpId in the below code is the Hidden Metadata Id from the TaxonomyHiddenList.
https://github.com/msisgreat/ps/blob/master/ViewFilterByMetadata.ps1
Modern Sites – View Filter By Metadata or Taxonomy
This blog is about how to create a View in document library to filter documents by Metadata / Taxonomy field. The blog will discuss for both modern and classic SharePoint site
Modern Site – How to Create View Filter
Consider you want to load all documents from Library filtered by Taxonomy / Metadata field. Currently there is no straight way to create view filter by Metadata field. Modern SharePoint allows to do that indirect way. Below are the steps to follow to create view which filter by metadata. In my scenario I have a document library “Documents” which has several type of documents with the category. The category is the Metadata / Taxonomy field. The Category has EU as top level TermSet, below is the screenshot of the termset and terms.
Step – 1:
Create Document Library with the Taxonomy / Metadata field. In my case Category column with EU termset with all the terms. The document library has several folders and documents inside tagged with the metadata needed.
Some of the sample documents tagged with metadata
Step – 2:
Create a view to show all the documents without folder. Name the view as “Files” the name can be any meaningful name which you can create. While creating the view make sure you select “Show all items without Folders” under Folders. In my case I want to see all documents uploaded to library without any folders. This view is the temporary view without the Metadata filtering. Next step we create the view with Metadata.
Below is the view I created with the name Files and load all documents. This is the temporary view to see all files
Step – 3
To make use of the Metadata filtering enable the Filter on the right side panel. Once you see the panel select the filter needed to be applied. In my case I selected O365 since I want to see only O365 files.
Step – 4:
Click on the Files view drop down on the right side. Select Save View As. This will show Save As dialog. Enter the view name and Save to create a View which can be filtered by Metadata.
Programmatically set column default values for folders – CSOM & PS
In this blog I will describe about the option to set the column default value settings. SharePoint 2013 document libraries have the option to set the default column values at the folder level. Assume that I have a document library with the below folders
Whenever the document is uploaded to the SharePoint folder the uploaded document needs to be automatically tagged with the SharePoint metadata. The column can be set with the default values for each folder. Without getting the user to tag manually the document can be automatically tagged with the default value. This can be done under Library Settings with Column default value settings option
When the default values are assigned, the view will be shown as below
Once the values are assigned the document library will save the value in an Xml format under the forms directory. The values are saved in the client_LocationBasedDefaults.html. Below is the xml format of the value saved in the file
<a href="/ESPC15/Shared%20Documents/Microsoft">
Microsoft
</a><a href="/ESPC15/Shared%20Documents/Others">
Others
</a><a href="/ESPC15/Shared%20Documents/Microsoft/ASPNET">
12;#ASPNET|c59a6216-895a-4c04-8377-63b5f9f78ed2
</a><a href="/ESPC15/Shared%20Documents/Microsoft/Azure">
13;#Azure|add6b6fe-358e-4e37-a15b-90ab58ac153e
</a>
If you notice above the values are stored as an anchor tag with the metadata Taxonomy Term Id and the GUID of the particular Term. So to assign the term to other folders we can just add the xml node to this file and upload it to the forms directory of the document library.
CSOM approach to add default values
Using CSOM PowerShell script we can download the file from the forms folder of the document library to save it to local drive. Then the local file can be modified to add the nodes needed.
Below are the steps followed for the CSOM PS
Step 1: Connect to the document library using the PS CSOM script
Step 2: Download the file client_LocationBasedDefaults.html from the forms directory to save it to local folder to change
Step 3: Change the file to add the details needed, in my case added the tag for the SharePoint folder to set Term
Step 4: Upload back to the document library forms folder
Step 1 & 2
https://github.com/msisgreat/ps/blob/master/Download_client_LocationBasedDefaults.ps1
Add-Type -Path "C:\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Microsoft.SharePoint.Client.Runtime.dll"
$username = "name@domain.com"
$password = ""
$destUrl = "" ## https://site
$srcLibrary = "Documents"
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
Write-Host "connecting..."
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($destUrl)
$ctx.Credentials = $credentials
$srcWeb = $ctx.Web
$srcList = $srcWeb.Lists.GetByTitle($srcLibrary)
$ctx.Load($srcWeb)
$ctx.Load($srcList)
$ctx.ExecuteQuery()
Write-Host "connected"
#### Step 1: download the html file
# Get forms folder in library
$formsFolder = $srcList.RootFolder.Folders.GetByUrl("Forms")
$ctx.Load($formsFolder)
$ctx.ExecuteQuery()
# Get client_LocationBasedDefaults.html file in forms library
$LocationBasedDefaultsXML = $formsFolder.Files.GetByUrl('client_LocationBasedDefaults.html')
$ctx.Load($LocationBasedDefaultsXML)
$ctx.ExecuteQuery()
# Download file
$fileInfo = [Microsoft.SharePoint.Client.File]::OpenBinaryDirect($ctx, $LocationBasedDefaultsXML.ServerRelativeUrl)
$fstream = New-Object System.IO.FileStream("C:\client_LocationBasedDefaults.html", [System.IO.FileMode]::Create);
$fileInfo.Stream.CopyTo($fstream)
$fstream.Flush()
$fstream.Close()
Write-Host "Downloaded the file successfully"
### download the file complete
Step 3
Example if you want to set the default term for SharePoint folder add the below entry to the file downloaded
<a href="/ESPC15/Shared%20Documents/Microsoft/SharePoint">
16;#SharePoint|36079b03-5612-4cc2-86b8-178061d83ee0
</a>
If you want to find the term id and the GUID you can use the F12 dev window at the internet explorer on the term store.
Step 4:
Once the file client_LocationBasedDefaults.html is modified with the added term the final file will look like this
<a href="/ESPC15/Shared%20Documents/Microsoft">
Microsoft
</a><a href="/ESPC15/Shared%20Documents/Others">
Others
</a><a href="/ESPC15/Shared%20Documents/Microsoft/ASPNET">
12;#ASPNET|c59a6216-895a-4c04-8377-63b5f9f78ed2
</a><a href="/ESPC15/Shared%20Documents/Microsoft/Azure">
13;#Azure|add6b6fe-358e-4e37-a15b-90ab58ac153e
</a><a href="/ESPC15/Shared%20Documents/Microsoft/SharePoint">
16;#SharePoint|36079b03-5612-4cc2-86b8-178061d83ee0
</a>
Now upload back the file to the same location to set the values. The below script will upload back the file to the folder.
https://github.com/msisgreat/ps/blob/master/Upload_client_LocationBasedDefaults.ps1
Add-Type -Path "C:\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Microsoft.SharePoint.Client.Runtime.dll"
$username = "name@domain.com"
$password = ""
$destUrl = "" ## https://site
$srcLibrary = "Documents"
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
Write-Host "connecting..."
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($destUrl)
$ctx.Credentials = $credentials
$srcWeb = $ctx.Web
$srcList = $srcWeb.Lists.GetByTitle($srcLibrary)
$ctx.Load($srcWeb)
$ctx.Load($srcList)
$ctx.ExecuteQuery()
Write-Host "connected"
Write-Host "uploading..."
$root = $srcList.RootFolder
$ctx.Load($root);
$ctx.ExecuteQuery()
$folder = $root.Folders
$ctx.Load($folder);
$ctx.ExecuteQuery()
foreach($f in $folder)
{
$ctx.Load($f)
$ctx.ExecuteQuery()
Write-Host $f.Name
if($f.Name -eq "Forms")
{
Write-Host "Inside " $f.Name
$fci = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$fci.Content = [System.IO.File]::ReadAllBytes("C:\client_LocationBasedDefaults.html");
$fci.Url = "client_LocationBasedDefaults.html";
$fci.Overwrite = $true;
$fileToUpload = $f.Files.Add($fci);
$ctx.Load($fileToUpload);
$ctx.ExecuteQuery()
Write-Host "Uploaded .. "
}
}
Write-Host "End Script"
Senthamil 