Second Life of a Hungarian SharePoint Geek

March 29, 2017

Working with the REST / OData Interface from PowerShell

Filed under: OData, PowerShell, REST, SP 2013 — Tags: , , , — Peter Holpar @ 20:56

If you follow my blog you might already know that I am not a big fan of the REST / OData interface. I prefer using the client object model. However there are cases, when REST provides a simple (or even the only available) solution.

For example, we are working a lot with PowerShell. If you are working with SharePoint on the client side at a customer, and you are not allowed to install / download / copy the assemblies for the managed client object model (CSOM), you have a problem.

Some possible reasons (you should know, that the SharePoint Server 2013 Client Components SDK is available to download as an .msi, or you can get the assemblies directly from an on-premise SharePoint installation):

  • You might have no internet access, so you cannot download anything from the web.
  • If you happen to have internet access, you are typically not allowed to install such things without administrator permissions on the PC. It’s quite rare case, if you or the business user you are working with has this permission.
  • You have no direct access on the SharePoint server, so you cannot copy the assemblies from it.
  • You are not allowed to use your own memory stick (or other storage device) to copy the assemblies from it.
  • Even if there is no technical barrier, company policies might still prohibit you using external software components like the CSOM assemblies.

In this case, using the REST interface is a reasonable choice. You can have a quick overview of the REST-based list operations here.

The main questions I try to answer in this post:

  • Which object should I use to send the request?
  • How to authenticate my request?
  • How to build up the payload for the request?

First of all, I suggest you to read this post to learn some possible pitfalls when working with REST URLs from PowerShell and how to avoid them with escaping.

Reading data with the SharePoint REST interface

Reading data with a GET request

Sending a GET request for a REST-based service in PowerShell is not really a challenge, might you think, and you are right, it is really straightforward most of the cases. But take the following example, listing the Id and Title fields of items in a list:

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(‘$listTitle‘)/Items?`$select=Id,Title"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = ‘application/json;odata=verbose’

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
# ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.
#$response = $reader.ReadToEnd()
$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

$result = ConvertFrom-Json -InputObject $response
$result.d.results | select Id, Title

If you would use

$response = $reader.ReadToEnd()

instead of

$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

then you became this exception, when trying to convert the JSON response:

ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.

The reason, that the JSON response of the server contains the fields Id and ID. JSON is case-sensitive, but PowerShell is not, so it is an issue if you want to convert the JSON response to a PowerShell object. You can read more about it in this post, although I don’t like the solution proposed there. Although it really helps to avoid the error, but it uses the case insensitive replace operator instead of the case sensitive creplace, so it converts both fields into a dummy field. PowerShell seems to have no problem with the duplicated properties.

Instead of using a System.Net.WebRequest object, we can achieve a shorter version using the Invoke-RestMethod cmdlet. Note, that we don’t select and display the Id property in this case to avoid complications. See my comments about that in the next section discussing the POST request.

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(‘$listTitle‘)/Items?`$select=Title"
$headers = @{ ‘Accept’ = ‘application/json; odata=verbose’}
$result = Invoke-RestMethod -Uri $url -Method Get -Headers $headers -UseDefaultCredentials
$result.d.results | select Title

Reading data with a POST request

There are cases when you have to use the POST method instead of GET to read some data from SharePoint. For example, if you need to filter the items via a CAML query. In the following example I show you how to query the file names all documents in a library recursively that are older than a threshold value:

$listTitle = "YourDocuments"
$offsetDays = -30

