Monthly Archives: July 2018

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" 

PowerShell CSOM – Hide site columns from edit and new form

In this blog I will explain how to hide some of the columns from the edit and the new form for the list. CSOM has the API to hide column. There are three main api to hide / show the column

  1. SetShowInEditForm
  2. SetShowInNewForm
  3. SetShowInDisplayForm

Consider the below sample scenario where you are developing a Leave Application using custom list. When the user fills in the Leave application you don’t want to show status and comment field. So in the below scenario you want to hide status and comment field from edit form and the new form for the list. But the same fields need to be shown on the view form.

Run the below script to hide those fields from both the form.

https://github.com/msisgreat/ps/blob/master/HideColumn.ps1

Add-Type -Path "C:\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Microsoft.SharePoint.Client.Runtime.dll"

$username = "username@domain.com"
$password = ""
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
Write-Host "Connecting to site ..."
$srcUrl = "" ## https://sitename/sites/sitename
$srcLibrary = "Leave Application"
$srcContext = New-Object Microsoft.SharePoint.Client.ClientContext($srcUrl)
$srcContext.Credentials = $credentials
$srcWeb = $srcContext.Web
$srcList = $srcWeb.Lists.GetByTitle($srcLibrary)
$srcContext.Load($srcList)
$srcContext.ExecuteQuery()
Write-Host "Connected successfully"

$fields = @("Application Status","Approver Comment")
foreach($fieldname in $fields)
{
    Write-Host "Hiding from edit & new form: " $fieldname
    $fieldToEdit = $srcList.Fields.GetByTitle($fieldname);
    $srcContext.Load($fieldToEdit)
    $srcContext.ExecuteQuery()
    $fieldToEdit.SetShowInEditForm($false)
    #$fieldToEdit.Update()
    $srcContext.ExecuteQuery()
    $fieldToEdit.SetShowInNewForm($false)
    #$fieldToEdit.Update()
    $srcContext.ExecuteQuery()
    #$srcWeb.Update()

}

Sample PowerShell output

After the script is successfully executed the fields are hidden in the new and edit form. Below is the screenshot after the fields are hidden

Migration of documents in SharePoint Online between site collections

In this blog I am going to share the code to copy documents between site collections using CSOM on Office 365 SharePoint. The below code uses the recursive method to get all documents from source site collection and copy it to the target site collection. The script also preserves the modified and modified by details after copying.

In this blog I am using the latest CSOM targeted to Office 365, you can download the latest here

The sample code is in the PowerShell command. First download the CSOM dll and save it to the folder to be used. The PowerShell command will load the specific dll to use the API.

https://github.com/msisgreat/ps/blob/master/DocMigrationSitecollections.ps1


