JSON Integration with SharePoint

JSON data can be integrated and synchronized codeless with native SharePoint lists using the Layer2 Business Data List Connector. In case you are looking for a data integration with SharePoint Online, you will find the right solution here.

Features of JSON integration in SharePoint

Very easy to setup in a few minutes

Create a SharePoint list, click "Connect to external data source" in the list settings, select the data provider, enter connection settings and data query as shown below. That's it.

Connected data always up-to-date

The connected JSON data updates automatically in the background (via SharePoint Timer Job), or alternatively, on-demand (Action Menu / Ribbon Button, URL, via workflow, API).

One-way and optional two-way connection

You can write-back the changes made in SharePoint to the external JSON data source automatically with full CRUD (Create / Update / Delete) functionality. The SharePoint list can act as a full-featured front-end for external systems.

High Security

SharePoint Secure Store can be used to store security relevant configuration information safely in one central place. Users are working with the SharePoint lists as an external data cache with highest security and performance.

Well-known BCS "external list" issues and limitations are completely solved

All list features are to you. Views, sorting and grouping, filters, calculated fields, search, managed metadata. Lookups, additional columns and attachments can be created as normal. All kind of lists can be used, e.g. contacts, tasks, calendar, or custom lists. You can take external data offline via Outlook.

Workflows and notifications on JSON data change

List workflows and change notifications per RSS or email can be used to take business actions in SharePoint, when JSON data records are changed.

Sample Connection configuration to connect to JSON (Layer2 Business Data List Connector Screenshot)

Field Mapping for JSON Data Integration with SharePoint (Layer2 Business Data List Connector Screenshot)

Screenshot of Synced list with JSON external data via Layer2 Business Data List Connector

JSON specific SharePoint List Configuration settings

Please note the following about JSON-specific settings.

Please download and install the CDATA Provider directly from the vendor: https://www.cdata.com/drivers/json/ado/. After that you can select the CData ADO.NET Data Provider for JSON in the list of installed providers.
You can make use of any connection string option supported by the data provider. Here is a list of these available options: http://cdn.cdata.com/help/DJC/ado/Connection.htm
It will be the best to start with a free JSON test site: URI=https://jsonplaceholder.typicode.com/posts
You can make use of all SQL queries your data provider supports. Please start with the following querie, if you are unsure: SELECT * FROM JSONData
Please specify a primary Key. Write-back is generally supported by the JSON data provider. Please make sure that a primary key is defined for this.

Advise and Information

For more complex JSON content note the "JSON Path" parameter in the connection string.
Write-back synchronization is generally supported. Your JSON service must allow writing for this.
Complex SharePoint fields like lookups, user or group picker etc. are generally supported. You have to make sure providing values that fit into the fields.

Find more Use Cases for your SharePoint integration

Take your next steps?

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.

Register for free download.

Keep your SharePoint in sync. Download and try the Layer2 Business Data List Connector today.

Free Trial