$urlBase = "http://YourSharePointSite/"
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems?`$select=FileLeafRef"

$viewXml = "<View Scope=’Recursive’><ViewFields><FieldRef Name=’Created’/><FieldRef Name=’FileLeafRef’/></ViewFields><Query><Where><Lt><FieldRef Name=’Created’ /><Value Type=’DateTime’><Today OffsetDays=’$offsetDays’ /></Value></Lt></Where></Query></View>"

$queryPayload = @{ 
                   ‘query’ = @{
                          ‘__metadata’ = @{ ‘type’ = ‘SP.CamlQuery’ };                      
                          ‘ViewXml’ = $viewXml
                 } | ConvertTo-Json

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

# displaying results
$result.d.results | select FileLeafRef

Just for the case of comparison I include the same payload in JavaScript format:

var queryPayload = {
                     ‘query’ : {
‘__metadata’ : { ‘type’ : ‘SP.CamlQuery’ },
                         ‘ViewXml’ : viewXml

As you can see, these are the most relevant differences in the format we need in PowerShell:

  • We use an equal sign ( = ) instead of  ( : ) to separate the name and its value.
  • We use a semicolon ( ; ) instead of the comma ( , ) to separate object fields.
  • We need a leading at sign ( @ ) before the curly braces ( { ).

The Invoke-RestMethod tries to automatically convert the response to the corresponding object based on the content type of the response. If it is an XML response (see the authentication part above) then the result will be a XmlDocument. If it is a JSON response then the result will be a PSCustomObject representing the structure of the response. However, if the response can not be converted, it remains a single String.

For example, if we don’t limit the fields we need in response via the $select query option:

$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems"

then the response includes the fields Id and ID again. In this case we should remove one of these fields using the technique illustrated above with the simple GET request, before we try to convert the response via the ConvertFrom-Json cmdlet.

Note: If you still use PowerShell v3.0 you get this error message when you invoke Invoke-RestMethod setting the Accept header:

Invoke-RestMethod : The ‘Accept’ header must be modified using the appropriate property or method.
Parameter name: name

So if it is possible, you should consider upgrading to PowerShell v4.0. Otherwise, you can use the workaround suggested in this forum thread, where you can read more about the issue as well.

If you are not sure, which version you have, you can use $PSVersionTable.PSVersion to query the version number, or another option as suggested here.

Creating objects

In this case we send a request with the POST method to the server. The following code snippet shows, how you can create a new custom list:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘AllowContentTypes’ = $true; ‘BaseTemplate’ = 100;
                    ‘ContentTypesEnabled’ = $true; ‘Description’ = ‘Your list description’; ‘Title’ = $listTitle                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

The response we receive in the $result variable contains the properties of the list we just created. For example, the Id (GUID) of the list is available as $result.d.Id.

Updating objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to MERGE. The following code snippet shows, how to change the title of the list we created in the previous step:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘Title’ = ‘YourListNewTitle’                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’; ‘IF-MATCH’ = ‘*‘; ‘X-HTTP-Method’ = ‘MERGE’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

Deleting objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to DELETE. The following code snippet shows, how you can delete a list item:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/Items(1)"

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘IF-MATCH’ = ‘*’; ‘X-HTTP-Method’ = ‘DELETE’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Headers $headers -UseDefaultCredentials

Note: Although the documentation states, that “in the case of recyclable objects, such as lists, files, and list items, this results in a Recycle operation”, based on my tests it is false, as the objects got really deleted.

Final Note: This one applies to all of the operations discussed in the post. If the SharePoint site you are working with available via HTTPS and there is an issue with the certificate, you can turn off the certificate validation, although it is not recommended in a production environment. You should include this line in your code before making any web requests:

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

How to Create a Simple “Printer Friendly” Display Form

Filed under: JavaScript, SP 2013, SPD — Tags: , , — Peter Holpar @ 05:44

Our users needed a simply way to print items in SharePoint, that mean only item properties without any ribbon or navigation elements.

Assuming you have a list ‘YourCustomList’ available at the URL http://YourSharePoint/Lists/YourCustomList, the standard display form of a list item (in this case the one with ID 1) would be:


This page contains however the site navigation elements and the ribbon as well. Appending the query string parameter IsDlg=1 (like http://YourSharePoint/Lists/YourCustomList/DispForm.aspx?ID=1&IsDlg=1) helps to remove the navigation parts, but the ribbon remains.

Our solution to remove the ribbon was to add this very simple JavaScript block via a Script Editor Web Part to the display form page (DispForm.aspx). I suggest to insert the Script Editor Web Part after the existing List Form Web Part on the page.

function getParameterByName(name, url) {
    if (!url) url = window.location.href;
    name = name.replace(/[\[\]]/g, “\\$&”);
    var regex = new RegExp(“[?&]” + name + “(=([^&#]*)|&|#|$)”),
        results = regex.exec(url);
    if (!results) return null;
    if (!results[2]) return ”;
    return decodeURIComponent(results[2].replace(/\+/g, ” “));

if (getParameterByName(‘IsPrint’) == ‘1’) {
  var globalNavBox = document.getElementById(‘globalNavBox’);
  if (globalNavBox) { = ‘none’;

Note: You can switch the display form to page edit mode via the ToolPaneView=2 query string parameter (see more useful hints here), for example:


The main part of the solution, the getParameterByName method was borrowed from this forum thread. It helps to get a query string parameter value by its name. Using this method we check, if there is a parameter IsPrint, and if it is there having a value of 1, the we make the globalNavBox HTML element, that is actually a placeholder for the ribbon, invisible.

It means, if we call the display form by the URL http://YourSharePoint/Lists/YourCustomList/DispForm.aspx?ID=1&IsDlg=1&IsPrint=1 then there is no ribbon or other navigation element on the page. Using this URL format you can even add a custom action, for example, a new button to the ribbon or an edit control block (ECB) menu-item (see example later in the post), or refer a print form directly from a document or from an e-mail.

In the above case, the users can then print the page via right-clicking with the mouse and selecting Print… from the pop-up menu. Alternatively we could inject a Print button on the form itself. This technique will be demonstrated below.

In this case we use JQuery, and our JavaScript code is a bit more complex, so we store it into a separate file in the Site Assets library of the site, and refer only the files in the Script Editor Web Part:


Our JavaScript code (printForm.js) would be in this case:

function getParameterByName(name, url) {
    if (!url) url = window.location.href;
    name = name.replace(/[\[\]]/g, "\\$&");
    var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
        results = regex.exec(url);
    if (!results) return null;
    if (!results[2]) return ”;
    return decodeURIComponent(results[2].replace(/\+/g, " "));

var sheet = (function() {
    // Create the <style> tag
    var style = document.createElement("style");

    // Add a media (and/or media query) here if you’d like!
    style.setAttribute("media", "print")

    // WebKit hack 😦

    // Add the <style> element to the page

    return style.sheet;

$(document).ready(function() {
  if (getParameterByName(‘IsPrint’) == ‘1’) {
    sheet.insertRule("#globalNavBox { display:none; }", 0);
    sheet.insertRule("input { display:none; }", 0);

    $(‘input[value="Close"]’).closest(‘tr’).closest(‘tr’).append(‘<td class="ms-toolbar" nowrap="nowrap"><table width="100%" cellspacing="0" cellpadding="0"><tbody><tr><td width="100%" align="right" nowrap="nowrap"><input class="ms-ButtonHeightWidth" accesskey="P" onclick="window.print();return false;" type="button" value="Print"></input></td></tr></tbody></table></td><td class="ms-separator">&nbsp;</td>’);

In this case we inject a Print button dynamically and don’t hide the ribbon, but use the technique illustrated here to add CSS styles to hide UI elements (ribbon and the buttons) only in the printed version via the media attribute of the style sheet.

Note: The above code is for a SharePoint site with English UI. Since the value of the Close button is language dependent, you should change the code if you have a SharePoint site with another culture settings. For example, in a German version the JQuery selector would be:


In this case you should have to save the script using Unicode encoding instead of ANSI to prohibit the loss of special character ‘ß’.

Finally, I show you how to create a shortcut to the form in the ECB menu using SharePoint Designer (SPD).

Select your list in SPD, and from the Custom Actions menu select the List Item Menu.


Set the fields as illustrated below:


The full value of the Navigate to URL field:

javascript:OpenPopUpPageWithTitle(ctx.displayFormUrl + ‘&ID={ItemId}&IsDlg=1&IsPrint=1′, RefreshOnDialogClose, 600, 400,’Print Item’)

We use the OpenPopUpPageWithTitle method and a custom made URL to show the printer friendly display form with the necessary query string parameters. See this article on more details of the OpenPopUpPageWithTitle method.

After saving the custom action, you can test it in your list:


This is the customized form having the extra Print button on it:


And that is the outcome of the print:


March 26, 2017

Generating Pseudo GUIDs for Your Project Server Entities

Filed under: PowerShell, Project Server, Tips & Tricks — Tags: , , — Peter Holpar @ 06:24

As you might have known, since the version 2013, Project Server utilizes pseudo-GUIDs to improve Project Server performance. These ones has the format of a “classical” GUID, but actually generated sequentially. As Microsoft states in this TechNet article:

"We handle GUIDs a little better in Project Server 2013 – and in many places they are sequential GUIDs which cause less index fragmentation"

This topic is quite good described in the Project Conference 2014 presentation Project Worst Practice – Learning from other peoples mistakes by Brian Smith. See the video recording between 6:08-13:54, or the slides 10-14.

One of the main components of the pseudo-GUID generation is the NewSequentialUid method of the Microsoft.Office.Project.Server.Library.PSUtility class:

public static Guid NewSequentialUid() 

  Guid guid; 
  if (NativeMethods.UuidCreateSequential(out guid) != 0) 
    return Guid.NewGuid(); 
  byte[] b = guid.ToByteArray(); 
  Array.Reverse((Array) b, 0, 4); 
  Array.Reverse((Array) b, 4, 2); 
  Array.Reverse((Array) b, 6, 2); 
  return new Guid(b); 

So if you want to use the same kind of pseudo-GUIDs for your own custom entities you create from code, you can get the IDs by invoking the method (for example, via PowerShell). The code sample below illustrates, how to get a single ID, or a batch of  IDs (in this case, 5 of them):

# load the necessary assembly
# generate a single sequential ID
# or generate a range of sequential IDs, in this case, five of them
(1..5) | % { [Microsoft.Office.Project.Server.Library.PSUtility]::NewSequentialUid().Guid }

March 25, 2017

Microsoft.Workflow.Client.InvalidRequestException: Failed to query the OAuth S2S metadata endpoint – The remote server returned an error: (400) Bad Request

Filed under: Certificates, SP 2013, Workflow — Tags: , , — Peter Holpar @ 21:11

Recently we installed a new Workflow Manager farm (a single-server one) on the front-end server of one of our SharePoint farms.

I wanted to register the Workflow Manager for a web application in the SharePoint farm via the PowerShell cmdlet:

Register-SPWorkflowService -SPSite https://YourSharePointSite -WorkflowHostUri https://YourWorkflowManagerServer:12290 -ScopeName YourScope –Force

But I received an error like this one:

Register-SPWorkflowService : Failed to query the OAuth S2S metadata endpoint
at URI ‘https://YourSharePointSite/_layouts/15/metadata/json/1&#8217;.
Error details: ‘An error occurred while sending the request’. HTTP headers received from the server – ActivityId:
d10c4cbb-bde4-4040-b09f-1ace1491dc87. NodeId: YourWFNode. Scope: /YourScope.
Client ActivityId : b89c2ff9-8560-458e-9ea2-31ec6c8fde36.
At line:1 char:1
+ Register-SPWorkflowService -SPSite https://YourSharePointSite/&#160; -W …

In the Event Viewer (Application and Services Logs / Microsoft-Workflow / Operational) we had this error:


Failed to query the remote endpoint for the S2S metadata document. Details: System.Net.Http.HttpRequestException: An error occurred while sending the request. —> System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. —> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.
   at System.Net.TlsStream.EndWrite(IAsyncResult asyncResult)
   at System.Net.ConnectStream.WriteHeadersCallback(IAsyncResult ar)
   — End of inner exception stack trace —
   at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
   at System.Net.Http.HttpClientHandler.GetResponseCallback(IAsyncResult ar)
   — End of inner exception stack trace —

In the ULS logs we had this error message:

Microsoft.Workflow.Client.InvalidRequestException: Failed to query the OAuth S2S metadata endpoint at URI ‘https://YourSharePointSite/_layouts/15/metadata/json/1&#8217;. Error details: ‘An error occurred while sending the request.’. HTTP headers received from the server – ActivityId: d10c4cbb-bde4-4040-b09f-1ace1491dc87. NodeId: YourWFNode. Scope: /YourScope. Client ActivityId : b89c2ff9-8560-458e-9ea2-31ec6c8fde36. —> System.Net.WebException: The remote server returned an error: (400) Bad Request.     at Microsoft.Workflow.Common.AsyncResult.End[TAsyncResult](IAsyncResult result)     at Microsoft.Workflow.Client.HttpGetResponseAsyncResult`1.End(IAsyncResult result)     at Microsoft.Workflow.Client.ClientHelpers.SendRequest[T](HttpWebRequest request, T content)     — End of inner exceptio…

