Reading files from Sharepoint

[jensc]

Hello,

I have been trying to be able to read files from a sharepoint using the sharepoint API's recently but with no luck.

Does anyone on here have any experience with this?

Mainly I'd like to be able to read excel files, but if that is not possible, reading CSV or just normal .txt files would also be fine as a work around.

I've tried several different ways to do this... like GetCSVFile and GetText, neither have worked for me.

Latest error I am facing when trying to read a .txt file using GetText is this: 

JavaException: com.thingworx.common.exceptions.InvalidRequestException: Invalid Content Type: application/octet-stream

Not sure why though as I am using the standard "application/json;odata=verbose" as requested by sharepoint in the Accept header field.

Thank you.

[jensc · Accepted solution]

Hello,

So finally managed to get it to work, both reading and writing files on sharepoint.

Here's how I did it in case someone finds this post in the future:

To read a file:

let url = "https://{site_url}/_api/web/GetFileByServerRelativeUrl('/Folder Name/{file_name}')/$value"

let header = {
    "Authorization": "your_bearer_token",
    "Accept": "application/json;odata=verbose",
    "Content-Type": "application/json;odata=verbose",
};

let params = {
   /* PUT YOUR HTTP PARAMS HERE */
};
    
let resultQuery = Resources["ContentLoaderFunctions"].LoadBinary(params);

// this is used to convert the binary result to a string, so not needed to save a file, but could be good if you need to get your data in the service.
var result = me.ConvertBlobToString({
   Blob: resultQuery /* BLOB */
});

// this saves your file to a thingworx repository of your choice.
Things["YourFileRepository"].SaveBinary({
   path: "/YourFileName.FileEnding" /* STRING */,
   content: resultQuery /* BLOB */
});

To convert the binary returned from your "LoadBinary" function (me.ConvertBlobToString above):

let blobArray = [];
for (var i = 0; i < Blob.length; i++) blobArray.push(Blob[i]);
let string = base64EncodeBytes({
	array: blobArray
});
var result = base64DecodeString(string);

Input type is of type "blob" and output is of type "string" for above service. 

To create a file on your sharepoint platform:

let url = "https://{site_url}/_api/web/GetFolderByServerRelativeUrl('/Folder Name')/Files/add(url='{file_name}',overwrite=true)";
    
let header = {
    "Authorization": "your_bearer_token",
    "Accept": "application/json;odata=verbose",
    "Content-Type": "application/json;odata=verbose",
};
    
let params = {
    /* PUT YOUR HTTP PARAMS HERE 
    header: header,
    content: <This can be a string or come from a file on your file repository>
    */
};

	/* 
    For excel files use PostBinary result will be a blob type:
    	result = Resources["ContentLoaderFunctions"].PostBinary(params);
    you can then parse that into json:
        result = me.ConvertBlobToString({
		blob: resultQuery
	});
        result = JSON.parse(string);
    For regular text files uses PostText
    	result will be a JSON
    	result = Resources["ContentLoaderFunctions"].PostText(params);
    */

Hope this helps someone else in case they are struggling with integration to sharepoint.

[VladimirRosu]

Very nice addition to the community, I highly appreciate you doing this!

[HN_10940166]

Hi @jen,

Does this method require some sort of authentication for Sharepoint organization site owner. Recently, I tried another way using Rest API service of ThingWorx and turning my Sharepoint site into an App. But when I tried to call the API, it said that I would need authentication from the organization sharepoint site admin.

[jensc]

Hello @HN_10940166,

Yes, this requires authentication (and permission) to Sharepoint.

I think though that recently (not yet implemented) Sharepoint will start doing the authentication and permissions differently from how it has been.

This is the link I used for "understanding" the permissions: Microsoft link

Basically, I gave the App full control of my sharepoint (I'm sure if you want to be more "secure", you can change these permissions to only allow the use of certain API's).

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

If you'd like, I could try to add the instructions that we have internally for getting the bearer token that is needed. But not sure if it is that that you are having issues with.

Regards,

Jens C.