Excel Services
This section provides the details for connecting to SharePoint Online and SharePoint On Premises.
Note: Microsoft has stated that they plan to sunset the Excel Services API for SharePoint Online in February 2022. Please consider migrating to the provider for Excel Online for future access to your SharePoint data.
Connecting to a Workbook
Regardless of which edition of SharePoint you are using, set File to the Excel workbook. This path is relative to the following properties:
Library: The Shared Documents library is used by default. You can use this property to specify another document library in your organization; for example, if you want to connect to OneDrive for Business, set this property to "Documents".
Folder: You can use this property to specify a path to a subfolder in a library. The path is relative to the library name specified in Library.
Connecting to Spreadsheet Data as Tables
The provider detects the available tables based on the available objects in the underlying API.
The APIs surface different API objects; select the API based on the organization of your spreadsheets and your SharePoint version:
OData: The OData API allows access to tables defined from Excel table objects (not ranges or spreadsheets), which you create by clicking Insert > Table in Excel. When connecting to the OData API, the provider may not return any tables if you do not have table objects defined in your workbook. Set UseRESTAPI to true to connect to spreadsheets or ranges as tables.
REST: The REST API enables access to tables defined from Excel table objects, ranges, and spreadsheets. This is the default API. Requesting a large number of rows from ranges and spreadsheets is restricted by the REST API, and the provider limits the number of rows returned to 100 by default. The provider also defaults to detecting column names from the first row; set Header to disable this.
With DefineTables additionally set, you can define tables based on ranges, using the Excel range syntax. A range that is too large will be restricted by the API.
Connecting to SharePoint Online
Set SharePointEdition to "SharePoint Online" and set the User and Password for an Azure Active Directory account.
Set the Url to a site collection to query workbooks in all nested subsites. Set the Url to a site to query workbooks in that site only.
To authenticate to SharePoint Online, set AuthScheme to the authentication type and set User and Password, if necessary.
| URL | Example URL |
|---|---|
Site | https://teams.contoso.com/teamA or https://teamA.contoso.com |
Site Collection | https://teams.contoso.com |
Windows (NTLM): This is the most common authentication type. As such, the provider is preconfigured to use NTLM as the default; simply set the Windows User and Password to connect.
Basic:With the Basic authentication method, the user account credentials are sent as plaintext. To use this authentication type, set AuthScheme to Basic and set the User and Password.
Digest:With the Digest authentication method, the user account credentials are sent as an MD5 message digest. To use this authentication type, set AuthScheme to Digest and set the User and Password.
NEGOTIATE: To authenticate with Kerberos, set AuthScheme to NEGOTIATE. Please see Using Kerberos for details on how to authenticate with Kerberos.
Forms: This allows authentication through a custom authentication method, instead of Active Directory. To use this authentication type, set AuthScheme to FORMS and set the User and Password.
Authenticating with ADFS
Set the AuthScheme to ADFS. The following connection properties need to be set:
User: Set this to the ADFS user.
Password: Set this to ADFS password for the user.
SSODomain (optional): The domain configured with the ADFS identity provider.
Below is an example connection string:
|
Authenticating with Okta
Set the AuthScheme to Okta. The following connection properties are used to connect to Okta:
User: Set this to the Okta user.
Password: Set this to Okta password for the user.
SSODomain (optional): The domain configured with the OKTA identity provider.
The following is an example connection string:
|
Authenticating with OneLogin
Set the AuthScheme to OneLogin. The following connection properties are used to connect to OneLogin:
User: Set this to the OneLogin user.
Password: Set this to OneLogin password for the user.
SSODomain (optional): The domain configured with the OneLogin identity provider.
The following is an example connection string:
|
Authenticating with PingFederate
Set the AuthScheme to PingFederate. The following connection properties are used to connect to PingFederate:
User: Set this to the PingFederate user.
Password: Set this to PingFederate password for the user.
SSODomain (optional): The domain configured with the PingFederate identity provider.
The following is an example connection string:
|
Connecting to SharePoint On Premises
Set SharePointEdition to "SharePoint OnPremise" and set the Url to your server's name or IP address. Additionally, set SharePointVersion and the authentication values.
To authenticate to SharePoint OnPremise, set AuthScheme to the authentication type and set User and Password, if necessary.
Note: When connecting to SharePoint On-Premises 2010, you must set UseRESTAPI to true.
- Authenticating with Kerberos
Please see Using Kerberos for details on how to authenticate with Kerberos.
- Authenticating with ADFS
Set the AuthScheme to ADFS. The following connection properties need to be set:
User: Set this to the ADFS user.
Password: Set this to ADFS password for the user.
SSOLoginURL: Set this to the WS-trust endpoint of the ADFS server.
The following SSOProperties are needed to authenticate to ADFS:
RelyingParty: The value of the relying party identifier on the ADFS server for Sharepoint.
Below is an example connection string:
AuthScheme=ADFS;User=ADFSUserName;Password=ADFSPassword;SSOLoginURL=https://<authority>/adfs/services/trust/2005/usernamemixed;SSO Properties ='RelyingParty=urn:sharepoint:sp2016;';
Last updated