The SharePoint site https://YourSharePointSite and the Workflow Manager endpoint URL https://YourWorkflowManagerServer:12290 were both available without any issue (e.g. no problem with the certificate too), on both nodes (front-end and application servers) of the SharePoint farm, as well as from client computers.

The articles I found about the issue (like this one or this one) explained the problem with the reason, that the SharePoint endpoint URL (in our case ‘https://YourSharePointSite/_layouts/15/metadata/json/1‘) is not accessible, probably because of a name resolution issue. In our case that was definitely not the issue, because if I switched the SharePoint URL from HTTPS to HTTP (via changing the Alternate Access Settings for the site + bindings in IIS manager), I was able to run the registration script successfully:

Register-SPWorkflowService -SPSite http://YourSharePointSite -WorkflowHostUri https://YourWorkflowManagerServer:12290 -ScopeName YourScope –Force -AllowOAuthHttp

After switching back the URL to HTTPS we had the problem again.

My next assumption was, that the service account for the Workflow Manager does not have the root certificate of the SSL certificate under the Trusted Root Certification Authorities.

So I’ve started the Microsoft Management Console (mmc.exe) and added the Certificates snap-in for the service account of the Workflow Manager Backend service:




I found that the list of Trusted Root Certification Authorities contains the root certificate of the SSL, so it could not be a problem either.

As next step, I’ve logged in on the Workflow Manager server (that is the front-end server of the SharePoint farm) the using the Workflow Manager service account to test the connection to the SharePoint site interactively via Internet Explorer. In this case I was faced with the problem, that the SharePoint site https://YourSharePointSite has a certificate warning. As I opened the certificate for the site in Internet Explorer, I saw only the very last entry in the certificate chain (for example, the entry for YourSharePointSite), but none of the certificates above. I’ve found it either, that the account has configured not to use a proxy server. I enabled the proxy connection, then restarted Internet Explorer, and voila no more issues with the certificate. I was able to register the Workflow Manager as well. I don’t exactly know, what was the problem, but I assume, the certificate revocation list was not available without the proxy, and that prohibited the certificate validation necessary for the registration of the Workflow Manager.

March 4, 2017

How to Change the Service Account for the Workflow Manager

Filed under: SP 2013, Workflow — Tags: , — Peter Holpar @ 21:49

A few weeks ago we made a mistake when installing Workflow Manager in a new environment, as we have chosen a wrong account name as the service account for Workflow Manager.

As a first try, we simply changed the identity of the application pool assigned to the Workflow Manager (called WorkflowMgmtPool) in IIS and restarted the pool, but after the change we had an error when accessing the workflow related pages in SharePoint:

Application error when access /_layouts/15/Workflow.aspx, Error=The remote server returned an error: (500) Internal Server Error.   at Microsoft.Workflow.Common.AsyncResult.End[TAsyncResult](IAsyncResult result)     at Microsoft.Workflow.Client.HttpGetResponseAsyncResult`1.End(IAsyncResult result)     at Microsoft.Workflow.Client.ClientHelpers.SendRequest[T](HttpWebRequest request, T content)    9d19d89d-48f7-c052-732f-a59123539aa3
System.Net.WebException: The remote server returned an error: (500) Internal Server Error.    at Microsoft.Workflow.Common.AsyncResult.End[TAsyncResult](IAsyncResult result)     at Microsoft.Workflow.Client.HttpGetResponseAsyncResult`1.End(IAsyncResult result)     at Microsoft.Workflow.Client.ClientHelpers.SendRequest[T](HttpWebRequest request, T content)    9d19d89d-48f7-c052-732f-a59123539aa3

In the Workflow Manager event logs (Event Viewer/Applications and Services Logs/Microsoft-Workflow/Operational) we found this error message:

Error processing management request. Method: GET, RequestUri: https://YourSharePoint:12290/YourScope, Error: System.Security.Cryptography.CryptographicException: Keyset does not exist

   at System.Security.Cryptography.Utils.CreateProvHandle(CspParameters parameters, Boolean randomKeyContainer)
   at System.Security.Cryptography.Utils.GetKeyPairHelper(CspAlgorithmType keyType, CspParameters parameters, Boolean randomKeyContainer, Int32 dwKeySize, SafeProvHandle& safeProvHandle, SafeKeyHandle& safeKeyHandle)
   at System.Security.Cryptography.RSACryptoServiceProvider.GetKeyPair()
   at System.Security.Cryptography.X509Certificates.X509Certificate2.get_PrivateKey()
   at Microsoft.Workflow.Common.EncryptionHelper.DecryptStringWithCertificate(X509Certificate2 encryptionCertificate, String encryptedText)
   at Microsoft.Workflow.Management.WorkflowEncryptionSettings.InitializeInternal()
   at Microsoft.Workflow.Management.WorkflowServiceConfiguration.get_EncryptionSettings()
   at Microsoft.Workflow.Management.WorkflowServiceConfiguration.GetResourceManagementConnectionStringFromConfig()
   at Microsoft.Workflow.Management.WorkflowServiceConfiguration.get_ConfigProvider()
   at Microsoft.Workflow.Management.WorkflowServiceConfiguration.GetWorkflowServiceConfiguration()
   at Microsoft.Workflow.Gateway.HttpConfigurationInitializer.CreateServiceContext(String nodeId, NamespaceSender namespaceSender)
   at Microsoft.Workflow.Gateway.HttpConfigurationInitializer.EnsureInitialized(String nodeId, NamespaceSender namespaceSender)
   at Microsoft.Workflow.Gateway.HttpConfigurationInitializer.Initialize(HttpConfiguration config, String nodeId, NamespaceSender namespaceSender)
   at Microsoft.Workflow.Gateway.Global.EnsureConfigInitialized(String nodeId)
   at Microsoft.Workflow.Gateway.Global.Application_BeginRequest(Object sender, EventArgs e)


It seems the account had no permission to access a certificate or something like this, so we changed back the application pool identity an searched for a better solution.

We found a few useful resources on the web, discussing how the account change should be performed (see here, here and here).

So we run this script from Workflow Manager PowerShell console on our single-node workflow farm:

Set-SBFarm –RunAsAccount <YourDomain\UserName>
$RunAsPassword = ConvertTo-SecureString -AsPlainText -Force ‘<Password>’
Update-SBHost -RunAsPassword $RunAsPassword

As the result of the script above, the identity of the following Windows services has been changed to the account specified in the script:

  • Service Bus Gateway
  • Service Bus Message Broker
  • Service Bus Resource Provider
  • Service Bus VSS
  • Windows Fabric Host Service

The identity of the Workflow Manager Backend service was not changed, nor the application pool identity of the Workflow Manager in IIS

The script grant the following database roles in the Service Bus databases:

  • Workflow_SB_Container (role granted: ServiceBus.Operators)
  • Workflow_SB_Gateway (roles granted: SBProjectStore.Operators, ServiceBus.Operators)
  • Workflow_SB_Management (role granted: Strore.Operators)

There was however no permission granted on the following workflow-related databases:

  • Workflow_Farm
  • Workflow_Instance
  • Workflow_Resource

As a next step of the identity change (following the suggestion from one of the above referenced forum threads), we changed manually the account of the Workflow Manager Backend service, and restarted it. It caused however further problems, granting permissions for the account on the before mentioned three WF databases (WFServiceOerators role, or db_owner) did not helped either.

The symptoms we faced to were:

  • We were able to start workflow (at least, no error message at this place) from the SharePoint UI, but happened  nothing, we can not stop the workflows from the UI.
  • At the web-endpoint of the Workflow Manager (https://YourSharePoint:12290/YourScope) we had this error message:

<Error xmlns:i=""&gt;
  <Message>The data or messaging layer is unavailable. Please retry after 300 seconds.</Message> 

In the Event Viewer we had a lot of errors like:

The Workflow Manager cannot contact Service Bus service after retrying for ’28’ minutes. Please verify if the Service Bus service is up and running. The Workflow Manager failed at location ‘ServiceBusNamespaceListener.GetSessionAndStateWithRetryAsyncResult.HandleException’ due to exception: System.UnauthorizedAccessException: 40100: Unauthorized.TrackingId:b006a351-d6bc-4b4e-a178-a4a1d689fee9_GYourSharePoint_GYourSharePoint,TimeStamp:27.02.2017 11:04:31 —> System.ServiceModel.FaultException: 40100: Unauthorized.TrackingId:b006a351-d6bc-4b4e-a178-a4a1d689fee9_GYourSharePoint_GYourSharePoint,TimeStamp:27.02.2017 11:04:31


and warnings like:

Service Bus exception swallowed at location ServiceBusNamespaceListener.GetSessionAndStateWithRetryAsyncResult.HandleException. System.UnauthorizedAccessException: 40100: Unauthorized.TrackingId:c0f820e5-bc7f-4186-8d8f-41899f014c84_GYourSharePoint_GYourSharePoint,TimeStamp:27.02.2017 11:05:19 —> System.ServiceModel.FaultException: 40100: Unauthorized.TrackingId:c0f820e5-bc7f-4186-8d8f-41899f014c84_GYourSharePoint_GYourSharePoint,TimeStamp:27.02.2017 11:05:19


The few discussions related to similar problems we found on the web (like this one or this one) did not help to much, so we decided to set back the original  account of the Workflow Manager Backend service, and restarted it again. Our workflows are functioning now, but I am really keen to know, how we could change the identity of the Workflow Manager Backend service as well.

Using PowerShell and REST with Project Server (or SharePoint) for Reporting

Filed under: OData, Project Server, REST — Tags: , , — Peter Holpar @ 21:43

If you are working with Project Server or SharePoint Server, you should not ignore the potential provided by PowerShell and the REST (OData) interface to create simple reports. You should although at the same time be aware of  a few pitfalls of this combination as well.

Let’s see the next code example first. Its goal is to output the list of projects to the screen, including their Id, Name and ProjectSiteUrl properties:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&quot;

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "application/json;odata=verbose"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data
$result.d.results | select Id, Name, ProjectSiteUrl

If you test the URL http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl from the browser, you see, that all of these three properties are returned. However, if you run the above script from console, you find, that the ProjectSiteUrl column is empty for all of the projects.

If you use the ProjectData OData endpoint instead of the ProjectServer endpoint, and select the corresponding properties, all of the properties will be omitted by the script:

$url = "http://YourProjectServer/PWA/_api/Projects?$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl&quot;

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "application/json;odata=verbose"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data
$result.d.results | select ProjectId, ProjectName, ProjectWorkspaceInternalUrl

Note: If you have a localized version of Project Server, you can either use an OData query including the localized entity and property names, like:


or switch back to the English version by injecting [en-US] segment after the ProjectData endpoint:


Of course, in the first case you should change the property names used in the select statement in the PowerShell script to match the names used in the REST query.

Let’s see another example. In the next case, our goal is to create a .csv file, that one can easily import to Excel, including the name and the RBS (resource breakdown structure) of the resources.

  1. $baseUrl = "http://YourProjectServer/PWA/_api/ProjectServer&quot;
  2. $rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq 'RBS'&$expand=Entries&$select=Entries/InternalName,Entries/Value"
  3. $resourceUrl = $baseUrl + "/EnterpriseResources?$select=Name,Custom_000039b78bbe4ceb82c4fa8c0c400284"
  5. #rbs
  6. $rbsRequest = [System.Net.WebRequest]::Create($rbsUrl)
  7. $rbsRequest.UseDefaultCredentials = $true
  8. $rbsRequest.Accept = "application/json;odata=verbose"
  10. $rbsResponse = $rbsRequest.GetResponse()
  11. $rbsReader = New-Object System.IO.StreamReader $rbsResponse.GetResponseStream()
  12. $rbsData = $rbsReader.ReadToEnd()
  14. $rbsResult = ConvertFrom-Json -InputObject $rbsData
  15. $rsbEntries = $rbsResult.d.results.Entries.results
  17. #resources
  18. $resRequest = [System.Net.WebRequest]::Create($resourceUrl)
  19. $resRequest.UseDefaultCredentials = $true
  20. $resRequest.Accept = "application/json;odata=verbose"
  22. $resResponse = $resRequest.GetResponse()
  23. $resReader = New-Object System.IO.StreamReader $resResponse.GetResponseStream()
  24. $resData = $resReader.ReadToEnd()
  26. $resResult = ConvertFrom-Json -InputObject $resData
  28. $resResult.d.results | % {
  29. select -Input $_ -Prop `
  30.     @{ Name='Name'; Expression={$_.Name} },
  31.     @{ Name='RBS'; Expression={$rbs = $_.Custom_x005f_000039b78bbe4ceb82c4fa8c0c400284; If ($rbs.results -is [System.Object[]]) {$rsbEntries | ? { $_.InternalName -eq $rbs.results[0] } | % { $_.Value } } Else {''} } }
  32.     } | Export-Csv -Path ResourceRBS.csv -Delimiter ";" -Encoding UTF8 -NoTypeInformation

Note: The –NoTypeInformation switch of Export-Csv ensures that no type information would be emitted as header into the .csv file. The -Delimiter ";" and the -Encoding UTF8 settings help to produce a .csv file in a format and encoding that can be opened in Excel simply by clicking on the file.

The symptoms are similar as in the first case, only the resource name is included in the file, but the RBS value not.

I’ve included this last code sample in a code block not just because it is a bit longer as the former ones, but because I help that the highlighting helps you to understand the base problem with our scripts, even if you did not catch it at the first example. Have you recognized, that the query options ($filter, $select and $expand) have a different color, as the rest of the query text? Actually, they have the very same color as the variable names (like $baseUrl or $resRequest) in the code. It is because they are handled really as variable names. Since we used double quotes in the code to define the string literals for URLs, and it means PowerShell should parse the string and replace possible variable names with the values of the variable. As we didn’t define variables like $filter, $select or $expand, they are simply removed from the string (replaced by an empty string). See this short explanation for details.

Instead of double quotation marks we should use single quotation marks to leave the query options intact, but in this case we should escape the single quotes (using two single quotation marks) used in the REST query itself.

For example, instead of:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&quot;

we should simply use:

$url = ‘http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&#8217;

and instead of::

$rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq ‘RBS’&$expand=Entries&$select=Entries/InternalName,Entries/Value"

we should use:

$rbsUrl = $baseUrl + ‘/LookupTables?$filter=Name eq ”RBS”&$expand=Entries&$select=Entries/InternalName,Entries/Value’

Note, that the value RBS is enclosed by two single quotation marks on both sides, and not by a double quotation mark!

Alternatively, you can use the double quotation marks to define the strings for the REST queries (for example, if you still would like PowerShell to parse it from some reason), but in this case, you should escape the dollar sign in the query options to disable parsing them out from the string.

For example, instead of:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&quot;

we should simply use:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?`$select=Id,Name,ProjectSiteUrl"

and instead of::

$rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq ‘RBS’&$expand=Entries&$select=Entries/InternalName,Entries/Value"

we should use:

$rbsUrl = $baseUrl + "/LookupTables?`$filter=Name eq ‘RBS’&`$expand=Entries&`$select=Entries/InternalName,Entries/Value"

See this description for more details about PowerShell string parsing and escaping methods.

If you compare our first two examples (the one with the ProjectServer and the other one with the ProjectData endpoint) the results are different, because in the first case the ProjectSiteUrl property is not part of the standard set of properties returned by default for projects via the ProjectServer endpoint, but ProjectData returns all properties, the ProjectWorkspaceInternalUrl property too, even if it is not specified in a $select query option.

In the third case, our query should have returned the entries of the RBS lookup table, but since the query options got lost, it simply return an overview about all lookup tables.

March 3, 2017

SharePoint Designer Workflow Gets Suspended after Task Completion – How to Get Field Value from a Workflow Task via Lookup

Filed under: SP 2013, SPD, Workflow — Tags: , , — Peter Holpar @ 06:21

Nowadays we are working quite a lot with SharePoint Designer 2013 based workflows. On workflows I mean the “new”, Workflow Manager based ones.

Recently we wanted to access a workflow task field beyond the standard outcome to use its value in another part of the workflow. For example, we need the value of the Description field, as the explanation of the decision made on the form (rejection vs. approval).


To achieve that, we stored the workflow task Id in a variable called TaskID (see above), and planned to use it as a lookup value from the task list (see below). Note, that we used the ID field in the lookup list, Data Source is Assocciation: Task List, that is the standard Worklow Tasks list in our case.


The value of the TaskID variable is returned as integer:


After publishing the workflow and creating an item to test it, the workflow task was created. We entered some text in the Description field, and approved the task. We found, that the workflow gets stuck in the Suspended status. Resuming it has not helped either.


The error description we had:

RequestorId: 3c361109-ce76-de39-0000-000000000000. Details: An unhandled exception occurred during the execution of the workflow instance. Exception details: System.FormatException: Input string was not in a correct format. at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at Microsoft.Activities.Expressions.ParseNumber`1.Execute(CodeActivityContext context) at System.Activities.CodeActivity`1.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

The resources we found on the web here, here and there did not help to much, but the error message itself did.

The reason of the error was, that the TaskID (a variable of type String) we have from the Assign a task action is actually the Guid of the task item, but we wanted to use it to look up the task based on its ID field (an Integer). Of curse, the workflow engine was not able to convert the Guid to an integer value.

The correct lookup is illustrated below. We use the GUID field for as the lookup field, and TaskID is returned as a string:



With this “minor” modification the workflow runs as expected.

After we solved the problem I found that the the original requirement (getting field value from a specific workflow task as data source via lookup) was already discussed and solved earlier, see this thread and this one.

‘The URL "[url]" is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.’ Error When Changing the URL of a Web Site

Filed under: SP 2013 — Tags: — Peter Holpar @ 06:17

Recently one of our SharePoint administrators wanted to change the address of a site via Site Settings / Title, description, and logo:


He got an error with a correlation ID. Based on this ID we found this entry in the ULS logs:

<nativehr>0x80004005</nativehr><nativestack></nativestack>The URL "/Sites/SiteX" is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.

We had the same error message in the PowerShell console, when we tried to change the URL of the site from PowerShell, as described in this post:

$web = Get-SPWeb http://YourSharePointServer/Sites/SiteX
$web.ServerRelativeUrl = ‘/SiteX_New’

The same symptoms, if we try to do it as described here:

Get-SPWeb http://YourSharePointServer/Sites/SiteX | Set-SPWeb -RelativeUrl SiteX_New

This message was of course wrong and misleading, as we could access the web both from the UI and from script. As it turned out, an other error preceded the one above in the logs:

System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)     at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)     at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)     at System.Data.SqlClient.SqlDataReader.TryHasMoreResults(Boolean& moreResults)     at System.Data.SqlClient.SqlDataReader.TryNextResult(Bool…
…ean& more)     at System.Data.SqlClient.SqlDataReader.NextResult()     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock)  ClientConnectionId:71163353-b397-4ada-99fd-be1e09547586  Error Number:8152,State:13,Class:16
ExecuteQuery failed with original error 0x80131904

