Nav
You are viewing an older version of this section. Click here to navigate to the latest version.

Microsoft SharePoint 2013 Connector

Microsoft SharePoint is a web application platform for content and document management, intranet portals, collaboration, extranets, websites, and enterprise search. 

The Microsoft SharePoint connector for Anypoint platform enables integration with SharePoint 2013 through its REST API.

Overview

Supported operations:

  • Lists and List Items API: Create, delete, retrieve, and update lists and list items.

  • Files and Folders API

    • Files: Add, retrieve content, retrieve metadata, delete, check out, undo check out, check in, publish, unpublish, approve, deny, recycle, and copy files.

    • Folders: Create, delete, and retrieve folders. 

Supported Microsoft SharePoint versions:

  • Microsoft SharePoint 2013

  • Microsoft SharePoint Online

Authentication

The Microsoft SharePoint connector supports different authentication schemes based on which
Microsoft SharePoint that you access:

  • Supported Microsoft SharePoint authentication on-premise:

    • NTLM

    • Claims-based authentication

  • Supported Microsoft SharePoint authentication online:

    • Claims-based authentication

  • Unsupported authentication: Kerberos

Claims-Based Authentication

Configuring the connector to use claims-based authentication requires the following information from the Security Token Service (STS) that to use for authenticating:

  • Sts Url: The STS endpoint that accepts username and password for authenticating users and understands the WS-Trust 1.3 protocol. When the STS is Microsoft’s ADFS (Active Directory Federation Services), this url usually is https://youradfs.com/adfs/services/trust/13/usernamemixed.

  • Sts App Identifier: A string that identifies the Sharepoint site in the STS. It is also known as Relying Party Identifier, Client Identifier, Scope, or Realm. When the STS is Microsoft’s ADFS, this value can be discovered in the AD FS Management console under AD FS at T*rust Relationships* > Relying Party Trusts > (Sharepoint Site’s relying part properties) > Identifiers tab.

    You can obtain the Sts App Identifier by logging into the Sharepoint Site that you want to be accessed by opening the Site Url in a web browser. If there is more than one authentication provider configured for a site, a drop-down menu lists the options. Select the desired STS to be redirected to the STS’s login page. At this point, the address bar of the web browser contains a URL that includes the following query parameters:

    wa=wsignin1.0&wtrealm=uri%3amule%3asp80

    The parameter *wa* tells the STS that a sign in is being initiated. The *wtrealm* contains the URI-encoded value for the Sts App Identifier. In the example, uri%3amule%3asp80 is uri:mule:sp80. The unencoded value is the parameter for the connector’s configuration.

Install and Configure

To install a connector, see Installing a Connector from Anypoint Exchange.

Configuring Your First Flow

To configure:

  1. In Anypoint Studio, click File > New > Mule Project.

  2. Specify a Project Name and click Finish.

  3. Click the Global Elements tab.

  4. Click Create.

  5. In the Search text box, type sharepont.

  6. Click Microsoft SharePoint and click OK.

  7. Choose the Global Type to configure:

    WindowsGlobalTypes

  8. Legacy Connection:

    1. Fill in the Username, Password, and Site URL.

    2. For authentication:

      • If using a self-signed SSL certificate, and click the Disable SSL certificate validation checkbox.

      • To connect with Claims Authentication, fill in STS URL (Security Token Service) and scope (Relying Party Identifier).  The STS URL has to point to the endpoint of the STS that accepts username and password as authentication credentials and understands WS-Trust 1.3 standard. In ADFS, the endpoint is usually https://myadfs.com/adfs/services/trust/13/usernamemixed . Also, the endpoint has to be enabled in ADFS (it is enabled by default).

      • To connect with NTLM Authentication, fill in Domain.

      • To connect to SharePoint Online, leave  NTLM and Claims inputs empty. Just specify a UsernamePassword, and Site URL.

  9. Click Test Connection to ensure the connection works correctly:

    SPGlobalElementProps 

The other connection types require similar information. 

