Read Sharepoint Lists

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:

Yes, I can read and write to sharepoint with the token and Palladian nodes. I’ve gone from having no methods to do this a day ago to having three methods to do it now - amazing! :slight_smile:

3 Likes

Hi @AnotherFraudUser,

thanks very much for your work.
I got the knodes running, get a connection, results and everything.
Now I just wanted to know, if there is any possibility to get the latest 5000 entries instead of the first 5000.

Thank you and Greetings

Hi @MarvinSteck,

will look into it :slight_smile:
Have to say I created it thinking - well 5000 should be enough :see_no_evil:

Will add something so you can retrieve the last 5000 / all cases.
Should be ready in the next few days - will give you an info here :+1:

3 Likes

Hi @MarvinSteck,

the sharepoint nodes are now updated (in the next few minutes) and include the following options:

Load all items in list: all items in the Sharepoint list will be retrieved (depending of your list size this might take a bit).
Item Limit: if you do not want to retrieve all items - here you can set the maximum number of items to retrieve
Loading Order: In which order the Items are retrieved (either Ascending or Descending from creation date)

grafik

Could only test the nodes with rather small lists - so would be great if you could provide feedback if this works as indended on your large list :slight_smile:

1 Like

Hi @AnotherFraudUser,

thanks for the fast response, help and implementation.
For my workflows with less then 5000 items it still works fine, but for the List with more than 5000 Items I always get the error “Execute failed: JSONObject[“d”] not found.”. Doesn’t mather if i go with “Load all items in list” or 100 items ascending/descending (tried both).

Thank you & Greetings

Marvin

1 Like