How to use Select to simplify Create-HTML-Table and Create-CSV in MicrosoftFlow

/
pexels-select-sort-complexity.jpeg

This is a blog post about how using Data Operations - Select in Microsoft Flow can dramatically simplify Create HTML Table or Create CSV.

This is mostly inspired by a conversation with @gmfer that I feel needs a bit more explanation for anyone that's curious.

Technically, this is also a blog post about how these three operations are fundamentally the same thing.  They iterate over an array and perform map/reduce.  As a developer as well as a language nut, I love map/reduce.

Plan

  • What is the problem with Create HTML Table (or Create CSV)
  • Why the fix is Select
  • The underlying language expression holds the answers to all the questions

The Data Operations actions

The problem

The problem is hard to explain but becomes very apparent as soon as you try to use Create HTML or Create CSV.

My example list is my Xbox Achievements - I'm pulling them into a HTML Table and a CSV File

html-simple.png

Problem 1 - Sometimes bugs with connectors (like SharePoint)
Problem 2 - Expression Editor does not appear - forcing us to write expression without help (use a compose action as a workaround)

Problem 3 - decide to define headers it wipes out existing columns.

This way is quite tricky, but we can get to the result, which looks great.  The downside is we have to know the expressions.

 

The Select

Let's see how we do this with Data Operation - Select, and why it is easier because we can use the Expression editor

The expressions are moved into a previous Select operation, that makes create HTML action (or create CSV action) very simple - just use the defaults.

 

The language

For the curious, the root of all three action lies in the expression.

Create HTML Table

"Create_HTML_table": {
    "type": "Table",
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "format": "HTML",
        "columns": [
            {
                "header": "Title",
                "value": "@item()?['Title']"
            },
            {
                "header": "State",
                "value": "@item()?['progressState']"
            },
            {
                "header": "Unlocked",
                "value": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
            }
        ]
    }
}

Create CSV Table

"Create_CSV_table": {
    "type": "Table",
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "format": "CSV",
        "columns": [
            {
                "header": "Title",
                "value": "@item()?['Title']"
            },
            {
                "header": "State",
                "value": "@item()?['progressState']"
            },
            {
                "header": "Unlocked",
                "value": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
            }
        ]
    }
}

Create CSV table and HTML table are the same.  If we don't want headers, the JSON changes to:

        "columns": [
            {
                "value": "@item()?['Title']"
            },
            {
                "value": "@item()?['progressState']"
            },
            {
                "value": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
            }
        ]

Select and Create Table (with automatic columns) look like this

"Select": {
    "type": "Select",
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "select": {
            "Title": "@item()?['Title']",
            "State": "@item()?['progressState']",
            "Unlocked": "@formatDateTime(item()?['timeUnlocked'], 'yyyy-MM-dd')"
        }
    }
},
"Create_HTML_table_2": {
    "runAfter": {
        "Select": [
            "Succeeded"
        ]
    },
    "type": "Table",
    "inputs": {
        "from": "@body('Select')",
        "format": "HTML"
    }
}

So if we look at the part for the field mappings being defined in Select as well as how they are listed in Create Table - Columns, the syntax of each item property expression is identical.

In a way, understanding the select expression helps us know what to put back inside the Create CSV/HTML Table actions.

Why can't we just use the same Select UI for Create CSV/HTML Table

Ultimately, the UI for Select allows mapping not only to fields but also to complex JSON objects, and goes far further than the header/value fields that Create CSV/HTML table actions need.  So while I love the Select action's UI since it works well with expression editor, that UI can't be used to replace the Create CSV/HTML table actions UI directly.

 

Summary

  • We looked at how to use Create CSV/HTML Table with expressions (this works, but is hard)
  • I recommend using Select to pick out the fields and the name of the field that you need, and then use automatic columns
  • And we looked at the underlying expressions and see these three are nearly identical.  They all do map reduce of a complex collection of objects and project them into a simpler (or different formatted) result.
  • Thank you @gmfer for the discussion and ultimately the motivation to write all this down.

 

Run any PnP-PowerShell in one AzureFunction from Microsoft Flow

/
pexels-photo-tool.jpg

Are you missing a SharePoint connector in Flow?  Do you need that one extra thing that's only in PnP-PowerShell (because there are 300 cmdlets in PnP-PowerShell)?  Frustrated with having to write another AzureFunction just for this one thing?

 

