2021 - break and thrive

/

Hello, you.

This is a very personal post.

2020 was tough on everybody, and it looks like 2021 may eventually bring a little bit of reprieve. I only have a modest and simple wish:

kurt-cotoaga-9Nq1IUhhayg-unsplash.jpg


For an immediate, urgent and heavy family matter, I must take a really significant break to try and ensure me and my family survives 2021. For our young family, this is a time of immense personal pain and loss.

  • I must take a break to be with my family — at least until April, but may be many more months. There’s no hurrying this, I want to take a much time as we need.

  • My consulting clients are aware of what’s happening and are so supportive. I have some reduced work with them that I will fulfill.

  • Flow Studio / Power Studio will continue, in the short time there’s a bunch of additional updates in the preview dev build dev.flowstudio.app that needs to be validated and pushed over into production.

  • Power Clarity will continue, but in a much, much slower pace.

  • My writings on the blog or YouTube will be random. There are mornings when I can muster some energy to write, in code or in blogs. If you enjoy them and want to see more, you REALLY have to tell me because I need (and otherwise lack) energy to produce them.

  • I’m unlikely to respond to questions on Twitter or LinkedIn

What can you do for us?

  • If you use Power Automate - I would really appreciate it if you would have a look at powerstudio.app, either for yourself or tell people about it. If it’s helpful to you, consider subscribe to it. If it doesn’t help you - let me know what might push you over the line to be a fan.

  • If you are already one of our 5400+ users or fans of Power Studio and wants to see the future evolved version - check out powerclarity.app

  • If you like Power Apps - you really should check out my GamePad PCF component and tell people about it.

  • I’m keeping my circumstances personal, but a generic statements that has a time component like hope you’ll get through it quickly, or hope you recover soon can miss the mark because I want as much time as we can to still have together. A nicer thing to say is probably please survive as long as you can.

  • Please put it in your calendar to check in on me every month from now, may be beginning of March, or beginning of April. Future me probably need someone to check in on me.

  • Years in the future, when the pandemic is over, invite me to your home for a meal.

How are you feeling?

We are free falling. There’s no parachute. When we hit the ground, at least one of us won’t survive, but we may all fall apart. We are already falling apart.

Self-service Microsoft Form for external guests to use Power App

/

This is a walk through of the steps to create a self-service guest user sign up using Microsoft Forms and redirects to the Power App.

Steps

  • Create a Microsoft Form to capture external user’s email address

  • Create the app registration to be able to make a Microsoft Graph invitation call

  • Use Power Automate via Microsoft Graph invitation API to add this email as external user to tenant

  • Add user to a group that the app is shared with

  • Redirect back to the Power App (or SharePoint extranet site, or Teams)

Create Microsoft Form

  • Change setting to allow anonymous - anyone can access this if they go to the form’s public shared URL

Create App Registration in Azure AD Portal

  • Go to Azure AD portal > App registration

  • You should name this app similar to your Power App name - it doesn’t need to be the same, but it’s less confusing to your tenant admins when reviewing the registered apps in Azure Portal.

  • As this app registration is only used for your tenant - it can be Single Tenant

  • Add Application Permission for User.Invite.All

  • This is a tenant admin consent required permission, so grant it here, or ask a tenant admin to grant it.

  • You will need to generate a Client Secret (no screen shot included), save that client secret.

  • You will also need to copy the Client ID

  • You will also need to copy the Directory ID (tenant ID)

Oh we need a Power App

  • Made a quick Power App here - the text label shows the current user’s email. For external user this would show external email (to proof this works).

  • For Sharing - I’ve made this Power App shared with Everyone.

  • We need to copy the Web link with the tenant-id

Now we need a Power Automate (Flow)

  • Run on Microsoft Form submission

  • We’ll need this redirect URL - when external guest invite is complete, redirect to our Power App

  • Because we registered an “Application Permission”, we can use the HTTP action to call Microsoft Graph without using delegate permission.

  • See also https://docs.microsoft.com/en-us/graph/api/invitation-post?view=graph-rest-1.0&tabs=http

  • Enter: Tenant, Audience, Client ID, Client Secret (not included in picture)

  • You should have all these values by this point.

  • The JSON message to send to Microsoft Graph should have the redirect URL back to the Power App

  • If successful we will get a user Object ID - we should add this to a Group as you need.

  • In my simplified example since my Power App is shared with Everyone - the external guest user will get access to it without being a member to a group.

