By Cathy Dew
Unfortunately, you can’t make your clients keep everything they wish to share via their CMS within the CMS’s control. This means you’ll be designing custom connections to 3rd party applications to pull important data into Kentico for display and analysis. Since the rise of SharePoint Online, we are seeing more and more clients needing to collect information residing in a SharePoint Online Site Collection or Subsite and bring that into Kentico. The data collection is complicated by the ability within SharePoint to create ad-hoc metadata. While internally structured, the document library or list metadata does not lend itself to simple collection and discovery outside of SharePoint. You need to have a plan in place and a well-defined metadata model to build a robust connection between Kentico and SharePoint Online.
Understanding RESTful APIs to Collect Data From SharePoint
In this two-part series, we’ll discuss the plumbing needed to setup a solid pull-only connection between Kentico and SharePoint Online. Today we’ll cover what is a RESTful API and how to get a package of JSON data from a target SharePoint Online library. Next week, we’ll cover how to integrate that data into a custom Kentico Page Type and transformations for display to your user base.
What is a RESTful Service?
Representational state transfer (REST) is a way to provide a stateless communication pathway between computer systems. The benefit of using REST is the simplicity of the process, and the ability of the responding service to send back data packages as XML, JSON, or HTML. Unlike SOAP (Simple Object Access Protocol) web services, which is a well-defined protocol, REST is an architectural design and the exact implementation of RESTful APIs can vary between applications.
The benefit of RESTful communications is that normal HTTP communications is readily adapted to the architectural design. URLs contain all the information about the service requested (note: REST doesn’t, or shouldn’t, use the query string parameter). The type of HTTP Method explains the direction of the service request: GET, PUT, PATCH, POST, DELETE. And depending upon the options made available, the requesting service can provide the response type package; JSON, XML, HTML
For example:
- https://api.foo.org/library
- https://api.foo.org/library/training
- https://api.foo.org/library/training/on-boarding
In a., the RESTful API should return a list of available libraries. With b., the API should return a list of documents within the “training” library. And for c., the API should return information about the “on-boarding” document.
These are just simple examples of working with a RESTful API, there are obviously authentication needs and other HTTP Client communication requirements to make this all work. Therefore, you’ll want to make sure that you have access to an API document for the 3rd party application you are working with in order to setup your communications successfully.
SharePoint and REST
Starting with SharePoint 2013, Microsoft included a set of RESTful APIs to communicate with SharePoint via custom web client applications and other non-SharePoint services. Initially the RESTful services were not as robust as the original client object model. However, over the last 5 years, Microsoft has built out the RESTful services to be the de-facto communication service for 3rd party application to work with SharePoint.
URL
One of the challenges with REST is its simplicity and some APIs require multiple requests to drill down and get the full data set you really want. Luckily with the SharePoint Online RESTful APIs you can “stack” your requests to assist in minimizing the number of calls to get your complete data set.
For our example today, we are trying to get a full listing of the documents, including their metadata, from a target SharePoint Library. Normally, you would request a list of the documents, and then iterate through each document to get it’s meta data.
But with stacking, we can put the full call into one URL and get back everything we need:
/_api/Web/GetFolderByServerRelativeUrl('FolderRelativeURL')?$expand=Folders,Files/Author,Files/ListItemAllFields
Mr. Anderson explains how this works in his post: Get all SharePoint Document Library Files and Folders. Here are a few more important links for understanding SharePoint’s REST API:
While we still have to iterate through the folder list and then drill into each folder returned; this process minimizes our data calls to one per folder in the library. You’ll also need to setup multiple calls if you end up with a folder that has more than 5000 files directly in it.
SharePoint JSON Response
One benefit of getting a JSON (or XML) object back from SharePoint Online is we can directly attach to the Files branch or the Folders branch, and not have to iterate through the full collection to find and separate out the data we want. Here is an example JSON branch for a file and for a folder from the GetFolderByServerRelativeUrl described above:
FILE:
{
"odata.type": "SP.File",
"odata.id": "https://foo.sharepoint.com/teams/kentico/_api/Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared Documents/Book.xlsx')",
"odata.editLink": "Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared%20Documents/Book.xlsx')",
"Author@odata.navigationLinkUrl": "Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared%20Documents/Book.xlsx')/Author",
"Author": {
"odata.type": "SP.User",
"odata.id": "https://foo.sharepoint.com/teams/kentico/_api/Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared Documents/Book.xlsx')/Author",
"odata.editLink": "Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared%20Documents/Book.xlsx')/Author",
"Id": 11,
"IsHiddenInUI": false,
"LoginName": "i:0#.f|membership|kentico@foo.org",
"Title": "Kentico CMS",
"PrincipalType": 1,
"Email": " kentico@foo.org ",
"IsEmailAuthenticationGuestUser": false,
"IsShareByEmailGuestUser": false,
"IsSiteAdmin": false,
"UserId": {
"NameId": "10033fff92e2947c",
"NameIdIssuer": "urn:federation:microsoftonline"
}
} ,
"ListItemAllFields@odata.navigationLinkUrl": "Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared%20Documents/Book.xlsx')/ListItemAllFields",
"ListItemAllFields": {
"odata.type": "SP.Data.Shared_x0020_DocumentsItem",
"odata.id": "97064a5f-c3c2-42d6-9280-a3c3980a7499",
"odata.etag": "\"56\"",
"odata.editLink": "Web/GetFileByServerRelativePath(decodedurl='/teams/kentico/Shared%20Documents/Book.xlsx')/ListItemAllFields",
"FileSystemObjectType": 0,
"Id": 530,
"ServerRedirectedEmbedUri": "https://foo.sharepoint.com/teams/kentico/_layouts/15/WopiFrame.aspx?sourcedoc={690d8e2b-4725-4c54-84e1-164bf1b174e6}&action=interactivepreview",
"ServerRedirectedEmbedUrl": "https://foo.sharepoint.com/teams/kentico/_layouts/15/WopiFrame.aspx?sourcedoc={690d8e2b-4725-4c54-84e1-164bf1b174e6}&action=interactivepreview",
"ContentTypeId": "0x01010029DDF7A871714E40A5813E7928F3994E",
"Title": null,
"SharedWithUsersId": [
71
],
"SharedWithDetails": "{\"i:0#.f|membership|2plus2@foo.org\":{\"DateTime\":\"\\/Date(1506007980570)\\/\",\"LoginName\":\"kentico@foo.org\"}}",
"PublishingStartDate": null,
"PublishingExpirationDate": null,
"ComplianceAssetId": null,
"Description0": "Book Generic",
"Language": {
"Label": "English",
"TermGuid": "c91e8605-b8cc-4344-8765-07260c9adb70",
"WssId": 1
},
"ID": 530,
"Created": "2017-03-23T09:38:05",
"AuthorId": 11,
"Modified": "2017-10-26T15:14:21",
"EditorId": 49,
"OData__CopySource": null,
"CheckoutUserId": null,
"OData__UIVersionString": "15.0",
"GUID": "c341972f-7660-469c-874d-32dec5ac2ab3"
},
"CheckInComment": "",
"CheckOutType": 2,
"ContentTag": "{690D8E2B-4725-4C54-84E1-164BF1B174E6},58,38",
"CustomizedPageStatus": 0,
"ETag": "\"{690D8E2B-4725-4C54-84E1-164BF1B174E6},58\"",
"Exists": true,
"IrmEnabled": false,
"Length": "21764",
"Level": 1,
"LinkingUri": "https://foo.sharepoint.com/teams/kentico/Shared%20Documents/Book.xlsx?d=w690d8e2b47254c5484e1164bf1b174e6",
"LinkingUrl": "https://foo.sharepoint.com/teams/kentico/Shared Documents/Book.xlsx?d=w690d8e2b47254c5484e1164bf1b174e6",
"MajorVersion": 15,
"MinorVersion": 0,
"Name": "Book.xlsx",
"ServerRelativeUrl": "/teams/kentico/Shared Documents/Book.xlsx",
"TimeCreated": "2017-03-23T16:38:05Z",
"TimeLastModified": "2017-10-26T22:14:21Z",
"Title": "",
"UIVersion": 7680,
"UIVersionLabel": "15.0",
"UniqueId": "690d8e2b-4725-4c54-84e1-164bf1b174e6"
}
FOLDER:
{
"odata.type": "SP.Folder",
"odata.id": "https://foo.sharepoint.com/teams/kentico/_api/Web/GetFolderByServerRelativePath(decodedurl='/teams/kentico/Shared Documents/Leadership Folder)",
"odata.editLink": "Web/GetFolderByServerRelativePath(decodedurl='/teams/kentico/Shared%20Documents/Leadership%20Folder)",
"Exists": true,
"IsWOPIEnabled": false,
"ItemCount": 0,
"Name": "Forms",
"ProgID": null,
"ServerRelativeUrl": "/teams/kentico/Shared Documents/Leadership Folder",
"TimeCreated": "2016-02-26T20:08:07Z",
"TimeLastModified": "2016-02-26T20:08:07Z",
"UniqueId": "cf47c5fa-a04b-4f5e-8969-1c9c8f865971",
"WelcomePage": ""
}
Parsing the results
For this example, we are interested in the following fields from each File Object:
- Title
- Description
- Folder Path
- Link to URL
- REST URL
- Language
- Category
- Create Date
- Modified Date
- Author Name
- Author Email
Each of these target attributes are embedded in the File Object data. Depending on whether the attributes is a default column or a custom metadata column determines where we will find the data. For the most part, the default data fields will be found in the root of the File Object and the Custom Metadata fields will be found in the ListItemAllFields sub branch.
One caveat, if you are using a Term Store or List Look up for your Metadata, you may have to query those metadata locations for the actual text label for the metadata, SharePoint stores a GUID (for the Term Store assignment) and a row ID (for the List Record) instead of the actual text value.
Once you have all the target Metadata you are ready to bring all that data into Kentico for display. We’ll cover that process next week.
Conclusion
This article just scratches the surface as to the RESTful options available to sharing data between Sharepoint Online and Kentico. 2Plus2 is a Kentico Gold Partner and would love to help you get the most out of your SharePoint Online and Kentico setups. Go online to schedule a free consultation with our team or call 510-652-7700 today.