(Trumpet please) Behold!  I present the last AzureFunction (for PnP-PowerShell) you'll ever need to write.

Run-Any-PnP-PowerShell

And then, we'll call it from Flow.  Hmm wait, I think we can do better with the title...
(Trumpet please) Behold!

Run-Any-PowerShell in Flow

Wait, wait, no, why stop there with the title...
(Trumpet please) Behold!

Run Any Compute You Want in Flow

 

Plan

  • Create our Run-Any-PnP-PowerShell script
  • Call it from Flow
  • Call it to do some random compute for us

Create our Run-Any-PnP-PowerShell script

First - create an AzureFunctions app.  Install PnP-PowerShell in it. 

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-pnp-provisioning#upload-the-pnp-powershell-module-for-your-azure-function

I have an old post in 2016 that has similar steps, but the ones from SharePoint PnP Team is much more current.

If you have one already - great, just add another method. 

Select PowerShell from Experimental.  I call it Run-Any-PnP-PowerShell

Write the method.

# we need to import PnP-PowerShell cmdlets
Import-Module "D:\home\site\wwwroot\modules\SharePointPnPPowerShellOnline.psd1"

# POST method: $req
$script = Get-Content $req -Raw

# prepare credentials from environment USER/PW
$securepw = ConvertTo-SecureString $env:PW -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential ($env:USER, $securepw)

# run the script from POST body
Invoke-Expression $script -OutVariable out | Tee-Object -Variable out

# catch output and send it back
Out-File -Encoding Ascii -FilePath $res -inputObject $out

6 lines of magic.

Call this from Flow

This works because Azure Functions is pretty clever about guessing the output of the Function, and changes the content type to JSON

Call it to do some random compute

Here's the same Function doing complex maths.  It'll also handle XML, JSON, .NET Framework and run any of the existing cmdlets or any extra ones we decide to import.

I think this ASCII module isn't very good.

 

Security [updated]

The feedback I got immediately was we'll need to secure that.  That's fair.  Here's a bunch of pretty important links to secure such a crazy "weapon of mass scripting".

Guard your AzureFunction activation key

Because you are calling AzureFunction from within a Flow - technically, only people that use the Flow can see the URL.

IP Filtering

Turn on IP Address Filtering for Azure Functions https://docs.microsoft.com/en-us/azure/app-service/app-service-ip-restrictions 
And the list of IP Addresses for your Flow runs from https://docs.microsoft.com/en-us/flow/limits-and-config#ip-address-configuration 

Secure by Azure AD

You can secure the Function with Azure AD https://docs.microsoft.com/en-us/azure/app-service/app-service-mobile-how-to-configure-active-directory-authentication

Your Flow HTTP request will need to authenticate as well before it can call your AzureFunction.  https://twitter.com/johnnliu/status/943761628671090688

Implementing Azure Functions Managed Service Identity

https://docs.microsoft.com/en-us/azure/app-service/app-service-managed-service-identity

This prevents the username/password being stored in the environment which can be read by the script.  With MSI, the credentials are stored in KeyVault.

Switch to Azure Automation

Azure Automation's native integration with KeyVault may offer the extra security you want.  I personally find it slower to start up, and the connector to retrieve the output is one extra step.  But this is definitely possible.

Add Persistent Logging

Because all the scripts has to come through your Flow - add a logging step that ensures scripts that are run are stored away with a log.

 

Finally

I do apologize I published this initially without talking about some security options.  Over the next day it became pretty clear that we need to secure such a crazy technique.

On the other hand, there was some pretty good discussions - so may be leaving out the security section for one day was a good idea after all.

Test yourself. A MicrosoftFlow Expressions Quiz!

/

I had an idea a while ago that a Quiz built with Microsoft Form would be a great way to learn and improve our knowledge of Microsoft Flow expressions.  For a long time there was just two questions on date time expressions.

I saw some entries since my previous post, and this sunny Saturday morning I decided to add more questions and ramp it up to 10.

board-chalk-chalkboard-459793.jpg

So, test yourself! The test is completely anonymous, and it gets really hard at the end, so I suggest unless you are PhD #DoctorFlow - treat this as an open book quiz. 