Result

  • Switch to an external tenant (special thank you for Blackmores for example here)

  • Fill in the form

  • See the flow trigger and create an invitation from my tenant (Flow Studio Solutions) to guest tenant (Blackmores)

  • The guest email is added as a guest user type in my tenant

  • In the email of the guest email - Microsoft Azure AD sends a B2B invitation email

  • Note the redirect URL is the Power App we will be redirected to at the end of the guest invitation process

  • First time accepting will create this permission dialog reviewing that you (as guest) indeed want to be added to the external tenant. They will see your email, name and photo.

  • After successful redirect - the Power App loads, and look I’m using the Power App as an external user with external email address.

  • If the external user clicks the accept in the email again - they will skip the permission review process and be redirected by Azure AD into the Power App directly.

Variations and extra homework

  • The triggering mechanism doesn’t need to be Microsoft Forms. It can be a HTTP Request trigger that accepts an email address. In a scenario where I’ve build a child tenant to a parent tenant - some javascript on the child tenant send a POST request to the flow to initiate the invitation.

  • The email doesn’t need to be sent directly. The invitation API returns a redemption URL - which can be returned by the Flow in a HTTP Response to a calling javascript, and the user can be redirected to that redemption URL directly without needing to go through an email.

  • You can also capture the redemption URL and create your own email template.

  • You don’t need to redirect to Power App - you can be redirected to a SharePoint extranet that now includes the new guest user. Or to the Teams (by adding the guest user to the team’s group membership).

Ninja List: The great migration of SharePoint Workflow to Power Automate

/

Hello, I’m John Liu, #FlowNinja

I am both MVP in Office App and Services (SharePoint) and Power Platform (Flow), I’ve been writing tools to help (4000+ in over 3500 tenants) makers manage and maintain their flows for the last two years. As a bonus, I also grok Power Platform licensing.

The announcement that SharePoint 2010 Workflows will be terminated on SharePoint Online is one that I’ve been planning for, even before Microsoft announced they would deprecate the workflow engine.

SharePoint 2010 and 2013 workflow engines are based on Windows Workflow Foundation. That team doesn’t exist. There’s no WF in .NET. Not to mention that WF is not an engine that’s designed for the cloud era - it runs on heavy machines, serialized through databases. It doesn’t auto-scale, or handle webhooks or does anything async. It’s an pre-cloud relic that doesn’t know how to get access to unlimited serverless compute.

But yet, putting aside my own criticisms of the old workflow engine, those workflows are still extremely business critical. So the Office team has been shouldering the maintenance of those workflows - while the world moves on to a cross services and cross platform solution built on Logic Apps and Power Automate.

I didn’t think we were ready yet to make the move in 2020. But we have to draw the deadline somewhere, so, let’s start by scanning and figuring out what is next.

This is my master ninja list of resources.

This is a highly curated list. I’ll be updating this until there are no more SharePoint 2010 and 2013 workflows.

Microsoft’s announcement

https://support.microsoft.com/en-us/office/sharepoint-2010-workflow-retirement-1ca3fff8-9985-410a-85aa-8120f626965f

SharePoint PnP team’s modernization scanner

https://docs.microsoft.com/en-us/sharepoint/dev/transform/modernize-scanner

SharePoint 2010 Workflows - August 1 (off on new SharePoint Online tenants) November 1 (off for all tenants)
SharePoint 2013 Workflows - Already deprecated, future termination expected.

Microsoft Official Guidance

https://docs.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/migrate-from-classic-workflows-to-power-automate-flows

Community Resources

Todd Klindt

Sympraxians

SharePoint PnP

The main plan

Scan

  • Don’t panic.

  • Run the migration scanner and work out how many workflows you actually have. Then narrow it down to what are actually business critical.

  • Todd Klindt in a follow up blog post talks about scanning the workflow history list in each site to determine if a flow has been running recently. This would help determining if an workflow is old and is it even being used.

Migrate

  • Re-write SharePoint Designer Workflow as Power Automate

  • Will need to connect to existing workflow via SharePoint Designer

  • In most cases, old SharePoint Designer Workflows run on change of a list item - these can be replaced with Flow’s Item Modified trigger or File Modified trigger.

  • If you have SharePoint Designer Site Workflows, or list workflows that have a Start Workflow dialog, then these need to be replaced with “SharePoint For Selected Item Trigger” in Flow

  • If you need to replace the out of box “Request Sign Off” or “Content Approval” workflows which both have Power Automate replacements, see Shane Young’s video
    https://www.youtube.com/watch?v=RAADqTG2EZM

  • You may want to reuse workflow functionality, there are a few ways to go about with this:

    • Use HTTP to call child Flows (this is a premium feature)

    • You can use Solutions which helps with creating Parent Flow and Child Flow, but Solutions doesn’t help with multiple sites, so this isn’t a perfect case.

    • You can use Power Studio’s migrate to help you copy and manage deployment of a template flow across dozens and hundreds of SharePoint sites.

  • Power Studio’s Clipboard feature can be used to save snippets and re-use them with Flow designer’s copy and paste clipboard.

  • Power Studio’s Edit JSON feature can be used to rapidly clone an existing flow, and then modify it further (advanced user).

