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.
