SharePoint Online and Microsoft Flow with custom action code in Azure Functions

Let’s start today with new way of building workflows from Microsoft – Microsoft Flow.
Because many of companies use SharePoint as first and best choice for internal portal platform I want to connect Microsoft Flow with it.

Microsoft Flow is like a wizard and you can create a lot of standard business flows with just few clicks and settings. But because we are developers, we want to create something more – we want to add to our flow some custom action, created by Azure Functions.

Microsoft Flow is located on this URL: https://flow.microsoft.com

It can be connected to many types of platforms like this in image below.

051816_1545_MicrosoftFl4-1

We have a lot of different flow templates predefined and we can simply use it.

2017-08-31_1418

Go to My flows and create new one from blank. Choose trigger When an item is created.

2017-08-31_1422

Connect it to SharePoint Online site, where you have some test list named Task (Task List Template).

2017-08-31_1425

Add new action step and search for HTTP type of action.

2017-08-31_1426

Now you have to go to Azure portal: https://portal.azure.com

Firstly create new SQL database named SharePointTaskFlow in which you will have one table with mail templates.

Create table with this script:

CREATE TABLE [dbo].MailTemplates
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [Type] NCHAR(6) NOT NULL,
    [Subject] NVARCHAR(100) NOT NULL,
    [Body] NVARCHAR(MAX) NOT NULL
)

Fill table with two mail templates:

  1. Type: CREATE
    Subject: New Task – {Title}
    Body:Hello {Author}!You have successfully created a task named {Title}.

    Best regards,
    RaspeR87

     

  2. Type: UPDATE
    Subject: Updated Task – {Title}
    Body:Hello {Author}!You have successfully updated a task named {Title}.

    Best regards,
    RaspeR87

Then copy connection string which you will use it in your Azure Function.

2017-08-31_1431

Create new Azure Function App named RR87TestFA.
Then create new function like on images below.

2017-08-31_1433

2017-08-31_1434

Go to Application settings and add connection string copied before.

2017-08-31_1437

Copy next part of code to your Azure function (run.csx file):

#r "System.Configuration"
#r "System.Data"

using System.Net;
using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;

class MailTemplate
{
    public string Subject {get; set;}
    public string Body {get; set;}
}

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    string type = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "type", true) == 0)
        .Value;
    string title = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "title", true) == 0)
        .Value;
    string author = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "author", true) == 0)
        .Value;

    MailTemplate responseObject = null;

    var str = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(str))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT Subject,Body FROM MailTemplates WHERE Type = '" + type + "'", conn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    responseObject = new MailTemplate
                    {
                        Subject = reader.GetString(0).Replace("{Title}", title),
                        Body = reader.GetString(1).Replace("{Title}", title).Replace("{Author}", author)
                    };
                }
            }
        }
    }

    return responseObject == null
        ? req.CreateResponse(HttpStatusCode.BadRequest, "Error (QueryString parameters missing or no results in DB")
        : req.CreateResponse(HttpStatusCode.OK, responseObject);
}

This code check for QueryString parameters named:

  • type: type of action on SharePoint list item (CREATE, UPDATE, DELETE)
  • title: title of task
  • author: author of task

Then we establish connection to database and query for CREATE type of mail template. We package subject and body replaced by corrected title and author into object from MailTemplate class and send it as response.

Copy function url because you will need it in next step.

2017-08-31_1500

Go back to Microsoft Flow. In previously created HTTP action define method and uri of your HTTP request like that in image below (uri is copied from previous step). Don’t forget to add type, title and author querystring parameters from List Item Fields.

2017-08-31_1456

Add new action step to your Microsoft Flow and search for Parse JSON type of action.

2017-08-31_1504

Set content to Body from previous step of your Microsoft Flow and define this kind of Schema:

2017-08-31_1505

{
  "type": "object",
  "properties": {
    "Subject": {
      "type": "string"
    },
    "Body": {
      "type": "string"
    }
  }
}

Add last action step to your Microsoft Flow and search for Send an email type of action.

2017-08-31_1507

Set it with email from Created By SharePoint List Item Field. Add Subject and Body from parsed JSON in previous step of Microsoft Flow.

2017-08-31_1508

Save your flow. Named it as you like.

Test your solution. Add new item to your Task list in SharePoint Online site.

2017-08-31_1512.png

In a few seconds you will receive new mail like that below – generated by your workflow in Microsoft Flow.

2017-08-31_1513

Cheers!
Gašper Rupnik

{End.}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: