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.

Taking PDF snapshot of any SharePoint list item for approvals with Flow

This is a post about combining two steps I've blogged recently.  Combined they form a technique that can be used with approval workflows to provide a point in time snapshot.

I have been building up a library of really useful Flow blocks.  Because you can combine them in pretty interesting and important ways.

Flow: List Item to PDF

  • Using Flow-Each - we iterate through each property of the SharePoint List Item
  • Use Select to map each property and its value to an array
  • Use Create HTML table and we have a table
  • Sprinkle some CSS for decoration
  • Send to PDF

PDF Result

This isn't too bad - definitely workable.  Need some cleanup.  But right away, we can already do:

  • Send pre-approval list item (or document metadata) as a PDF attachment along with the approval email
  • Save a post-approval list item as part of the record keeping process.

Bonus Points: Clean up HTML

  • Create new JSON with just Author and Editor's email (you can also use DisplayName)
  • Union that with Get List Item
  • Use the new result as the Object to continue.
  • Add Filter-Array after Flow-Each
  • Create new Array, keep only elements that doesn't contain _x
  • Use this in the Pivot arrays to table

PDF Result: round two

pdf-second.png

A successful run

This is how everything looks at the end.  This doesn't show the details of each step, but is intended to show you how all the pieces look together at the end. 

Final thoughts:

  • Special thanks to Andrew Jolly who commented in an earlier blog post that we can use PDF to create records
  • I use this as a Nested Flow as part of our Approval workflows to create PDF snapshots of list items. 
  • Create PDF as attachment along with the pre-approval email
  • Create PDF as snapshot after approval
  • If you want to use SharePoint's Records Management features - you can declare the PDF as records.  You will need a simple AzureFunction to do this.
  • Lots of techniques for manipulating JSON - filter, select and union
  • You can use For Each as well, if you are using that - then it's better to use Array Variable, and use the Append action.

Oh, I went out of my way to not use variables - they are overrated if you don't want to use For-Each

  • Special mention to the Culmsee's that you don't have to use variables if you want to keep things pretty ;-)
no-variables.png

You must copy all your Flows to SharePoint - simple ideas are the most brilliant

Another crazy idea while travelling on the train home.  This one is so simple, in hindsight, I reckon others will come up with this soon.  So I'm writing this first.

When I was testing the idea on Friday night, I did a livestream hack and that's on Mixer https://mixer.com/Sousily?vod=12283142 - skip to 13:50 that's when we started the hack.

Plan

Copy all your Flow definitions to SharePoint
On File Change, push your change from SharePoint into Flow
(TODO warning: make sure you don't create an infinite loop)

Why SharePoint

Primary benefits:

  • Backup
  • Versioning - see changes
  • Kick-ass built in JSON Editor for advance hacks
  • For your team to collaborate
  • And by the end of this blog post - you can push change back into Flow, you have both Backup AND Restore.

Copy all your Flow definitions to SharePoint

some notes:

  • You don't need the Filter array if everytime you run the Flow you will copy everything into SharePoint
  • I only want to do "changed in the last day" filter, because I want to put this Flow into a Schedule
  • Then in SharePoint, because it has versioning controls, I will only see an updated version if the Flow was changed that day 

SharePoint JSON Experience

A big thank you to Sohail Merchant who told me how to get to JSON Editor via the Preview Panel in SharePoint.  I was going to write this whole post about using OneDrive for Business.

In May 2015 - I put up a SharePoint User Voice request to allow .json as an allowed file type in SharePoint and OneDrive.  This was done and rolled out in October 2017.  

But the SP/OD team went beyond and included a JSON editor.  Which is why we have this kind of amazing integration between products.

Wonderful.

On File Change, push your change from SharePoint into Flow

The dance with Update Flow action is very delicate.  The note is:

  • Set definition to string variable first
  • Update Flow will now 'see' this variable, and let you assign it
  • Then change the object definition back to "Object" type

You must do these steps or it won't work

Nearly

Make sure you check On Change - Upload.  I suggest you use a different account to write Flows to SharePoint.

Then On Change - if the Modified By is this Flow account, skip the upload.

Otherwise the schedule trigger will download the Flows, and trigger the File change which will upload the Flows and tomorrow the schedule trigger will re-download the Flows.  It would be quite silly.

Now we are in an Awesome place

Look at all the Versions

 

Simple ideas are the best ideas.  You need to build one of this.

Immediate Next Self-Inception-Hack

Any time, you think, hey I have a tool that can update itself.  Then you realize Flow is Self-Hosting.

And you can use it to improve itself.

I have a For-Each in the Copy Flows to SharePoint

So I immediately hacked parallel settings into the first Flow above.

scan-flows-faster.png

Scanning changed Flows is now faster.  (slightly, since the for-each is in parallel).

 

 

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:

[
  "a",
  "b",
  "c"
]

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(/*)')

Result

[
  "a",
  "b",
  "c"
]
for-each-json.png

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.