cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Reading files from Sharepoint

jensc
13-Aquamarine

Reading files from Sharepoint

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jensc
13-Aquamarine
(To:jensc)

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.

View solution in original post

2 REPLIES 2
jensc
13-Aquamarine
(To:jensc)

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.

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

Announcements