Stitching PeopleSoft and SharePoint

We often need to integrate legacy solutions with more modern counterparts. This gets particularly tricky for systems like PeopleSoft as they live in their own tech bubbles, which are usually decades old despite the attempts of the vendor's to maintain and make them current. This integration is not the easiest journey.

For our solution, we needed to originate file attachments in PeopleSoft and make SharePoint the final destination. SharePoint should also contain some metadata from PeopleSoft, to organize data filtering and grouping in SharePoint. While there are many options on how this can be built, the most simple is to leverage JavaScript and SharePoint REST API.

One encountered roadblock was user authentication with SharePoint. There were two options: Graph API or SharePoint OAuth API. Graph API is newer and may be a better fit when using SharePoint in a cloud environment, however not all API methods are available there. If you have an on-prem install of SharePoint, then OAuth API is the best option.

There is no magic behind the REST API's. Microsoft provides well documented API resources (https://docs.microsoft.com/en-us/archive/msdn-magazine/2013/may/sharepoint-2013-understanding-and-using-the-sharepoint-2013-rest-interface). Although it is relatively difficult to implement in PeopleSoft. At this time we are running PeopleSoft 8.55, and there is limited support for both the RESTful services and OAuth.

To overcome this problem, I built a very simple solution delegating most of the OAuth HTTP functionality to Java (https://www.cedarhillsgroup.com/knowledge-base/kbarticles/using-apache-httpclient-in-peoplecode/. As an overview, you must obtain two data items before you can make SharePoint calls: bearer token and digest. Once you get both - you can generate HTTP calls either in JavaScript or PeopleCode. Sequence diagram below shows how we built upload functionality:

There is a bit of setup that is necessary on the SharePoint side, as we need the following configuration for the REST calls: client ID, client secret, principal and realm. For more details visit https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/authorization-code-oauth-flow-for-sharepoint-add-ins

In the steps below, $SP_SITE variable refers to the base SharePoint Site URL.

1. Client ID and Client Secret are generated by SharePoint when you register an app. They should be kept private in your application and should not be exposed to the public as it would be a security risk. The values can be added by accessing $SP_SITE/_layouts/15/appregnew.aspx

Please note that for this project, we will not use App Domain / Redirect URL so they can be set to any values.

2. Principal is an internal SharePoint ID and should be set to 00000003-0000-0ff1-ce00-000000000000

3. Grant permissions to the app by navigating to $SP_SITE/_layouts/15/appinv.aspx

<AppPermissionRequests AllowAppOnlyPolicy="true"><AppPermissionRequest
Scope="http://sharepoint/content/sitecollection/web"
Right="Read"/></AppPermissionRequests>

Realm ID can be the part coming after the @ sign in the app identifier.

If you need another reference for this setup, please take a look at https://www.anexinet.com/blog/getting-an-access-token-for-sharepoint-online/

Once client ID, client secret, principal and realm are obtained, we need to take care of PeopleSoft-specific implementation details. We use direct Java HTTP calls to obtain both the digest and bearers in OAuthRequest class:

First, generate the configuration JSON:

{
"OAuth.SP.clientId": "VALUE FROM STEP 1",
"OAuth.SP.clientSecret": "VALUE FROM STEP 2",
"OAuth.SP.principal": "00000003-0000-0ff1-ce00-000000000000",
"OAuth.SP.targetHost": "$SP_HOST.sharepoint.com",
"OAuth.SP.realm": "VALUE FROM STEP 3",
"OAuth.SP.targetUrl": "https://accounts.accesscontrol.windows.net/REALM FROM STEP 3/tokens/OAuth/2",

"OAuth.SP.siteUri": "SITE URL"
}

Complete JSON should look similar to this:

{
"OAuth.SP.clientId": "9ca26b53-4f73-5593-923e-e2ef35ec621f",
"OAuth.SP.clientSecret": "JfsnLde9wQQiq12x0ivg0a4n5sBjCTTDsefE8=",
"OAuth.SP.principal": "00000003-0000-0ff1-ce00-000000000000",
"OAuth.SP.targetHost": "mysite.sharepoint.com",
"OAuth.SP.realm": "333429234-b439-8c25-dd2ad28cd496f4772",
"OAuth.SP.targetUrl": "https://accounts.accesscontrol.windows.net/333429234-b439-8c25-dd2ad28cd496f4772/tokens/OAuth/2",

"OAuth.SP.siteUri": "https://mysite.sharepoint.com/sites/NicksSite"
}

Once this config is ready, it can be added to the OAuthRequest class, and you can test it in PSUnit (https://github.com/nvg/psdev/blob/master/sharepoint/OAuthRequestTest.pc):

Local PSDEV:O365:OAuthRequest &req = create PSDEV:O365:OAuthRequest();
&req.readConfig(0, 0);
Local string &token = &req.getBearer();
Local string &digest = &req.getDigest(&token);

In order to obtain token & digest, all that is necessary is to instantiate the class, and then use the corresponding methods. Sample run of this test is included below:

Source code for the classes can be found here https://github.com/nvg/psdev/tree/master/sharepoint

Once you have those items in place, you can either develop additional SharePoint REST API calls directly in PeopleCode, or add JavaScript to the page to make request through the browser. Here is an example IScript used to generate JavaScript content:

PSDEV_O365 JavaScript might be similar to the one below:

For a complete documentation, please see https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-the-sharepoint-javascript-apis-to-work-with-sharepoint-data

This should be a good starter for SharePoint and PeopleSoft integration. Ping me if more details are need.