Read Sharepoint Lists

Hello:
I can’t find information that allows me to configure Knime to read the content of a sharepoint list. I have been able to import files in Sharepoint, but lists are impossible. I have not found any clear example.

Thanks a lot

Hello @juanqui3C,

from my understanding you have to use KNIME REST Client Extension nodes to deal with SharePoint lists. Check these two topics:


Br,
Ivan

Hello

I appreciate the interest. I saw this information, tried it out and got error 403. I see that this is a subject about which there is no clear information for a non-technical user like me. So far, I have managed to send to PowerBI , read from Salesforce , read files in Sharepoint , and even run SAP, but this option of reading directly the sharepoint lists is not possible for me and I am forced to update the excel linked to the list and import it into the workflow.
If there was a small sample workflow it would be great, but I didn’t find anything

Regards.

Hello @juanqui3C,

think there can’t be a sample workflow which you can run as it would mean someone needs to allow public access to SharePoint?

Regarding your error 403 seems to be access forbidden? And you are getting it from GET Request node?

Additionally there is feature request to be able to read/write SharePoint lists and have given it +1 for you.

Not bad for a non-technical user :wink:

Br,
Ivan

2 Likes

Hi @ipazin

It’s strange that the system allows me to access the files of that same Sharepoint with the Microsoft Authentication (Labs) node and not its lists. Maybe if that same node came with that option it would be perfect. It is a SHP over which I have full control.
I tried Get Request and POST Request, with different combinations and none of them worked for me.
Right now, what I am thinking about is using the External Tool node to execute a powershell that can update the excel linked to that SHP list. What I want is to cross the SHP table with Salesforce and with SAP information collected also with External Tool.

Someday I will achieve it. :grinning:
Thank you very much.

1 Like

Hello @juanqui3C,

the reason why you can’t access list are cause they are not yet supported. Someone will update ticket once there is some progress with it.

Not a bad idea using External Tool. Good luck!

Br,
Ivan

1 Like

Hi Ivan!

Is there any update about this topic? I would be really helpful if could have the chance to read SP lists from Knime…

Hello @bermq,

not for now. Have added +1 on related ticket.

However just came across SharePoint related nodes from following extension:

@AnotherFraudUser maybe these nodes can read SP lists?

Br,
Ivan

2 Likes

Hi @ipazin,

currently the get sharepoint list node is missing.
But wanted to add that node this weekend.

*However the nodes are still in development - so the usebility is a bit clunky but they work :slight_smile:

2 Likes

Hello @AnotherFraudUser,

not bad :slight_smile: If I work with SharePoint would give it a go and provide feedback :smiley:

Br,
Ivan

1 Like

Hi @bermq, Hi @ipazin,

I added the “Get SP List Items” Nodes to my repository.
(i guess they will showup tomorrow for Knime 4.3)

It currently retrieves the first 5000 entries of a sharepoint list - which you can search by title.
It returns the sharepoint response as JSON (might add a direct output as table later)

You can use it as follows:

For the login you have to configure the client-id/tennant id/sharepoint url as well as die client secret:
grafik

Then define in the Get SP List Items the SharePoint Name as well as the list you want to retrieve (as well as the SP token from the first node):

The response json from the sharepoint looks something like this:

     {
       "d" : {
         "results" : [ {
           "__metadata" : {
             "id" : "sdfsdf-sdfsdfsdf-saddfdfgdfg",
             "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)",
             "etag" : "\"1\"",
             "type" : "SP.Data.ExampleListItem"
           },
           "FirstUniqueAncestorSecurableObject" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/FirstUniqueAncestorSecurableObject"
             }
           },
           "RoleAssignments" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/RoleAssignments"
             }
           },
           "AttachmentFiles" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/AttachmentFiles"
             }
           },
           "ContentType" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/ContentType"
             }
           },
           "GetDlpPolicyTip" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/GetDlpPolicyTip"
             }
           },
           "FieldValuesAsHtml" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/FieldValuesAsHtml"
             }
           },
           "FieldValuesAsText" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/FieldValuesAsText"
             }
           },
           "FieldValuesForEdit" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/FieldValuesForEdit"
             }
           },
           "File" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/File"
             }
           },
           "Folder" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/Folder"
             }
           },
           "LikedByInformation" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/LikedByInformation"
             }
           },
           "ParentList" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/ParentList"
             }
           },
           "Properties" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/Properties"
             }
           },
           "Versions" : {
             "__deferred" : {
               "uri" : "https://exampleSharepoint.sharepoint.com/sites/Test_SP_Site/_api/Web/Lists(guid'00000s-asdb-4345-2345-0123802890')/Items(1)/Versions"
             }
           },
           "FileSystemObjectType" : 0,
           "Id" : 1,
           "ServerRedirectedEmbedUri" : null,
           "ServerRedirectedEmbedUrl" : "",
           "ID" : 1,
           "ContentTypeId" : "0x002098298hsdkfhsdkfhsdkjhkahsdhasdklhasdkjashdkhkfl",
           "Title" : "Test",
           "Modified" : "2021-04-04T18:04:43Z",
           "Created" : "2021-04-04T18:04:43Z",
           "AuthorId" : 10,
           "EditorId" : 10,
           "OData__UIVersionString" : "1.0",
           "Attachments" : false,
           "GUID" : "00000s-asdb-4345-2345-0123802890",
           "ComplianceAssetId" : null,
           "Decision" : true,
           "Text1" : "AAA",
           "Text2" : "BBB",
           "Date" : "2021-04-13T22:00:00Z"
         }
     	]
       }
     }

Use Json-Path to retrieve the information you need.
->you could also get the download information if you need pictures or files from the lists
Here I only need the normal column information

Result will then look like this:
grafik

Example List in SharePoint was:

The Nodes use the SharePoint Rest v1 API - here you have to create a client in your SharePoint and give it the needed authorizations.
You can do that from the following pages in your sharepoint:
_layouts/15/appregnew.aspx

and give the needed authorization to the app:
_layouts/15/appinv.aspx

Detailed Example how to create the client information (as well as how the get requests behind the node works):

As well as more information from MS:

Source Code for the nodes can be found here (but basically they just execute the needed get/post requests):

Not the easiest to use - but after the client setup in sharepoint it at least works - and is a bit easier then creating the Get/Post requests manually

4 Likes

*you could also just look into the get request I create in the two nodes and then do it yourself with a get/post node or two java snippets.
Basically most of the work is creating the correct header and form data structure :slight_smile:

1 Like

Hi all, @AnotherFraudUser,
Is there any way to expose the SP access token from the Get SP AccessToken node as a variable for use in other nodes, e.g, a POST Request node? I have a working POST request to generate a SP access token in Postman but can’t seem to get it to work successfully in Knime, so I thought about the Get SP AccessToken as a workaround.
A perhaps related issue to not being able to get the POST request to generate a SP token is that when I try to use the GETSPListItems node as shown in the workflow above it throws an error: {“error_description”:“Exception of type ‘Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException’ was thrown.”}
Does anyone have an idea about how to solve either of these issues, or preferably both? :slight_smile:

Sure i can put in an option to give the token as a string instead of a credential variable :+1:

Also if you can share an example workflow with your post request try - I can check if I can see why it does not work :slight_smile:

"Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException’ was thrown.”
I do not know this error :thinking: - will check if i can find something out about it :slight_smile:
… does maybe this help with the error? AudienceUriValidationFailedException SharePoint token error?
Else could you maybe share the used configuration of the node as an example (just change the login/token/url information to something else - I just need to see the general format of the tokens/login urls)…It does sound somewhat the the tennant or path is not in the expected format :thinking:

Thanks, much appreciated!

Screenshots of the POST request node config:



1 Like

Hi @arrtee,

okay that looks fine to me…
However I also did not manage to get it to work with the Post Node either :frowning:
Not sure how the correct format for the constant body should be…or if it is just not possible to add form data there :frowning:

Simple Java Code with java snippet nodes works e.g.:

	    HttpPost post = new HttpPost(url);
	    post.setHeader("Content-Type", "application/x-www-form-urlencoded");

	    /* Adding URL Parameters */
	    List<NameValuePair> urlParameters = new ArrayList<NameValuePair>();
	    urlParameters.add(new BasicNameValuePair("grant_type", "client_credentials"));
	    urlParameters.add(new BasicNameValuePair("client_id", clientId));
	    urlParameters.add(new BasicNameValuePair("client_secret", m_clientSecret.getPassword()));
	    urlParameters.add(new BasicNameValuePair("resource", resource));
	    
	    post.setEntity(new UrlEncodedFormEntity(urlParameters));

	    HttpResponse response = client.execute(post);


        
	    String json_string = EntityUtils.toString(response.getEntity());
	    String accessToken = null;
	    
	    
        if (response.getStatusLine().getStatusCode()==200)
        {
        	
        	JSONObject temp1 = new JSONObject(json_string);  
    	    accessToken = temp1.get("access_token").toString();
        }	

As well as with the Palladian Web Nodes:

grafik

As well I added a new node to return the Token as a String ( Get Rest Access Token As String Node).
It does the same as the another node and outputs additionally a string variable for the token.
The Update should be availible in a few minutes.

Regarding the “Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException” could you check what part of the sharepoint URL you are using for the parameters:

So if your List is on the first lvl in the BFM_Test sharepoint for the group.sharepoint.com site:
Url in Browser something like: https://group.sharepoint.com/sites/BFM_Test/Lists/Test/AllItems.aspx

Then your configuration of the GETSPListItems node should look like this:
grafik

1 Like

Thanks @AnotherFraudUser ! Can’t see the new node yet but I’m sure it’ll appear shortly. In the meantime, the Palladian nodes worked a treat and I’m really grateful for that. I think the problem with the POST request node does lie in how form data is handled (or not handled, as may be the case).

For the AudienceUri error, my URL in the browser looks like this:

https://group.sharepoint.com/sites/mysite/Lists/My%20List/AllItems.aspx

and the node config:

image

I’ve tried with a space and with “%20” in the list title but neither seem to work.

1 Like

Hi @arrtee,

yes - I think for most more complicated cases the palladian nodes work was better (but might be a problem if you want to run the workflow on a knime server)

did you update the extension to the latest version?
Could you try to start knime with the - clean option? :slight_smile:

Just put -clean at the top in the knime.ini
grafik

Your configuration of the GetSPList Nodes looks correct :see_no_evil:
Will try to look further why you get the error (but quite hard too do that without getting the error myself :frowning: )

2 Likes

-clean did the trick :slight_smile: Can see the new node and it works like a charm. Great work!

2 Likes

Great! :slight_smile:
So now you are able to get your list extracted - right? Token with Palladian/the new node and get lists through a normal post request? :partying_face:

Will still look into the other error so it might help future sharepoint knimers to extract lists without this trouble :see_no_evil: