Read Sharepoint Lists

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

Hi @MarvinSteck,

thanks for testing :+1:
Will create a list with more than 5000 items an test where the problem is.
(takes quite long to add hundred of items to a sharepoint list :see_no_evil: not sure if there is some shortcut :thinking:)

Hopefull later today an update will be ready.

Hi @MarvinSteck,

found and fixed the issue - the update is availible :slight_smile:
(hopefully this time it also works for you)
Tested with two different list (5008 and 6300 items).

Problem was that the API selection is restricted from 5000 onwards (or whatever limit is set in the sharepoint itself). If the sharepoint item limit is reached then I cannot sort by non indexed fields. (in my case creation date)
I changed the sorting field to “ID” - which seem to work for both lists I tested (and should be similiar to creation date)

2 Likes

HI @AnotherFraudUser,

works perfekt now. I’m able to get a number of rows either ascending or descending and i can get all 11500 rows (takes some time though :wink: ).

Thank you very much & Greetings

3 Likes

Hi @MarvinSteck,

that sounds great! :partying_face:

Hi @AnotherFraudUser,

I just tried to run a workflow with the Get SP AccessToken as String node on KNIME server but it failed. The error message is “MISSING Get SP AccessToken as String 0:937 - WARNING: Node can’t be executed - Node “Get SP AccessToken as String” not available from extension “AF Utility Nodes” (provided by “AnotherFraudExcel”; plugin “org.AnotherFraudExcel.PasswordManager” is not installed)”. This seems related to the excel password manager node so I’m not clear why it should prevent the SP node from working. Can you help with this please?
Thanks in advance.

Hi @arrtee,

could you show your workflow (just a simple screenshot of the setup) as well as the settings of the SharePoint Access Token Node?
(just hide all confidential information)

Can you try to update the extension to the newest version?
The sharepoint nodes as well as the Excel Nodes are included in the same extension (so i think the error message about the excel passwort manager is a bit misleading)

1 Like

Hi all,

just wanted to inform you that with 4.5 we introduced the SharePoint List Reader which can be used with the Microsoft Authentication Node. @AnotherFraudUser we really appreciate the efforts for the open source community by providing such nodes and we would be more than happy if we could get feedback from your side, since you seem to have some experience with Sharepoint lists :slight_smile:

4 Likes

Thanks for your reply. The error message is different when I right click on the workflow and try to execute manually as opposed to scheduling. The error message when executing manually says that the extension is missing. This is odd because we’ve just updated to Server 4.14.1 this week so we’ll check the install and let you know.

@AnotherFraudUser we’ve updated the extension but still getting the same error message:
image
Seems to suggest that the node is missing from the extension?

Hi @arrtee,

Do you get the same error message when you create a new knime workflow with the sharepoint connector node?

the error still sounds like there is a problem with the installation of the extension :thinking:

Could you try to start the local knime with the “clean” option in the knime.ini?

47b84acf6a73991d380e986ca248c932b335e2ed(1)

I started with the -clean in the .ini file and created a fresh workflow with only the Get SP AccessToken as String node. The node works fine locally but on our KNIME server:
image
:frowning: I’ll create a support ticket for this issue because perhaps there’s something wrong with our KNIME server installation.

Hi again @AnotherFraudUser,
Out of interest/and as a sort of backup plan, I was trying your java snippet above in post 16 Read Sharepoint Lists - #16 by AnotherFraudUser. I figured out most of the import statements but I couldn’t find how to resolve client.execute(post). Has .execute been deprecated? If so, what would I replace this with? I also didn’t understand why m_clientSecret.getPassword() is used or what it means. Has getPassword also been deprecated? I’m a total java novice btw, so please excuse my naive questions.

Hi @arrtee,

if it works locally but not on the server maybe two additional questions.
You added the extension to the knime server executor as well - right?
Could you start the server executor with the clean option as well (I guess you already did but just to make sure you did it on the server as well)

For just doing it in the java snippet node you can ignore most statements starting with “m_”
e.g. m_clientsecret reference the input field in the node where you input the clientsecret in the UI - you can just enter the secret direcly in the snippet instead e.g.:

urlParameters.add(new BasicNameValuePair(“client_secret”, “yourclientSecret” ));

I think you will need to set the used bundles (Apache HTTP) in the java snippet for it to work. It should be availible in the bundle selection of the node.
I could create an example workflow with just java snippets on the weekend (i think I wont come to this before :see_no_evil:)

1 Like

We’re rechecking the KNIME server installation.

As for the java snippet method, thank you, that’s very kind. I identified these bundles so far but couldn’t find one that would resolve client.execute():
image

1 Like

Hi @arrtee,

attached the working example workflow with javasnippets.
Some of the node options are just hard-coded text in the snippets e.g. proxy settings
grafik
All general options are given through string inputs at the workflow beginning

This should run with KNIME 4.5 (for previous versions you would have to use older bundles of the httpclient :slight_smile:
grafik

SharePoint List.knwf (21.6 KB)

I hope the example is somewhat readable even without much java knowledge

1 Like