SharePoint Client API

The SharePoint Web Client API connector is used to connect to SharePoint using the Client Object Model and can be used to work with Lists and Document Libraries.

The SharePoint Client API provider can be connected to the following editions of SharePoint.

• SharePoint 2010
• SharePoint 2013
• SharePoint 2016
• SharePoint Online (Office 365)

The Client API connector can read and write data into SharePoint Lists and Document Libraries. However please note that the connector is limited by the capabilities of the SharePoint Client API.

Connection

To connect to a List or Document Library enter the URL to your SharePoint site which should be similar to http://<sharepointurl>/<site1>/<site2>. You then need to select the correct authentication type and enter in the credentials required to connect to SharePoint. To connect select Connect & Create Library Connection to save the connection to the connection library.

The credentials will then be checked against SharePoint and a window will open where you can select the list or document library you want to connect to. Once this has been selected click OK to continue.

If you are saving the connection to the connection library then a window will pop up prompting for a connection name and optional description. Enter in a name to help you identify the site in the tree and click OK.

The site and it's items will now be accessible from the connection library window after clicking the refresh button.

Properties

A brief description of each property in the connection can be found below.

Property	Description
SharePointUrl	Specifies the URL to the SharePoint Site that contains the list or library to be connected to.
List Name	The name of the list or document library that the data source is connected to.
List View Name	The view that the data source is connected to this is normally the "All Items" view. If you want Data Sync to see a subset of List Items then you can select a user defined view.
List View Caml Query	You can specify a WHERE filter against the SharePoint List using the SharePoint Caml Query syntax.`
Date Time Handling	Specifies how DateTime values should be handled. If you use Local Timezone DateTime values then you should choose Local rather than UTC. Choosing local causes data sync to convert SharePoint UTC values into your local timezone. When updating SharePoint local will cause your DateTime values to be converted into UTC.
Document Library Update Behaviour	Describes how Document Libraries are updated, by default a new file is copied into SharePoint on each ADD and UPDATE operation. If you only want to update the metadata then set this to 'MetaDataOnly` so that a new file is not uploaded.
Query Limit	Specifies the number of SharePoint List items to return in each HTTP Request. 5000 is the maximum supported by SharePoint. Data Sync will request all list values from SharePoint in pages of this size.
Tidy Lookup Data	Used to remove SharePoint references from lookup values so 1;#Value becomes Value. See Lookup Columns for more information.
Show All Columns	By default Data Sync will hide SharePoint columns that are created by default and not all that useful. Setting ShowAllColumns=True will then return all columns from the List in the Datasource Schema.
Update Batch Size	Specifies the number of items to send to SharePoint in each HTTP Request.

Authentication Options

Current User

Use the current process identity as the authenticating user.

Credentials

Use the supplied windows Username, Password and Domain to authenticate with SharePoint.

Federated (Office 365)

Use credentials collected by logging in via browser based authentication with external Office 365 authentication provider.

info

This method collects the Federation Cookies from a browser session and uses them to authenticate with SharePoint. Since the cookies have a short expiry of only a few days this method does not work well in a production environment since the authentication must be preformed interactively to refresh the cookies.

ID Column

The SharePoint ID column is a read-only auto number column in SharePoint data sync cannot write a user value to this field therefore this column should not be part of your Schema Map when targeting SharePoint.

If you require a column to store the ID value from your source system we suggest that you create a new SharePoint column say ItemIDand use this as the target column for the ID value from your source system.

info

For Data Migration you can use the same ID column values if you repeatedly run the Compare-Sync process until you get zero results. Since eventually all the ID numbers your source data uses will be created in SharePoint and the values will eventually be in sync.

Lookup Columns

The Client API connector will attempt to connect lookup columns automatically when the related list is in the current site.

It will do this by retrieving the list values from the target list and doing a exact text match based on the supplied value and the value of the Title field in the related list.

Manual Lookup Columns

You can also manually apply the Lookup value by getting the ID number of the related item and build the standard SharePoint Lookup reference.

SharePoint uses an internal format for Lookup Values similar to 1;#Value One where the number represents the ID value of the item in the related list and the text is the SharePoint Title field of that item. To set lookup values you need to build this string and map it to the SharePoint column.

Data Sync by default will hide these column values and just extract the text value, so first you need to disable this by setting TidyLookupData=False.

info

TIP: Use the Data Preview feature in Data Sync to see the data that SharePoint returns.

Assuming you have added a lookup to your data source back to SharePoint to get the List ID value for the related item you can build the value like this.

IF(ISNULL(Lookup1_ID), NULL(), FORMAT("{0};#{1}", Lookup1_ID, Lookup1_Title))

Or even via in-line LOOKUPA/B functions

IF(ISNULL(LOOKUPB("ID", "MyList", WHEN("Title", myValue)), NULL(), FORMAT("{0};#{1}", LOOKUPB("ID", "MyList", WHEN("Title", myValue)), myValue))

DSID

DSID is a special column name that Data Sync uses to maintain a GUID value on a list. By creating a column called DSID on your list whenever Data Sync sees this column it will write a new Guid value back to it if it's empty. Data Sync can also write a value to this column where as it cannot update the GUID built-in column.

DSID is used in 2-Way sync scenarios where an ID value must be created on each side that is always unique.