Uploading files to SharePoint Online

Here are the steps to upload files to SharePoint Online using PeopleSoft's services/service operations.

I am running PeopleTools 8.61.x and there are some properties that are discussed below that are only available in the latest tools release. 

At a high level there are three steps

1. Generate Access Token

2. Using the access token generate Form Digest

3. Finally using both the access token and form digest upload file to SPO.

Service and Service Operation build

1. Created a Document Template (N_SPO_URI_TEMPLATE) with the following primitives defined as text.

TenantID, TenantName, SiteName, SPOFolder and FileName

2. Defined two non-rowset based messages - N_SPO_REQUEST and N_SPO_RESPONSE

3. Created a consumer REST service called as N_SPO.

4. Created three service operations all with REST method as POST - 

N_SPO_ACCESSTOKEN_POST, N_SPO_FORMDIGEST_POST and N_SPO_UPLOADFILES_POST

For the service operation N_SPO_ACCESSTOKEN_POST the REST base URL is 

https://accounts.accesscontrol.windows.net/ and template is {TenantID}/tokens/OAuth/2

For the service operation N_SPO_FORMDIGEST_POST the REST base URL is https:// and 

template is {TenantName}.sharepoint.com/sites/{SiteName}/_api/ContextInfo as we cannot provide any variables in the base URL.

Finally for the N_SPO_UPLOADFILES_POST service operation, the REST base URL is https:// and template is {TenantName}.sharepoint.com/sites/{SiteName}/_api/web/GetFolderByServerRelativeURL('/sites/{SiteName}/{SPOFolder}/')/Files/add(url='{FileName}',overwrite=true)

All other settings are default settings.

5. Updated the integrationGateway.properties file as follows - (this parameter may be available only in the latest tools release)

ig.UseDomainName.ExternalOperationNames=N_SPO_FORMDIGEST.v1,N_SPO_UPLOADFILES.v1

Had to provide external operational service name for these two. The first one related to access token generation worked without having to define it here. 

6. For the SPO site to which I want to upload the file, I need a Client ID and Secret, which is kind of like the user ID/password to make the connection. SharePoint admin can generate this. Value for &ApplicationID, &TenantID also provided by SharePoint admin. Value for variables like &TenantName, &SiteName, &SPOFolder derived by parsing the SPO URL to which I had to upload the file. &attachFileName is derived from the complete file path of the file to be uploaded.  

7. Now comes the peoplecode part to invoke these service operations. 

/* step 1 */

/* intialize service operation and update URL variables */

&reqMsg = CreateMessage(Operation.N_SPO_ACCESSTOKEN_POST);
&Doc_Tmpl = &reqMsg.GetURIDocument();
&COM_Tmpl = &Doc_Tmpl.DocumentElement;
&reqMsg.URIResourceIndex = 1;
&COM_Tmpl.GetPropertyByName("TenantID").Value = &TenantID;

/* this is how form-data can be provided */

&reqMsg.SegmentContentType = "application/x-www-form-urlencoded";
&bRet = &reqMsg.SetContentString("grant_type=client_credentials&client_id=" | EncodeURLForQueryString(&ClientID | "@" | &TenantID) | "&client_secret=" | EncodeURLForQueryString(&ClientSecret) | "&resource=" | EncodeURLForQueryString(&ApplicationID | "/" | &TenantName | ".sharepoint.com@" | &TenantID));

/* following HTTP property may only work in latest tools release */

&bRet = &reqMsg.IBInfo.LoadRESTHeaders();
&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Disable_URLEncodingBody", "True", %HttpProperty);

/* submit request and the parse json response */

&respMsg = %IntBroker.SyncRequest(&reqMsg);
&sResp = &respMsg.GenXMLString();

&AccessToken will have the access token received in the response. 

/* step 2 */

/* intialize service operation and update URL variables */

&reqMsg = CreateMessage(Operation.N_SPO_FORMDIGEST_POST);

&Doc_Tmpl = &reqMsg.GetURIDocument();

&COM_Tmpl = &Doc_Tmpl.DocumentElement;

&reqMsg.URIResourceIndex = 1;

&COM_Tmpl.GetPropertyByName("TenantName").Value = &TenantName;

&COM_Tmpl.GetPropertyByName("SiteName").Value = &SiteName;

