A Thesis on the Parse JSON action in Microsoft Flow

/

Parse JSON can be both intelligent and dumb.  It can help you a lot and it can hinder you just as much.

This blog post, we go deep and study the behaviour of the Parse JSON action, it's various problems and solutions.

Here we tackle the question - How do you use Parse JSON, actually?

Plan

  • Getting Started with Parse JSON
  • Problem - way too many properties
  • Problem - the array - item object and auto-wrapping
  • Problem - duplicate properties names?
  • Problem - null value properties
  • Problem - missing properties
  • How to quickly fix the schema
  • Result is Parse JSON will make the Dynamic Content Panel actually intelligent

Getting Started with Parse JSON

In general, the reason we use Parse JSON is because Flow doesn't know the format of the data we are receiving in our actions.  So we always start by running the actual method once - even if the Flow has only been partially completed.  We want to get a sample of the data we'll be seeing.

Look at all these properties in the Dynamic Content Panel.  Looks amazing.  Our problems are about to happen.  Don't worry we'll fix all of them.

 

Problem 1 - way too many properties

The schema Parse JSON generates is very very verbose.  So there are a lot of properties that we probably just don't care.

So copy the generated JSON Schema from the Parse JSON action, and use a text editor to have a look at it.  Here I'm using VS Code - switch the editor mode to JSON.

We see it has generated type information for various _links properties, or in this example, the halfTime results or odds of a football game.  We don't need this, so we can delete it from the schema.

 

Problem 2 - the array - item object and auto apply-each wrapping

Notice under the fixtures (array) we have an items property with each object.  This appears under Dynamic Content Panel as "fixtures - Item"

Becareful when clicking on this property, it will immediately unfold with an For-Each block.

It depends whether this is actually what we want - typically, when we aren't in a For-Each block, this is helpful.  But if we are already inside a For-Each block this may create a second nested for-each block and be quite unnecessary and confusing.

 

Problem 3 - duplicate properties names?

Hey why do I see duplicate names?  How can we tell which one is which?

The simplest way to ease this problem is to delete the properties in the schema that you don't want (same as problem 1).  This will reduce the number of duplicate properties that appears.  But sometimes, we still get some duplicate names - often I see the top level object has a 'name' and the child object has another 'name'

In this case, I check with the hover over to make sure I've selected the right choice.

 

Problem 4 - null value properties

(This problem category also applies to all type mismatch errors)

In general, Parse JSON generates the schema based on the first object it sees from the sample.  This is not always correct - since sometimes the properties may be null in the later objects.  For example in the football games data - earlier records has scores as integers, but later records haven't been played yet so scores are null.

This error usually fails the Flow.  Another mutation of this problem is when the number is a decimal, but the schema thinks it's integer.

The easiest way to fix nullable-values is to delete the type information for this property.

Using no type information is better than changing to something like type: "object" because the Dynamic Content Panel will always show these properties.

If goalsAwayTeam was set to object, then it wouldn't be available here.

 

Problem 5 - missing properties

Sometimes, a later object in the array doesn't have a property at all.

The fix is simple - remove the "y" from "required"

But sometimes this is half the problem - because the expression used after this may expect the property to exist and always available.

 

Result

Parse JSON will make the Dynamic Content Panel actually quite intelligent.  But it requires some clean up maintenance on the JSON schema.

It is up to each scenario to decide if there's value to fix the JSON Schema, or to skip using Parse JSON and just use item()?['property-name'] expressions directly.

 

How to get live FIFA Worldcup results via Microsoft Flow into your SharePoint Intranet WebPart

/

Github tweeted a link to a NPM NodeJS CLI project that uses data from http://football-data.org.  Seeing that, I decided we need to build a SharePoint Modern SPFx webpart so we can load it into all our Intranets.

Plan

  • Use Microsoft Flow to call the football-data API
  • A Thesis in Microsoft Flow's Parse JSON action
  • Format the data and write it into a HTML segment in SharePoint
  • Build a simple SPFx webpart that will load the HTML segment into the DOM
  • Future: Using Events API
  • Future: Using API Key
  • Downloads

Use Microsoft Flow to call the football-data API

We want to build this:

First let's look at the data source API.