Add-Type -Path "C:\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Microsoft.SharePoint.Client.Runtime.dll"
$username = "name@domain.com"
$password = "”
$srcUrl = "" ## use the full URL of the site
$destUrl = "" ## use the full URL of the site
$srcLibrary = ""
$destLibrary = ""
$destinationFolder = "/sites//Shared Documents//" ## Make sure the folder name ends with /
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)
function CreateFolders
{
param (
[Parameter(Mandatory=$true)] $srcfolder,
[Parameter(Mandatory=$true)] [string] $destFolderPath
)
Write-Host "Source Folder:" + $srcfolder.Name + " dest folder:" + $destFolderPath -ForegroundColor Yellow
$SPOFolder = $destWeb.GetFolderByServerRelativeUrl($destFolderPath)
$FolderName = $srcfolder.Name
$NewFolder = $SPOFolder.Folders.Add($FolderName)
$destWeb.Context.Load($NewFolder)
$destWeb.Context.ExecuteQuery()
Write-Host "Folder Created - " + $FolderName -ForegroundColor Yellow
## get source folder details
$SrcFolderListItem = $srcfolder.ListItemAllFields
$srcContext.Load($SrcFolderListItem)
$srcContext.ExecuteQuery()
####
$SPOFolderItem = $NewFolder.ListItemAllFields;
$replacedUser =$destWeb.EnsureUser($SrcFolderListItem["Editor"].Email)
$SPOFolderItem["Editor"] = $replacedUser
$replacedUser =$destWeb.EnsureUser($SrcFolderListItem["Author"].Email)
$SPOFolderItem["Author"] = $replacedUser
$SPOFolderItem["Created"] = $SrcFolderListItem["Created"]
$SPOFolderItem["Modified"] = $SrcFolderListItem["Modified"]
$SPOFolderItem.Update()
$destContext.Load($NewFolder)
$destContext.ExecuteQuery()
$fileCol = $srcfolder.Files
$srcContext.Load($fileCol)
$srcContext.ExecuteQuery()
### Load the file to hash table to check with Target library.
$hashFiles = @{}
$DestfileCol = $NewFolder.Files
$destContext.Load($DestfileCol)
$destContext.ExecuteQuery()
foreach ($destFile in $DestfileCol){
$hashFiles.add($destFile.Name,$destFile.Name)
}
foreach ($f in $fileCol)
{
if($hashFiles.ContainsKey($f.Name) -ne "true") ## check whether file name already exists
{
$srcContext.Load($f)
$srcContext.ExecuteQuery()
$id = $srcfolder.Name
$nLocation =$NewFolder.ServerRelativeUrl.TrimEnd("/") + "/" + $f.Name
Write-Host $nLocation
try
{
$fileInfo = [Microsoft.SharePoint.Client.File]::OpenBinaryDirect($srcContext, $f.ServerRelativeUrl)
[Microsoft.SharePoint.Client.File]::SaveBinaryDirect($destContext, $nLocation, $fileInfo.Stream,$true)
$ListItem = $f.ListItemAllFields
$srcContext.Load($ListItem)
$srcContext.ExecuteQuery()
$fileCreated = $destWeb.GetFileByServerRelativeUrl($nLocation)
$destContext.Load($fileCreated)
$destContext.ExecuteQuery()
$DestListItem = $fileCreated.ListItemAllFields;
$replacedUser =$destWeb.EnsureUser($ListItem["Editor"].Email)
$DestListItem["Editor"] = $replacedUser
$replacedUser =$destWeb.EnsureUser($ListItem["Author"].Email)
$DestListItem["Author"] = $replacedUser
$DestListItem["Created"] = $ListItem["Created"];
$DestListItem["Modified"] = $ListItem["Modified"];
$DestListItem.Update()
$destContext.Load($fileCreated)
$destContext.ExecuteQuery()
}
catch
{
Write-Host $_ -ForegroundColor Red
}
}
}
$fL1FolderColl = $srcfolder.Folders
$srcContext.Load($fL1FolderColl);
$srcContext.ExecuteQuery();
foreach ($myFolder in $fL1FolderColl)
{
$srcContext.Load($myFolder)
$srcContext.ExecuteQuery()
CreateFolders $myFolder $NewFolder.ServerRelativeUrl
}
}
#### The main script starts here ######
$srcContext = New-Object Microsoft.SharePoint.Client.ClientContext($srcUrl)
$srcContext.Credentials = $credentials
$destContext = New-Object Microsoft.SharePoint.Client.ClientContext($destUrl)
$destContext.Credentials = $credentials
$srcWeb = $srcContext.Web
$srcList = $srcWeb.Lists.GetByTitle($srcLibrary)
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$listItems = $srcList.GetItems($query)
$srcContext.Load($srcList)
$srcContext.Load($listItems)
$srcContext.ExecuteQuery()
$destWeb = $destContext.Web
$destList = $destWeb.Lists.GetByTitle($destLibrary)
$destContext.Load($destWeb)
$destContext.Load($destList)
$destContext.ExecuteQuery()
########### this is to copy only certain folders
#$folder = $srcWeb.GetFolderByServerRelativeUrl($srcFolder)
#$srcContext.Load($folder)
#$srcContext.ExecuteQuery()
#Write-Host $destinationFolder
#CreateFolders $folder $destinationFolder
##############
foreach($item in $listItems)
{
if($item.FileSystemObjectType -eq "File")
{
#$srcContext.Load($item)
#$srcContext.ExecuteQuery()
$srcF = $item.File
$srcContext.Load($srcF)
$srcContext.ExecuteQuery()
$rootLocation = $destinationFolder + $srcF.Name
Write-Host $rootLocation
$fileContent = [Microsoft.SharePoint.Client.File]::OpenBinaryDirect($srcContext, $srcF.ServerRelativeUrl)
[Microsoft.SharePoint.Client.File]::SaveBinaryDirect($destContext, $rootLocation, $fileContent.Stream,$true)
$fItem = $srcF.ListItemAllFields
$srcContext.Load($fItem)
$srcContext.ExecuteQuery()
$fCreated = $destWeb.GetFileByServerRelativeUrl($rootLocation)
$destContext.Load($fCreated)
$destContext.ExecuteQuery()
$DListItem = $fCreated.ListItemAllFields;
$replacedUser =$destWeb.EnsureUser($fItem["Editor"].Email)
$DListItem["Editor"] = $replacedUser
$replacedUser =$destWeb.EnsureUser($fItem["Author"].Email)
$DListItem["Author"] = $replacedUser
$DListItem["Created"] = $fItem["Created"];
$DListItem["Modified"] = $fItem["Modified"];
$DListItem.Update()
$destContext.Load($fCreated)
$destContext.ExecuteQuery()
}
elseif ($item.FileSystemObjectType -eq "Folder")
{
$srcContext.Load($item)
$srcContext.ExecuteQuery()
$folder = $srcWeb.GetFolderByServerRelativeUrl($item.FieldValues["FileRef"].ToString())
$srcContext.Load($folder)
$srcContext.ExecuteQuery()
##################
Write-Host $destinationFolder
CreateFolders $folder $destinationFolder
}
}
Write-Host “Script End"

