Excel to JSON using Flow

Mar 8, 2019 Natraj Yegnaraman externalideas, flow, tips

I was reading through Scott’s recent post -> https://www.hanselman.com/blog/ConvertingAnExcelWorksheetIntoAJSONDocumentWithCAndNETCoreAndExcelDataReader.aspx and I was thinking that this could be done in a codeless way using Microsoft Flow. So, I tried to do this in Flow, and it was really easy to do this – under 15mins, as all the plumbing is already there. This is my Flow.

Excel to JSON Flow.png

The Flow is triggered by a HTTP GET and reads from an Excel file stored in OneDrive.

Trigger and Source.png

The output is already a JSON, but not a valid one probably one, as it has spaces and special characters in the key names, which map to the column names of the table.

Excel JSON.png

So, we can simply re-shape the data using Select, and dump the JSON response.

Select and Response.png

Now, when I call my trigger URL, the Flow executes and returns me the JSON.

Response JSON.png

With Flow add-on for Excel, you could run the same Flow from Excel itself, if you want to.

Hope you find this useful.

Flowception: Creating solution enabled Flow with Flow

Mar 1, 2019Mar 1, 2019 Natraj Yegnaraman flow, powerapps, tricks

Solution is a feature that has been used in the CRM space for a long time. Solutions support for Flow was announced last year -> https://flow.microsoft.com/en-us/blog/solutions-in-microsoft-flow/. One thing the post does not mention is that Flows have to be created from the solution record. If you have an existing Flow, that you want to package up into a solution, you cannot do that. To workaround this limitation, I have created a Flow to clone an exisiting Flow and make it solution enabled.

The Flow itself it not that long. Here is how it looks.

The Flow definition fits on a readable screenshot!

The first step is to select the existing Flow that you want to clone, into a solution enabled Flow. This can be done using the Flow Management Connector’s “Get Flow” action.

Solution enabled Flows, like solution enabled canvas apps, are also stored in the CDS database. The entity it used to store the Flow is called Process (logical name: workflow). It stores both the Flow definition, as well the the connection references.

LINQ output

However, the connection references are stored differently between CDS and Flow. Here is how the connections are stored in Flow.

[{
	"connectionName": "shared-flowmanagemen-f22a175e-d99e-4e41-8404-f6823b2d4d5e",
	"displayName": "Flow management",
	"id": "/providers/Microsoft.PowerApps/apis/shared_flowmanagement"
}, {
	"connectionName": "46c0ebf24ba6458f9a582abde1185b12",
	"displayName": "Common Data Service",
	"id": "/providers/Microsoft.PowerApps/apis/shared_commondataservice"
}]

Here is how the connections are stored inside CDS workflow.

{
	"shared_flowmanagement": {
		"connectionName": "shared-flowmanagemen-f22a175e-d99e-4e41-8404-f6823b2d4d5e",
		"source": "Invoker",
		"id": "/providers/Microsoft.PowerApps/apis/shared_flowmanagement",
		"tier": "NotSpecified"
	},
	"shared_commondataservice": {
		"connectionName": "46c0ebf24ba6458f9a582abde1185b12",
		"source": "Invoker",
		"id": "/providers/Microsoft.PowerApps/apis/shared_commondataservice",
		"tier": "NotSpecified"
	}
}

As you can see one is an array and another is an object. So, this means the connection JSON has to be reshaped, when we create a Modern Flow Process record, directly in CDS. We will use select action to reshape the data, and then do a replace to cleanup the JSON.

Flow Connection JSON.png

Connection References

The action below is the one that creates the Solution enabled Flow. You create the “Process” record using the CDS connection, and populate the Flow JSON in “Client Data” field.

Create Workflow.png

This is the formula I use in the concat.

concat
(
	'{"schemaVersion":"1.0.0.0","properties": { "definition": ', 
	body('Get_Flow')['properties']['definition'],
	', "connectionReferences": ', 
	variables('connectionReference'), '}}'
)

This creates the JSON that is accepted for the “Modern Flow” process record.

In the last step we activate (start) the newly created Flow.

Enable Flow.png

The Flow’s GUID is stored in a field called “workflowidunique” on the Process entity. So, we can use this to the retrieve the Flow, and activate it.

The crazy part of this Flow is that I was able to run the Flow on itself and add it to the solution, hence the title of the post.

Flow run.png

