Microsoft SharePoint Online Connector - Mule 3

Microsoft SharePoint Online Connector v1.0

Anypoint Connector for Microsoft SharePoint Online (SharePoint Online Connector) is a web app platform for content and document management, intranet portals, collaboration, extranets, websites, and enterprise search.

The connector enables integration with SharePoint through its REST API.

Release Notes: Microsoft SharePoint Online Connector Release Notes
Samples: Sharepoint Online Connector Demo
FAQ: About Frequently Asked Questions

Supported Operations

Lists and List Items API: Create, delete, retrieve, 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.

POM File Information

If you Mavenize your Studio project, you can use this information in your projects pom.xml file:

<dependency>
  <groupId>org.mule.modules</groupId>
  <artifactId>sharepoint-online</artifactId>
  <version>x.x.x</version>
  <classifier>mule-plugin</classifier>
</dependency>

Replace x.x.x with the version that corresponds to the connector you are using.

To obtain the most up-to-date pom.xml file information, access the connector in Anypoint Exchange and click Dependency Snippets.

Authentication

Supported Microsoft SharePoint Online authentication:

Microsoft Online (Office 365)
Microsoft Online (Office 365) with Security Token
Microsoft Online (Office 365) with Okta

Note: All can be configured to use an HTTP proxy for all types.

Okta Authentication

Configuring the connector to use Okta authentication requires the following information:

Okta Domain: The Okta endpoint that accepts the Okta username and password for authentication, for example: your-domain.okta.com
Okta Username: Your Okta username, for example: your-email@example.com
Okta Password: Your Okta password.
Okta API Token: Okta uses a bearer token for API authentication with a sliding scale expiration, for example: http://developer.okta.com/docs/api/getting_started/getting_a_token.html
Sharepoint Online Embedded Link: The link of your Microsoft Office 365 SharePoint Online application from Okta, for example: https://your-domain.okta.com/home/office365/0oa14mz7f4HAWypdZ1t7/31737

SharePoint Online Authentication

Specify the Username, Password, and Site URL.
Configure authentication:
- If using a self-signed SSL certificate, click the Disable SSL Certificate Validation checkbox.
- To connect to SharePoint Online, specify a Username, Password, and Site URL.
  
  Connections can be configured to use HTTP Proxy for all types.
Specify Proxy Host, Proxy Port, Proxy Username, Proxy Password if suitable.

Security Token Authentication

You can use a SAML security token to log in to SharePoint. You can provide an XML body via a POST request to get the security token that you put in the Studio Security Token field.

To obtain a security token, make a POST request to https://login.microsoftonline.com/extSTS.srf with this XML body:

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope"
      xmlns:a="http://www.w3.org/2005/08/addressing"
      xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <s:Header>
    <a:Action s:mustUnderstand="1">http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</a:Action>
    <a:ReplyTo>
      <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
    </a:ReplyTo>
    <a:To s:mustUnderstand="1">https://login.microsoftonline.com/extSTS.srf</a:To>
    <o:Security s:mustUnderstand="1"
       xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
      <o:UsernameToken>
        <o:Username>[username]</o:Username>
        <o:Password>[password]</o:Password>
      </o:UsernameToken>
    </o:Security>
  </s:Header>
  <s:Body>
    <t:RequestSecurityToken xmlns:t="http://schemas.xmlsoap.org/ws/2005/02/trust">
      <wsp:AppliesTo xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy">
        <a:EndpointReference>
          <a:Address>[endpoint]</a:Address>
        </a:EndpointReference>
      </wsp:AppliesTo>
      <t:KeyType>http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey</t:KeyType>
      <t:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue</t:RequestType>
      <t:TokenType>urn:oasis:names:tc:SAML:1.0:assertion</t:TokenType>
    </t:RequestSecurityToken>
  </s:Body>
</s:Envelope>

The response to this request contains the security token between the <wsse:BinarySecurityToken> tags.

After you have a security token, specify the token value in Studio:

Security token connection configuration in global element properties window

Security token: Enter the security token value.
Site URL: Enter your site URL in the form of https://<your site>.sharepoint.com.

Install the Connector

In Anypoint Studio, click the Exchange icon in the Studio task bar.
Click Login in Anypoint Exchange.
Search for the connector and click Install.
Follow the prompts to install the connector.

Configure Your First Flow

In Anypoint Studio, click File > New > Mule Project.
Specify a Project Name value and click Finish.
Click the Global Elements tab.
Click *Create.
Search for sharepoint.
Click Microsoft SharePoint and click OK.
Choose the Global Type to configure:
1. Click Test Connection to ensure that the connection works correctly:

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.

Create a Studio Flow

To create an Anypoint Studio flow:

From Anypoint Studio, click File > New > Mule Project.
Specify a Project Name and click Finish.
Search for http and drag an HTTP connector to the canvas.
Search for sharepoint and drag a Microsoft SharePoint connector next to the HTTP connector.
Search for json and drag an Object to JSON transformer next to the Microsoft SharePoint connector.
Double-click the HTTP connector and set Host to 0.0.0.0, Port to 8081, and Path to query. Click OK.
Double-click the Microsoft SharePoint connector and click the green plus symbol.
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 operation only appears in the Operation list after you have successfully connected to a SharePoint instance.
3. From the Language list, click DataSense Query Language.
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.

Run the Flow

In Package Explorer, right-click the project name and click Run As > Mule Application.

Check the console to see when the application starts. If no errors occur, this message appears:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ Started app 'sharepoint-online-demo'                     +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Browse to http://0.0.0.0:8081/query.
The list of documents are ordered by descending title and returns in JSON format (results vary according to your SharePoint Online instance).

[{"__metadata":{"id":"Web/Lists(guid'GUID_VALUE')/Items(4)","uri":"https://ec2-54-200-49-206.us-west-
2.compute.amazonaws.com/_api/Web/Lists(guid'GUID_VALUE')/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.

Create, Update, and Delete 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:

<sharepoint-online:list-create
	config-ref="Sharepoint_Online"
	doc:name="Sharepoint Online"
	baseTemplate="GENERIC_LIST"
	title="Title">
  <sharepoint-online:list ref="#[payload]"/>
</sharepoint-online:list-create>

Or define the attributes in the connector itself:

<sharepoint-online:list-create
	config-ref="Sharepoint_Online"
	doc:name="Sharepoint Online"
	baseTemplate="GENERIC_LIST"
	title="Title">
  <sharepoint-online:list
  	contentTypesEnabled="true"
	description="Description"/>
</sharepoint-online:list-create>

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

<sharepoint-online:list-delete
	config-ref="Sharepoint_Online"
	doc:name="Sharepoint Online"
	listId="LIST_ID" />

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.

Query List Items

Using the query builder:

In the left panel, each visible list appears. In the right panel, the fields of a selected list appear. If the field is a Lookup Field, use either SharepointListReference or SharepointListMultiValueReference.

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:

{
    "Title": "A title",
    "LookupFieldId": {
        "id": "1",
        "lookupListId": "aaaa-1111-bbbb-2222"
    },
    "MultiValueLookupFieldId": {
        "ids": [
            1,
            2,
            3
        ],
        "lookupListId": "cccc-3333-dddd-4444"
    }
}

Use this object with other connectors to retrieve the referenced object and with a for each component:

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

{
    "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:

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

You can use the internal or title field names in DSQL queries, as well as in other list’s operations that follow.

For example for the previous query:

SELECT AuthorId, Created, List3MultiId FROM <LIST_ID>

Replace <LIST_ID> with the list ID.

If titles follow Author, Date created, and Details, you can write the query using field names:

SELECT Author, 'Date created', Details FROM Inventory

You can mix internal and title fields:

SELECT AuthorId, 'Date created', List3MultiId FROM Inventory

Using internal and/or title fields is only supported within the following list operations:

Adding a new item to the list
Updating an existent item in the list
Querying items in the list

Note: To filter by a datetime field type, write the value using ISO-8601 format when specified in a DSQL clause, for example: Created > 2017-01-01T00:00:00-03:00.

Operations: File and Folder API

Using the File and Folder API allows you to create, retrieve, update, delete files and folders, 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.

To Create and Delete a Folder

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

The resulting flow appears as:

<sharepoint-online:folder-create config-ref="Sharepoint_Online"
url="/path/to/folder" doc:name="Sharepoint Online"/>

<sharepoint-online:folder-delete config-ref="Sharepoint_Online"
url="/path/to/folder" doc:name="Sharepoint Online"/>

Add a File

Upload a file by selecting a physical file or passing an input stream to the connector. A file is uploaded to a server using a relative URL. For example, you can use this example with a File Connector to upload files to a list.

Using an input stream:

<sharepoint-online:file-add config-ref="Sharepoint_Online"
fileServerRelativeUrl="/path/to/folder/filename"
fileContentStream-ref="#[payload]" overwrite="true"
doc:name="Sharepoint Online"/>

To upload large files, configure your SharePoint and IIS servers:

Set the Maximum Upload Size to 2047MB (max) at the SharePoint management console for the site.
Set the connection timeout for the IIS site to a high value.
Set the Maximum Allowed Content Length to 2147483647 for an IIS app at request filtering.

Note: The SharePoint REST API that the connector uses supports uploading files up to 2 GB. When working with large files, provide the system local path to the file in the localFilePath parameter value. This is the most efficient way to upload a file through the connector.

To Get 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:

<sharepoint-online:file-get-content config-ref="Sharepoint_Online"
doc:name="Sharepoint Online"
fileServerRelativeUrl="/path/to/folder/filename"/>

Get File Metadata

This operation requires only the file relative path:

<sharepoint:file-get-metadata config-ref="Sharepoint_Online" fileServerRelativeUrl="/Shared Documents/My File.txt" doc:name="Sharepoint Online">
</sharepoint:file-get-metadata>

Update File Metadata

This operation requires that the relative path of the file and its key-value properties be updated. The following example shows how to rename a file and its title:

<sharepoint:file-update-metadata config-ref="Sharepoint_Online"
  fileServerRelativeUrl="/Shared Documents/My File.txt" doc:name="Sharepoint Online">
	<sharepoint:updated-properties>
		<sharepoint:updated-property
		  key="Title">New Title Value</sharepoint:updated-property>
		<sharepoint:updated-property
		  key="FileLeafRef">NewFileName.txt</sharepoint:updated-property>
	</sharepoint:updated-properties>
</sharepoint:file-update-metadata>

Query Files and Folders

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

Using the query builder:

In 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.
In the right panel, for every document list, the same fields appear.
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:

Example:

<sharepoint-online:file-query config-ref="Sharepoint_Online" query="dsql:SELECT Author,ModifiedBy,Name,ServerRelativeUrl FROM #[header:inbound:documentListName]" recursive="true" doc:name="Sharepoint Online"/>

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

Other File Operations

You can Approve, Check In, Check Out, Deny, Publish, Undo Checkout, and Unpublish. Specify the file URL, and if needed, pass an additional comment as a parameter.

<sharepoint-online:file-publish config-ref="Sharepoint_Online"
doc:name="Sharepoint Online" fileServerRelativeUrl="" comment=""/>

Set File Metadata with the Update List Item Operation

You can get and set metadata on files to upload 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:

<flow name="sharepoint_demo_fileAddWithMetadata"
   doc:name="sharepoint_demo_fileAddWithMetadata">
   <http:inbound-endpoint exchange-pattern="request-response" host="0.0.0.0"
     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>

Resolve 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:

<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.

Attach a File to a List Item

To attach a file to a list item, use the ResolveObject operation:

<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__Online_Connection"
     query="dsql:SELECT ID,Title FROM LIST_ID 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__Online_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:

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.
Read a file into an input stream. Here it’s from c:\temp (find the path in the flow to replace it).
Create the list item attachment with the file.

Execute Direct Calls Using 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> converts to a JSON string.
String contains 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.

Work with the 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:

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":

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

You can use any language that can construct a List<string> for the multi-select column results property.

The following pseudo code demonstrates how to set Choice #1 and Choice #2 as the values for the ChoiceMultiSelect column:

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 executing an operation an error occurs, a SharepointException is thrown with a message about the error.

About Frequently Asked Questions

Which versions of SharePoint are supported by this connector?

The SharePoint Online connector supports SharePoint Online. Use the Sharepoint 2013 Connector for Sharepoint 2013 On-Premises.
What authentication schemes are supported by this connector?

For SharePoint Online, authentication using standard SharePoint online user credentials is supported as well as Okta authentication.
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 using the ResolveObject and ResolveCollection operations.
Are 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, 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 available in the Sharepoint Online Connector Demo
What Mule editions can I use this connector with?

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