Given source and target systems within a company’s intranet. The source system is a SharePoint server, though which K2 is surfaced. The target system is also a SharePoint server, through which Microsoft Project Online is surfaced. The challenge: to access the data on the target system through the use of REST services only — without a JSON descriptor.

What follows is a detailed description of my work to achieve that goal. The audience was the business people on my client team. They’re not technicians, but I wanted to give them an appreciation for the effort involved. As for the goal, well, I haven’t yet achieved it, and I think it’s fair to say I may not… but not for lack of trying.

background

I had originally planned to access the target data via a database connection between the source K2 blackpearl ecosystem and specific objects in the target system’s SQL Server databases. But accessing the target database would jeopardize the company’s ability to receive extended Microsoft support. My only option, then, was to use the Project Online REST API to access the data we require.

About REST

Representational State Transfer (REST) allows a representation of data to be retrieved and manipulated. It is a representation of the data in the sense that the data is provided using a system of notation. It should not be confused with accessing databases.

The REST application programming interface (API) may allow for information to be selected, updated, created, and deleted, depending on how the API was implemented. (This was an important note: Microsoft may or may not have implemented each of those methods for Project Online. Additionally, there may be controls in place to allow local administrators to permit or decline access to certain methods. I hadn’t yet found a way to tell.)

Options

As I see it, we have two options for achieving our goal. Option 1 is to take advantage of the built-in REST Service Object. Option 2 is to attempt to retrieve the data on a page-by-page basis directly from the SmartForms via client-side programming.

K2 REST Service Object

K2 has an OOTB capability to interface with external REST services. Properly configured, the REST Service Object will examine the properties and available methods of the external REST service and automatically create SmartObjects for method execution against the target service (recall my note above). This option offers the advantages of all configuration being handled through K2, and, at the business layer, data is accessed no differently than it would be were the data accessed directly from a relational database system.

The K2 REST Service Object examines the properties and methods of the target service using a descriptor file. This is a static file, residing on the K2 server. I have not yet discovered a means to locate or to generate such a file describing the Project Online REST objects and methods.

Retrieve Project Online Data Directly via Client-Side Programming

The second option involves writing client-side code to access the target API, instead of letting K2 “do the heavy lifting.” This option has several challenges, which are listed below. Unless a descriptor file from the previous option can be found or generated, this is the option that will be pursued.

The Challenge

To achieve our goal of accessing and manipulating target server data using REST services called from client-side code, each of the following requirements must be satisfied:

1. Construct REST queries using a client-side programming language which may be executed from within K2 SmartForms
2. Because the K2 SmartForms are surfaced through SharePoint, these REST queries must use the SharePoint Client-Side Object Model (CSOM) and the SharePoint Web Proxy
3. Because the K2 SmartForms are not located on the SharePoint server, the REST queries must include authentication/authorization to use the SharePoint Web Proxy
4. The REST queries must include authentication/authorization to access the Project Online server, to permit the methods to execute.

Discussion: “Schrödinger’s Code”

The greatest obstacle to our success using the client-side code method is SharePoint. The source application is created in K2 and surfaced through SharePoint using an app. The app allows for integration of a kind between the SharePoint server and the K2 server. The client-side code to access Project Online is written into a Web Control on the K2 SmartForm, meaning that code lives outside of SharePoint, despite being displayed in SharePoint. Because of this duality, our code must both conform to the SharePoint CSOM (because it’s “in” SharePoint) and be authorized to access the SharePoint Web Proxy (because it lives outside of SharePoint).

We’ve also the target services to consider. Project Online is also surfaced through SharePoint. Because our request is coming from a server outside of the Project Online server, our REST call must also include authentication/ authorization for Project Online.

Current State

SharePoint’s CSOM was implemented partially for security reasons. Any JavaScript – a natural choice of language for client-side code – introduced onto a SharePoint page from an external source (like the K2 server) will not work; even the simplest of scripts that don’t involve SharePoint at all are suppressed.

Fortunately, jQuery, which is a specialized JavaScript library, will work. I found example code for accessing the SharePoint Web Proxy in JavaScript – intended as part of a SharePoint plug-in – and used it as the basis for a standalone jQuery script. I can execute this modified code on a K2 SmartForm, and have results returned to a field on the form. Presently, most of the conditions enumerated above are satisfied; all that remains are the means of authenticating to the “source” SharePoint server and the “target” Project Online server.

Once I have a token for the “source” server SharePoint, I should be able to perform REST API calls to public resources (websites open to the public, like weather.com) and have results returned. (Without the token, the code returns an error result.)

Once I have tokens for both the “source” and “target” SharePoint servers, I should be able to perform queries against the Project Online server. These means of authentication and authorization will be required for each of our environments.

I continue to pursue Option 1 as I can. I just got the Project 2013 SDK installed earlier this week; I’m hopeful it contains, if not a descriptor, then something I can use as a the basis for a descriptor that will meet our requirements.

  

I’ll follow up on this as I can. Getting as far as I have has been three weeks of absolute struggle — either the coding trial-and-error kind, or the nobody-answers-their-g-d-email kind, or both. I’ve spent nights without restful sleep because I’d been DREAMING about the ajax code — and not in a good way.

I’m being made to give up on connecting to the target system for now, and it rankles, because I’ve fought so hard and come so very close… but, I’m dependent on the SharePoint team for help, and I can’t generate these tokens by myself, so I’m stuck.