The real problem was a few file URLs in one of the document libraries. The length of these URLs was already originally near the limit, and after changing the site URL with a longer path name would be these new URLs beyond the limitation.

On the content database level, the properties of the documents are stored in the AllDocs table. The DirName field (nvarchar(256)) contains the full directory path, including the site structure (for example ‘Sites/SiteX/Documents/FolderA/FolderC‘). The LeafName field (nvarchar(128)) contains the file name (for example ‘DocumentZ.docx‘). It means, if a site URL is being changed, only the value of the DirName field would be changed, only in this field can be the new value truncated, if its length is beyond the 128 character limit.

You can query the files having the longest DirName from the content database via the SQL query:

  [DirName], LEN([DirName]) AS DirNameLength
  FROM [Your_Content_DB].[dbo].[AllDocs]
  WHERE DirName LIKE ‘Sites/SiteX/%’
  ORDER BY DirNameLength DESC

If you happen to need the overall path (including both DirName and LeafName), you can query it as well:

  [DirName] + ‘/’ + [LeafName] AS Path, LEN([DirName] + ‘/’ + [LeafName]) AS PathLength
  FROM [Your_Content_DB].[dbo].[AllDocs]
  WHERE DirName LIKE ‘Sites/SiteX/%’
  ORDER BY PathLength DESC

