Microsoft Flow – Automatic Language Translations in SharePoint-based solutions

Had a quick play on the preview version of Microsoft Flow, one of the newest toys offered by Microsoft for creating automated workflows between a wide range of online apps and services. One feature interested me is the integration of Microsoft translator service which automates the translation between languages. I was thinking what kind of business requirements we can use this feature to fulfill.

My immediate idea is the auto-generation (and possibly distribution) of translated versions of an Office document (Word, Excel or PowerPoint) when it is dropped into OneDrive. I did actually give it a try, unfortunately, the translator service cannot parse the Office document format, and I can only get the plain txt files translated and generated successfully. As I believe there is really not many people using notepad to author their business documents, this attempt was failed.

As a SharePoint guy, another idea came up was to automate language translations on SharePoint based solutions. One possible business requirement I imagined out is to auto-translate service/support requests and replies between the languages used in a multinational company (assuming not every employee can speak English). In the rest of this blog post, I prototyped a simple service request app to test Microsoft Flow and the language translation feature.

The service request app is expected to work in this way:

  1. An employee from a non-English speaking country raises a service request (using his or her local language) into a SharePoint list, namely “Service Requests”.
  2. Once the request is added into the SharePoint list, a Microsoft Flow workflow (“Service Request Submission”) is triggered to translate the request “title” and “description” into English which is the language used in the enterprise services/supports department. The translated “title” and “description” fields along with other information of the service requestor are added into a separated SharePoint list, “Service tracker” (As the current version of Microsoft Flow does not support the update of a SharePoint list item yet, we have to use a separate list to store the translated fields).
  3. The enterprise services/supports department then solves the request, adds reply in the “Service tracker” list in English and marks the status of the request as “Completed”.
  4. Another Microsoft Flow workflow “Service Request Reply” monitors the update of the “Service tracker” list items, if the status of a request is changed to ‘Completed’, the Microsoft Flow workflow translates the “reply” field into the language used by the requestor and then sends an email with the reply message to the requestor.

In this example, two Microsoft Flow workflows, “Service Request Submission” and “Service Request Reply”, are created. The detailed steps to create these workflows are listed below:

“Service Request Submission” Workflow

Firstly, we add a “SharePoint online – When a new item is created” step and configure the SharePoint site url and the name of the “Service Requests” list.

Then, we add the “detect language” action to detect which language the request is using.


After that, we add the “translate text” action to translate the “title” and “description” fields into English.



Finally, we create an item in the “Service tracker” list with the translated “title” and “description” fields, and also the other information of the requestor (name, email and language) that will be used in the “Service Request Reply” workflow later.


After the “Service Request Submission” workflow is created, we can raise a request to the “Service Requests” SharePoint list, something like:


Waiting a short while, an entry is added into the “Service Tracker” list with the translated fields and the other information of the requestor.


“Service Request Reply” Workflow


The “Service Request Reply” workflow starts from the enterprise services/supports department adding the reply to the “Service tracker” list and marked the “status” field as “Completed”.


Firstly, we add the “SharePoint online – When a new item is created” step and configure the SharePoint site url and the name of the “Service Tracker” list.


We then add a condition step to check if the “status” field is marked as “Completed”.


If so, we translate the “Title” and “Reply” fields from English to the language used by the requestor.



At the end, we send an Office 365 email to the requestor with the translated reply.


After the “Service Request Reply” workflow is triggered and executed, the requestor will receive the email with the translated reply.


Implementing Similar Items Suggestion feature using SharePoint Search and AngularJS

When a user is trying to create a new discussion, idea, support request etc., a similar item may exist in the list already. To avoid duplicated entries and make users aware of the similar items, we can add a “Similar Items” panel to the item creation form which retrieves and shows the similar items based on the user’s input on the Title field. This post will suggest an approach to implement the ‘Similar Items’ panel using SharePoint Search and AngularJS.


The basic idea behind this approach is to use the keywords mentioned in the Title as the search terms and take advantage of the SharePoint Search ranking algorithm to push the most relevant items at the top of the returned search results.