Note: The Pooling Profile, Reconnection, and Notes tabs can be ignored. These are provided by Studio and contain default information.

Creating an Anypoint Studio Flow

To create an Anypoint Studio flow:

  1. From Anypoint Studio, click File > New > Mule Project.

  2. Specify a Project Name and click Finish.

  3. In the Search box, type http and drag an HTTP Endpoint from the Connectors section to the canvas.

  4. In the Search box, type sharepoint and drag a Microsoft SharePoint connector instance next to the HTTP Endpoint connector.

  5. In the Search box, type json and drag an Object to JSON transformer next to the Microsoft SharePoint connector.

    SPMuleFlow

  6. Double-click the HTTP Endpoint. Make sure Host is set to localhost and Port is set to 8081. Set the Path to query. Click OK.

  7. Double-click the Microsoft SharePoint connector and click the green plus symbol.

  8. Update the following configuration values:

    1. From the Connector Configuration list, click the Microsoft SharePoint configuration that was previously created.

    2. From the Operation list, click List query
      Note: The List query option only appears in the Operation list after you have successfully connected to a SharePoint instance.

    3. From the Language list, click DataSense Query Language.

  9. Click Query Builder:

    1. From the list of Types, click Documents.

    2. From the list of Fields, click ID, and Title.

    3. From Order By, click Title.

    4. From Direction, click  DESCENDING

      MSSPQueryBuilder 

Running the Flow

  1. In Package Explorer, right click on sharepoint2013-demo and select Run As > Mule  Application.

  2. Check the console to see when the application starts. You should see the following  message if no errors occurred:

    
                
             
    1
    2
    3
    
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    + Started app 'sharepoint2013-demo'                        +
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  3. Open an Internet browser and visit http://localhost:8081/query

  4. The list of documents are ordered by descending title and returns in JSON format  (results vary according to your SharePoint 2013 instance).

    
                
             
    1
    2
    3
    4
    5
    
    [{"__metadata":{"id":"Web/Lists(guid'2af685ae-5aec-4f60-b175-
    54b21b6bd668')/Items(4)","uri":"https://ec2-54-200-49-206.us-west-
    2.compute.amazonaws.com/_api/Web/Lists(guid'2af685ae-5aec-4f60-b175-
    54b21b6bd668')/Items(4)","etag":"\"1\"","type":"SP.Data.Shared_x0020_Document
    sItem"},"Id":4,"ID":4,"Title":"folder"}]

Operations: Lists and List Items API

Using the Lists and List Items API lets you create, retrieve, update, and delete SharePoint lists and list items.

Creating, Updating, and Deleting List Items

When creating or updating an item, specify the list ID. After you specify an ID, DataSense fetches the list’s metadata and the object builder shows each field that can be completed:


          
       
1
<sharepoint-2013:list-create config-ref="Sharepoint_2013" doc:name="Sharepoint 2013" baseTemplate="GENERIC_LIST" title="Title"> <sharepoint-2013:list ref="#[payload]"/> </sharepoint-2013:list-create>

Or define the attributes in the connector itself:


          
       
1
<sharepoint-2013:list-create config-ref="Sharepoint_2013" doc:name="Sharepoint 2013" baseTemplate="GENERIC_LIST" title="Title"> <sharepoint-2013:list contentTypesEnabled="true" description="Description"/> </sharepoint-2013:list-create>

For retrieving and deleting lists, only the list ID is necessary:


          
       
1
<sharepoint-2013:list-delete config-ref="Sharepoint_2013" doc:name="Sharepoint 2013" listId="8e306633-c600-40ab-80db-80f57968c0a1" />

Creating, Updating, and Deleting List Items

When creating or updating an item, specify a list ID. DataSense uses the list ID to fetch a list’s metadata. The Object Builder provides the fields you need to complete.

MSSPObjectBuilder

Querying List Items

Using the query builder:

On the left panel, every not hidden list appears. On the right panel, the fields of the selected list appear. If the field is a Lookup Field, the field type is either SharepointListReference or SharepointListMultiValueReference.

SPQueryBuilder

If any of these fields are selected to be returned by the query, two types of return objects are available, depending on the value of the Retrieve full objects for reference fields checkbox:

  • not checked: A summary object containing the reference object’s ID and the reference object list’s ID:

    
                 
              
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    
    {
        "Title": "A title",
        "LookupFieldId": {
            "id": "1",
            "lookupListId": "aaaa-1111-bbbb-2222"
        },
        "MultiValueLookupFieldId": {
            "ids": [
                1,
                2,
                3
            ],
            "lookupListId": "cccc-3333-dddd-4444"
        }
    }

    This object can later be used in another connector to retrieve the referenced object together with a for each component:

    MSSPListItemQuery 

  • checked. Retrieves the full object graph. In case there is a cycle, the summary reference object displays:

    
                 
              
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    
    {
        "Title": "A title",
        "LookupFieldId": {
            "Title": "Another title",
            "Id": "1",
            "Property1": "A value"
        },
        "MultiValueLookupFieldId": [
            {
                "Title": "Another title",
                "Id": "1",
                "Property1": "A value"
            },
            {
                "Title": "Another title",
                "Id": "2",
                "Property1": "A value"
            }
        ]
    }

    Example Query Text:

    SPExampleQText

Checking this option may cause large item lists with many reference fields to take a long time to retrieve.

Operations: File and Folder API

Using the File and Folder API allows you to create, retrieve, update, and delete files and folders, and also check in, check out, publish, approve, deny, copy, and recycle files from Documents Lists.

When using the folders operations, the server’s relative URL refers to where the folder is or will be. The URL can be in the format /site/docList/innerFolder or in docList/innerFolder  format. In the second case, the site specified in the connector’s configuration site URL parameter is used.

When using the files operations, the file server relative url refers to a folder server relative URL plus the filename: /site/docList/innerFolder/filename or docList/innerFolder/filename.

Creating and Deleting a Folder

You can create or delete a folder by specifying the server relative URL where the folder is or where you plan to create the folder.

The resulting flow looks:


          
       
1
2
3
<sharepoint-2013:folder-create config-ref="Sharepoint_2013" url="/path/to/folder" doc:name="Sharepoint 2013"/>

<sharepoint-2013:folder-delete config-ref="Sharepoint_2013" url="/path/to/folder" doc:name="Sharepoint 2013"/>

Adding a File

A file can be uploaded by selecting a physical file or passing an input stream to the connector, and it’s uploaded to the specified server relative URL. For example, you can use this together with a File Connector to upload files to a list. 

Using an input stream:


          
       
1
2
3
4
<sharepoint-2013:file-add config-ref="Sharepoint_2013"
fileServerRelativeUrl="/path/to/folder/filename"
fileContentStream-ref="#[payload]" overwrite="true"
doc:name="Sharepoint 2013"/>

Getting File Contents

The file content is returned as a byte array. For example, you can use this as an input of a File Connector to download files from a list:


          
       
1
2
3
<sharepoint-2013:file-get-content config-ref="Sharepoint_2013"
doc:name="Sharepoint 2013"
fileServerRelativeUrl="/path/to/folder/filename"/>

Querying Files and Folders

This operation returns all the files and folders that match the specified criteria, starting from the specified folder.

Using the query builder:

  • On the left panel, a document list from the SharePoint instance appears. The selected instance is used as part of the starting path to query the files and folders.

  • On the right panel, for every document list, the same fields appear.

  • Additionally, you can specify an inner folder or folders in the Folder Path input, to use as the starting path.

  • When selecting the recursive checkbox, files and folders are searched recursively in every folder of the starting path.

To set query builder options:

SharePointFolderPath

Example:


          
       
1
2
3
sharepoint-2013:file-query config-ref="Sharepoint_2013" query="dsql:SELECT Author,ModifiedBy,Name,ServerRelativeUrl FROM #[header:inbound:documentListName]" recursive="true" doc:name="Sharepoint 2013"/>

<sharepoint-2013:folder-query config-ref="Sharepoint_2013" recursive="true" query="dsql:SELECT ItemCount,Name,ServerRelativeUrl FROM #[header:inbound:documentListName] WHERE ItemCount &gt; 0" doc:name="Sharepoint 2013"/>

Other File Operations

Approve, Check In, Check Out, Deny, Publish, Undo Checkout, and Unpublish, are all very similar to use. Specify the file URL, and in some, pass an additional comment as a parameter.


          
       
1
2
<sharepoint-2013:file-publish config-ref="Sharepoint_2013"
doc:name="Sharepoint 2013" fileServerRelativeUrl="" comment=""/>

Setting File Metadata

You can get and set metadata on files that are uploaded to Document Libraries by using the Update List Item operation.

To set the properties of the file in the list, you must know the List Item Id. This can be retrieved using the deferred ListItemAllFields property.

The following flow illustrates how a File Add may chain directly to an Update List Item operation to upload a file to a list and set the metadata immediately after:


          
       
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<flow name="sharepoint_demo_fileAddWithMetadata"
   doc:name="sharepoint_demo_fileAddWithMetadata">
   <http:inbound-endpoint exchange-pattern="request-response" host="localhost"
     port="8081" path="upload" doc:name="HTTP"/>
   <sharepoint:file-add config-ref="Sharepoint"
     fileServerRelativeUrl="/Shared Documents/myfile.txt"
     overwrite="true"
     doc:name="Add file"/>
   <sharepoint:resolve-object config-ref="Sharepoint"
     doc:name="Get ListItemId of File"
     url="#[payload.listItemAllFields.__deferred.uri]"/>
   <sharepoint:list-item-update config-ref="Sharepoint" itemId="#[payload.Id]"
     listId="ccbfaf65-b53e-48ac-be19-adf45192ecc3" doc:name="Set file properties">
       <sharepoint:updated-properties>
           <sharepoint:updated-property key="Title">Test title</sharepoint:updated-property>
       </sharepoint:updated-properties>
   </sharepoint:list-item-update>
   <set-payload value="OK" doc:name="Set Payload"/>
</flow>

Resolving Deferred Properties

For performance reasons, many SharePoint operations return a basic set of data for an entity along with one or more deferred property references you can use to retrieve additional detail or related objects.

You can use the generic Resolve object or Resolve collection operations to resolve the deferred property set to a single Map<string,object> or a List<Map<string,object>> and access this information in the flow.

For example, this technique gets the full set of fields of a SharePoint File object:


         
      
1
2
3
4
<sharepoint:resolve-object config-ref="SharePoint"
  url="#[payload.listItemAllFields.__deferred.url]"
  doc:name="Microsoft SharePoint" >
</sharepoint:resolve-object>

Using the Mule Debugger or Logger component to log the payload, you can identify properties with a _deferred URL property.

Attaching a File to a List Item

To attach a file to a list item, use the ResolveObject operation as shown in this example:


         
      
1
2
3
4
5
6
7
8
9
10
11
<flow name="sp-testFlow2">
  <http:listener config-ref="HTTP_Listener_Configuration" path="/at" doc:name="HTTP"/>
  <set-variable variableName="FileNameToAttach" value="CHANGELOG.md" doc:name="Set FileNameToAttach"/>

  <sharepoint:list-item-query config-ref="Microsoft_SharePoint__NTLM_Connection" query="dsql:SELECT ID,Title FROM 82b2a455-3faf-4162-8276-63a1093fcc7e WHERE Title = 'test-list-item-1'" doc:name="Read List Item"/>
  <set-variable variableName="ListItemUrl" value="#[payload.next() .__metadata.uri]" doc:name="SetListItemUri from list item query result"/>
  <set-payload value="#[groovy:new FileInputStream('C:\\temp\\' + flowVars.FileNameToAttach)]" doc:name="Set file to attach as inputstream in payload"/>

  <sharepoint:resolve-object config-ref="Microsoft_SharePoint__NTLM_Connection" url="#[flowVars.ListItemUrl]/AttachmentFiles/add(FileName='#[flowVars.FileNameToAttach]')" resolveRequestType="Create" doc:name="create attachment"/>
  <json:object-to-json-transformer doc:name="Object to JSON"/>
</flow>

The flow shows how to:

  1. Get the list item URI by reading it from SharePoint. If you already have the list item because it’s being created in the same flow, you can use that one.

  2. Read a file into an input stream. Here it’s from c:\temp (find the path in the flow to replace it).

  3. Create the list item attachment with the file.

Executing Direct Calls Against the REST API

SharePoint REST API allows a large number of commands that can be reached though Resolve object and Resolve collection actions. These operations provide an authenticated call to a specified URL, and resolves into a Map and a List<Map> respectively.

The Resolve object operation accepts all the HTTP verbs (GET, POST, PUT/MERGE, DELETE) and allows sending a body in the request to the API. The body’s default value is the payload of the Mule message.

The body can be for API endpoints that accept a JSON:

  • Map<String, Object> that is converted to a JSON string.

  • String containing the JSON. This string is sent as-is.

For API endpoints that accept a file:

  • InputStream with the file. The stream closes after using it.

  • byte[] with the file. This byte arrays is sent as-is.

Working with Choice Column Type with Multiple Values

You can configure a Choice column type to allow multiple values. The metadata in Studio for columns accepting multiple values appears as follows:

SharePointChoiceMultiSelect

Assuming that the target List in SharePoint has a Title property and a multi-select column called ChoiceMultiSelect that accepts values "one""two", or "three", the following Groovy script constructs a payload that sets the selection to "one", "three":


         
      
1
[Title: "foo", ChoiceMultiSelect: [results: ["one", "three"]]]

Any language that can construct a List<string> for the multi-select column results property may be used to similar effect.

This block of pseudo code demonstrates how to set Choice #1 and Choice #2 as the values for the ChoiceMultiSelect column:


         
      
1
2
3
4
5
6
values = new List<String>
values.add("Choice #1")
values.add("Choice #2")
multiValuesMap = new Map<String, Object>
multiValuesMap["results"] = values
List-item["ChoiceMultiSelect"] = multiValuesMap

Exception Handling

Exception When Connecting

If the connector fails to connect with the SharePoint instance for any reason, an exception of type ConnectionException is thrown.

The exception message helps debug the cause of the exception.

Exception in Operations

If when executing an operation, an error occurs, then a SharepointException is thrown with a message about the error.

Frequently Asked Questions

Which versions of SharePoint are supported by this connector?

The SharePoint connector supports SharePoint 2013 on-premises, and SharePoint online versions.

What authentication schemes are supported by the connector?

Options for authentication against on-premises SharePoint instances include Claims Authentication (ADFS) and NTLM. For SharePoint Online, authentication using standard SharePoint online user credentials is supported.

What parts of the SharePoint object model are accessible by the connector?

Specific support for Files and Folders, Lists, ListItems and Attachments is offered. Additionally, all other entities of the SharePoint API are accessible in JSON form via the ResolveObject and ResolveCollection operations.

Is DataSense and DataMapper supported by this connector?

Yes, all supported entities and entity attributes are exposed to Studio by the connector for use with DataMapper.

What operations can I perform with the connector?

For the Lists and ListItems API, supported operations include Create, Retrieve, Update and Delete. For Files and Folders, operations include Add, retrieve content, retrieve metadata, delete, check out, undo check out, check in, publish, unpublish, approve, deny, recycle, and copy.

Are there any examples that show how to use the connector?

Yes, an example project for Anypoint Studio is freely available here: (link to sharepoint-connector-samples.zip)

What Mule editions can I use this connector on?

This connector is supported on any Enterprise Edition Anypoint platform running on any operating system and bit-ness, including the CloudHub integration PaaS.

See Also