SharePoint Online List Reader

This node reads a SharePoint Online list and outputs it as a KNIME table.

SharePoint Online list column type support

You can select to which KNIME types the SharePoint Online list column types should be transformed. The following describes the extend of the support for the different column types.
Fully supported column types:

Single line of text
Choice
Number
Yes/No
Date and time

Partially supported column types:

Multiple lines of text will contain HTML tags if the Use enhanced rich text option is enabled for the column in SharePoint.
Calculated columns are always Strings.
Currency columns are always Doubles. Thus the currency information is not displayed.
Person columns return a row ID in the "User Information List" (for more details see below).
Lookup columns return a row ID in linked list (for more details see below).

Unsupported column types:
For all unsupported column types the node will return the raw JSON value as String. The String can be transformed into the JSON type in the Transformation tab if the KNIME JSON Processing extension is installed.

Location
Hyperlink
Image columns are encoded as a JSON string containing JSON so you have to convert twice to JSON.
Task Outcome
External Data
Managed Metadata

Working with Person and Lookup columns

Person and Lookup columns reference data from other SharePoint Online lists. Instead of containing the value in the other list they store the value of the "ID" column of the row in that list. To be able to access the content you can use another SharePoint Online List Reader to read the lookup list and than use the Joiner node to join both lists using the "ID" column of the lookup list.

If you are dealing with a Lookup column, you can get the name of the column you are trying to join from the settings of the Lookup column (which can for example be found in the old web interface). The name of any additional columns that are looked up can be found in the table specification as names of those columns.

Person columns reference entries in the so called "User Information List" (with the internal name "users"). This is a hidden system list in the Root of each SharePoint site. To find it, you must check the "Show system lists" option in the SharePoint Online List Reader node. The name may be localized to your language so you should search for the internal name.

Generated Columns

Apart from the columns you define in a SharePoint Online list there are some which are present in each list e.g. ID. In addition SharePoint generates columns for some specific column types such as Location. The Microsoft API does not provide information about these columns or any layout information. To deselect unwanted columns and reorder the retained columns go to the "Transformation" tab.

Options

Settings

SharePoint site

There are three ways to specify SharePoint site:

Root site:Connect to the root site of the domain of the current user (see "Microsoft Connection" input port).
Web URL:Specify the web URL of a SharePoint site, for example https://mycompany.sharepoint.com.
Group site: Connect to the team site of a particular Office 365 group.

Subsite

If checked, then connect to a (nested) subsite of the SharePoint site specified above. Use the "Refresh" button to list all available subsites of the chosen SharePoint site. Note that this allows you only to access the document libraries of the subsite, not those of the parent site(s).

SharePoint list

Here you can select the list you want to read. The drop down menu shows the display name of the list and the internal name it in parentheses.

Show system lists

If checked, the drop down will also contain hidden system lists. This option must be checked if you want to access the "User Information List".

Transformation

Transformations: This tab displays every column as a row in a table that allows modifying the structure of the output table. It supports reordering, filtering, and renaming columns. It is also possible to change the type of the columns. Reordering is done via drag-and-drop. Just drag a column to the position it should have in the output table. Note that the positions of columns are reset in the dialog if a new file or folder is selected. Whether and where to add unknown columns during execution is specified via the special row <any unknown new column>. It is also possible to select the type, new columns should be converted to. Note that the node will fail if this conversion is not possible e.g. if the selected type is Integer but the new column is of type Double.
Reset order: Resets the order of columns to the order in the input file/folder.
Reset filter: Clicking this button will reset the filters i.e. all columns will be included.
Reset names: Resets the names to the names that are read from file or created if the file/folder doesn't contain column names.
Reset types: Resets the output types to the default types guessed from the input file/folder.
Reset all: Resets all transformations.
Enforce types: Controls how columns whose type changes are dealt with. If selected, we attempt to map to the KNIME type you configured and fail if that's not possible. If unchecked, the KNIME type corresponding to the new type is used.

Advanced

Connection timeout in seconds: Timeout in seconds to establish a connection or 0 for an infinite timeout.
Read timeout in seconds: Timeout in seconds to read data from an established connection or 0 for an infinite timeout.

Limit Rows

Skip first data rows: If enabled, the specified number of valid data rows are skipped.
Limit data rows: If enabled, only the specified number of data rows are read.

Input Ports

: A JWT credential as provided by the Microsoft Authenticator node.

Output Ports

: Read SharePoint list in a table

Popular Predecessors

Popular Successors

Views

This node has no views

Workflows

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.

Installation

To use this node in KNIME, install the extension KNIME SharePoint List from the below update site following our NodePit Product and Node Installation Guide:

v5.3

A zipped version of the software site can be downloaded here.

Plugin provider: KNIME AG, Zurich, Switzerland

Plugin version: 5.3.3.v202410291554

On NodePit since: 2024-07-09

Last update: 2024-11-15

Tags: Streamable

KNIME versions: Since v4.5

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!