Microsoft Flow: Transforming JSON

Here is a quick helper for your Microsoft Flow workflows:

I’m spending alot of time, using Variables and Compose actions to manipulate JSON structures, or to create text strings, or create arrays.

I was looking for a short cut – that is being able to use a single action, than lots of compose and set variable actions.

So the good news – there is one – its called Liquid templates – it allows you to transform JSON into anything else, including JSON. So you can transform from JSON => JSON, or JSON => HTML, etc.

The not so good news is it only works with Logic Apps, not Flow.

The better news is: there a npm module for that!

Create the Azure Function

So create a new Azure function, called ‘Liquid’, using JavaScript:

liquidcreate

Launch Kudu

liquiddev

Launch the command prompt, navigate to the folder:

D:\home>cd site\wwwroot\Liquid

Create a package.json file, with npm:

npm init

Answer the questions, and it will create a package.json file.

Now add the liquid-node package

npm install –save liquid-node

Now go back to the Azure function, and enter the following code into the index.js file


var Liquid = require('liquid-node');
var engine = new Liquid.Engine;

module.exports = function (context, req) {
    var template = req.body.liquidtemplate;    
    var data = req.body.data;

    engine.parseAndRender(JSON.stringify(template), data).then(
        function(result){
            context.log(result);
            context.res = {            
                status: 200,                   
                isRaw: true,
                body: result
            };
            context.done();
        }
    );
};

Quick walk through of the code:

  1. The request body will have two properties:
    1. liquidtemplate – this will be the Liquid Template
    2. data – this is the data that will be transformed
  2. It then called the Liquid engine to parse and render the transformed data
  3. It then sends the result back to the caller

Create the OpenAPI schema

The OpenAPI file will be as follows:


{
  "swagger": "2.0",
  "info": {
    "version": "1.0",
    "title": "Liquid Templates",
    "description": "Allows transforming by using the Liquid Templating engine"
  },
  "host": "myfunction.azurewebsites.net",
  "basePath": "/",
  "schemes": [
    "https"
  ],
  "consumes": [
    "application/json"
  ],
  "produces": [
    "application/json"
  ],
  "paths": {
    "/api/Liquid": {
      "post": {
        "description": "Transforms JSON using Liquid Templating engine",
        "summary": "Transforms JSON using Liquid Templating engine",
        "operationId": "LiquidTransform",
        "parameters": [
          {
            "name": "liquidbody",
            "in": "body",
            "description": "Liquid Transformation",
            "schema": {
              "$ref": "#/definitions/LiquidBodySchema"
            }
          }
        ],
        "responses": {
          "200": {
            "description": "OK",
            "schema": {
              "$ref": "#/definitions/DynamicLiquidSchema"
            }
          }
        }
      }
    }
  },
  "definitions": {
    "LiquidBodySchema": {
      "type": "object",
      "properties": {
        "liquidtemplate": {
          "type": "object",
          "description": "The Liquid Template to transform",
          "x-ms-summary": "Liquid Template"
        },
        "data": {
          "type": "object",
          "description": "Data to transform",
          "x-ms-summary": "Data"
        }
      }
    },
    "DynamicLiquidSchema": {
      "type": "object"
    }
  },
  "parameters": {},
  "responses": {},
  "securityDefinitions": {},
  "security": [],
  "tags": []
}

Quick walk through of the OpenAPI spec:

  1. Single call to /api/Liquid>
  2. Body is:
    
    {
       liquidtemplate: {name:'{{var1}}'},
       data: {var1: 'test', var2: 'test2'}
    }
    
  3. Returns the transformed data

Upload this as a custom connection in PowerApps

liquidupload

Add to your Flow

Now create a new flow, and add your new action:
liquidaddaction

Add your template, and your data, and it transform
liquidfact

If you would like to access the individual properties of the returned object, add a Parse JSON action
liquidparse

Put the template in as your sample json
liquidjsonsample

Then you can access the individual properties
liquidcardfacts

Hope this helps!

Advertisements

Actionable Messages [Part 3] – Inside an Enterprise Flow

I hope that you’ve read through the other two posts, about how to add a card in Flow, and how to process the actions in the card.

Now we going to dive head first into using it in the real world – in a large enterprise flow, and what are the gotchas to look out for. At the end of the article I’ll put all the ideas together, and show you how its done.

I’m going to reference a lot of other fellow SharePointer’s blogs here, as they all came up with the good ideas first, and others have probably come up with as elegant solutions for the same problems, this is a review of the strategies I’ve chosen, and some of the reasoning behind choosing them.

Feel free to add other ideas in the comments.

Variables, variables, and more variables

When developing a large workflow in Flow, variables are going to become your best buddy.

Lets first set out our goals, we want to be able to produce lots of different tasks in our workflow, and we want to reduce the amount of errors that we make when we create our JSON structures. So lets break down our actionable message into the following:

  • Instructions – this is the text we are going to include in our card that tells the user what to do. We would usually insert this into the text field of the first section of our card
  • Facts – this is usually a summary all the information, usually the list of fields from the form filled in to kick start the workflow
  • Actions – these are the actions that we want to be able to do on the card

If we store each of these three items into separate variables, then when we create the JSON of our card, we will see that it is much smaller, with less fields to modify between tasks – which should result in less mistakes.

We will find a few other items need to be stored in variables:

  1. URL of where to send the card – if you read the first part, you will see we created a separate flow, that actually sends the actionable card, this is the URL of this flow
  2. Image, Title and Comment of last responder – this something usually required in Corporate Workflows, where they want to see who responded to the last task, and what comment they left.

    For example, in an IT workflow, the team manager might leave a comment asking for some software to be installed on a new laptop, this then needs to be shown in the task sent to the IT team who is going to configure it.

  3. Teams and users – generally our workflows are designed to work with roles, not specific people, so we send a task to HR, or IT Team, not John or Jane in IT. So we should use variables to hold the actual person for the running instance.

Lets start our engines – initialize our variables

So lets start our new Flow, and add a whole set of Initialize Variable actions:

Heads up: It might look like a lot of Initialize Variables at first, but once you start producing your cards, you’ll see it works nicely, and reduces MISTAKES!

Creating our cards

