How and why I built a Microsoft Flow Custom Connector to integrate an external CRM system with SharePoint

About me

I'm a SharePoint and Microsoft Dynamics 365 CRM developer, a Microsoft Certified Technology Specialist, Microsoft Power Platform ninja, a husband and a pet lover. I've been writing SharePoint and Dynamics CRM solutions for the past 14 years and I'm having a blast with the tools and technologies that I work with in my day to day life.

I enjoy training young technology enthusiasts just as much as I enjoy learning myself.

The problem I wanted to solve

In January 2019, I was contacted by Paul from my client's office, who introduced me to an interesting challenge. Paul wanted to create an item in a SharePoint list whenever a deal was created in "Pipeline Deals" (a web based CRM system). He couldn't find any out of the box integrations, so he insisted on using WebHooks and writing code to accomplish this task.

I had something else in mind.

What is Creating a Microsoft Flow Custom Connector to integrate external systems with SharePoint?

I created a Microsoft Flow Custom Connector to solve this problem without writing any code. I used the connector in a simple Flow to pull the details of the newly created deal and created a SharePoint list item using the fields from the deal details. Problem solved!

Tech stack

The best thing that happened in the last 5 years in technology world is the evolution of low-code no-code tools and platforms. As a best practice, I always incline towards building reusable solutions that are easy to maintain, especially in the cloud environments.

Having said that, I decided to choose Microsoft Flow and it's Custom Connector offering along with the API and Automation options provided by Pipeline Deals .

This whole solution is very generic and can be easily applied to building integrations between other external systems and SharePoint (or other Office 365 apps).

The process of building Creating a Microsoft Flow Custom Connector to integrate external systems with SharePoint

I created a Microsoft Flow Custom Connector for Pipeline Deals using it's API methods for Deals (https://app.pipelinedeals.com/api/docs/resources/deals).

I then used this connector in the Flow that I created separately, leveraging the Pipeline Deals CRM System's Automation functionality which makes an HTTP POST Request when certain events happen (WebHooks), such as deal created, deal updated, deal deleted etc.

The Flow would execute each time a deal is created in Pipeline Deals and the custom connector action would pull all the details of the deal. Once these details are available in Flow, it's simple to create a SharePoint list item with the required fields.

Let's walk through the steps of building a custom connector:

1. Setup a trial account for " Pipeline Deals" - https://www.pipelinedeals.com/

2. Obtain the API Key from Account Settings--> PipelineDealsAPI
   https://app.pipelinedeals.com/admin/#/api

3. Create custom connector in Microsoft Flow as follows:

• Sign in to https://flow.microsoft.com with your Office 365 login
• Click on the gear icon on the top right corner and select "Custom Connectors" from the menu
• Click on "Create custom connector" and enter a name for your connector

• Fill in the details as shown below for General Tab

• Fill in the details as shown below for Security Tab

• Now that we have enetered the basic information, it's time for fun stuff. We will now create an action in the Definition Tab of the connector. Click on the New Action button on the left and fill the details as shown below.

Note: In this example we are only going to be using the GetDealById method of the Pipeline Deals API.

• In the Request section of the action, click on the "Import from sample" button and fill out the details as follows:

Note: The end point url for GetDealById method is "https://api.pipelinedeals.com/api/v3/deals/{id}.json?api_key=<your key>"

• Make sure that the Validation section shows "Validation succeeded."

That's it, your custom connector is ready. Now what?

4. Create a Microsoft Flow to consume the connector

• Note down the HTTP POST URL which is generated when you save the flow. We will be using this in the next step.
• The request body JSON schema for the first step in the flow is:

{
    "event_model": "Event model name",
    "event_action": "event action name",
    "event_model_id": 1234,
    "trigger": "Hello World"
}

• Use the following expression to extract the Deal Id from the first step in the flow. This Id will be used in the "Id" parameter of the custom connector in second step of the flow.

int(triggerFormDataValue('event_model_id'))

5. Let's create the Automation in Pipeline Deals.

• Go to your Account Settings--> Automations--> click "Create Automation"

• Use the HTTP POST URL from your Flow (previous step), this URL will be used in the Target URL of your Automation's "Do This:" section. Follow the steps below:

6. Test your Flow

• If you followed all the steps, then your Flow should trigger whenver a deal is created in Pipeline Deals

Challenges I faced

The biggest challenge was to figure out the correct end point and the request schema format. These challenges are not too difficult to sort out especially if there's proper documentation available. However, this is subjective to the external system you want to integrate with Office 365 ) using Flow.

Key learnings

Building a Custom Connector is simple as long as the correct end points are available. Custom Connectors are very powerful, especially for the folks in your team who are not very technically sophisticated. They don't need to write code, they can simple use the connector's actions and use them in their Flows to build awesome solutions.

Tips and advice

Always make sure that you enter the schema format exactly how it's mentioned in the API documentation, even the slightest mistake could end up being an hour long trial and error.

Final thoughts and next steps

Creating reusable components is the best way to execute efficient and effective development practices in an organization. A low-code no-code solution is not only easier, faster and cost effective to create but also less complex and time consuming to support.

Microsoft Flow is capable of handling complex tasks and allows us to create complex integrations between different platforms whether on premise or on cloud.