Connecting to SharePoint Lists and Excel Services
1. Overview
This article shows you how to create a data connector for SharePoint and Microsoft 365 Lists and for SharePoint Excel Services.
2. Connect to a SharePoint List
When creating a new data connector, set the Data Provider dropdown to the SharePoint List option that corresponds with your SharePoint site as follows.
2.1. SharePoint Lists
Choose SharePoint Lists for an on-premise SharePoint server or when using basic authentication in SharePoint Online.
Choose an authentication method. You will typically need to use specified or impersonated Windows credentials, or for SharePoint Online use Online credentials (this account should not use multi-factor authentication, or you can use the SharePoint Online List provider instead).
Next, set the SharePoint Site URL.
2.2. SharePoint Online List
Set Data Provider to SharePoint Online List in Dundas BI version 23.2 or higher to use modern authentication options with SharePoint Online. This requires setup by an Office 365 admin user:
- Accessing SharePoint Online requires using a X.509 certificate to authenticate, which you will need to create first.
- From the Azure Portal, register an Azure Active Directory (AD) app. Configure API permissions to grant Sites.Read.All, and go to Certificates & secrets to upload your certificate. Additional details are available here.
You can also follow a more detailed walkthrough that includes examples for creating a self-signed certificate and registering it along with SharePoint API permissions as part of Granting access via Azure AD App-Only in the SharePoint documentation.
In the Dundas BI data connector, enter the Application ID and Directory ID which can be found on the overview page of the Azure AD App, then under Certificate upload the X.509 certificate and enter the accompanying Certificate Password used when creating it. Enter the SharePoint URL for the site you want to access lists from.
3. Connect to SharePoint Excel Services
Excel Services is a service application that enables you to load and display live Microsoft Excel workbook data from on-premise SharePoint servers.
The following walkthrough shows you how to connect to an Excel file in SharePoint using the Excel Services provider. If you are not sure whether Excel Services is enabled on your SharePoint server, contact your SharePoint administrator.
First, create a new data connector from the main menu, then set a Name for it and set the Data Provider dropdown to SharePoint Excel Services.
Choose an authentication method. You will typically need to use Specified Windows credentials, or for SharePoint Online (e.g., Office 365) use Online credentials, and then enter the credentials.
Next, set the SharePoint Site URL and Excel File Name.
Click the submit button at the bottom of the dialog to begin discovering the available data. If you encounter a connection error such as We noticed that you haven't been interacting with this workbook, so we paused your session try selecting the Relay Credentials option in your settings.
Now you can access the data in a new metric set or in a view such as a dashboard.
Locate and expand the data connector in the Explore window to see the discovered sheets and drag one to the canvas to see its data.
4. Define structure
You may want to define, or modify, the data structure for the created data connector. For example, you can change the data type of a column.
When editing the data connector, click Define structure. The steps are similar to connecting to a regular Excel file.