Excel Add-In for Microsoft SharePoint

Build 24.0.8963

SOAP Data Model

The CData Excel Add-In for Microsoft SharePoint models Microsoft SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your Microsoft SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.

Customizing the Data Model

The add-in sets defaults to facilitate the maximum number of integrations; however, the following connection properties allow a greater granularity of customization, which is useful in advanced integrations:

  • CalculatedDataType: The data type to be used for calculated fields.
  • CreateIDColumns: Indicates whether or not to create supplemental Id columns for SharePoint columns that use values from information stored in other Lists.
  • FolderOption: An option to determine how to display folders in results. Enter either FilesOnly, FilesAndFolders, Recursive, or RecursiveAll.
  • PseudoColumns: Indicates whether to report pseudo columns as columns in the table metadata.

Tables

Tables describes the available tables.

The add-in can expose custom lists from Microsoft SharePoint that are not mentioned in the Tables. The data model illustrates a sample of what your SharePoint site might look like. The actual data model will be obtained dynamically based on your user credentials and SharePoint site.

Views

Typically, entities that cannot be modified are represented as Views, or read-only tables. You can also access custom views of a list as relational views.

To get data from a custom view of a list, you can set the ViewID pseudo column in the WHERE clause.

SELECT * FROM ListName WHERE ViewID='ID of the view'
You can get the ID of the view from the Views list. You must specify the List pseudo column to get a list of views for that list. For instance:
SELECT * FROM Views WHERE List ='ListName'

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They surface additional capabilities of the Microsoft SharePoint API such as searching, updating, and modifying information.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8963