/* add properties to request header, &AccessToken is from step 1 */

&bRet = &reqMsg.IBInfo.LoadRESTHeaders();

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Authorization", &AccessToken, %Header);

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Accept", "application/json;odata=nometadata", %Header);

/* submit request and the parse json response */

&respMsg = %IntBroker.SyncRequest(&reqMsg);

&sResp = &respMsg.GenXMLString();

&FormDigest will be populated form the response.

/* step 3 */

/* intialize service operation and update URL variables */

&reqMsg = CreateMessage(Operation.N_SPO_UPLOADFILES_POST);

&Doc_Tmpl = &reqMsg.GetURIDocument();

&COM_Tmpl = &Doc_Tmpl.DocumentElement;

&reqMsg.URIResourceIndex = 1;

&COM_Tmpl.GetPropertyByName("TenantName").Value = &TenantName;

&COM_Tmpl.GetPropertyByName("SiteName").Value = &SiteName;

/* if SPO folder path has space in it then it has to be encoded like &SPOFolder = EncodeURL("Shared Documents/General/SPO Test"); */

&COM_Tmpl.GetPropertyByName("SPOFolder").Value = &SPOFolder;

&COM_Tmpl.GetPropertyByName("FileName").Value = &attachFileName;

/* read the file to attach and GetBase64StringFromBinary */

&MTOMFile = GetFile("filename.xlsx", "R", %FilePath_Absolute);

If &MTOMFile.IsOpen Then

   &theBase64encodedString = &MTOMFile.GetBase64StringFromBinary();

   &MTOMFile.Close();

End-If;

/* add the bas464string to request message */

If (&reqMsg.SetContentString(&theBase64encodedString)) Then

   &reqMsg.SegmentContentType = "binary";

   &reqMsg.SegmentContentTransfer = %ContentTransfer_Binary;

End-If;

/* add properties to request header, last property is a HTTP property */

&bRet = &reqMsg.IBInfo.LoadRESTHeaders();

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("X-Request-Digest", &FormDigest, %Header);

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Authorization", &AccessToken, %Header);

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Accept", "application/json;odata=nometadata", %Header);

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", %Header);

&bRet = &reqMsg.IBInfo.IBConnectorInfo.AddConnectorProperties("Base64toBinary", "Y", %HttpProperty);

/* submit request and the parse json response */

&respMsg = %IntBroker.SyncRequest(&reqMsg);

&sResp = &respMsg.GenXMLString();

Parse the response or check response status &respMsg.HTTPResponseCode

or &respMsg.ResponseStatus to complete the processing.

Hope this helps.

Mass upload/download files

Recently I was presented with a requirement wherein the user should be able to mass upload files from their local workstation to PeopleSoft and then mass download the files from PeopleSoft to their local workstation. The files would be stored in the database record.

I am running PT 8.59.x and PeopleSoft does provide a couple of functions MAddAttachment and DetachAttachment for this exact requirement. 

The MAddAttachment function is pretty straight forward. I was able to call it via FieldChange event, where in a dialog box is presented to the user to select a single file or multiple files and the files are loaded to the URL value provided as part of the function parameter. The function also has a parameter which can limit the number of files uploaded at a time. Just like the AddAttachment function this function also converts the filenames which have special characters like a space, amersand, plus sign etc. to underscores. More info about this under the "Understanding the File Attachment Functions" and "File Name Considersations" section in Peoplebooks.

For my requirement I was displaying the files loaded via MAddAttachment in a grid on the page. Now I provided another button, and on FieldChange of this button the plan was to invoke DetachAttachment to run through the files in the grid and download them locally to the user's workstation. For some reason this does not work. The code would run through the grid but would download only the last file in the grid, no errors anywhere.  So following is what I did to get around this limitation.

I am using GetAttachment first to read through the grid rowset and download the files to a temporary location on the server. I am programmatically creating a folder structure to download the files to. Then I am using java clases to compress or zip up the files in the temporary folder. Process to compress files available here. Then I am using PutAttachment function to upload the single compressed/zip file back into the database. Finally I am calling DetachAttachment to download the compressed/zip file to the user's local workstation. As part of post-cleanup I am using DeleteAttachment to delete the compressed/zip file from the database and then using RemoveDirectory to delete the temporary location created on the sevrer to download the files to.