Set Document Library Webpart to Specific Folder – SharePoint Online

This blog gives you step by step details on how to set the document library web part view to specific sub folder. OOB SharePoint online does not support view to show the sub folder of the document library. This can be achieved in 2 ways

  1. By changing the view page Query using the SharePoint Designer
  2. Java Script to modify the view Query to sub folder.

In this blog I will show how to change the view Query using the JavaScript. Below are the steps to achieve

Step 1: Create a view to the document library

Step 2: Get the View GUID

Step 3: Create a Page to execute JavaScript

Step 4 Create Page to add document library Webpart

Follow the below steps to run the script to modify the view. This script needed to be run just once to change the view. Once the view is changed the web part view will use the query to show the sub folder.

Step 1:

Create a document library and create a view to be able to use it in the web part. In the below scenario I am using the Shared Documents library and created the FolderView.aspx. This folderview.aspx is a normal view created for the document library.

This folderview.aspx will be changed to show the documents inside the MyFolder folder.

Step 2:

To get the view GUID, edit the folderview.aspx and scroll down to the last to see the GUID. This is the GUID of the view in my case “41770fbe-48c8-4541-9629-cf6119f81c02

Step 3:

Execute the javascript using the Script Editor webPart. Add the Embed Code and the below script.

Script

https://github.com/msisgreat/js/blob/master/WebPartViewJs.txt

///////////////////////////

<script type='text/javascript'>
var context = new SP.ClientContext.get_current();
web = context.get_web();
list = web.get_lists().getByTitle('Documents');
var webPartView = list.getView('{41770fbe-48c8-4541-9629-cf6119f81c02}');
context.load(list);
context.load(webPartView);
context.executeQueryAsync(Function.createDelegate(this, addWebPartSuccess), Function.createDelegate(this, addWebPartFail));
function addWebPartSuccess(sender, args) {
if (webPartView) {
 alert('webpartview valid');
 webPartView.set_scope(2); // recursiveall
 webPartView.set_viewQuery('MyFolder');
 webPartView.update();
 context.load(webPartView);
 context.executeQueryAsync(Function.createDelegate(this, updateSuccess), Function.createDelegate(this, updateFailed));
//alert('call finished');
 }
else {
 alert('Unable to get view');
 }
}
function addWebPartFail(sender, args) {
 alert('web part failed');
}
function updateSuccess(sender, args) { alert('changed the view'); }
function updateFailed(sender, args) { alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace()); }
</script>

///////////////////////

If the below message is shown, then the view is changed.


Step 4:

Create the page to add the web part for the document library. In my case I created the page called Sub Folder View from the SitePages library

After adding the webpart below is the view

Once the page is added and selected the FolderView the webpart will show the sub folder.