To create our card, we need four things:

  1. Set our instructions;
  2. Set our Facts;
  3. Set our actions;
  4. Card

Probably our Facts could be set at the beginning of the flow, and then just reused – so add a list of all the fields from our starting form, and its done.

Our Actions will only need to be reset, if it changes from task to task.

Our instructions will change for every task.

The actual JSON of our card, will differ very little between cards, so we can do some good paste and copying.

How to paste and copy with Flow – this takes a little bit of practise. In my experience, it is dependant on the browser you are using to create your flow.

My preference is Chrome. This is why, if you are copying the following:

flowpaste

If you paste and copy from another browser into a text editor, like Visual Studio Code, it could come out like:

“activityImage”: “CardPicture×​”,
“activityTitle”: “Approved by: Last responder×​”,
“activitySubtitle”: “Comment left: Last comment×​”

See the little × next to the name of the variable, this means pasting back into an action isn’t going to work. If you use Chrome, it will copy as:

“activityImage”: “@{variables(‘CardPicture’)}”,
“activityTitle”: “Approved by: @{variables(‘Last responder’)}”,
“activitySubtitle”: “Comment left: @{variables(‘Last comment’)}”

This has copied it as with the functions, and now if you paste it into an action, it will work.

In your card, you will have the following two fields:

“@type”: “MessageCard”,
“@context”: “http://schema.org/extensions”

Double up the @ symbol when pasting and copying, so it would be
“@@type”: “MessageCard”,
“@@context”: “http://schema.org/extensions”

Or else it thinks it marks the start of a function.

Side note: I’ve noticed it also depends on the editor – Visual Studio Code works great. SubLime is bit temperamental, sometimes copying and pasting correctly, sometimes not.

State Machines

After a lot of discussions and experimentation, I must agree with Pieter Venstra and Serge Luca, state machines are definitely necessary.

Here are two excellent posts on implementing state machines in Flow:

  1. Office 365 – Flow – Implementing a State Machine using Microsoft flow
  2. https://sergeluca.wordpress.com/2018/01/21/pattern-for-implementing-state-machines-with-microsoft-flow/

Here are the important lessons learnt while implementing a state machine:

  1. First, you cannot see inside the do until loop
  2. Beware of errors
  3. Keep track of where you are

SLAs, reminders, and escalations

Business users usually have SLA’s – time limits to complete a task. If they miss an SLA they usually want one of the following to happen:

  • Get a reminder sent
  • Escalate to a manager
  • End the task

I’ll do a quick article on adding SLA’s to our Flow.

Group your actions

OK, this is a bit silly to state, but group your actions together, use Scopes to group, allowing you to navigate around your workflow a bit easier.

People Images

One of the nice things about Actionable Messages, is to make use of the Activity Image, Title and Subtitle to produce something like this:

activitimage

So, how do you get the image into your card?

To get the image, use the following:

data:image/JPEG;base64,@{base64(body(‘Respondent_Photo))}

Putting it together

When we send a task, the set of actions is:

  • Variables – Set Variable: Card Text
  • Variables – Set Variable: Card Actions
  • Variables – Set Variable: Card
  • Http Webhook – Sends the card
  • Data Operations – Parse JSON: Parse the response

At this point, we have composed the card, sent it, and received the response. Now we should get the respondent photo, name, and store these, so the set of actions is:

  • Office 365 Users – Get user photo (V2): Retrieves the photo
  • Office 365 Users – Get user profile (V2): Retrieves the respondent details
  • Variables – Set Variable: Last respondent
  • Variables – Set Variable: Last comment
  • Variables – Set Variable: Card Image

First Part – Sending the Card

So a flow looks like this:

cardvars

The Card Text are the instructions:

cardtext

The Card Actions, are the set of actions, such as:

cardaction

Here is the full JSON for the Card Action

The Card Facts, are the set of fields from the form that was filled out:

cardfacts

To create it, we need to create an array of facts, with the follow syntax:

createArray(
  json(concat({"name": "Field1", "value": "', triggerBody()?['Field1'], '"}')),
  json(concat('{"name": "Field2", "value": "', triggerBody()?['Field2'], '"}'))}'))
)

So basically, an array, with each item having two parameters:

  1. name – which is the name of the fact;
  2. value – which is the value of the fact.

Most of the time, some facts will only appear on a condition, such as a Temporary Staff member will have an end date to their contract, so then you can keep adding them as follows:

cardextrafact

The If statement is the condition

Then you add an Compose Action which has the following formula:

union(
  variables('Facts'),
  json('[{"name": "End Date", "value": "triggerBody()?['EndDate'] "}]')
)

The Set Variable will just set the Card Facts variable to the result of the Compose Action
cardupdatefact

The Card, is the full JSON of the card we are sending off to our Card Flow.

cardfull


{
  "users": [
    "@{variables('HR Manager')}"
  ],
  "card": {
    "originator": "12345678-9012-3456-7890-123456789012",
    "summary": "HR Workflow: HR Approval Task",
    "themeColor": "0072C6",
    "hideOriginalBody": true,
    "sections": [
      {
        "title": "**HR Workflow**",
        "startGroup": true,
        "activityImage": "@{variables('CardPicture')}",
        "activityTitle": "Approved by: @{variables('Last responder')}",
        "activitySubtitle": "Comment left: @{variables('Last comment')}",
        "text": "@{variables('CardText')}",
        "facts": @{variables('CardFacts')}
      },
      @{variables('CardActions')}
    ],
    "@@type": "MessageCard",
    "@@context": "http://schema.org/extensions"
  },
  "id": "@{guid()}",
  "listid": "@{triggerBody()?['ID']}",
  "list": "@{triggerBody()?['{Path}']}",
  "cardtype": "HRWorkflow"
}

A quick walk through of the card:

  1. The users are variables, so we can retrieve them through a query –
    for example retrieve who the HR manager is by querying AD, so when the next HR Manager arrives, we don’t need to touch our workflow;
  2. The Activity image, title and subtitle are variables – so we can assign them after every action, and then show them in the next action
  3. The text is a variable, allowing us to just update our variable
  4. The facts are already assigned to the Card Facts variable
  5. The Card Actions are already assigned to the Card Actions variable

Hopefully when you look at this you will see its much smaller than the full card json, and easier to copy and maintain across multiple tasks in a single workflow.

Now that we have our full Card JSON, we can send it off to our Card Flow, using the HTTP Webhook Action.

cardhook

The body property is the following function:

replace(string(variables('Card')), 'FUNCTIONURL', listCallbackUrl())

So we convert our Card variable – which is an JSON object – to a string, then we replace the token FUNCTIONURL with the Http Webhook callback url.

In the flow, it will then wait for the Azure Function (which we created in Part 2), to reply to the webhook url.

Once we receive the reply, when we have a Data Operations – Parse JSON action, which will parse the reply from our Azure Function

cardjsonsetup

We put in the following simple schema:

{
    "type": "object",
    "properties": {                
        "comment": {
            "type": "string"
        },
        "respondent": {
            "type": "string"
        },
        "choice": {
            "type": "string"
        }
    }
}

This gives us the comment, who responded, and the choice they made.

Second Part – Retrieving who dunnit

Add the following set of actions to record who responded to the task:

cardresponse

First, add a Get User Profile action, to retrieve the details of the respondent

cardrespondent

Add a Get User Photo action to get the photo of the respondent

cardrespphoto

Add a Set variable to save the full name of the respondent

cardlastrespondent

Add a Set Variable to save the comment left

cardlastcomment

Add a Set Variable action to save the photo

cardphoto

The formula is:

base64(body('Get_user_photo_(V2)'))

Now you have composed your card, send it, and processed the reply.

You can repeat this for each card, and have a fully functional Flow, that sends actionable cards, and receives the replies.

 

Actionable Messages [Part 2] – Processing the action, and sending it back to Microsoft Flow

This is Part 2 of my Actionable Message series.

OK, you’ve been to the Actionable Message playground, read up all about it, and now you excited to get started!

While the documentation will help you create the format of the card, and make it look good, you will find it ends with the following:

"actions": [
   {
     "@type": "HttpPOST",
     "name": "Submit",
     "target": "http://..."
   }
]

So how do you implement the https://... ?

Authentication – does it or doesn’t it?

Here is a quick lesson learnt, when you click on your action button – Outlook, Office 365 Outlook, and Microsoft Teams does send back a JWT token, so we do know who did it, but if you use an Azure Function that is integrated with Azure AD, you don’t get the message. (If you know why, leave a comment below).

Azure Functions – here we come!

To write the bit of code that receives the HttpPOST response, we are going to use Azure Functions.

Note – Just a note about Azure Functions – there are 2 hosting plans – Consumption Plan and App Service Plan

If you choose the Consumption plan – because of cost, be aware that after a certain time, the Function goes to sleep, and will then do a “cold” start when being called again, making your responses quite slow.

So we are going to create an Azure function, which is a normal HTTP trigger, and is not integrated with Azure AD. So click create new on your function app, and choose HTTP trigger (I’ve used JavaScript, you can choose any language you comfortable with).

functioncreate

Type the name of the response, and select Authorisation level to be Anonymous

FunctionConfig

So to create our function that is going to process our Actionable Message post, we need to do three things:

  1. Need to get the details of the person who submitted it;
  2. Send a message to the Microsoft Flow, so it can continue onto the next action;
  3. Return a card back to the Actionable Message.

Checking the Authentication Token

This is quite simple to do, because its already been done for us.

Go look on GitHub at the sample: (there is a c# sample too)

Action Request Token Verification Node.js Sample

You’ll see from the sample that we need to do the following to get it to work in our Azure function:

  1. Copy the files from GitHub into our function, so on your dev box, get the GitHub sample:
    git clone https://github.com/OfficeDev/outlook-actionable-messages-node-token-validation.git
  2. On your Azure Functions, click on Platform features, and launch Kudu.
    kudu
  3. Launch the command shell
    kuducmd.png
  4. Click “site”, “wwwroot”, and there will be a folder with the same name as your function. Inside currently only two files
    kudufolder
  5. Add the following files:
    1. ActionableMessageTokenValidator.js
    2. OpenIdMetadata.js
    3. package.json
  6. To get the node modules install, just run “npm install” to get the packages installed
    kudunpm
  7. Now we can edit our function to get the user:
    module.exports = function (context, req) {
         // Get Token
         var token;
    
         context.log("Starting");
    
         if (req.headers && req.headers.hasOwnProperty('authorization')) {
                   var auth = req.headers['authorization'].trim().split(' ');
                   if (auth.length == 2 && auth[0].toLowerCase() == 'bearer') {
                        token = auth[1];
              }
         }
    
         if (token){
              var validator = new validation.ActionableMessageTokenValidator();
    
              validator.validateToken(
                   token,
                   "https://mysite.onmicrosoft.com",
                   function (err, result) {
                        if (err) {
                             context.log('error: ' + err.message);
                             context.res= {
                                  status: 401
                             };
                             context.done();
                        } else {
                             context.log("Valid Token");
                             var email = result.actionPerformer.toLowerCase();
                             context.log(email);
    
                             context.res = {
                                  headers: {
                                       'CARD-ACTION-STATUS': 'Successfully completed task.'
                                  },
                                  status: 200
                             };
                             context.done();
                        }
                   }
              );
         }
         else
         {
              context.log("NO TOKEN");
              context.res= {
                   status: 401
              };
              context.done();
         }
    };
    

    Here is a brief explanation of the code: The first part

    var token;
    
    if (req.headers && req.headers.hasOwnProperty('authorization')) {
         var auth = req.headers['authorization'].trim().split(' ');
          if (auth.length == 2 && auth[0].toLowerCase() == 'bearer') {
              token = auth[1];
         }
     }
    

    This get the token from the http header, and stored it in the token variable.

    var validator = new validation.ActionableMessageTokenValidator();
    

    Initialises the Actionable Message Token Validator.

    validator.validateToken(
           token,
           "https://mysite.onmicrosoft.com",
           function (err, result) {
              ...
           }
    )
    

    This validates the token.

    NOTE: The documentation is quite obscure, the URL https://mysites.onmicrosoft.com is called service target URL. Putting both the mysite.onmicrosoft.com address, and the mysite.sharepoint.com failed to pass the validation test for the domain.

    You can disable this check, by changing the following line in the “Actionable MessageTokenValidator.js” file, find the line:

    jwt.verify(token, key, verifyOptions);

    remove the verifyOptions, so it becomes:

    jwt.verify(token, key);

    This way, you don’t need to spend ages trying to figure out what the “Service Target URL” means for Office 365.

    The last bit of the code, finds the email address

    var email = result.actionPerformer.toLowerCase();
    
  8. Now that we have the user, we need to send a reply back to Microsoft Flow. Before we show the code, and talk through, there is small architectural decision to make.

    Remember at this point, our user is sitting with a swirling circle waiting for our Azure Function to return the reply, so we have 3 choices:

    1. Send message to the Microsoft Flow inline, and then return the reply;
    2. Put a message in a queue, so that another Azure function can reply to our Microsoft Flow;
    3. Fan out the Azure function, using Durable functions

    Durable functions for this is a lot more effort than needed, and there doesn’t seem to be much difference between inserting into a queue, and replying to the Microsoft Flow under light load. If your reply starts to take longer under load, first separate the function out using a queue.

    In our Actionable Message, our HttpPOST action is returning the following:

     "@type": "ActionCard",
            "actions": [
              {
                "@type": "HttpPOST",
                "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Approve\", \"returnurl\":\"RETURNURL\"}",
                "name": "Submit",
                "target": "https://mysite.azurewebsites.net/api/Action"
              }
    

    So if we look at the body that is being sent to our Azure Function, the url that the Microsoft Flow is waiting for a response on is in the returnurl field.

    So first we get the host and path that we need to send a response to Microsoft Flow on:

    var returnhost = req.body.returnurl.slice(req.body.returnurl.indexOf("//")+2, req.body.returnurl.indexOf('.com')+4);    
    var returnpath = req.body.returnurl.substr(req.body.returnurl.indexOf('.com')+4);                     
    

    Now that we got the destination to notify Microsoft Flow, let send all the details back to them, so get the body of the response:

    var post_data = JSON.stringify({
        'choice': req.body.choice,
        'respondent': email,                        
        'comment' : req.body.comment                        
    });
    

    We sending back what the choice field, the comment field, and who performed the action.

    We then create the header:

    var header = {
        'Content-Type': 'application/json',
        'Content-Length': post_data.length
    };
    

    The POST options

    var post_options = {
        host: returnhost,
        port: '443',
        path: returnpath,
        method: 'POST',
        headers: header
    }; 
    

    And then we send it to Microsoft Flow

    var post_req = https.request(post_options, function(res) {
        res.setEncoding('utf8');
        res.on('data', function (chunk) {
            context.log('Response: ' + chunk);
        });
    
    });
    post_req.on('error', (e) => {
        context.log(e);
    });
    post_req.write(post_data);
    post_req.end()  
    

    At this point, Microsoft Flow receives the reply, and continues to the next Action.

  9. The last part is returning a response to the card. One of the great features of Actionable Messages, is that you can send a response card back.

    So lets create a response card, create a facts array, with a list of the fields submitted.

    var facts =[
       {"name": "Comment left", "value": req.body.comment}
       {"name": "Submitter email", "value": email}
    ];
    

    Send the rest of the card in the response

    context.res = {
        headers: {
            'CARD-ACTION-STATUS': 'Successfully completed task.',
            'CARD-UPDATE-IN-BODY': 'true'
        }, 
        status: 200,                   
        isRaw: true,
        body:{ 
            "@type": "MessageCard",
            "@context": "http://schema.org/extensions",
            "summary": summary,
            "themeColor": "188D18",
            "sections": [
                {
                    "title": "**Task Summary**",
                    "startGroup": true,
                    "activityTitle": "This was completed",
                    "activitySubtitle": "Thank you for completing the task.",
                    "facts":facts
                }
            ]
        }                    
    };
    context.done();
    

We are now completed the Azure Function, which receives the HttpPOST, notifies Microsoft Flow, and sends back a response card.

Actionable Messages [Part 1] – Add the card to Microsoft Flow

This is Part 1 of my Actionable Messages Series. The full series will be:

  1. Add the card to Microsoft Flow;
  2. Processing the actions, and sending it back to Microsoft Flow;
  3. Using Actionable Message in an enterprise Flow;
  4. Tips and tricks – give your cards the professionals edge.

What are cards?

OK – bear with me, I know you read the docs, and experimented with the Message Card Playground, but we have to start somewhere, so lets explain cards first.

Pick up your phone, and switch it on, you’ll see something similar to this:

mobile

These are called cards – they are little cards, with just enough information for you to understand what it is about, and make a decision about what you want to do.

Applications like Twitter, Facebook and others, show information in similar ways, and allow you to act on them, so in Twitter, you will see a card similar to this:

inspire

At the bottom there are different actions that you can do, such as Re-tweet, or reply etc. These are called Actions.

So what business users have asked for is to be able to use Cards in business.

So Microsoft has come up with Actionable messages – cards with a standard format, that you can perform Actions on.

Microsoft Flow – why do I need cards?

Microsoft Flow is a fantastic workflow engine, and one of the common uses is to build human business workflows – these are workflows that pass tasks from Team to Team in  a business to help streamline a business process.

Often, a task will require the following:

  1. To collect fields in the task, such as a comment, a date, or pick an option off a list;
  2. Have multiple outcomes – so might have Approve, Reject, Review or even different options.

In Microsoft Flow, there are currently two ways to send a task:

  1. Send an approval email in Microsoft Outlook;
  2. Start an approval action, which sends an approval via email and the Microsoft Flow app.

The main constraints of these two options are that you cannot collect any fields, except for a comment, and they are not that customisable.

So the best way to send Tasks, is using Actionable Messages.

How do I send a card in Microsoft Flow?

First, you will need to create the card, which is a JSON object. So navigate to the Message Card Playground, and create your card.

The card we going to create in this tutorial is going to have the following parts:

  1. Single section, with a simple list of facts;
  2. Two actions, Approve and Reject

You will see that once you can do this simple card, any type of card can be sent with Microsoft Flow.

Card JSON

  1. First add the section, which will look like this:

    {
    "startGroup": true,
    "title": "**Pending approval**",
    "text": "Please review this request",
    "facts": [
    { "name": "Name:", "value": "Mr Smith" },
    { "name": "Company", "value": "My Dev Comp"},
    { "name": "Address", "value": "1 First Avenue, Second City, Third State"}
    ]
    }

    This will produce the following on the card
    carfacts

  2. Next, add the list of actions:
    {
        "potentialAction": [
            {
                "@type": "ActionCard",
                "name": "Approve",
                "inputs": [
                    {
                        "@type": "TextInput",
                        "id": "comment",
                        "isMultiline": true,
                        "title": "Reason"
                    }
                ],
                "actions": [
                    {
                        "@type": "HttpPOST",
                        "name": "Submit",
                        "target": "FUNCTIONURL",
                        "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Approve\", \"returnurl\":\"CALLBACKURL\"}"
                    }
                ]
            },
            {
                "@type": "ActionCard",
                "name": "Reject",
                "inputs": [
                    {
                        "@type": "TextInput",
                        "id": "comment",
                        "isMultiline": true,
                        "title": "Reason"
                    }
                ],
                "actions": [
                    {
                        "@type": "HttpPOST",
                        "name": "Submit",
                        "target": "FUNCTIONURL",
                        "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Reject\", \"returnurl\":\"CALLBACKURL\"}"
                    }
                ]
            }
        ]
    }
    

    Lets look at some of the items in this json:

    1. FUNCTIONURL – this should be replaced by the URL of the Azure Function that we will write in Part 2;
    2. CALLBACKURL – this is going to be the URL that Microsoft Flow is going to wait on for a reply;
    3. body is the information that is going to be submitted in the POST request, that will occur when our button is pressed.

    This will produce the following on the card:

    CardApprove

  3. The completed JSON card will look like this:
    {
        "@type": "MessageCard",
        "@context": "http://schema.org/extensions",
        "summary": "This is the summary property",
        "themeColor": "0075FF",
        "sections": [
            {
                "startGroup": true,
                "title": "**Pending approval**",
                "text": "Please review this request",
                "facts": [
                    { "name": "Name:", "value": "Mr Smith" },
                    { "name": "Company", "value": "My Dev Comp"},
                    { "name": "Address", "value": "1 First Avenue, Second City, Third State"}
    
                ]
            },
            {
                "potentialAction": [
                    {
                        "@type": "ActionCard",
                        "name": "Approve",
                        "inputs": [
                            {
                                "@type": "TextInput",
                                "id": "comment",
                                "isMultiline": true,
                                "title": "Reason"
                            }
                        ],
                        "actions": [
                            {
                                "@type": "HttpPOST",
                                "name": "Submit",
                                "target": "FUNCTIONURL",
                                "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Approve\", \"returnurl\":\"CALLBACKURL\"}"
                            }
                        ]
                    },
                    {
                        "@type": "ActionCard",
                        "name": "Reject",
                        "inputs": [
                            {
                                "@type": "TextInput",
                                "id": "comment",
                                "isMultiline": true,
                                "title": "Reason"
                            }
                        ],
                        "actions": [
                            {
                                "@type": "HttpPOST",
                                "name": "Submit",
                                "target": "FUNCTIONURL",
                                "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Reject\", \"returnurl\":\"CALLBACKURL\"}"
                            }
                        ]
                    }
                ]
            },
            {
                "startGroup": true,
                "activitySubtitle": "Please complete the task."
            }
        ]
    }
    

Sending the card in Microsoft Flow

To send the card in Microsoft Flow, we are going to:

  1. Add the card JSON into the flow
  2. Create the sender
  3. Send the card

Add the card JSON into the flow

Add the JSON into the flow – simple.

Ummm, lets talk about how you assign something in Microsoft Flow. We can use variables. So this is simple, add an “Initialise Variable” action:

varinit

When you initialise the variable you have the option of either creating it as an object, or a string

vartype

If you look back at our JSON, you will see we need to replace the token CALLBACKURL with an actual url, so lets make it string, then we can use the @replace function to replace it.

The advantage of variables, is we can update them, using the “Set Variable” action

varset

This is a great way to store the card, and reuse it.

NOTE: The “Initialise Variable” action cannot be add inside any scope – so not inside a scope, or a condition, or switch case. In a large workflow, this could result in it being very long.
vartop

The alternate way is to make use of the “Compose” action

composeaction

The compose action can be included at any scope.

In this example, we going to use variables

Create the JSON

Add an “Initialise Variable” action, and insert our JSON

varcard

Create the sender

First, a short explanation of what we are trying to achieve.

We need a callback URL so we can notify Microsoft Flow. There is currently only one way to get a callback url in Microsoft Flow, that is to use the “HTTP – HTTP Webhook” action. So that means your HTTP Webhook has to call something, that will send the message for you, in order for you to use your callback url.

Hold on, if I’m calling something else, can I create my own custom connector, this will simplify everything?

So glad you asked, because I spent ages making custom connectors until I discovered the following in the documentation that explains why I cannot create a custom connection, which supports callback’s myself:

webhook

We are now going to create a Flow (or Azure Logic Apps), that will send out our Card.

We will need to expand our JSON, so we include:

  • List of emails to send the card to

So our JSON structure will be

{
    "users": [
        "user1@email.com", "user2@email.com", "user3@email.com"
    ],
    "card": {
        ... Card JSON ...
    }
}

In our new flow, we create it off the “Request” trigger

reqtrigger

The request body JSON schema will be simply an object:

{
    "type": "object"
}

Set the method to POST, so the Request is

requestjson

Now interpret our JSON, by adding a “Parse JSON” action

parsejson

Normally to use this action, you give it a sample of your input, and it will produce the schema for you. I have gone through the entire Actionable Message specification, and added all possible parameters. Here is the complete schema:

{
    "properties": {
        "card": {
            "properties": {
                "@@context": {
                    "default": "http://schema.org/extensions",
                    "description": "Required. Must be set to http://schema.org/extensions.",
                    "title": "@@context",
                    "type": "string",
                    "x-ms-summary": "@@context",
                    "x-ms-visibility": "internal"
                },
                "@@type": {
                    "default": "MessageCard",
                    "description": "Required. Must be set to MessageCard.",
                    "title": "@@type",
                    "type": "string",
                    "x-ms-summary": "@@type",
                    "x-ms-visibility": "internal"
                },
                "correlationId": {
                    "description": "The correlation Id property simplifies the process of locating logs for troubleshooting issues. Were commend that when sending an actionable card,your service should set and log a unique UUID in this property.\nWhen the user invokes an actionon the card, Office365 sends the Card-Correlation-Id and Action-Request-Id headers in the POST request to your service. Card-Correlation-Id contains the same value as the correlationId property in the card. Action-Request-Id is a unique UUID generated by Office365 to help locate specific action performed by a user. Your service should log both of these values when receiving action POST requests.",
                    "format": "uuid",
                    "title": "correlationId",
                    "type": "string",
                    "x-ms-summary": "correlationId"
                },
                "hideOriginalBody": {
                    "description": "Only applies to cards in email messages\nWhen set to true, causes the HTML body of the message to be hidden. This is very useful in scenarios where the card is a better or more useful representation of the content than the HTML body itself, which is especially true when the card contains actions(see below.)\nConsider hiding the original HTML body If the card itself contains all the information a user would need If the content of the card is redundant with the content of the body Do always include a nice HTML body, even if it is going to be hidden.The HTML body is the only thing an email client that doesn't support cards will be able to display. Furthermore, cards are not included when replying to or forwarding emails, only the HTML body. Don't hide the body when it is complementary to the information presented in the card.For example, the body of an expense report approval might describe the reporting details while the card just presents a quick summary along with approve/decline actions.",
                    "title": "hideOriginalBody",
                    "type": "boolean",
                    "x-ms-summary": "HideOriginalBody"
                },
                "originator": {
                    "description": "Required when sent via email, not applicable when sent via connector. For actionable email, MUST be set to the providerID generated by the Actionable Email Developer Dashboard.",
                    "title": "originator",
                    "type": "string",
                    "x-ms-summary": "originator,"
                },
                "potentialAction": {
                    "description": "A collection of actions that can be invoked on this card.",
                    "items": {
                        "type": "object"
                    },
                    "maxItems": 4,
                    "minItems": 0,
                    "type": "array",
                    "x-ms-summary": "PotentialActions"
                },
                "sections": {
                    "description": "Sections",
                    "items": {
                        "type": "object"
                    },
                    "type": "array",
                    "x-ms-summary": "Sections"
                },
                "summary": {
                    "description": "The summary property is typically displayed in the list view in Outlook, as away to quickly determine what the card is all about.\nDo always include a summary. Don't include details in the summary. For example, for a Twitter post, a summary might simply read \"New tweet from @someuser\" without mentioning the content of the tweet itself.",
                    "title": "summary",
                    "type": "string",
                    "x-ms-summary": "summary,"
                },
                "text": {
                    "description": "The text property is meant to be displayed in a normal font below the card's title. Use it to display content, such as the description of the entity being referenced, or an abstract of a news article.\nDo use simple Markdown, such as bold or italics to emphasize words, and links to external resources. Don't include any call to action in the text property. Users should be able to not read it and still understand what the card is all about.",
                    "type": "string",
                    "x-ms-summary": "Text"
                },
                "themeColor": {
                    "description": "Specifies a custom brand color for the card. The color will be displayed in a non-obtrusive manner.\nDo use themeColor to brand cards to your color. Don't use themeColor to indicate status.",
                    "type": "string",
                    "x-ms-summary": "Theme Colour"
                },
                "title": {
                    "description": "The title property is meant to be rendered in a prominent way, at the very top of the card. Use it to introduce the content of the card in such a way users will immediately know what to expect.\nExamples Daily news New bug opened Task <name of task> assigned Do keep title short, don't make it a long sentence. Do mention the name of the entity being referenced in the title. Don't use hyperlinks (via Markdown) in the title.",
                    "type": "string",
                    "x-ms-summary": "Heading"
                }
            },
            "type": "object"
        },
        "users": {
            "description": "List of Users",
            "items": {
                "type": "string"
            },
            "type": "array",
            "x-ms-summary": "users"
        }
    },
    "type": "object"
}

Now we need to create the HTML for the email message, so add a “initialise variable” action, and add the following:

logicvar

The “card” is comes from the Parsed JSON, which is

@{body(‘Parse_JSON’)?[‘card’]}

Now to send the actual email, use the “SMTP – Send Email” action

smtpsend

If this is the first time you are using the “SMTP – Send Email” action, it will ask for the connection details, which are:

  • Connection Name – Office365
  • SMTP Server Address – smtp.office365.com
  • User Name – your email address, or another account which you have the username and password for, such as a service account
  • Password – the password (please record in the comments below)
  • SMTP Server Port – 587
  • Enable SSL – tick this

NOTE: What account to use? This is the account that you registered in the Actionable Message Developer Dashboard. If used any other account, when you send to anyone else, it will not work.

So on the Dashboard, when you create a new provider, and its asks for for the “Sender address”, this is the one you must authenticate the SMTP Send email action with
dashboardemail

The Connection will appear as:

smtpconnect

Populate the fields:

  • From – the account specified in the Actionable Message Developer Dashboard;
  • To – select the “users – item” from the Parse JSON structsntpusers
  • Subject – add the subject from the Parse JSON
  • Body – add the EmailMessage variable
  • Is HTML – mark as true
  • Importance – mark as normal

The SMTP – Send Email action will be configured as:

smtpfull

The sending workflow is now complete – press the “Create Flow” button at the top

createflow

When you edit the flow again, at the top of the Request trigger, is the HTTP POST URL – take a copy of it.

httpurl

The completed “Sending Message Flow” will look like:

sendingcard

Send the card

Right, now down to the final set of actions, in order to get our Actionable Message to our user.

In our flow, we currently have:

  • Initialise Variable action to set up our card

We now need to add two more actions to get our Actionable Message to our user, which are:

  • Add the list of users to send to
  • Send this to our “Sending Cards” Flow, so it can be received by our user

So in our Flow, add a compose action, before the “Initialise Variable” action for our card, and call it Users.

We will create a list of users to send to, so using the “createArray” method, we add the users, like:

createArray(‘user1@email.com’, ‘user2@email.com’, ‘user3@email.com’)

composearray

Now edit our “Initialise Action” for our card, and add in the additional JSON.

So at the begining, add
jsonbe

and at the end, just add an extra }.

So our JSON now is:

{
    "users": @{outputs('Users')},
    "card": {
        "@type": "MessageCard",
        "@context": "http://schema.org/extensions",
        "summary": "This is the summary property",
        "themeColor": "0075FF",
        "sections": [
            {
                "startGroup": true,
                "title": "**Pending approval**",
                "text": "Please review this request",
                "facts": [
                    {
                        "name": "Name:",
                        "value": "Mr Smith"
                    },
                    {
                        "name": "Company",
                        "value": "My Dev Comp"
                    },
                    {
                        "name": "Address",
                        "value": "1 First Avenue, Second City, Third State"
                    }
                ]
            },
            {
                "potentialAction": [
                    {
                        "@type": "ActionCard",
                        "name": "Approve",
                        "inputs": [
                            {
                                "@type": "TextInput",
                                "id": "comment",
                                "isMultiline": true,
                                "title": "Reason"
                            }
                        ],
                        "actions": [
                            {
                                "@type": "HttpPOST",
                                "name": "Submit",
                                "target": "FUNCTIONURL",
                                "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Approve\", \"returnurl\":\"CALLBACKURL\"}"
                            }
                        ]
                    },
                    {
                        "@type": "ActionCard",
                        "name": "Reject",
                        "inputs": [
                            {
                                "@type": "TextInput",
                                "id": "comment",
                                "isMultiline": true,
                                "title": "Reason"
                            }
                        ],
                        "actions": [
                            {
                                "@type": "HttpPOST",
                                "name": "Submit",
                                "target": "FUNCTIONURL",
                                "body": "{\"comment\":\"{{comment.value}}\", \"choice\":\"Reject\", \"returnurl\":\"CALLBACKURL\"}"
                            }
                        ]
                    }
                ]
            },
            {
                "startGroup": true,
                "activitySubtitle": "Please complete the task."
            }
        ]
    }
}

Now add a “HTTP – HTTP Webhook” action

httpwebook

Choose the following:

  1. Subscibe – Method – POST
  2. Subscribe URI – the URL that can from the “Sending Workflow”
  3. Subscribe – Body – the Card variable. What we need to do at the point is replace the token CALLBACKURL with our Callback url, so the code is

    replace(variables(‘Card’), ‘CALLBACKURL’, listCallbackUrl())

The HTTP Webhook action is configured:

HttpAction

The flow should look like:

completedflow

Update the flow, and trigger it.

What should happen, is the card should be send to the “Sending Workflow” flow, which should then send you an email to the users in the “Users” list, and it should display in Outlook as an actionable card.

Next in the series is how to get the response back from the card, and continue your flow.

PowerApps – Embed a PowerApp list form inside a SharePoint Page

This is the scenario, you create a SharePoint list, and then you customise the form inside PowerApps.

customize

Now you want to host this form inside a Modern SharePoint page so that new items can be submitted easily.

First Attempt – add the form onto a page

OK – so to add a list form you do the following:

  1. Get the Application ID – this is easy, once you open the list form in PowerApps, the url is
    https://create.powerapps.com/studio/#action=sp-edit-formdata&app-id=APPID
  2. Add the following iframe to a embed webpart
    embed
    The iFrame is:

    
    <iframestyle="margin: 0 0 0 40px; border: none; padding: 0px;" width="1024px"
    height="768px"
    src="https://web.powerapps.com/webplayer/iframeapp?hideNavBar=true&source=iframe&screenColor=rgba(104,101,171,1)&
    appId=/providers/Microsoft.PowerApps/apps/APPID">
    </iframe>
    
    

Once you added this, the app will appear, but you will notice the following:

  1. The form (by default), is in EDIT mode, and is editing an item in the list – what we want is to insert a new item;
  2. There is no way to submit the new form.

Second Attempt – adjust the form so it works outside the list

Navigate to the list and customize the form. The first thing you need to do is set the mode to “New”, not “Edit”

new

Second change, is to add a submit button, that only appears when the form is shown outside the list page.

So first, add a new custom card

addcustomcard

Call it “ButtonCard”, and add a Button to it

buttoncard

The way the button is going to work, is it will save the form, and then display a thank you message.

So first – create the thank you message.

  1. Add a new screen
    newscreen
  2. On a screen, add your thank you message

Now, you just have to set the OnSelect method on the button, so it saves, and shows the thank you screen


SubmitForm(Form);
Navigate(ThankyouForm, ScreenTransition.Fade)

There is more task to perform – this button, should only show on the SharePoint Modern page, and not inside the list (where there is a submit button at the top). So Change the Visible property of the ButtonsCard, to the following:


If(Form.Mode = FormMode.New, true, false)

This will show the submit button when its on the SharePoint page

Now when you return to your SharePoint page with the embedded form, it should display and work as expected.

 

SharePoint Online Migration – User Profile Mapping File

Busy doing a SharePoint Online Migration, and while using our test instance, realized I needed to map users from one premise to online.

In the documentation for ConvertTo-SPOMigrationTargetedPackage, it says you can create a User Profile mapping file. All it says is its a CSV file with three columns.

So after trying different combinations, and using ILSPY, this is how to create the user mapping file for Migration content into SharePoint online.

The User Profile mapping file format

  1. The first line is a header – it ignores the content, just add a header line, like:
    OnPremSID,UPN,isGroup
  2. The first column, OnPremSID, you need to calculate out of the value in the UserGroup.xml file produced by Export-SPWeb.So in the UserGroupXml, the User element looks like:
    <User ID="1" Name="First Last" Login="i:0#w|mydomain\first.last" Email="first.last@mycompany.com" ISDomainGroup="false" IsSiteAdmin="false" SystemID="...." IsDeleted="false" Flags="0"/>

    At first, I thought the OnPremSid would be the login, but that is incorrect.
    It is the value stored in SystemID attribute, and this value is Base64 encoded.

    To decode the SystemID attribute, and get the OnPremSID which is required, using the following PowerShell function:

    function Convert-To-SID ($systemId) {
     $array = [System.Convert]::FromBase64String($systemId)
     $str = [System.Text.Encoding]::UTF8.GetString($array)
     $decodedSid = $str.SubString("i:0).w|".Length)
     return $decodedSid
    }
  3. To automatically produce the entire User Profile mapping file, you can run the following PowerShell script, which takes in the UserGroup.xml file, and returns the required CSV:
    $userGroupFile = "C:\Migration\SPOTemp\Shared Documents\UserGroup.xml"
    $userProfileMap = "UserProfiles.csv"
    
    function Convert-To-SID ($systemId) {
     $array = [System.Convert]::FromBase64String($systemId)
     $str = [System.Text.Encoding]::UTF8.GetString($array)
     $decodedSid = $str.SubString("i:0).w|".Length)
     return $decodedSid
    }
    
    [Xml] $userGroup = Get-Content $userGroupFile
    
    #Header
    "OnPremSID,UPN,isGroup" | Out-File $userProfileMap
    
    $userGroup.UserGroupMap.Users.User | foreach-object {
     $sid = Convert-To-SID($_.SystemId)
     $sid + "," + $_.Email + "," + $false.ToString()
    } | Out-File $userProfileMap -Append

    This will produce the User Profile csv file, which is badly documented in the TechNet article, in the correct format.
    When using the ConvertTo-SPOMigrationTargetedPackage, add the -UserMappingFile option, pointing it to UserProfiles.csv produced by the script above.

Some Gotchas

Here are some Gotcha’s that can help you out some more.

  1. Different types of authentication: The code above only caters for Windows Authentication. If you change the line
    $str.SubString("i:0).w|".Length)

    Then you can cater for the following:

    1. Claims auth –  c:0+.w|
    2. Other – forms
  2. Deleted accounts – if the IsDeleted attribute of the User Element on UserGroup.xml is “True”, then the user has been deleted, and do not write them out;
  3. Accounts deleted from Active Directory – these cause the entire process to fail, because when it decodes the Base64 string, it inserts a bunch a wierd characters into the UserProfile.csv file, and the PowerShell command ConvertTo-SPOMigrationTargetedPackage fails. To detect these:
    !($_.SystemId.StartsWith('AQUA'))

 

Putting all this into a function called GetUserProfiles, will look like so:

function Convert-To-SID ($systemId) {
 $array = [System.Convert]::FromBase64String($systemId)
 $str = [System.Text.Encoding]::UTF8.GetString($array)
 $decodedSid = $str.SubString("i:0).w|".Length)
 return $decodedSid
}

function GetUserProfiles ($userGroupFile)
{ 
 [Xml] $userGroup = Get-Content $userGroupFile

 #Header
 "OnPremSID,UPN,isGroup" | Out-File $userProfileMap
 
 if ($userGroup.UserGroupMap.Users -ne $null -and $userGroup.UserGroupMap.Users.User -ne $null) {
 "Processing users"
 $userGroup.UserGroupMap.Users.User | foreach-object {
 if ($_.IsDeleted -eq 'False' -and !($_.SystemId.StartsWith('AQUA'))) {
 "Added: " + $_.Email
 $sid = Convert-To-SID($_.SystemId)
 $sid + "," + $_.Email.Replace("@mydomain.com", "@testdomain.com") + "," + $false.ToString() | Out-File $userProfileMap -Append
 } 
 else {
 "Deleted:" + $_.Email
 }
 }
 }
}

I hope I saved at least one devops migrator some time.

Nintex and Page Libraries

Nintex is a really fantastic workflow engine, but it unfortunately doesn’t play nicely with Page libraries and the approval process in pages.

A page goes from Draft -> Submit -> Approve

There is no option out of the box to trigger a Nintex Workflow with “Submit” is pressed

So we need to do the following two things:

1. Create a list event receiver that will recognise a page being submitted for approval;

2. Webservice to allow a page to be approved or rejected inside a workflow.

List Event Receiver

The main challenge with the list event receiver is recognizing the different between Submit, Approve, and Reject. After much digging, it appears “vti_doclibmodstat” property will have a value of 2 if it is submitted.

The code below captures the submit, and launches the workflow:

public override void ItemUpdating(SPItemEventProperties properties)
{
      bool bSubmitted = IsSubmitted(properties); 
      if (bSubmitted)
      {
           StartWorkflow(properties);
      }
       base.ItemUpdating(properties);
 }

To check is submit has been clicked, check the “vti_doclibmodstat” value:

private static bool IsSubmitted (SPItemEventProperties eventProperties)
 {
       SPItemEventDataCollection afterProperties = eventProperties.AfterProperties;
       object obj = afterProperties["vti_doclibmodstat"];
       if (obj != null)
       {
          int num = Convert.ToInt32(obj, CultureInfo.InvariantCulture);
          if (num == 2)
          {
            return true;
          }
       }
       return false;
 }

To start the workflow, loop through the associated workflows and launch the one you require:

private void StartWorkflow(SPItemEventProperties properties)
 {
   foreach (SPWorkflowAssociation workflowAssoc in properties.List.WorkflowAssociations)
   {
        if (workflowAssoc.Enabled && workflowAssoc.Name== "Page Approval")
        {          
            SPSecurity.RunWithElevatedPrivileges(delegate()
             {
                    SPWorkflow workflow = properties.Web.Site.WorkflowManager.StartWorkflow(properties.ListItem, workflowAssoc, string.Empty);
              });
         }
   }
 }

WebService

There is no out of the box web service method to approve or reject a page. So we need to make a small asmx web service that will Approve or reject a page.

Approval and Rejection is no other than getting access to the SPFile and calling Approve, or Deny

So to approve:

[WebMethod]
 public void Approve (string url, string comment)
 {
   SPWeb web = this.GetWeb();
   // Get relative
   if (url.StartsWith("http")) { url = url.Substring(url.IndexOf('/', 8)); }
   SPFile file = web.GetFile(url);
   if (file!=null)
   {
     file.Approve(comment);
      file.Update();
   }
 }

To reject:

[WebMethod]
 public void Reject(string url, string comment)
 {
    SPWeb web = this.GetWeb();
    // Get relative
    if (url.StartsWith("http")) { url = url.Substring(url.IndexOf('/', 8)); }
    SPFile file = web.GetFile(url);
    if (file != null)
    {
       file.Deny(comment);
       file.Update();
    }
 }