Vipul Kelkar - SharePoint,Office 365, Azure: September 2015

Wednesday, 23 September 2015

Export a term set to 'Importable' CSV using JavaScript in SharePoint online

It is a well known fact that we can import term set in SharePoint using a CSV file. This is particularly helpful when you are working in multiple on-premise environments/multi-tenant environment and you want to replicate a deep and complex term hierarchy everywhere. This can be very tedious if done manually. Also, you may not always have the liberty to use server side code with SharePoint online to export the term set. Here, we'll explore a way to export a term set in CSV file using JavaScript only.

Note, the implementation does not work in IE (I have tested this in IE 9) because of the way we export data to CSV from client-side. But it works very well in Chrome. I did not have much time to explore the reason for this but I plan to do so in the near future :)

The format of CSV which is acceptable during import is provided to us as a sample file in SharePoint. To see the format, navigate to Term store management and click on the Taxonomy node.

The columns in the CSV are in the following order

"Term Set Name", "Term Set Description", "LCID", "Available for Tagging", "Term Description", "Level 1 Term", "Level 2 Term"......"Level 'n' Term".

You can see from the test file to write each term, a proper hierarchy has to be maintained.

For testing purpose, I have created two term groups, one to Export a term set and one to import from CSV. The term set contains 'Regions'

get_pathOfTerm() :

Before going into the code, I want to mention this method which has made writing the hierarchy of term set to CSV so easy !! When we use the "getAllTerms()" method on a term set, the terms are returned without its hierarchy details. So if we have to write the terms according to the hierarchy, we might have to take the longer route and iterate through each term and its children. This is where "get_pathOfTerm()" comes to our rescue. This method returns the hierarchy of a particular term, with each level separated by a semi-colon ';'. What this means is, referring to the above screenshot (term set hierarchy) if I get the path of the term "New York", the value returned is

North America;USA;New York

Now, we anyway need the values as comma separated since we are writing it to a CSV. Just replace the semi-colon with comma and you have the string for the entire hierarchy of a term you want to write.

I have developed a simple SharePoint hosted app to demo this. We will provide the GUID of a term set to our app which then exports that particular term set. To get GUID of a term set, click on the term set that you want to export in Term Management and in the right pane you'll find the unique identifier at the bottom.

Import this into a term set.

Here is the full code of the functionality

	<asp:Content ContentPlaceHolderID="PlaceHolderAdditionalPageHead" runat="server">

	<script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.9.1.min.js"></script>
	<script type="text/javascript" src="/_layouts/15/sp.runtime.js"></script>
	<script type="text/javascript" src="/_layouts/15/sp.js"></script>
	<script type="text/javascript" src="/_layouts/15/sp.requestexecutor.js"></script>
	<script type="text/javascript" src="../Scripts/Exporttermset.js"></script>
	<!-- Your CSS stylesheet and JavaScript references go here -->

	</asp:Content>

	<%-- The markup and script in the following Content element will be placed in the <body> of the page --%>
	<asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">

	<label>Enter the term set ID : </label>
	<input id="inputTermSetId"></input>
	<input type="button" value="Export term set" id="btnExport" onclick="ExportTermSet()"></input>
	<a id="downloadLink" href="" download="data.csv"/>


	</asp:Content>