To achieve more accurate results, we need remove the Stopwords from the Title, otherwise the Stopwords will mislead the search engine and return irrelevant results. Stopwords refer to the most common words in language but with no real meaning, e.g., “is”, “almost”, and “some”. In this blog post, GeekLad’s Stopwords removal script is used to remove the Stopwords from the Title sentence. More details will be provided later. After the Stopwords are removed, we will have a line of text with the keywords. Before applying this line of the text to search engine, we need add “OR” operator between each word, otherwise the search engine will try to search the content matching with the whole line of the text.

In the rest of this blog post, I will go through the steps for implementing the “Similar Items” panel.

Step 1: Configure the Search
Firstly, we need create a new Result Source to scope the search on the target list. In this post, I use the OOTB Discussion list as the example.


After created the Result Source, we need get the soruceid which will be used later when query the Search Rest API.


Step 2: Create the search term optimization service
As mentioned above, we need tidy up the Title line to remove Stopwords and add “OR” operator. As the AngularJS framework is used to implement the “Similar items” panel, an AngularJS service “SearchTermsOptimisation” is created to encapsulate the logic.

similarItemsApp.service('SearchTermsOptimisation', function() {

    this.optimiseSearchTerms = function(searchTerms) { 

    	//remove stop words
    	var searchTermsWithoutStopWords = removeStopWords(searchTerms.trim());

    	//add 'OR' between words
    	return searchTermsWithoutStopWords .replace(/ /g, ' OR ');


    The stopwords removal code below is from
    Written by GeekLad
    var removeStopWords = function(cleansed_string){

      	var x;
    	var y;
    	var word;
    	var stop_word;
    	var regex_str;
    	var regex;
    	var stop_words = new Array(

    	// Split out all the individual words in the phrase
    	words = cleansed_string.match(/[^s]+|s+[^s+]$/g)

    	// Review all the words
    	for(x=0; x < words.length; x++) {
        	// For each word, check all the stop words
        	for(y=0; y < stop_words.length; y++) {
            	// Get the current word
            	word = words[x].replace(/s+|[^a-z]+/ig, "");   // Trim the word and remove non-alpha

            	// Get the stop word
            	stop_word = stop_words[y];

            	// If the word matches the stop word, remove it from the keywords
            	if(word.toLowerCase() == stop_word) {
                	// Build the regex
                	regex_str = "^\s*"+stop_word+"\s*$";      // Only word
                	regex_str += "|^\s*"+stop_word+"\s+";     // First word
                	regex_str += "|\s+"+stop_word+"\s*$";     // Last word
                	regex_str += "|\s+"+stop_word+"\s+";      // Word somewhere in the middle
                	regex = new RegExp(regex_str, "ig");

                	// Remove the word from the keywords
                	cleansed_string = cleansed_string.replace(regex, " ");
    	return cleansed_string.replace(/^s+|s+$/g, "");



The Stopwords removal function “removeStopWords” is from GeekLad. Please refer to for details. It is pretty easy to add “OR” operator between words. We just need to trim the text line first and then do a string replacement to replace all spaces in the text line with “OR”.

Step 3: Create the search service
The search is conducted through Search REST API using JQuery Ajax call which is encapsulated in the “SearchService”.

similarItemsApp.service('SearchService', function() { = function(searchTerms) {

var deferred = $.Deferred();

url: _spPageContextInfo.webAbsoluteUrl +
method: "GET",
headers: {
"accept": "application/json; odata=verbose",

success: function (data) {

error: function (err) {

return deferred.promise();



Step 4: Create the controller
The controller “SimilarItemsController” is created with only one method “search” which firstly calls the SearchTermsOptimisation services to get the optimized search terms and then calls the SearchServices to query the relevant items using the optimized search terms.

similarItemsApp.controller('SimilarItemsController', function ($scope, SearchService, SearchTermsOptimisation) {


$ = function (searchTerms){

//call SearchTermsOptimisation service to remove Stopwords and add "OR" operator
var optimisedSearchTerms = SearchTermsOptimisation.optimiseSearchTerms(searchTerms);

//query search results through SharePoint Search Rest API
var results = data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

angular.forEach(results, function(result, key) {
var similarItem = {};
similarItem.path= result.Cells.results[2].Value result.Cells.results[3].Value
similarItem.title = result.Cells.results[4].Value;
similarItem.description = result.Cells.results[5].Value;



//TODO handling error



Step 5: Create the View
This is the view template I created for the “Similar Items” panel as an example, but you can style the “Similar Items” panel in any way you like.

<div id="lm-similaritems-app">
<div id="lm-similaritems">
<div class="lm-header">Similar Items</div>
<div class="lm-item"><a class="lm-item-line">{{item.title}}</a>
<span class="lm-item-line">Created by {{}}</span></div>

Step 6: Use the “Similar Items” panel to list form
After the step 5, the core part of the “Similar Items” panel has been done, you can decide the way to use it. In this example, I bind the panel to the OOTB New list form. The JQuery code “SPListNewFormBind.js” for the binding is shown below.


$("input[title='Subject Required Field']").keyup(function(){

var searchTerms = $("input[title='Subject Required Field']").val();

//call AngularJS Controller 'search' action

$('#lm-similaritems-app').insertAfter("input[title='Subject Required Field']");


To load the binding code and the “Similar Items” panel into the Sharepoint New list form, you can reference the binding code file “SPListNewFormBind.js” from the AngularJS view we just created and then add a content editor web part to link to the AngularJS view.

Power Query #1 – Power Query can be used as a SharePoint Metadata Viewer

Power Query is the self-service ETL tool from Microsoft, however, it can also be used as a SharePoint Metadata viewer to inspect the schema of SharePoint sites, lists, content types… What we need is to make use of the Power Query OData connection to SharePoint Rest API and use the relation drill down feature to navigate through the SharePoint objects.

For example, we can connect to a SharePoint site (“”) using the OData connection, and the Power Query will load all attributes of the site and also the links to drill down to the related Record or Table.


For example, we can drill down to the Lists table which load the metadata of all the lists in the site. One tip here is that you can select which columns of attributes to show instead of show a long list of all columns.



Then, you can further drill down to the Table or Record related to a list, e.g., the list of related Fields of the ‘Documents’ library list. In one of my previous blog post, I need to inspect the Hidden attribute of the LikedBy field and the tool I was using is SharePoint Manager, and now Power Query can be another option.


SharePoint Tips – Customising Discussion Board List JSLink

One feature on the SharePoint 2013 Discussion Board list is very confusing that the ‘likes’ count showing on the list view and the item detail page is not actually the number of likes given to the discussion item but instead the aggregated count including both the likes to the discussion item and also the likes to the replies. As you can see from the snapshot below, there is only 1 like given to the discussion item but the likes count showing as 2 which includes the 1 like to the reply.



It looks like just a small issue but causes quite confusion to users. I looked into customising the OOTB Discussion Board template to make the likes count only showing the likes to the discussion item itself. It looks a pretty straightforward job until I see the OOTB JSLink file for the Discussion Board template ‘sp.ui.discussions.js’, 3,000+ lines of code, unfriendly naming, complex methods calling chain. I decided to stop complaining the readability and maintainability of XSLT code after I see the ‘sp.ui.discussions.js’ file.

It did take me quite a bit of efforts to get my head around the code and to locate the function for rendering the likes count:


As the code above shown, the ‘DescendantLikesCount’ field instead of the ‘LikesCount’ is used to render the likes count. Double checked with SharePoint Manager, the ‘LikesCount’ is the field to store likes for discussion item itself.


After located the field to render ‘likes’ count, the solution was simple that we just need to replace the ‘DesendantLikesCount’ field with the ‘LikesCount’ field in the rendering function of ‘likes’ count. Firstly, we need create a supplemental JSLink file to overwrite the rendering function, e.g., ‘linxiao.sp.ui.discussions.ext.js’ but can be any name. Then, we need include the overwritten rendering function in the file:

SP.UI.Discussions.PostBehavior.prototype.$2B_0 = function SP_UI_Discussions_PostBehavior$$2B_0($p0, $p1, $p2, $p3) {
        var $v_0 = this.jsonItem['ItemChildCount'];

        if (SP.UI.Discussions.Helpers.isNullOrUndefinedOrEmpty($v_0)) {
            $v_0 = '0';
        $p0.write(String.format(SP.Utilities.LocUtility.getLocalizedCountValue(Strings.STS.L_SPDiscNumberOfReplies, Strings.STS.L_SPDiscNumberOfRepliesIntervals, Number.parseLocale($v_0)), String.format('<span class=\"{0}\">{1}</span>', $p3, $v_0)));
        var $v_1 = '';
        var $v_2 = '';

        if (SP.UI.Discussions.Helpers.doesSchemaContainField(this.$0_0.ListSchema, 'AverageRating')) {
            $v_1 = this.jsonItem['DescendantRatingsCount'];
            if (SP.UI.Discussions.Helpers.isNullOrUndefinedOrEmpty($v_1)) {
                $v_1 = '0';
            $v_2 = SP.Utilities.LocUtility.getLocalizedCountValue(Strings.STS.L_SPDiscNumberOfRatings, Strings.STS.L_SPDiscNumberOfRatingsIntervals, Number.parseLocale($v_1));
        else if (SP.UI.Discussions.Helpers.doesSchemaContainField(this.$0_0.ListSchema, 'LikesCount')) {
            $v_1 = this.jsonItem['LikesCount'];
            if (SP.UI.Discussions.Helpers.isNullOrUndefinedOrEmpty($v_1)) {
                $v_1 = '0';
            $v_2 = SP.Utilities.LocUtility.getLocalizedCountValue(Strings.STS.L_SPDiscNumberOfLikes, Strings.STS.L_SPDiscNumberOfLikesIntervals, Number.parseLocale($v_1));
        $p0.write(String.format($v_2, String.format('<span class=\"{0}\">{1}</span>', $p3, $v_1)));
        var $v_3 = !SP.UI.Discussions.Helpers.isNullOrUndefinedOrEmpty(this.jsonItem['BestAnswerId']);

        if ($v_3) {
            $p0.addAttribute('src', GetThemedImageUrl('spcommon.png'));

Lastly, we need edit the Discussions list web part and set the JSLink property as: sp.ui.discussions.js|{path} /linxiao.sp.ui.discussions.ext.js, and then the ‘like’ count will be rendered as we expected.



Unhidden/Export SharePoint LikedBy Field using JSOM

SharePoint 2013 allows users to ‘like’ list items, and the users who have liked an item are stored in the ‘LikedBy’ field of the list item. This field has been set as an internal hidden field out-of-box which cannot be exported to Excel or displayed on the list forms.


If we try to export the list, we can see the LikedBy field is not exported.


In one of my previous projects, I need to export the list of users who have liked an item to excel for MI reporting purpose. After inspecting the schema of the LikedBy field using SharePoint Manager, we can see that the Hidden attribute is set as TRUE.


We need to set this attribute to FALSE to make it exportable, and also we want to set the ShowInDisplayForm attribute as TRUE in order to show the list of users on the Display list form. We can take advantage of the ‘set_schemaXml’ method offered by SharePoint JSOM to set the attributes from client-side. The code is shown below:


function loadContext() {
var ctx = SP.ClientContext.get_current(),
//get the LikedBy field
field = ctx.get_web()
ctx.load(field, 'SchemaXml');
ctx.executeQueryAsync(function() {
var fieldScript = field.get_schemaXml(),

//unhide the LikedBy attribute
fieldScript = fieldScript.replace('Hidden="TRUE"', 'Hidden="FALSE" ReadOnly="TRUE" ShowInDisplayForm="TRUE"');


After running the code, we can see the LikedBy field appears on the Display list form:


Also, we are able to export the field into the excel file.


Please note the ‘ReadOnly’ attribute need to be as FALSE, otherwise the end-users will be able to edit/modify who has liked the item from UI!