November 30, 2016

Using Edge.js as a Replacement for win32ole

Filed under: ActiveX, NodeJS, SP 2013 — Tags: , , — Peter Holpar @ 21:13

Last month I had to create a NodeJS script that invokes methods of ActiveX object. I work in a Windows-only environment, so it should be no problem. I find the win32ole package quickly. Based on its description and the samples I’ve found, it seemed to be the perfect tool for my requirements. However, as many others (see issues on GitHub, and a lot of  threads about the build problem on StackOverflow), I had issues installing the package in my environment:

OS: Windows Server 2008 R2 SP1, Windows Server 2012 R2
npm: 2.15.9
node: 4.5.0

The last two lines are from the output of the npm version command.

As far as I see, a package win32ole depends on (node-gyp) fails to build:

npm ERR! win32ole@0.1.3 install: ‘node-gyp rebuild’
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the win32ole@0.1.3 install script ‘node-gyp rebuild’.

As agape824 commented on Aug 28 2015 regarding a similar issue:

“I solved the problem
by installing node.js v0.8.18 & npm v1.4.28.
Previous erros were produced by different version of node files (eg. v8.h).”

So I’ve removed all NodeJS and npm installation on one of our systems, and installed the suggested versions. Downloaded the node-v0.8.18-x64.msi, and the right npm version was installed via the command:

npm install npm@1.4.28 -g

Now invoking the command npm version results:

node: 0.8.18
npm: 1.4.28

and we can install win32ole using:

npm install –save-dev win32ole

Having win32ole installed, we can create NodeJS scripts that interact with ActiveX object.

For example, you can get the content of a web page via the MSXML2.XMLHTTP object:

var win32ole = require(‘win32ole’);

var url = "";
var xhr = new ActiveXObject("MSXML2.XMLHTTP");"GET", url, false);

Of course, you can do it much easier and a platform-independent way using other NodeJS libraries, it is just to illustrate, how to invoke ActiveX object methods. However, you can perform other, not such trivial actions using the win32ole library as well, like interacting with the Windows application that support automation via ActiveX object (like Excel, Word, or Outlook, see the examples here), or even access the HTML DOM loaded into your Internet Explorer browser, and extract values from it.

In my case I needed Internet Explorer to perform Windows-integrated authentication against a SharePoint server. In this case, SharePoint returns an authentication ticket in the response page (in the hidden input field ‘__REQUESTDIGEST’), that one can include in subsequent requests.

var win32ole = require(‘win32ole’);
var uri = "

var ie = new ActiveXObject(‘InternetExplorer.Application’);
  // displaying the UI of IE might be useful when debugging
  //ie.Visible = true;
  while(ie.ReadyState != 4) {
  var token = ie.Document.getElementById("__REQUESTDIGEST").value;
  console.log(‘*** exception cached ***\n’ + e);

So the win32ole package would be really great, but it has not been updated in the past 4 years or so, and we don’t work with obsolete node and npm versions just to be able to use this package. Instead of that, we tried to find a replacement solution for win32ole. And I think, we’ve found something that is even better than win32ole, and it is the Edge.js package. Edge.js enables interaction between your NodeJS and .NET code in both direction, and not only on the Windows platform, as it supports Mono and CoreCLR as well. It supports PowerShell, and other languages beyond C#, like F#, Lisp or Python just to name the most important ones.

To tell the truth, creating ActiveX object and invoking their methods only a very small subset of functionality enabled by this package. Obviously, you can not create ActiveX objects on operating systems that do not support them, but it is not the limitation of the package.

After you install the Edge.js package, for example:

npm install –save-dev edge

you can create NodeJS scripts that invokes your C# code. In the C# code you can create ActiveX objects and invoke their members as well. In the following simple example create an instance of the WScript.Shell ActiveX object, and displays a greeting message via its Popup method:

var edge = require(‘edge’);

var wshShell = edge.func(function () {/*
  async (input) => { 
       dynamic wshShell = Activator.CreateInstance(Type.GetTypeFromProgID("WScript.Shell"));
       wshShell.Popup("Hello, " + input + "!");

        return string.Empty;

wshShell("world", function (error, result) {
    if (error) throw error;

Or we can re-create our win32ole example showed above using Edge.js, and read the authentication token via the HTML DOM in Internet Explorer:

var edge = require(‘edge’);

var uri = "http://YourSharePointServer&quot;;

var getToken = edge.func(function () {/*
    async (uri) => { 

            dynamic ie = Activator.CreateInstance(Type.GetTypeFromProgID("InternetExplorer.Application"));
            // if you want to see the UI (for example, when debugging)
            //ie.Visible = true;
            while (ie.ReadyState != 4)
            var token = ie.Document.getElementById("__REQUESTDIGEST").value;

        return token.ToString();

getToken(uri, function (error, result) {
    if (error) throw error;

An alternative solution to the above is to read a SharePoint web page via the MSXML2.XMLHTTP object and parse the HTML DOM via cheerio to get the hidden field that contains the request digest.

var edge = require(‘edge’);
var cheerio = require(‘cheerio’);

var uri = "http://YourSharePointServer&quot;;

var getToken = edge.func(function () {/*
    async (uri) => {

            dynamic xhr = Activator.CreateInstance(Type.GetTypeFromProgID("MSXML2.XMLHTTP"));
  "GET", uri, false);

            return xhr.responseText;

getToken(uri, function (error, result) {
    if (error) throw error;
    $ = cheerio.load(result);

I hope these scripts help other developers frustrated by the build issues of win32ole to create workarounds. I think Edge.js is a really useful NodeJS package, I am sure I will find a lot of application areas for it in the future. In contrast to win32ole, Edge.js is a living project, and that is very important to us. Many thanks to Thomas Janczuk for creating and supporting this gem! Keep up the excellent job!

Creating an AngularJS Directive for Mouse Hold

Filed under: AngularJS — Tags: — Peter Holpar @ 21:12

Most of the time we use AngularJS to create our single-page application (SPA) in SharePoint and Project Server.

Recently we had a requirement that AngularJS does not provide an out-of-the-box solution for: there is a container on the page that displays a fix number of items and two buttons (one is located above the container, the other is below the container), that should scroll the items in the container back and forth. When the user clicks the upper button, new items should appear at the bottom of the container and the top items should disappear. When the user clicks the button below, the items formerly scrolled out at the top should re-appear and the items below should disappear. The user must be able to scroll the items one-by-one. However, there is a large number of items, so clicking the buttons 20 times just to scroll 20 items down would be rather inconvenient for the users.

So we need something similar, that we already have in the scroll bar in our traditional Windows applications:

  • If the user clicks on the arrow button at the bottom of the scroll bar, and holds the mouse button in the position (there is a single mouse down event but no mouse up event), the application will scroll the content in small steps down.
  • Similarly, if the user clicks on the arrow button at the top of the scroll bar, and holds the mouse button in the position (there is a single mouse down event but no mouse up event), the application will scroll the content in small steps up.
  • If the user clicks on either of the arrow buttons, holds the mouse button in this position, but moves the mouse pointer out of the area of the arrow button (the mouse down event is raised in the area of the button, but a mouse out event is raised before the mouse up event) the content will be scrolled only while the mouse pointer is over the arrow button.
  • If the user clicks on the screen as the mouse pointer is out of the arrow button area, holds the mouse button in this position, and moves the pointer over the arrow button only later (the mouse down event is raised outside the area of the button) the content will be not scrolled.

To sum up the above rules for our case: the single mouse down event should happen while the mouse pointer is over the button, and the action performed by the application (in our case it was scrolling up / down) is repeated until either a mouse up event or a mouse out event occurs.

We decided to implement the requirements as a reusable component in AngularJS, namely a directive, that encapsulates the functionality and enables to apply it to various HTML elements declaratively.

We also wanted to provide the following parameters to our component:

  • Which action the component should repeat, similar to the other, built-in AngularJS events, like ng-click. Its value should be the name of the JavaScript function available in the scope.
  • The time interval to configure the frequency of the repetition the action. Its value should be a numeric value of the delay in milliseconds.
  • The time interval to configure the delay for starting the repetition (for example, the user has to hold the mouse button down for 1 sec. to start the repetition, but once it is started, the action is performed in every 0.2 sec.) was in our first scope of work, but it was selected as victim of  feature cutting.

We found several similar solutions on web blogs and forums, but none of them fulfilled our demands completely, or they simply just didn’t work.

Our implementation was created as an attribute-level AngularJS directive: the mandatory ‘on-mouse-hold’ attribute contains the name of the function that would be invoked as action for the mouse hold event. The optionally ‘mouse-hold-repeat’ attribute contains the delay for the repetition (in milliseconds), the default value is 0.5 sec.

Note: In this post I illustrate the usage of the directive in a non-SharePoint-specific application for those of you who are not interested in SharePoint, and to separate this piece of functionality from the other (IMHO not less interesting) SharePoint-related stuff. I plan to write a further post about using the directive in a SharePoint-specific application, namely how to load items dynamically in case of scrolling using the JavaScript client object model.

The following HTML snippet illustrates using the directive in simple case. There are two buttons having different actions and delays. In this case we simply count a numeric value up and down.

  1. <div ng-app="myApp" ng-controller="counterCtrl">
  2.     <button type="button" on-mouse-hold="countUp">Count up</button>
  3.     <div>{{counter}}</div>
  4.     <button type="button" on-mouse-hold="countDown" mouse-hold-repeat="50">Count down</button>
  5. </div>

The functionality of the AngularJS directive is implemented in the JavaScript code below:

  1. 'use strict';
  3. var myApp = angular.module('myApp', []);
  5. myApp.controller('counterCtrl', function ($scope) {
  7.     $scope.counter = 0;
  9.     $scope.countDown = function () {
  10.         $scope.counter–;
  11.     }
  13.     $scope.countUp = function () {
  14.         $scope.counter++;
  15.     }
  17. }).directive('onMouseHold', function ($parse, $interval) {
  18.     var stop;
  20.     var dirDefObj = {
  21.         restrict: 'A',
  22.         scope: { method: '&onMouseHold' },
  23.         link: function (scope, element, attrs) {
  24.             var expressionHandler = scope.method();
  25.             var actionInterval = (attrs.mouseHoldRepeat) ? attrs.mouseHoldRepeat : 500;
  27.             var startAction = function () {
  28.                 expressionHandler();
  29.                 stop = $interval(function () {
  30.                     expressionHandler();
  31.                 }, actionInterval);
  32.             };
  34.             var stopAction = function () {
  35.                 if (stop) {
  36.                     $interval.cancel(stop);
  37.                     stop = undefined;
  38.                 }
  39.             };
  41.             element.bind('mousedown', startAction);
  42.             element.bind('mouseup', stopAction);
  43.             element.bind('mouseout', stopAction);
  44.         }
  45.     };
  47.     return dirDefObj;
  48. });

If you want to test the functionality online, visit this page on jsfiddle.

Older Posts »

Create a free website or blog at