view raw ExportTermSet.html hosted with ❤ by GitHub

	'use strict';

	var hostWebUrl;
	var appwebUrl;
	var context;
	var taxonomySession;
	var termStore;
	var termSet;
	var terms;

	(function(){

	hostWebUrl = decodeURIComponent(GetQueryStringParameter("SPHostUrl"));
	appwebUrl = decodeURIComponent(GetQueryStringParameter("SPAppWebUrl"));

	$.getScript(hostWebUrl + "/_layouts/15/SP.RequestExecutor.js",function(){
	$.getScript(hostWebUrl + "/_layouts/15/SP.Taxonomy.js",function(){

	})
	});
	})();

	function ExportTermSet()
	{
	var termSetID = "";
	termSetID = $("#inputTermSetId").val();
	if(termSetID !== "")
	{
	context = SP.ClientContext.get_current();
	taxonomySession = SP.Taxonomy.TaxonomySession.getTaxonomySession(context);

	// Using the default term store. This can be modified to get by ID if required.
	termStore = taxonomySession.getDefaultSiteCollectionTermStore();
	// Gets the term set with the specified ID
	termSet = termStore.getTermSet(termSetID);

	terms = termSet.getAllTerms();
	context.load(termSet);
	context.load(terms);
	context.executeQueryAsync(function(){
	GenerateCSV();
	},
	function(sender,args){
	alert("An error has occurred while accessing taxonomy term store : " + args.get_message());
	})
	}
	else
	{
	alert("Enter a term set ID");
	}
	}

	function GenerateCSV()
	{
	var downloadLink = $("#downloadLink");
	var termSetName = termSet.get_name();
	var termSetDescription = termSet.get_description();
	var maxLevels = 0;
	var termValues = [];

	// For some reason the term set description had newline character appended to it which should be removed.
	termSetDescription = termSetDescription.replace(/(\r\n\|\n\|\r)/gm,"");

	var termEnumerator = terms.getEnumerator();
	while(termEnumerator.moveNext()){

	var currentTerm = termEnumerator.get_current();

	// Path returns the hierarachy of the terms separated by ";"
	var termPath = currentTerm.get_pathOfTerm();
	var termDescription = currentTerm.get_description();
	var termAvailable = currentTerm.get_isAvailableForTagging();
	var levels = termPath.split(";").length;

	// Depending on number of ';', we calculate the max depth of the term set.
	// This will be useful to decide the number of columns in the CSV
	if(levels > maxLevels)
	{
	maxLevels = levels;
	}

	// Replace the ";" character by ','. Since we are writing CSV, this will be helpful
	var strTermValues = termPath.replace(/;/g, ",");

	// Generate an array of the terms.
	termValues.push({Terms:strTermValues,
	TermDescription:termDescription,
	TermAvailable:termAvailable,
	Levels:levels});
	}

	// Sort the array based on levels. This is required to generate the CSV properly
	// to ensure say term at level 5 does not appear above term at level 3
	termValues.sort(function(a,b){
	return a.Levels - b.Levels;
	});

	var CSVFirstRow = "Term Set Name,Term Set Description,LCID,Available,Term Description";
	for(var i=1;i<maxLevels+1;i++)
	{
	CSVFirstRow += ",Level " + i + " Term";
	}

	// LCID is left blank.
	// TermValues[0] to write the first term of the term set
	var CSVSecondRow = termSetName + "," + termSetDescription + "," + "" + "," + termValues[0].TermAvailable + "," + termValues[0].TermDescription + "," + termValues[0].Terms;
	var csvContent = CSVFirstRow + "\n" + CSVSecondRow + "\n";

	// Write remaining terms starting from index 1
	for(var j=1;j<termValues.length;j++)
	{
	csvContent = csvContent + " , , ," + termValues[j].TermAvailable + "," + termValues[j].TermDescription + "," + termValues[j].Terms + "\n";
	}

	downloadLink.attr("href", 'data:Application/octet-stream,' + encodeURIComponent(csvContent))[0].click();
	alert("Term set '"+ termSetName +"' exported successfully");
	}

	function GetQueryStringParameter(parameterName)
	{
	var params = document.URL.split("?")[1].split("&");
	for(var i=0;i<params.length;i++)
	{
	var param = params[i].split("=");
	if(param[0]==parameterName)
	return param[1];
	}
	}

view raw Exporttermset.js hosted with ❤ by GitHub

I found the method to export data to CSV using JavaScript on Raymond Camden's blog.

Hope this helps !

Tuesday, 1 September 2015

Index web property bag using JavaScript object model-AngularJS in SharePoint online

SharePoint 2013 added a new capability to index the property bags. Indexing property bag through the server side object model code is pretty easy. The WEB object has a property called "IndexedPropertyKey" which is a collection of all the web properties that should be indexed. Here is a reference article . However, doing this through client object model is tricky because we do not have access to IndexedPropertyKey through CSOM. Vesa Juvonen has made our life easy by documenting a work-around to do this through CSOM.