Monitor

  • Flow’s run history is only stored for 28 days

  • You can write a flow history to a separate list within your Flow to keep track of crucial flow run metadata

  • You may want to consider using Trigger Conditions which will help you ignore trigger events and make flow’s run history more manageable.

  • You can use Power Studio’s Run History to help you pinpoint which run you want to review, or even resubmit.

  • You can also use Power Clarity which provides continuous flow run monitoring and error notification.

  • Power Clarity gives reports of which Flows are running on what SharePoint sites.


The Pain Points

The Gaps

  • No actions that perform “Wait until a field changed to X”.

Licensing

  • Automated Flows run as the maker - if the Flow needs premium connections, then that is either 1 P2 license for that maker - this is cheaper
    If you need a lot of capacity, you can also consider the Per-Flow plan

    • Scheduled flows are Automated Flows

    • HTTP Request (service flows) are Automated Flows

  • Instant Flows run as the current user - so if an instant flow needs premium connections - then that is best covered with the Per-Flow plan.

    • For Selected Item / File are Instant Flows

    • Flow Button are Instant Flows

  • A Flow called from Power Apps is covered by the Power App’s license. So if the flow needs premium, then the Power Apps needs premium, but the flow doesn’t need an extra flow-premium.

    • Power Apps Trigger are this category - they are instant flows, but are covered by Power Apps license.

  • Flows are no longer calculated by “Runs” or “pooled runs” - this was removed a while ago. There are no longer any ‘run limits’. But instead, there are service limits relating to number of API calls. Standard and Premium tiers have different API call quotas per day. Flow action can fail if you have run out of number of API calls in that period.

    • This may affect you if you are using “service user accounts” to run your flows - I recommend the easiest way is to plan and assign P2 to these “service user accounts”.

    • For very high volume Flow runs that are critical and can’t be filtered via trigger conditions, consider using Logic Apps and bring your own Integration Account ($$).

Updates (2020-09-17)

Let me know if I missed anything

Reach out, this is an ever updating list. We will add more resources - both our own and those from the community. Our goals here is to align with yours and Microsoft’s:

  • Make sure your business process workflows continues moving forward.

  • Teach and create patterns to help you make your own flows, and make them better.

  • Build tools that will help you do more, faster, if you don’t have the time.


How to provision SharePoint list and resources with only standard Microsoft Flow using ExecuteSiteScriptAction

/

I wanted to blog about an interesting technique I was testing - how to call ExecuteSiteScriptAction from Microsoft Flow - and using that to provision SharePoint site.

We’ll do this all with Microsoft Flow and it is all part of the Flow / Office-Seeded license so there’s no extra cost.

Steps

  • YouTube: How to create custom SharePoint list in one action using ExecuteSiteScriptAction and Flow

  • Documentation links for Site Design and Scripting

  • ExecuteSiteScriptAction seems to be something new allowing us to directly execute site script without saving it as a site script first

  • Future ideas

I did a recording of this on YouTube last week, this blog post will cover the steps and goes a bit deeper into the technical details.

Documentation links for Site Design and Scripting

  • https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-json-schema

  • https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/get-started-create-site-design

ExecuteSiteScriptAction

ExecuteSiteScriptAction is a REST command available on the API, although doesn’t seem to be mentioned in the REST documentation (yet)

  • https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-rest-api#rest-commands

I actually found out about this from reverse engineering the new SharePoint “Create List from Excel Spreadsheet” feature. I was reading how it was done in JavaScript, and came across this end point.

I did a search for ExecuteSiteScriptAction and finds a reference in PnPJS.

  • https://pnp.github.io/pnpjs/sp/site-scripts/#execute-site-script-action

  • https://github.com/pnp/pnpjs/issues/1096


What does this all mean?

ExecuteSiteScriptAction allows us to execute site script directly, without first saving it as a site script and then execute the site script.

So it’s ideal to provision a complex SharePoint resource.

Previously, I do this either via a series of REST calls, or use REST with $batch or call Azure Functions, but all those methods are more complex. Using Site Script is a much easier way to quickly ask SharePoint to provision the resource on the server side, without multiple trips of communicating with the server.

To do this in Flow (watch the video):

Use this in the first compose - create a JSON of the site script to createSPList with several subactions.