You can now add the Flow into the Solution, from web.powerapps.com

The newly created Flow, will have the same step Flow name, you specific in the first Get Flow step, prefixed with “Solution: “

Add Flow.png

The solution with the Flow can now be exported and imported into a new CDS environment. I hope this helps you to package some of you old Flows into a solution. This Flow can further improved by listing all Flows in the environment and doing the same process or cloning it, rather than specifying a specific Flow at design time.

You can download the Flow from https://1drv.ms/u/s!AvzjERKFC6gOwWC7Ywi5fgguPW1s

If you have any comments/feedback, please share them on the post or tweet me @rajyraman.

CDS, Microsoft Flow and DateTime formats

Dec 6, 2018Dec 7, 2018 Natraj Yegnaraman dynamics365, flow, tricks

EDIT (7/12): You can download the Flow from https://1drv.ms/u/s!AvzjERKFC6gOwCqKnnAvMZA6mDsW if you just want to jump straight into exploring the actual Flow, instead of reading the article.

DateTime and Timezones seem to be the flavor of the month, so I thought I will take a crack at the problem as well. Coming from CRM background and spending a lot of time fiddling around the internal schema to understand the plumbings I came up with the following approach which I believe is decoupled and flexible.

In CDS, there is an entity called “UserSettings”, which stores a whole bunch of information regarding the user preferences. If you have used the awesome XrmToolBox tool called “UserSettings Utility”, you would remember this screen.

Formats

The “UserSettings” entity is the source of this information. So, why not use the same for managing the timezones and datetime formats?

For re-usability, I want to create a Flow that just returns me the timezone information based on the executing user. Here is how it looks.

Get Timezone and Formats.png

Let us understand this step by step.

Step 1:

This is the HTTP trigger, which can take in a parameter for activedirectoryguid. This is the unique identifier for the Office 365 user.

Step 1.png

Step 2:

Get the Office 365 profile of the current user. If the activedirectoryguid is not passed in step 1, the intention is to use the current user’s Office 365 information.

Step 2.png

Step 3 & 4:

Initialise the activedirectoryguid variable, and query the CDS entity “Users”, and retrieve the user that matches the activedirectoryguid.

Step 3 and 4.png

Since activedirectoryguid can be passed on the trigger as well, I use the following coalesce expression, to set the variable to either the passed value on the trigger or from the Office 365 profile in step 2. This is the expression.

coalesce(triggerBody()?['activedirectoryguid'],body('Get_my_profile_(V2)')?['id'])

Step 5 & 6:

Retrieve “User Settings” entity and the associated “Time Zone Definitions” record based on the user’s timezone.

Step 5 and 6.png

The filter for User Settings is

first(body('Get_System_Users')?['value'])['systemuserid']

The filter for Time Zone Definitions is

first(body('Get_User_Settings')?['value'])['timezonecode']

Step 7:

This is the last step, where we return all the format and timezone information.

Step 7

Below are the expressions for the returned properties:

timezone

first(body('Get_User_Timezone')?['value'])['standardname']

dateformat

replace(first(body('Get_User_Settings')?['value'])['dateformatstring'],'/',first(body('Get_User_Settings')?['value'])['dateseparator'])

timeformat

replace(first(body('Get_User_Settings')?['value'])['timeformatstring'],':',first(body('Get_User_Settings')?['value'])['timeseparator'])

dateseparator

first(body('Get_User_Settings')?['value'])['dateseparator']

timeseparator

first(body('Get_User_Settings')?['value'])['timeseparator']

Now let us look at some sample output. For a user located in US, here is how the output of the Flow looks like.

US Output

Compare this with someone who is in Australian timezone.

Australia Output

Since this Flow now contains the logic for getting the formats and timezones, it can be utilised for another Flow that needs this information. For example look at the sample Flow below

Calling Flow

Calling Flow1

Below is the expression, I use to convert the “createdon” returned by the CDS Get Record, step which returns the datetime in UTC.

convertFromUtc(body('Get_record')?['createdon'],body('Parse_JSON')?['timezone'],concat(body('Parse_JSON')?['dateformat'],' ',body('Parse_JSON')?['timeformat']))

If you compare this Flow output and the record properties from Dynamics 365, it becomes obvious that the datetime and format is correctly displayed. Note that the CDS connector returns the datetime in UTC.

Output

CRM Record Properties

