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:
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:
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