Power Apps API changes - no longer accept Azure Management token

There has been recently a major change to the API and authorization that Flow Studio uses to access the Power Platform. As we have just pushed the update to production, we want to take a bit of time to write this blog post - why we are doing this, and how it might affect you.

Flow Studio uses a mixture of APIs available through Azure, Microsoft Graph, and Power Platform to provide a tool that lets us focus on our flows, and make sure they continue running successfully. Since the very beginning in 2018, Flow Studio does this by requesting an Azure authorization token when the user logs in. Since the Power Platform APIs sit on top of the Azure platform, this token was sufficient to access across multiple APIs.

In 2021 there was a big update with Power Automate APIs and now in 2023 there's a second update with Power Apps APIs. As a result, we made the decision to switch to granular, Power Platform specific permissions.

Scope, Permissions

"read flows", "manage flows" are required permissions to access Power Automate - see and update your flows, shared flows or solution flows.

"read activities" allows Flow Studio to read recent Power Automate events - we use this to figure out which flows have been running recently.

"read approvals" and "manage approvals" are related to Power Automate's approval feature. Flow Approvals is not an area we currently have major feature development in, but it is a tab that we have available, so we ask for this permission to maintain the feature.  Since Power Automate approvals v2, some of the data is also available via the default solution DataVerse entities. So if you are keen to do some approvals reporting, you can access them that way.

"Power Apps Service API" is for reading Power Apps and Power Platform connections.

"basic profile" is what most apps request to read the user's email address and display name.

"maintain access" is the "offline" access - since Flow Studio is a single web application we store your token in the browser's web storage temporarily (this is standard MSAL functionality).

Trust and Verified Publisher

Flow Studio is a trusted, verified publisher. This tick is provided by Microsoft, and tells our customers that we aren’t some random new app that’s popped up now asking for your permissions. We have been around since 2018, and have operated under Microsoft publisher guidelines in our use of the APIs and the care we have for our customers. If we do a bad thing, Microsoft knows how to find us.

We are a legal company registered in Australia, our office operates out of Sydney.

Path forward for Flow Studio

So from Flow Studio v1.1.41 or later - users will need to re-login and grant the new set of permissions to continue using Flow Studio.

Flow Studio for Teams and Enterprise is updated as of v0.1.070

Other users might be affected by this

• If you use older versions of Power Platform Power Shell

• If you have very old Power Apps connectors created from a long time ago

• If you are using Office CLI to perform some actions on the Power Platform

You may see specific errors referring to "The received access token has been obtained from the wrong audience or resource".

Turning a new page

Wanted to write again, and let everyone know what I’ve been up to. I ended up taking a break through most of 2021 and 2022 simply resting, recovering, and doing light work from home.

2023 resumed with a big bang, I found motivation and drive to dive back into the many projects I’ve temporarily shelved in the last two years. I’ve also became pretty handy with a bunch of home DIY projects. It was a big change to the old me that only knew how to do digital projects but not physical projects. Perhaps more on that in a future post.

Flow Studio

We’ve had several Flow Studio fixes in the last two months

• there was an API pagination fix since the API no longer accepts 250 records at once and restricts us to only 50. (That means more pages and API call takes longer)

• API auth fix relating to Power Apps is in-progress.

• There’s a second API skip/continuation token fix.

• We’ve also tweaked the way trail is applied when anyone wants to try Flow Studio pro - you can sign up and trial will be available for two weeks - you can cancel the subscription before the trial end date to avoid being charged, if Flow Studio isn’t suitable for you.

Clarity / Flow Studio for Teams and Enterprise

We’ve had renewed interest in Flow Studio for Teams and Enterprise (Clarity) through the last few years.

• Flow Studio for Teams will be tweaked to focus on monitoring critical flows and alerting users when their business critical flows fail. This will be priced simply and does not offer governance capabilities.

• Flow Studio for Enterprise will be focused on the turnkey Power Platform Governance story, adding new features to scan more areas of the Power Platform, and integrate with CoE starter kit.

• So far this year, we’ve added BYO Azure Storage. There’s been a lot of fixes to API breakages in Power Apps area this year.

Contract Work

I started a regular part time contract work in Sydney CBD, so if you are local, hit me up for a coffee.

• I’m working with a lot of Power BI reports

• There’s a lot of Power Automate doing the heavy lifting as well.

• We are also talking about adding some Power Apps visuals to allow executive comments to be collected during a report presentation.

Community

Several of the meetups, conferences and events that I used to participate in are becoming active again. I hope to see more of the community not just virtually, but physically as well. I hope to be able to grab a coffee with you soon.

Parse CSV through Code in Power Automate Custom Connection

I was inspired reading Alex Shlega and Hiroaki Nagao ’s posts on using code with custom connections. So I set out to give it a go and work on another common problem I have: Parse CSV

First a picture showing you how it works.

View fullsize

Give it text, the action returns array of arrays.

Microsoft’s docs are here.
Write code in a custom connector | Microsoft Docs

And particularly, I need to parse CSV without using additional libraries, and using only the existing libraries available here. I noted that we do have access to System.Text.RegularExpressions, so I started my planning there.

Because parsing CSV correctly is a problem best sorted via use of a tokenizer, I went looking for a regular expression pattern that treats each line as a series of tokens. There are many patterns, but I like this one that I found on stackoverflow the best for my needs. https://stackoverflow.com/a/48806378

Code

So the code takes all the content of the body and splits by line breaks, then the regular expression is run over every line using Matches (this method returns multiple matches giving us a MatchCollection of tokens). In each match, I look for Group[2] which is the value without quotes “ and “. But if failing that match, we take Group[1] value.
We do not take the Match.Value because that would include the comma.

/end of regular expression explanation.

We cast the matches back to array via Linq and then back to JArray and return that back to Flow.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        if (this.Context.OperationId == "csv")
        {
            return await this.HandleCSV().ConfigureAwait(false);
        }

        HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.BadRequest);
        response.Content = CreateJsonContent($"Unknown operation ID '{this.Context.OperationId}'");
        return response;
    }

    private async Task<HttpResponseMessage> HandleCSV()
    {
        var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false);

        // (?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))
        // https://stackoverflow.com/a/48806378

        var re = new Regex("(?!$)(\"((?:(?:\"\")*[^\"]*)*)\"|[^\",\r\n]*)(?:,|$)");
        var lines = Regex.Split(contentAsString, "\r\n|\r|\n");

        var result = new JArray(lines.Select(line=>{ 
            var matches = re.Matches(line);
            return new JArray(matches.Cast<Match>().Select(match => {
                return match.Groups[2].Success ?
                match.Groups[2].Value :
                match.Groups[1].Value;
            } ).ToArray());
        }).ToArray());

        var response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = CreateJsonContent(result.ToString());
        return response;
    }
}

View fullsize

explain the regex and match groups

Swagger

This is the custom connection swagger YAML file.

swagger: '2.0'
info: {title: CustomCode, description: Custom Code, version: '1.0'}
host: johnliu.net
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /Csv:
    post:
      responses:
        default:
          description: default
          schema:
            type: array
            items: {}
            description: Array
            title: Array
      summary: Parse CSV
      description: Parse CSV
      operationId: csv
      parameters:
      - name: value
        in: body
        required: true
        schema: {type: string, description: Text, title: value}
      x-ms-visibility: important
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

I want to add more parameters over time, and that will involve a tweak to the input parameters on the Swagger definition. But that’s probably a task for another day.

Links:

Write code in a custom connector | Microsoft Docs

C# code in Power Automate: let’s sort a string array? | It Ain't Boring (itaintboring.com)

Calculate Sum & Average in Power Automate using C# code in a custom connector - MoreBeerMorePower (hatenablog.com)

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

Power Studio Update April 2020

I wanted to share our most recent newsletter for Power Studio (previous Flow Studio) and Power Clarity.

It was supposed to be monthly, but everything’s gone crazy, so now it is irregular.

Power Studio Update April 2020

Irregular Newsletters

We hope everyone's staying safe, be kind and taking care of each other, this is an unprecedented time in our generation, and it'll take love and perseverance to get through this.

In Flow Studio Solutions (the company behind Power Studio and Power Clarity) - we've taken on a mix of side consulting in addition to the online products.  While the progress may seem a bit slower, it ensures we have the runway to keep continuing for the future to come.

Since our newsletters has unfortunately become irregular, this is a pretty long email, we have several months' catch up in one big read.

• Too Many Emails?

• YouTube channel on extreme Flow techniques

• Inspirations from Power Platform product updates

• Flow Studio becomes Power Studio

• Power Clarity progress

• We are further apart, but we are also closer than ever before

Too Many Emails?

Many of us are finding unique challenges working from home.  Emails and chat messages are replacing previous physical conversations.  Some of us are finding our inbox overflowing.

If you are feeling overwhelmed with emails, and if our particular email doesn't spark joy.  If you are not getting value from our email newsletters.  If the email is sent to your service account but you'd like to read it on your personal account instead.  If you've changed roles and no longer work with the Power Platform.  If any of these sounds like you...

It is our sincere hope that our newsletters will bring you joy.  And if we aren't doing that, please, feel free to let us know and unsubscribe (link at the bottom).  Take care of yourselves first - you can always find us and re-subscribe later, if you like.

Unsubscribe (this link doesn’t work here on my blog)

YouTube channel on Flow mastery techniques

Over the last few months we've published several advanced flow techniques on our ever growing YouTube channel.

Learn techniques with Select

Many of these techniques combines advanced concepts such as Select, Filter Array and advanced expressions

• Advanced Format Number
  Flow format number advanced tips and tricks

• Fast Left Join
  Flow fast-left join technique

• Fast Distinct
  Flow super-fast distinct technique with union

• Complex HTML Table
  How to create Complex HTML Tables with Select

Inspirations from Power Platform product updates

There are several major updates to the Power Platform in the 2019 Release 2 and the upcoming 2020 Release 1.  Some of these features have already been released in preview or made it to general availability.

We wanted to point to a few big items, but also share some inspirations about the type of applications we can now build with these updates - which were difficult to build in 2019

• Power Apps - instrumentation

• Power Apps - Microsoft Teams app integration

• Power Apps - external users

• Flow - Adaptive Card improvements

• Flow - Assign approvals to O365 Group (roadmap)

Inspiration 1

Power Apps instrumentation for monitoring and governance.  When we add a simple instrumentation key to application insights - we can now easy track users as they use business critical applications.  This provides a far easier way for makers to build usage dashboards, far more accessible than previous methods of reading Office 365 Audit Logs (which maker may not have access to).  We also have far better detail that we can add to our instrumentation logging messages.

Inspiration 2

Power Apps external users support now allows any external users to use Power Apps.  They would need their own license, or be assigned one through Azure AD.  An external invitation link can be generated via Power Automate, allowing any external users to request and be granted external access with the flow talking directly to MS Graph via Azure AD guest invite, as well as ensuring correct permissions are assigned to the datasource - CDS or SharePoint.  This is one of the most perfect scenarios for allowing users from a different tenant to instantly access existing Power Apps applications without switching logins.  The end user experience is seamless with simply a browser link.

Inspiration 3

Adaptive Card updates to Flow - particularly "Send Adaptive card to channel and wait for response" now allows the Microsoft Teams integrated FlowBot to send media rich adaptive cards - and wait for users to provide custom response - including complex form fields.
This superpower allows flow to contact a user on demand to request for additional details that may be missing in the original data source.  Either as form data completion, or within an advance approval scenario.  It can be run on demand mid-process to quickly request for additional details or decision making.  We predict this to be one of the hottest new flow patterns in 2020.  Best of all - this functionality can run great on the standard Office 365 license.

Flow Studio becomes Power Studio

There are several major updates since our last newsletter for Flow Studio.

• We added monitoring for Power Apps in the freemium tier, and now we can quickly see all our Power Apps as well as Flows.  The list of apps shows the latest commit message.

• Since we added Power Apps - we decided to rename Flow Studio to Power Studio.  This turned out to be really fortunate, since Microsoft Flow was renamed in November to Power Automate.
  Both Flow Studio and Power Studio links will work.

• We added a developer build to test new features, if you are keen to see what's baking, here's a link to the test server for all the wonderous and dangerous new experiments.

• We added a toolbar to make some of the common actions more visible.  We've realized through testing that many users don't realize we have a contextual menu where a lot of the hidden gems are hiding, so this is a way for us to begin to surface these functions.

• We've given Export to Excel a major update - this was previously in preview and didn't work well (many of the columns were blank).  So finally we've fixed export to Excel.

• We've also deployed our latest update for generating a Mermaid markdown for flows.  This is a special markdown for flow chart documentation usable in JIRA and Azure DevOps.  This can be exported to SVG, PNG or Mermaid markdown.  This feature remains in preview - please give us feedback.

• We had two UX regression bugs with Save button missing in Edit JSON and Migrate plans new button not working, thanks to so many of you reaching out we had this fixed quick.

View fullsize

View fullsize

View fullsize

Try Power Studio

Power Clarity Progress Update

We began our work on Power Clarity in earnest over December 2019, and has hit several development milestones.  Our goal is to have all our Power Studio customers being able to sign up and trial Power Clarity.

Power Clarity is our turnkey, automated monitoring and governance solution for the Power Platform.  We took what we learnt from Power Studio, and ramp it up to do continuous scan of the entire company.  All our Power Platform environments, Power Apps, flows, connections, makers etc, and from that collected metadata, we return reports, tools and automated policies to help you manage and maintain your Power Platform assets mapped to the best practices in the community.

We are really excited to show you Power Clarity soon.

View fullsize

View fullsize

View fullsize

Grain Clarity of your Power Platform

We are further apart, but we are also closer than ever before

We were hoping to meet so many of you in person, but the current crisis has made us all separated from each other.

But in a way, we are probably closer than ever before.  So many of our user groups are now virtual, which means John would love to present at your user group, or if you'd like a call just to chat and catch up.  Let us know.

Catch us presenting at

• M365 May - Australia and NZ

• Virtual DC Power Automate and Power Apps 

• Sydney Power Apps and Flow 

Power Studio will always be a freemium product. So there will always be a free tier with access to see your existing Apps and Flows.  We hope you will leave us a comment about a feature that you would like to see in Power Studio.