Initially I wanted to call the Flow that returns the formats and timezone using the “Start Flow” action on the Flow Connector, but it doesn’t seem to be picked up the response, so I had to resort to the whole HTTP action, which is not ideal.

It seems “Start Flow”, simply enables the Flow, does not actually run the Flow. Not sure why it is named in a misleading way.

Hope this is helpful.

Using Flow to notify solution imports

Nov 18, 2018Dec 20, 2018 Natraj Yegnaraman dynamics365, flow, tricks

EDIT (20/12/2018): I updated my Flow and made some improvements. You simply have to set the Timezone on the triggering action and you are all set. The Flow will email the solution list, to the user running the Flow. The download link has been updated to point to the updated solution.

I would not call it sneaky, but sometimes when I find the Dynamics 365 CE UI or behaviour has changed slightly, I can attribute it to some update that was applied to the environment. There are email notifications for major updates, but none for minor updates or patches that can happen frequently. So, I decided to solve this problem using Flow.

Every solution import into the system causes an Import Job record to be created. If a Flow can be scheduled to run everyday, and query the Import Job records that were started the previous day, we can easily keep track of what is happening in the environment.

The first step is to trigger the Flow on a preset schedule, and read the Import Job records.

Import Job Flow.png

Below are the expressions that I use for the boundary dates:

addDays(convertFromUtc(utcNow(),’AUS Eastern Standard Time’), -1, ‘yyyy-MM-dd’)
convertFromUtc(utcNow(),’AUS Eastern Standard Time’,’yyyy-MM-dd’)

The next step is to pick up only the information we need from the returned result, and project it to a form that is conducive for email.

Import Job Compose

With the exception of Solution Name, all the other properties are retrieved from the XML on the Data property. Below are the formula’s for those:

Publisher – xpath(xml(items(‘Parse_Import_Job_XML_and_re-map’)?[‘data’]),’string(//Publisher/Descriptions/Description[1]/@description)’)
Started On – convertFromUtc(items(‘Parse_Import_Job_XML_and_re-map’)?[‘startedon’],’AUS Eastern Standard Time’,’dd-MM-yyyy HH:mm:ss’)
Old Version – xpath(xml(items(‘Parse_Import_Job_XML_and_re-map’)?[‘data’]),’string(//upgradeSolutionPackageInformation/fileVersion)’)
New Version – xpath(xml(items(‘Parse_Import_Job_XML_and_re-map’)?[‘data’]),’string(//upgradeSolutionPackageInformation/currentVersion)’)

The last few steps are to email out the results, if any solutions were imported the previous day.

Email Solution.png

Email Step.png

The result is a barely formatted table, with the list of solutions that were imported the previous day.

Solution Import Email

With this approach, no one can sneak up a solution import on you. You have visibility over what is going on in the system.

You can download and install the Flow solution from https://1drv.ms/u/s!AvzjERKFC6gOwC24012ncrls0z5f

Hope this is helpful to stay on top of imports.

Role based Canvas Apps

Nov 14, 2018 Natraj Yegnaraman powerapps

The inspiration for this post is this post from Geetha -> https://svaghub.wordpress.com/2018/11/03/role-based-security-in-powerapps-using-spgroups/. The idea is to basically use Flow to check what kind of permission the current user has, so that some functionalities can either be lit up or hidden in the canvas app. In her post, Geetha was using SharePoint user group to get this information. Being from a CRM background, I want this to be retrieved from CDS + I am not a big fan of SharePoint.

Attempt 1: Use DataSourceInfo(‘Users’, DataSourceInfo.CreatePermission), to check if the current user can create Users, which means they are Sys Admin. But, there seems to be a limitation on DataSourceInfo, which means it does not return the correct permissions for CDS entities. It always returns true, which is not correct. So, this attempt was not successful. This would have so much easier if it had worked, as all the logic would be entirely in the canvas app.

Attempt 2: Flow magic. I didn’t want to head this route, but since DataSourceInfo did not work, I had to use Flow to solve the problem. Solution is one of the restricted CDS entities. Only certain roles have access to read this entity OOB. Below are those roles.

So, I am using this as the flag to show or hide canvas apps controls. Below is my Flow.

There are two parallel branches, one if there is an exception while retrieving Solution records, which means that the user cannot read “Solution” entity and another branch if they have read privilege on “Solution” entity. Depending on which branch the Flow takes, “canread” can be true or false. I can use the result to show or hide controls on the canvas app. The two branches have the appropriate “configure run after” set.

configure run after.png

The clunky bit about this, which I don’t like is that fact that Flow will report that the execution has failed, when Flow takes the “Cannot read Solutions” branch.

Flow result.png

In the programming world, handling an exception appropriately and continuing like nothing happened, is not considered a failure, but it looks like Flow has a different opinion on this.

Potential improvement to the design: Create a new CDS entity called “Canvas App Permission” and create attributes on this entity, to manage which areas in Canvas App should be shown or hidden. Create multiple records of this new entity and assign this to teams or individual users, depending on how you want the permissions to be applied in canvas apps. The Flow can then retrieve this entity, and PowerApps can use this result of the Flow to hide/show areas or functionality.

Hope this is useful. Credit to Geetha for coming up with the original idea.

Building Spirograph using PowerApps

Nov 2, 2018 Natraj Yegnaraman powerapps

Over the past few weeks, people have been demonstrating some cool games, built entirely using PowerApps. The reason being, that there was a contest from ThoseDynamicsGuys. If you are interested in finding out about some interesting games from this contest, watch this video from Mr.Dang himself -> https://www.youtube.com/watch?v=0-ZWqs_emQA where he reviews the games.

There were some apps that caught my eye during this period:

Power Flappy by Scott Durow
Power Roulette by Geetha Sivasailam
Coin Dog by Makato Maeda
Air Resistance by Nagao Hiroaki

I was especially fascinated by what Scott and Makato accomplished with the side scrolling nature of the games, how Geetha managed to rotate the roulette as there is no rotate angle property on images and how Nagao managed to calculate the trajectory and resistance of the ball. The first step to learning is to understand how other people do it, so I spend 4-5 days to understand how these apps have been developed.

I then wanted to develop something of my own, using the concepts that I had learnt. Spirograph was the first thing that I thought of. The easiest part was to get the equations to calculate x and y. I had to then learn the basics of starting and stopping a timer, and how to create a sense that the pattern was being “drawn”.

Since image control can render SVG, I decided to try this approach. My first challenge was how do I calculate the x and y on every tick of the timer. So, I decided to follow the approach demonstrated by Brian Dang, that involve checking and un-checking a toggle control.

Every time the toggle is checked, I can increment the iterator variable and calculate x and y for the line to be drawn and the string for the SVG path with all the lines. In SVG you can draw a line, by moving to a location specifying Mxy and then drawing the line using Lxy. Below is the formula in the OnCheck event of the slider

Set(VarI, VarI+1);
UpdateContext({x:
(((RSlider.Value-rSlider.Value) * (Cos(rSlider.Value*VarI/RSlider.Value))) + (aSlider.Value * Cos(VarI*(1-(rSlider.Value/RSlider.Value))))),
y:
(((RSlider.Value-rSlider.Value) * (Sin(rSlider.Value*VarI/RSlider.Value))) - ((aSlider.Value * Sin(VarI*(1-(rSlider.Value/RSlider.Value))))))
});
Collect(Lines, {Row: VarI,
X: x,
Y: y});
Set(PathVar, PathVar & If(PathVar="", "M", "L") & x & "," & y);
true

I can then simply set the SVG path’s d property from the PathVar variable, that has all the line co-ordinates. Below is the value for the Image property of the Image control that renders the Spirograph.

SVG Path.png

Below a video of the app in action. You can play around with the sliders and be fascinated by the patterns that it generates.

Here are some interesting patterns the app generated.

You can download the app from the PowerApps Community Gallery -> https://powerusers.microsoft.com/t5/Community-Apps-Gallery/Spirograph/m-p/175447

References:

SVG Paths – https://developer.mozilla.org/en-US/docs/Web/SVG/Tutorial/Paths#Line_commands

Math behind Spirograph – http://www.mathematische-basteleien.de/spirographs.htm

Building a MVP Notifier using Flow and Azure Functions

Sep 3, 2018 Natraj Yegnaraman flow, functions

Since MVPs are now announced every month, I have found it hard to track down the new awardees in Business Applications area before 3rd of every month. So, I thought I will build a notifier using Flow and Functions. This is the logic:

Seed the inital MVP data from mvp.microsoft.com, to figure out the new MVPs every month into Azure Tables
Schedule the Flow at 1st of every month at 5 p.m. PDT. Hopefully by this time, everyone has filled out atleast their name in the MVP profile.
Retrieve the MVP data again from mvp.microsoft.com
Figure out the new MVP and post a message to Slack. Add the new MVP details to Azure Tables

The logic that retrieves the details from mvp.microsoft.com uses Azure Functions. Below is the project.json for that Function App.

{
  "frameworks": {
    "net46":{
      "dependencies": {
        "AngleSharp": "0.9.10",
        "Newtonsoft.Json": "11.0.0.2"
      }
    }
   }
}

Next, is the actual logic that returns MVP list for the category passed in the URL.

using System.Net;
using AngleSharp;
using AngleSharp.Dom;
using AngleSharp.Dom.Html;
using System;
using System.Runtime.InteropServices;
using System.Text;
using Newtonsoft.Json;

public static async Task Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info("C# HTTP trigger function processed a request.");

    // parse query parameter
    string mvpCategory = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "category", true) == 0)
        .Value;

    if (mvpCategory == null)
    {
        // Get request body
        dynamic data = await req.Content.ReadAsAsync();
        mvpCategory = data?.category;
    }

	var pageNumber = 1;
	var webClient = new WebClient();
	IHtmlDocument doc;
	IHtmlCollection mvpProfiles = null;
	List mvps = new List();
	var htmlParser = new AngleSharp.Parser.Html.HtmlParser(new Configuration().WithDefaultLoader());
	do
	{
		var data = webClient.DownloadData(new Uri($"https://mvp.microsoft.com/en-us/MvpSearch?ex={WebUtility.UrlEncode(mvpCategory)}&sc=s&pn={pageNumber++}&ps=48"));
		doc = htmlParser.Parse(Encoding.UTF8.GetString(data));

		mvpProfiles = doc.QuerySelectorAll(".profileListItem");
		var currentPage = mvpProfiles
		.Select(d =>
		{
			var nameAndLocation = d.QuerySelector(".profileListItemFullName a");
            var mvpUrl = nameAndLocation.GetAttribute("href");
			return new MVPInfo
			{
				Name = nameAndLocation.TextContent,
				Url = $"https://mvp.microsoft.com{mvpUrl}",
				Country = d.QuerySelector(".profileListItemLocation .subItemContent").TextContent,
                Id = mvpUrl.Substring(mvpUrl.LastIndexOf("-")+1)
			};
		})
		.ToList();
		mvps.AddRange(currentPage);
	} while (mvpProfiles.Any());

    var json = JsonConvert.SerializeObject(mvps, Formatting.Indented);
    return mvpCategory == null
        ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a MVP category on the query string")
        : new HttpResponseMessage(HttpStatusCode.OK)
          {
                Content = new StringContent(json, Encoding.UTF8, "application/json")
          };
}

public class MVPInfo
{
	public string Name { get; set; }
	public string Url { get; set; }
	public string Country { get; set; }
    public string Id { get; set; }
}

It uses the AngleSharp library to parse the response returned by mvp.microsoft.com search.

Below is the Flow that uses this Function, to populate the Azure Table.

Flow Overview.png

As you can see below the Flow runs at 5 p.m on 1st of every month, and gets the current MVP list from Azure Table, MVPAwards.

Azure Tables.png

The partion key for the table is month and year and the row key is the actual MVP Id.

Azure Tables Definition.png

Since this Flow and the associated Function were built for my personal use, I call the Function directly without using a custom connector. I pass the award category on the URL of the Functions itself. If you want to use a custom connector, refer one of my earlier posts.

Azure Function Call.png

After parsing the JSON returned by the Azure Function, I can use the “select” function to map the returned data, so that I can compare the data based on the MVP Id and insert the new MVPs into the Azure Table, if needed. The partion key will the current year and month (yyyyMM).

Flow Select.png

Next is the crucial bit, where I do the actual comparison.

Compare MVPs.png

The “filter” function can be used to see whether there are any matches in MVP list that was retrieved from Azure Table i.e. the previous month MVPs. If no results were returned, that means they were not a MVP last month. So, they are a new MVP and this can be stored in Azure Tables and can also be posted to Slack.

IF Then Logic.png

Post to Slack is using the Webhooks registered on the channel.

Slack Web Hooks.png

After the Flow has finished running, the output gets posted to Slack.

MVP awards.png