Basically the way this works is - all the properties that need to be indexed are encoded and stored in a "vti_indexedpropertykeys" property. In a scenario where multiple properties need to be indexed, the encoded values are separated by a PIPE "|" character. In this post we are going to index the web properties using JavaScript object model on a SharePoint online site. We will develop a SharePoint hosted app to add and index a property. Here is how our application will look like.

Lets go ahead and add a couple of new properties to be indexed.

The checkbox facilitates the user to choose whether the new property should be indexed or not. As we have chosen to index the properties , the vti_indexedpropertykeys is created in the web properties with the encoded values of property keys separated by PIPE character

Once the incremental crawl completes, the web properties that we just added are indexed.

Lets go through the code

	<script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.9.1.min.js"></script>
	<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
	<script type="text/javascript" src="/_layouts/15/sp.runtime.js"></script>
	<script type="text/javascript" src="/_layouts/15/sp.js"></script>
	<script type="text/javascript" src="/_layouts/15/sp.requestexecutor.js"></script>
	<script type="text/javascript" src="../Scripts/PropertyBag.js"></script>

	<div ng-app="myApp">
	<div ng-controller="SPWebPropertiesController">
	<label>Property Key:</label>
	<input ng-model="NewPropertyKey"></input>
	<label>Property Value:</label>
	<input ng-model="NewPropertyValue"></input>
	<label><input type="checkbox" name="indexedProp" ng-model="AddToIndexedProperty">Add to Indexed property</label>
	<button ng-click="AddProperty()">Add property</button>
	<br/>
	<table border="1">
	<thead>
	<tr border="1">
	<th>Property</th>
	<th>Value</th>
	</tr>
	</thead>
	<tbody>
	<tr ng-repeat="prop in WebProperties" border="1">
	<td>{{prop.Key}}</td>
	<td>{{prop.Value}}</td>
	</tr>
	</tbody>
	</table>
	</div>
	</div>

view raw PropertyBag.html hosted with ❤ by GitHub

The AngularJS code has a controller "SPWebPropertiesController" and an AngularJS service "WebPropertiesService" which contains function to fetch and add new web properties. The function $scope.GetWebProperties uses AngularJS defer and on success, it populates the keys and values of the properties. It also checks whether the key "vti_indexedpropertykeys" exists and sets the flag $scope.vtiIndexedPropertyKeysExists accordingly.

All the AngularJS and Javascript code below is a part of single JavaScript file. It is separated here in this post and GIT for the ease of understanding.

	var myApp = angular.module("myApp",[]);
	var hostWebUrl = decodeURIComponent(getQueryStringParameter("SPHostUrl"));
	var appWebUrl = decodeURIComponent(getQueryStringParameter("SPAppWebUrl"));
	var web;
	var webProps;
	var context;
	var factory;
	var hostWebContext;


	function getQueryStringParameter(paramToRetrieve) {
	var params = document.URL.split("?")[1].split("&");
	var strParams = "";
	for (var i = 0; i < params.length; i = i + 1) {
	var singleParam = params[i].split("=");
	if (singleParam[0] == paramToRetrieve)
	return singleParam[1];
	}
	}

	function PopulateContext()
	{
	context = new SP.ClientContext(appWebUrl);
	factory = new SP.ProxyWebRequestExecutorFactory(appWebUrl);
	context.set_webRequestExecutorFactory(factory);
	hostWebContext = new SP.AppContextSite(context,hostWebUrl);
	}

	myApp.controller("SPWebPropertiesController",function($scope,WebPropertiesService){

	$scope.WebProperties=[];
	$scope.NewPropertyKey='';
	$scope.NewPropertyValue='';
	$scope.AddToIndexedProperty = false;

	//Flag to indicate whether the property "vti_indexedpropertykeys" exists in the property bag.
	$scope.vtiIndexedPropertyKeysExists = false;

	// Calls the WebPropertiesService function "FetchWebProperties". If it succeeds, populates the property keys and values.
	$scope.GetWebProperties = WebPropertiesService.FetchWebProperties($scope).then(
	function(webProps){
	var properties = webProps.get_fieldValues();
	// Push property bag keys and values
	for(var prop in properties)
	{
	$scope.WebProperties.push({Key:prop,Value:properties[prop]});
	}

	// Check if "vti_indexedpropertykeys" property exists in the property bag. If yes, set the flag
	var found = $.map($scope.WebProperties, function(val) {
	if(val.Key == 'vti_indexedpropertykeys'){
	$scope.vtiIndexedPropertyKeysExists = true;
	}
	});
	},
	function(){
	alert("Failed");
	}
	);

	// Calls the WebPropertiesService function to add a new property.
	$scope.AddProperty=function(){
	WebPropertiesService.AddNewProperty($scope);
	};
	})

view raw PropertyBag.js hosted with ❤ by GitHub

FetchWebProperties function:

This function gets all the properties from the property bag and returns it to the controller where the key and values are populated in $scope.WebProperties=[];

AddNewProperty function:

- This function adds a new property to the property bag using the set_item(Property_Key, Property_Value) function. If you want to modify the value of a property, use "webProps.set_item(Existing_Property_Key, New_Value)".
- If we have chosen to index the property using the checkbox, we must get the encoded value of the property key. This is done using the EncodePropertyKey function.
- Checks whether the key "vti_indexedpropertykeys" exists, using the flag that we have set in GetWebProperties function of the controller. If it exists, it adds the encoded property key.
- If the "vti_indexedpropertykeys" is not present, it creates this new key and then adds the encoded property key that has to be indexed.

EncodePropertyKey function :

This function converts the property key to an encoded string. Initially I observed the bytes array in C# console application and Javascript conversion and noticed that the '0's from JS conversion were missing. So I added those (line 5 in the below function)

	function EncodePropertyKey(propKey){
	var bytes = [];
	for (var i = 0; i < propKey.length; ++i) {
	bytes.push(propKey.charCodeAt(i));
	bytes.push(0);
	}
	var b64encoded = window.btoa(String.fromCharCode.apply(null, bytes));
	return b64encoded;
	}

view raw EncodePropertyKey.js hosted with ❤ by GitHub

WebPropertiesService code :

	myApp.service("WebPropertiesService",function($q){

	this.FetchWebProperties=function($scope){

	var dfd = $q.defer();

	PopulateContext();
	web = hostWebContext.get_web();
	webProps = web.get_allProperties();
	context.load(web);
	context.load(webProps);
	context.executeQueryAsync(
	function(){
	dfd.resolve(webProps);
	},
	function(sender,args){
	alert("Failed in fetching properties : " + args.get_message());
	dfd.reject(args.get_message());
	}
	);

	return dfd.promise;
	}

	this.AddNewProperty=function($scope){

	// Adds a new property or modified the value of an existing property.
	webProps.set_item($scope.NewPropertyKey,$scope.NewPropertyValue);
	if($scope.AddToIndexedProperty === true)
	{
	var encodedPropKey = EncodePropertyKey($scope.NewPropertyKey);
	if($scope.vtiIndexedPropertyKeysExists)
	{
	var indexedProperties = webProps.get_item("vti_indexedpropertykeys");
	if(indexedProperties.indexOf(encodedPropKey) >-1)
	{
	// Means the property is already indexed
	alert("This property is already indexed");
	}
	else
	{
	// Add the encoded value to the property bag vti_indexedpropertykeys
	var addEncodedKey = indexedProperties + encodedPropKey + "\|"
	webProps.set_item("vti_indexedpropertykeys",addEncodedKey);
	}
	}
	else
	{
	// vti_indexedpropertykeys does not exist. Add the new property.
	webProps.set_item("vti_indexedpropertykeys",encodedPropKey);
	}
	}
	web.update();
	context.executeQueryAsync(
	function(){
	alert("Property added. Value : " + webProps.get_item($scope.NewPropertyKey));
	},
	function(sender,args){
	alert("Failed in adding property : " + args.get_message());
	}
	);
	}

	})

view raw PropertyBag-AngularService.js hosted with ❤ by GitHub