SharePoint Excel Services Connection

See the Connector Marketplace topic. Please request your administrator to start a trial or subscribe to the Premium SharePoint Excel Services connector.

SharePoint Excel Services is a component of SharePoint that allows for the sharing and management of Excel workbooks within the SharePoint environment.

This topic describes how to authenticate to SharePoint Excel Services and configure any necessary connection properties in the SharePoint Excel Services connection connector.

Connection Configuration #

Each connection property available in the SharePoint Excel Services connector is explained below.

Connection Name #

The name of the connection to be created should be provided. This is the name that will display on the list of available connections.

Auth Scheme #

The scheme used for authentication. Accepted entries are Basic, NTLM, Digest, Forms, and ADFS.

Use the following options to authenticate:

• Basic: Set this to use HTTP Basic authentication.

• NTLM: Set this to use your Windows credentials for authentication.

• Digest: Set this to HTTP Digest authentication.

• Forms: Set this to use Forms authentication.

• ADFS: Set this to use Single Sign-On authentication with ADFS.

URL #

The base URL for a site or site collection.

The following are examples of valid URLs:

• http://server/SharePoint/

• http://server/Sites/mysite/

• http://server:90/

The provider will use URL to derive URLs for other calls to the server.

User #

The username of the account used to authenticate to the server.

Password #

The password of the account used to authenticate to the server.

SSO Login URL #

The identity provider’s login URL.

STS URL #

The URL of the security token service (STS) when using Single Sign On (SSO).

This property only needs to be set when using Single Sign On with a local Active Directory Federation Services (ADFS).

Define Tables #

Define the tables within a spreadsheet.

This property is used to define the ranges within a spreadsheet that will appear as tables, when using the REST API. The value is a comma-separated list of name-value pairs in the form:

• [Table Name]=[Sheet Name]![Range].

Table Name is the name of the table you want to use for the data and will be used when issuing queries. Sheet Name is the name of the sheet within the spreadsheet and Range is the range of cells that contain the data for the table.

Here is an example DefineTables value: DefineTables="DefinedTable1=Sheet1!A1:N25,DefinedTable2=Sheet2!C3:M53"

Note: If the name of a defined table is the same as one returned by default (e.g. same name as a worksheet), the defined table will replace the default table.

File #

The name of the Excel file to which to connect (including the extension “.xlsx”). The file must exist.

Folder #

The folder containing the workbook specified by the File property.

The full, hierarchical path of the subfolder in a Library where the File can be found.

For example if the File is located in a folder called “SubFolder” within the folder called “BaseFolder”, the property will be set to:

• /BaseFolder/SubFolder/

SharePoint Version #

The version of the SharePoint server to which you are connecting.

Accepted entries are SharePoint 2013 and SharePoint 2010.

Use REST API #

Whether or not the REST API is used for retrieving data.

Whether or not the REST API is used for retrieving data. SharePoint 2010 must use the REST API. In SharePoint versions after 2010, both the REST API and OData API are available.

Use the REST API to access spreadsheets and ranges as tables as well as table objects. The OData API enables access to only table objects.

Use NTLM V1 #

Determines whether the driver will attempt to connect with NTLMv1 or NTLMv2 (default).

Enable SSL #

Option for creating the connection with SSL enabled.

Upload Keystore File #

Option to upload keystore file.

Add Configuration: Additional properties can be added using this option as key-value pairs.

After entering all the details, click on the TEST button.

If the connection service identification and authentication details are provided correctly, a success message stating “connection available” is generated.

Click on the CREATE button to save the changes.

If the details are incorrect or the server is down, you will get a message “Connection unavailable”.