Reference

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language

The Quiz

https://forms.office.com/Pages/ResponsePage.aspx?id=IFLmJmFV70aXg85fIEiSQVa44zYuPAVGrF-ZBk0inDpUMk1FT1hGOFo3N1ZEN0tVWk8wR1hRNDNINS4u

Special thanks to many of the early testers (Paul Culmsee, Fausto, Pieter, Sandy Ussia, Daniel Christian) that gave me a lot of feedback - I try to put in lots of comments regarding why a certain answer isn't correct, and clarify what I mean in many of the questions with examples of the result we wanted.

I'll leave this up indefinitely - use it to test your knowledge, and/or use it as a learning tool for some of the examples.  

Two complementary MicrosoftFlow podcasts in March, and Two Plateaus of MicrosoftFlow

/
Two complementary MicrosoftFlow podcasts in March, and Two Plateaus of MicrosoftFlow

John, you just recommended people learn Expressions and you cheered when Jon says Expressions is going away?!  Why are you so inconsistent?!

I finished listening to two parallel podcasts and there's some great contrasts between them that I wanted to point out, and write them down.

Read More

Design a "Delay until SharePoint File Changed" HTTP+Webhook for MicrosoftFlow

/

I love challenges - I love hearing people say "We need a Delay until File Changed" action in Microsoft Flow.  I agree.  But those are the same questions that sets one inquisitive mind to wander.

Plan

  • The Special Puzzle Piece
  • The generic nested Flow
  • The parent Flow
  • Results
  • Extend the technique

The Special Puzzle Piece "HTTP + Webhook"

Flows are triggered based on events - we can't easily have "a second trigger" in the middle of a Flow.  But there's a magical action in Flow that does do a 'wait' - and that's the HTTP + Webhook action.

"HTTP + Webhook" sounds like we plan to call someone else' webhook.  But we can use that to call our own webmethods.  So the idea then is that in the parent Flow, where we are building an approval, we'd delegate a 'wait' to a child nested flow and then have a generic Nested Flow that would perform any sort of 'wait'.  When the wait is over, the child Flow calls the callback URL and returns data back to the parent.

I first read about HTTP Webhook can be used this way reading @sp_muser blog post on Actionable Messages with Azure Functions. 
https://spwondering.wordpress.com/2018/01/17/actionable-messages-part-1-add-the-card-to-microsoft-flow/

HTTP Webhook left a very deep impression with me after reading that blog post.  Like a new lightbulb that won't go away.
Sometimes, we adapt new techniques.  This opens doors to new designs.  I hope this will help more people to be creative.

The nested child Flow

This is the child Flow - it uses a HTTP Request trigger and we need two arguments - a callback url, and a filepath to watch for in SharePoint.

We immediately check the file in SharePoint and remember the modified datetime (store this in a variable- modified).

Next, enter a do-until loop, and delay every 5 minutes and then grab the file's metadata again and check if the modified time has changed.  When it has changed, we exit the do-util loop, and call the original callback url with a plain HTTP action.

In your real scenario, think about whether 5 minute wait is too unnecessary, it may be better to check once an hour, or only a few times a day, if you don't want to loop too much.

The Parent Flow

In the parent workflow, where we need to delay until a File has changed - use the HTTP Webhook action, call the URL of the child Nested Flow, pass in the URL of the SharePoint file, as well as the listcallback() from the HTTP Webhook action.

Notice when the parent workflow is running and it enters the HTTP Webhook, the parent Flow enters a long-waiting asynchronous wait, magically.

Result

I go and modify the file that we are watching.

Notice the child Nested Flow wakes up after the next delay and detects the file's modified time has changed.

This exits the do-until loop, and call the callback URL.

Calling the callback URL wakes up and collapses the parent Flow

See the parent "waiting" action completes in some time in the future.  In my example, it was 10minutes.  But in real world this can be days or weeks.  Flow's timeout is 30 days.  So we can build quite complex, long-waiting, asynchronous processes.

Extend this technique

We can use multiple parallel HTTP Webhooks if we are waiting for multiple files or process updates to finish.

The nested Flow may not just check a file - it can be any long running process.

There's also a consideration that a long running HTTP Webhook works very similar to an Approval process and the Flow will happily wait for all these steps to return before collapsing and fan-in.