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.

 

Office 365 Groups Management As A Service: Episode II: Know your groups with Flow, MSGraph

This is the second post on building a group management tool with Flow and MSGraph.  In this post we talk about how to get a list of all your groups and copy them to a list in SharePoint so you can do more fancy things with them.

Episode I: Create Groups

Plan

  • Call MSGraph with Flow to get a list of all your Office 365 Groups
  • Create a SharePoint list to store them
  • Create/Update SharePoint list items
    (these steps above are good enough, the steps below are bonus points)
     
  • Delta Query
  • More Details
  • Parallel Execution
  • [NEW] Paging (if you have more than 100 groups) - suggestion to this post by @mikaelsvenson

MSGraph to List Groups

Some of you may have seem my love letter tweet.

  • MS Graph endpoint is https://graph.microsoft.com/v1.0/groups - docs 
  • Authority is https://login.microsoftonline.com/
  • Audience is Resource: https://graph.microsoft.com
  • Tenant ID, Client ID and Client Secret you'll need to register an App-Only credential app to get these.  Grant that App permission to Read Groups

 

Create a SharePoint List to store them

Complete the Flow to store Groups to SharePoint

This picture is complex, because it has a lot of tricky parts that's mostly caused by the way Flow's Editor works.  Flow's editor will try to filter the variables available to you by type, the Parse JSON action will parse JSON into a strongly type object following a strict schema.  This lets your subsequent actions work well.

BUT when your action returns null then suddenly the Parse JSON action can fail.  You'll then need to go back to the schema, and change some of the error "types" to "any".  You also may need to remove some of the fields from "required"

A few tweaks to the Parse JSON:

These tweaks are necessary because Parse JSON will fail if one of your groups don't have a createDateTime, classification or displayName.  You need to run this on your environment and check whether the fields need to be required or tweaked.

 

Paging

 

 

 

Delta Query

Because MS Graph supports Delta Queries - we can call the Graph with a Delta Query link, essentially, each time we call it, it'll return a nextLink (or a deltaLink).  So we just remember that, and use it the next time we want to call MS Graph again, and it will tell us just the differences.

See the delta query picked up one new Group I created for testing.

With a delta query - you can reliably set the Flow to run several times a day, and it will only send new/updated Groups back into the SharePoint list.

 

 

More Group Details

Sometimes you want more information on that group.  You can call Get group (on Azure AD connector) to get more information on the group.

Parallel 

We can configure the foreach action in Flow to run in parallel with 20 concurrency.  You'll need to add this to the definition JSON.

parallel.png
"runtimeConfiguration": { "concurrency": { "repetitions": 20 } }
flow-parallel.png

See the task of writing 19 Office 365 groups to sharepoint list was done in 4 seconds.

Result

 

[New] Paging

This section is added as @mikaelsvenson pointed out that I wasn't handling paging for groups.  Mikael also told me to use $top so I don't have to create a few hundred groups for testing.

Always listen to Mikael.

But I don't want to do loops - I've seen a pagination control, I want to know what that does.

 

Go to the settings for HTTP action and the first one is Pagination.
Turn that on, and set the limit to 5000.

  • Pagination controls seems to merge the results of multiple requests into one value array.  This is good.
  • Pagination controls works on Delta query - this is also good.
  • Pagination results does not return the deltaLink for next Delta query.  This is not so good.  So if we are planning to merge both Pagination and Delta Query we'll need to may be make two calls.

Notes

  • Use HTTP with Azure AD Auth to get all my groups in one call
  • Save that to SharePoint
  • Deal with Delta Queries with Flow
  • Configure Parallel execution in Flow so we can do this super fast.  This isn't code that runs one group at a time...  why would we do that when we can hit 20 at a time :-) 

 

 

Office 365 Groups management as a service - Flow, Functions and MSGraph

Because Office 365 Groups is a key component of group membership in Office 365, there will always be an evolving story on how to manage it, extend it, report on it and automate it.

Microsoft will continue to add more complex features to Azure AD Premium.  And this will be an enterprise grade solution to many customers that wants these features.  But there is a lot of room for partners to build Groups Management solutions.  

Ultimately, we have realized this: Our client's organization has unique rules, and there's a need to customize, fortunately, we have all the tools at our disposal, and they are not hard to do.

Group Management Life Cycle

This is a post in a series about Office 365 Groups management life cycle.  This is the first post - we will discuss the history Office 365 Groups creation.

Office 365 Groups Creation

There have been many blog posts about how we can automate Groups creation.  So this discussion is about understanding the underlying pieces, and how we must see these pieces as the building blocks to solve all your future problems.

Timeline of availability of O365 Groups creation methods

2015

2016

  • MS Graph API for group creation available
  • PnP Sample Solution - Vesa demo'ed in late 2016 with bots and Azure App Service
  • Azure Function enabled very simple hosting of small code

2017

    Connect-PnPMicrosoftGraph (PR by Mikael Svenson Feb 2017)
    New-PnPUnifiedGroup (available October 2016)

One Flow Connector to rule them all

I only know this: Everything we know got simplified

So here we are.  At the end of the first blog post on Groups Management - we need to understand the trend:

First, we have API
Then we have PowerShell, we have AzureFunctions for code or HTTP Request in Flow
Eventually we have a Flow Connector

We must see this pattern.  Everything we want to build is in one of these stages.

When we understand these pieces - there's nothing we can't build, for practically free.  The question isn't "No, this is too hard" or even "This should be free or out of box".

The question we should all be asking is "Where do I find a piece that fits here, right now" because I have a crazy customization need.

In 2017, everything got abstracted and simplified.  This trend will continue into the future - there will be more Flow Connectors.  Azure Functions will get more upgrades - durable functions are absolutely amazing.  MS Graph will get more API endpoints.  Life will be even more amazing.

Future of this series

I am not alone in what we are building for our customers - many consultants, partners and ISVs have already moved on to more complex issues:

  • Creation - Complex approval chains for Groups creation
  • Creation - Which products to enable on Groups creation
  • Creation - Post-Groups creation SharePoint site templating
  • Maintain - Scheduled Groups compliance audit reporting 
  • Maintain - Owner leaving organization scenario
  • Maintain - Members changed roles scenario
  • Closure - Expiration policies
  • Closure - Group archiving and closure

There are a lot of solutions to solve.  I want to cover more of Office 365 Groups life cycle management, with Flow and Functions, all on top of what MS Graph gives us already.

If you are interested in this topic or have shared some of the ideas you are working on - please share them with me and I would be happy to link to your work.