{
  "verb": "createSPList",
  "listName": "Customer_Tracking",
  "templateType": 100,
  "subactions": [
    {
      "verb": "setTitle",
      "title": "Customer Tracking"
    },
    {
      "verb": "setDescription",
      "description": "List of Customers and Orders"
    },
    {
      "verb": "addSPField",
      "fieldType": "Text",
      "displayName": "Customer Name",
      "isRequired": false,
      "id": "c532fcb9-cdb3-45c6-8247-c784dcd58e1a",
      "internalName": "customer_name",
      "addToDefaultView": true
    },
    {
      "verb": "addSPField",
      "fieldType": "Text",
      "displayName": "Customer Name 2",
      "isRequired": false,
      "id": "c532fcb9-cdb3-45c6-8247-c784dcd58e1b",
      "internalName": "customer_two",
      "addToDefaultView": true
    },
    {
      "verb": "addSPFieldXml",
      "schemaXml": "<Field ID=\"{c532fcb9-cdb3-45c6-8247-c784dcd58e1c}\" Type=\"Choice\" DisplayName=\"Customer Category\" Required=\"FALSE\" Format=\"Dropdown\" StaticName=\"customer_category\" Name=\"customer_category\"><Default>Operations</Default><CHOICES><CHOICE>Operations</CHOICE><CHOICE>IT</CHOICE><CHOICE>Legal</CHOICE><CHOICE>Engineering</CHOICE></CHOICES></Field>"
    },
    {
      "verb": "addSPField",
      "fieldType": "Text",
      "displayName": "Text Field",
      "isRequired": false,
      "addToDefaultView": true
    },
    {
      "verb": "addSPField",
      "fieldType": "Number",
      "displayName": "Number Field",
      "internalName": "ElectricSlide",
      "addToDefaultView": true,
      "isRequired": true
    }
  ]
}


Then create a second JSON

{
  "actionDefinition": "@{string(outputs('createSPList-definition'))}"
}

Finally, send that to SharePoint via REST

// _api/Microsoft.Sharepoint.Utilities.WebTemplateExtensions.SiteScriptUtility.ExecuteSiteScriptAction()


{
  "accept": "application/json; odata.metadata=minimal",
  "content-type": "application/json;charset=utf-8"
}

That’s it - all the actions are standard, and doesn’t require calling out to a webservice.


Future ideas / Homework

  1. Imagine Flows now can easily call this to create needed SharePoint list if it doesn’t exist.

  2. Or to upgrade SharePoint lists across many sites (use variable for site url).

  3. A new PowerApps can call an accompanied Flow to create the list that it needs.

  4. We can also use many of the other features within Site Script to modify permissions, set theme and colours etc.

  5. We can even use AddSPFieldXML to add complex fields via XML definition.

  6. As a final thought - I think the Site Script definition is a much easier way to define provisioning steps, since it’s really designed to be low code.

  7. Being able to use one technique (Site Script) to provision assets during:

    • Create Sites using out of box experience

    • Invoke with Power Automate (Flow)

    • And we can also invoke Site Script from SPFx in JavaScript through PnPJS

    • This kind of shared skill re-use, and with Microsoft extending site script makes this a great technique to learn and be familiar with.

  8. Daniel Laskewitz reached out and told me that we can add the new SharePoint REST API for creating new sites to completely automate creating new sites as well as provisioning the scripts all using Power Automate. Here’s the doc link he sent me. Thank you Daniel!

    https://docs.microsoft.com/en-us/sharepoint/dev/apis/site-creation-rest





ApolloServer, AzureFunctions and local debugging

/

I wanted to jog down some notes that’ll help with local debugging of Apollo Server with Azure Functions.

Firstly, due to changes to Azure API routing - specifically x-ms-privatelink-id having strange invalid characters - we need to expand the function handler and blank out the x-ms-privatelink-id property.

https://github.com/Azure/azure-functions-host/issues/6013

Now to get local debug happening - we must configure CORS.

Add the cors settings - origin can be ‘true’ for every url, or ‘http://localhost:4200’ if only for your local build.

Additionally, copy the access-control-request-headers to Access-Control-Request-Headers because ApolloServer applyMiddleWare is case sensitive.

https://github.com/apollographql/apollo-server/issues/4178

const graphqlHandler = server.createHandler({
    cors:{
        origin: true,
        credentials: true,
    }
});
export default (context: Context, req: HttpRequest) => {
    // https://github.com/Azure/azure-functions-host/issues/6013
    req.headers['x-ms-privatelink-id'] = '';
    // apollo-server only reads this specific string
    req.headers['Access-Control-Request-Headers'] = req.headers['Access-Control-Request-Headers'] || req.headers['access-control-request-headers'];
    return graphqlHandler(context, req);
}

These changes by itself isn’t enough, we also need to make sure the Azure Functions runtime is forwarding the options method correctly to our Apollo Server. Make sure ‘methods’ contains ‘options’ otherwise the Azure Functions local runtime will not accept the browser pre-flight request.

(this second one was the one that got me for a while - why wasn’t Apollo Server options being called?!)

{
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "get",
        "post",
        "options"
      ]
    },
    {
      "type": "http",
      "direction": "out",
      "name": "$return"
    }
  ],
  "scriptFile": "../dist/graphql/index.js"
}