ForEach Property in #MicrosoftFlow JSON. With XPath? #microblog

I can't think of a way to do "ForEach Property Of JSON" in MicrosoftFlow or LogicApps - so I came up with this method that involves XPath.

Take example this JSON

  "a": 1,
  "b": 2,
  "c": 3

I want to do ForEach over the properties, so I need a way to convert this into:


The usual suspects don't seem to work:

  • ForEach (only array)
  • Data Operations - Select (only array)
  • Array (wraps one object into array of one object)
  • CreateArray (wraps multiple objects into array)
  • Split - this could be used, but we'll have a hard time with nested JSON

Lets do XPath

XML objects must have one root element.  So let's wrap a root around our JSON

  "root": {
    "a": 1,
    "b": 2,
    "c": 3

This next XPath splits each XML element under /root/ into a Nodeset (array of XML elements).

xpath(xml(outputs('Compose_2')), '/root/*')

Data Operations - Select

for each XML node, select just the name, map this for each node

xpath(item(), 'name(/*)')



I'm sure there'll be a better way one day.  But for now this will get me through.  I need this to be able to read nested JSON structures as part of my bigger plan.

JSON cheatsheet for Microsoft Flow

I have a big blog post in the works for a significant dive in Microsoft Flow functionality.  But I realized that before we get there, we need to GET GOOD at doing JSON in Microsoft Flow.

So this is the cheat sheet.  With all the caveats.  Enjoy!

Update: added a compound JSON object construction.

This is the part of the cheatsheet series on Microsoft Flow.

  1. JSON cheatsheet for Microsoft Flow (this article)
  2. Nested-Flow / Reusable-Function cheatsheet for Microsoft Flow
  3. Building non-JSON webservices with Flow
  4. One Connection to Proxy Them All - Microsoft Flow with Azure Functions Proxies
  5. Building Binary output service with Microsoft Flow

var X = { "x": 1 }

{ "x": 1 }

And this is the running results

X = JSON.parse("{ 'x': 1 }")

"@json('{\"x\": 2}')"

X is updated.  But the run result panel for Set variable shows incorrect INPUTS value of X

{ ...X, { 'y': 2 } }
jQuery.extend( X, { 'y': 2 } )

"@union(variables('X'), json('{\"y\":2}'))"

Union is great - it'll let you add properties to existing objects easily.

var Y = { ...X, { 'y': 2 } }


Use union to set variable

if ( Y.x != null )

sometimes, you need to check if an object contains a property

sometimes, you suddenly don't need the " double quotes for conditional statements. 
sometimes I feel this is a bug.



The UX can get quite silly.  It forgets how to render the basic view after you save.

(Y.x || Y.y)

"@coalesce(variables('Y').x, variables('Y').y )"


Use Coalesce to pick the first non-null value.  If the property doesn't exist this will error.  Protect the check with contains

X = Y

And we end this cheat sheet on a simple one.  Assigning variables to another.

Note you can't currently use the variable in setting the variable itself.

This would have been REALLY useful...  Because this would have let us keep appending to existing variable.


Finally, object composition
var Z = { z: Y }

"@json( concat( '{ \"z\": ', variables('Y'), ' }'  ))"

The result, see how a compound JSON object has been constructed.

AzureFunctions Work Fan-out with Azure Queue in PowerShell

So I really like using PnP-PowerShell to chain up and perform complex operations in Office 365, and linking them up with AzureFunctions and Flow.

Scenario - scan all tenant's site collections

I bump into another problem today - I needed to scan all my site collections within the tenancy and start a Flow that will notify and apply site closure policy and lock the site.

We can scan all the site collections in a tenant in one request Get-PnPTenantSites - but we want to make sure the job doesn't time out.

So we need to fan-out the workload to a queue, and trigger multiple AzureFunctions to scan each site collection in parallel.

Problem - PoSH Queue binding only 1 output

As soon as I started writing the PoSH - I remembered, with the default PoSH Queue binding - you can only write 1 message to the Queue.

Unlike C# where you could do multiple:

foreach(var message in messages) {
    await outQueue.AddAsync(message);

In PoSH - if you are using the default integration tab to set up an Output Binding to AzureQueue.  Then you can only write one message to the Queue.


How to write multiple messages to Queue in PoSH?

It turns out I've already solved this once before in April, but I had completely forgotten this, because I DIDN'T BLOG IT.
Let that be a lesson to all developers - Always blog something cool that you did.  Because you will need it in two months when your memory failed you.

# if you have been using the storage in other functions 
# you will already have the connection string in your 
# function's app settings - reuse it

$storeAuthContext = New-AzureStorageContext -ConnectionString $env:azurefunctions3a585851_STORAGE 

$outQueue = Get-AzureStorageQueue –Name 'my-queue-name' -Context $storeAuthContext
if ($outQueue -eq $null) {
    $outQueue = New-AzureStorageQueue –Name 'my-queue-name' -Context $storeAuthContext

# this example isn't scanning sites - just going through files in a library
$items | % {
    $item = @{
        source = $_.FieldValues.FileRef;
        target = ($destination + "/" + $_.FieldValues.FileLeafRef)

    # Create a new message using a constructor of the CloudQueueMessage class.
    $queueMessage = New-Object `
        -TypeName Microsoft.WindowsAzure.Storage.Queue.CloudQueueMessage `
        -ArgumentList (ConvertTo-Json $item)

    # Add a new message to the queue.




Taking a picture with PowerApps and sending to SharePoint with help of Azure Functions

Taking a picture with PowerApps and sending to SharePoint with help of Azure Functions

Sometimes, after having written a selfie app in Silverlight, JavaScript, even an Add-in (SharePoint Online), you want to do it again with PowerApps.  This is that article.  I think it's really fun.  And I think it's funny I'm solving the world's problems one AzureFunction at a time.  And I think I need help.

Read More

April PnP JavaScript special interest group call and Azure Functions demos

Shortly after the March Azure Functions demo, I reached out and asked Patrick about coming back to do a follow up focused on JavaScript - specifically PnP-JS-Core.  As I've completely skipped it in the March call/demo that was focused on PnP PowerShell (and C#).  When I first started playing with Azure Functions I was doing everything in JavaScript - so it was nice to return to be able to do this demo. 

I'm a bit more mindful of the time, but this whole demo is on PnP-JS-Core.

We focused on a few things that people asked in the PnP-PowerShell call in March:

  • What about JavaScript - can you show JavaScript in Azure Functions
  • Isomorphic PnP-JS-Core - running on NodeJS - if you are going to use JavaScript on the client, might as well use the same code on the server.
  • Authentication using Sergei's node-sp-auth (congrats on MVP award!)
  • How to test your Azure Functions locally via azure-functions-cli
  • Live debugging with VSCode (locally)
  • How to pack your JavasScript AzureFunctions so that you don't need to deploy the massive node_modules (which is both costly for storage, and has a higher startup time).  We use azure-functions-pack

SharePoint's Future is full of JavaScript

Lots of quick little demos that makes a nice introduction scenario - but if you have not seen Azure Functions before, this is best viewed as a supplementary follow up to the first PnP Call in March.

Related Links