That's nice - we don't need to figure out which competition is the 2018 World Cup.  It's 467.

http://api.football-data.org/v1/competitions/467

We want the game fixtures though, so we will call

http://api.football-data.org/v1/competitions/467/fixtures

(from http://api.football-data.org/documentation )

 

A Thesis in Microsoft Flow's Parse JSON action

To make the Select Operation Easier, we have two choices:

  • "type the expressions manually" or
  • "configure Parse JSON to make our lives easier"

Parse JSON is a detailed exercise that requires a full post by itself, since it requires description of various problems that we'll see, and how to get around them.  The Parse JSON step is the next blog post.

http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow

 

Format the data and write it into a HTML segment in SharePoint

If we skipped Parse JSON - then we'll need to manually type out the expressions.

We are selecting From:

body('HTTP'_world-cup-fixtures')?['fixtures']

And we need the properties:

date: convertFromUtc(item()?['date'], 'AUS Eastern Standard Time')
Day: item()['matchday']
Team 1: item()['homeTeamName']
Team 2: item()['awayTeamName']
Team 1 Goals: item()['result']['goalsHomeTeam']
Team 2 Goals: item()['result']['goalsAwayTeam']
Status: item()['status']
 

If we used Parse JSON - then the step to select the properties are easier.

 

We do the work in Parse JSON to describe what the types of the properties are - this allows the dynamic content pane to be more intelligent in showing us the appropriate choices.

Still have to type out the date conversion to local-time, unless you want UTC time.

We have our nice HTML table now.

flow-fifa-request-call.jpg

 

Build a simple SPFx webpart that will load the HTML segment into the DOM

For this step, I cheated and just used Mikael Svenson's wonderful Modern Script Editor webpart

Then I pasted in this code: 

<div id="fifa-games"></div>
<script>
window.fetch('/Shared%20Documents/fifa-2018-fixtures.html', {
  credentials: "same-origin"
}).then(function(response){
  return response.text();
}).then(function(text) {
  document.getElementById('fifa-games').innerHTML = text;
});
</script>

Basically, fetch the contents of the HTML from fifa-2018-fixtures.html and write that into DIV#fifa-games element.

You can also just use the old Content Editor webpart and use Content Link property to the file, but that doesn't work on the new modern pages.

Results

Works really well in Microsoft Teams too.

 

Future: Using Events API

football-data.org has an Events API where they will call our callback URL when a team scores in a game fixture.

http://api.football-data.org/event_api

This requires a first Flow to loop through all the Fixtures in the FIFA 2018 competition and set up an event call for each fixture.  Then when those games are played - if any goals are scored - we can call the same parent Flow to immediately update the result HTML table.

This implementation won't require a scheduled recurrence to refresh the data daily (or hourly).  It can be refreshed on-demand!

Future: Using API Key

When calling the football-data.org API without an API-Key, there is a max rate limit of 50 calls per day.  We can sort of see this in the response header (it goes down by 2 per execution).

Now because this is running from a Microsoft Flow server, I have no idea if your calls are rate-limited by the same IP address as someone else running this in your Azure Data Centre region.  So if you do see rate limit, it is better to register an API Key with an email address, and then add that to the HTTP Request Header.

Do bulk insert/update in Microsoft Flow with two simple elegant filter-arrays

/

This is a blog post of a pattern that I've brooded over for months and finally came to an elegant solution.

Plan

  • Scenario: Bulk Insert / Update
  • Doing filter-arrays elegantly
  • Build it in two parts
  • Run it super quickly
  • Bonus: extensions

The style of this blog post mirrors my previous post on Do Group By in Microsoft Flow with two simple elegant loops.  I presented this during the June 2018 Microsoft Graph Community call.  This blog post is the text, searchable, extended version.

Scenario: Bulk Insert / Update

Bulk insert and update is a very common problem with scheduled Microsoft Flow.  Basically, we have a list of resources - it could be a SharePoint list, an Excel file, a CSV import file, a SQL table, result of a Microsoft Graph call... etc, and we want to either update or insert the entire table into a destination - another SharePoint list etc.

In the example, we will do this with Microsoft Graph's List Groups call.

 

First, call Microsoft Graph groups with a GET request - this gets back a JSON array of Office 365 Groups.

 

We need a SharePoint list to write this to - I made a list with a few columns: groupId is important, the rest is just useful: description, visibility, createdDateTime and renewedDateTime.  I also suggest deletedDateTime, since now groups have a recycle period before true deletion.

 

The plan is this: when this Flow runs, we call Microsoft Graph for list of groups, then perform fast-bulk update/insert into SharePoint list.

 

Doing elegantly with filter-arrays

The first step is to select a list of existing groupIDs from the current SharePoint list.  We do this with two actions.

What is this magic trick?  Select action has the ability to map array of objects into... anything we want.  A bit of code may explain this better:

var results = array.map( (item) => { "id": item.groupId } );
// results = [ {"id":"xxx1"}, {"id":"xxx2"} ]

var results = array.map( (item) => item.groupId );
// results = [ "xxx1", "xxx2" ]

The first UI with key/value lets us create result arrays of objects that has key/value properties.  The second UI form with just "value" lets us create flattened array of string, or number, or really complex nested objects.

Anyway, the result of the Select action is now an string array of groupIds that I've already got in my SharePoint list.

Then we follow with two Filter-Array on the JSON result from the source list: the Microsoft Graph array.

So we have two Filter Array actions.

"New FilterArray" contains rows from MS Graph Groups whose "id" does not exist in the array of "groupIDs" from SharePoint.

"Update FilterArray" contains rows from MS Graph Groups whose "id" already exists in the array of "groupIDs" from SharePoint.

 

Build it in two parts

The hard part is already done, the rest is simply wrapping it up.

Hew done!

The benefits:

  • Compose, Filter, Select are native Flow/LogicApps engine expressions and they run super fast.  Use them to do all the filters and sort and array.map
  • Do not check SharePoint for each row - so the SharePoint connector speed is much faster

For-Each is still slow, but we can do more

Run it super quickly

Now, we have two arrays and two loops through them, one does only create and the other does only updates.  They don't need to wait for each other.  In fact, they can all run in parallel.

This dramatically drops the duration

 

Extensions

We can add a really simple extension

  • When there are "new" records, we want an email about this.
  • Connect Flow Management's "List Flows as Admin" and be notified when new Flows are added to your environment by anyone.
  • Listen to Office 365 Service Admin Center messages and be notified when new events happen.  Push them into Microsoft Teams channel for discussion.

 

Do Group By in Microsoft Flow with two simple elegant loops

/

This is a problem I worked on with Fausto and is a common problem I see when working with rows of data in Microsoft Flow.

Plan

  • Scenario: Group By
  • Doing Group By Elegantly
  • Build it in two parts
  • Run it quickly

Scenario: Group By

Group-by is a common problem - but usually we see this a lot in the form of batching rows of data to some sort of output.  One frequent example is a schedule that looks up a bunch of tasks or messages for the day, and sends only ONE email with a group of tasks to that person.

Doing Group By Elegantly

How do we do Group-By elegantly, and quickly.  Yes, group-by can be done with for-each and append-to-array, but that approach leads to fairly complex looking inner loops and wide-layout if-conditions.

The solution that we got at the end is an elegant one with two for-each loops.  It is easy to follow and easy to test - which means you can easily reconfigure it to your own scenario.

Let's take an example

pivot-this-list.jpg

I have a list here with 6 rows from SharePoint and 3 names.  In the first loop, we want to reduce the array of 6 rows into unique names:

[ "Gandalf", "Boromir", "John Liu" ]

Then in the second loop, we can loop over each of these unique names and built the corresponding list of rows for each name:

[{
  "name": "Gandalf",
  "titles": [ "Wizard", "Remember Password"]
},
{
  "name": "Boromir",
  "items": [ "One Does not Simply", "**SPOILER**"]
},
{
  "name": "John Liu",
  "items": [ "Making up lists", "Ninja"]
}]

First Loop

In each loop, we use the union() expression to combine two arrays.  Union has the special ability that if an element already exists in the array it is omitted.

union( ["Gandalf", "Boromir"], ["Gandalf"] ) => ["Gandalf", "Boromir"]

We do the union in a compose action, and then put that result back into the array variable.

In my example, I'm using SharePoint's people fields - so I'm stepping into item()?['Person']?['Email']

That's first loop.  Easy to test - one expression.

Second Loop

Second loop involves looping through the first array of unique_names, and the first action stores that value into a compose step.

Then use Filter array to select only the rows with that unique name from the original table.
Then use Select to pick only the columns from the original table that we want to see.  (The majority of the second loop looks more busy, because I'm using techniques from my other blog post "using select to simplify your Create HTML Table")

I append the results as an object to a running array of results.  But it is also very easy to just send email at this point.

For each unique name (or email), this sends one email, the email body is a summary HTML table of the items in the list for this person.

Run it quickly

Expression operations like Compose, Filter, Select or Union are fast - most of the time running 0-second.

For Each steps and set variables are slower - because there are global locks being applied.  By reducing use of variables, we can make the loop go much faster.

Elegant and fast.

 

Summary

Two elegant loops.  No crazy if-conditions and checking if a value already exists in an array during append.

This is a very useful pattern if you are sending daily emails or summary notifications to a user and you want to batch the results.

I wanted to end here on more loops.  Loopity loop.

Save all your Flows to VSTS via HTTP REST in 8 actions

/

I saw VSTS has "Send an HTTP Request to VSTS" a few days ago, and a quick test had me pulling back repo information in JSON.

 

My brother visited me on Sunday afternoon, after having Australian BBQ and playing Monster Hunter World, I sent him back via the train station.  On the way, I mentioned to him that Flow has VSTS connector now.  He joked that "Ah so now you can finally check your Flows into a proper source control".

Well... about that.
See...  I believe Flow can do anything.  So as I was pulling into my garage I've got some ideas to try in my head.

Plan

  • The magic of "Send HTTP Request to System" connectors
  • VSTS REST API
  • How to send all our Flow definitions into VSTS

"Send HTTP Request to System" Connectors

I'm a big fan of "Send HTTP Request to System" type of Connectors.  First we saw that with SharePoint - allowing a pre-authenticated action to call any REST API on SharePoint.

Now we see it on VSTS.

How about MSGraph next? :-)  Pretty please.  For delegate access.

VSTS REST API

The VSTS REST API are documented here: 
https://docs.microsoft.com/en-us/rest/api/vsts/?view=vsts-rest-5.0

In particular, v5 is in v5-preview
To perform a "push" via REST, we need the last Commit ID, then a POST Push JSON.

https://docs.microsoft.com/en-us/rest/api/vsts/git/commits?view=vsts-rest-5.0
https://docs.microsoft.com/en-us/rest/api/vsts/git/pushes/create?view=vsts-rest-5.0#add_a_text_file 

How we do it in Flow

The 'trigger' could be anything - a manual button, or a recurrence trigger.
Get all My Flows and loop through them.

To GIT PUSH - we need to have the last Commit Id, so perform a get commits and take the last one.
Then use Compose to pull the commitId from the result JSON.

NOTE - this will fail if you have never commit to the repo.  So just go to that repo and init by commit the README.md manually first.

List My Flows action outside of the For Each doesn't actually return Flow Definition.  So inside the For Each, call Get Flow again with the Flow Name.

Next we compose the GIT PUSH body json.  It needs oldObjectId we got from GIT Commits earlier.  And we are either "add" or "edit" files.  I use the Flow Name.json to be the file name - that is a GUID.  My reasoning is that guids don't change, display names do.  So guid would keep better history.  You may decide display names are better filenames.

Cast the flow.properties.definition (JSON) into a String, and set that as "content" of the PUSH body.

Call VSTS REST again with POST pushes

grant-permissions-as-me.png

Trigger the Flow first time and we'll need to check and grant permissions to the two connectors.  They both run as me.  Auth is Easy and is Done. ;-)

Run Success!

flow-to-vsts-success.png

Run it - success!
Note because the PUSH has to run one after another - the commit must be re-fetched per PUSH.  We can build more complex POST body and have it commit every Flow in one PUSH.

Flow Definition JSON usually is one big long line.  Make sure you do Format Document if you want a human to read it.

Summary

Take regular backups of your Flows.  And then use VSTS's change history to see what changed!

Some may be brave enough to try take Flow definitions from VSTS and patch them into live Flows.  Please tell me if that works well :-O