Power Platform

Unfortunately, Power Automate does not have any support for Regular Expressions (regex or regExp), which can sometimes be very limiting, or make things which would be easy, quite complicated.

Regular expressions are very useful for extracting and replacing information from any text by searching for one or more matches of a specific search pattern. This blog post will not go into great detail about how to use Regular Expressions, but simply how to execute them with Power Automate.

Third party connectors exist which can perform Regular Expressions on behalf of Power Automate, but this blog post will focus on how to execute regular expression actions for free, within Power Automate.

So, as I said – there is no Regular Expression support within the standard Power Automate Actions, but there is regex support built into JavaScript, which can be accessed via Office Scripts in Excel Online. It’s possible to pass parameters into, and out of Office Scripts. So Excel Online can be used as a host for getting access to JavaScript functionality. If you’d like a demo of how this works, check out this video:

Regular Expression Match with Power Automate

Let’s start with an example, If I want to extract all of the email addresses from the string below:

This text contains email addresses, such as [email protected] and [email protected] – also [email protected] has been included and [email protected] is here too.

I can use the following regex pattern, with the flags g and i (global and case insensitive respectivly)

\b[A-Z0-9._%+-][email protected][A-Z0-9.-]+.[A-Z]{2,}\b

I got the regular expression to find email addresses from here. So in Power Automate, this is a simple two step flow:

As you can see from the above, my office script accepts three parameters:

• The string to perform the regex match on.
• The regex pattern to use.
• The flags to pass to the regex object.

and the output from the run script action is:

As you can see, we get back a JSON array of results. This is the code for the regexMatch function:

function main
(
  workbook: ExcelScript.Workbook,
  searchString: string, 
  regexPattern: string, 
  regexFlags: string
) : Array<string> {
  let matches: Array<string> = []
  let re = new RegExp(regexPattern, regexFlags);
  let matchArray: Array<string> = searchString.match(re);
  if (matchArray) {
    for (var i = 0; i < matchArray.length; i++) {
      matches.push(matchArray[i]);
    }
  }
  return matches;
}

Regular Expression Substitute with Power Automate

Another powerful function of regex is the search and replace function. Power Automate already has a useful replace function, but without the ability to find matches first, it can be limited. Let’s try another example:

Hi Customer Service,
Please take payment from my credit card. The number is:
4111 1111 1111 1111. Expiry date is 06/2022 and the CVV is 342.

In this example scenario the string contains a credit card number, for security reasons the objective is to find the credit card number and replace it. Again, this is simple, I used this regex pattern:

\d\d\d\d \d\d\d\d \d\d\d\d \d\d\d\d

with the g flag applied:

This time the function has an additional parameter called replaceString which specifies what matches will be replaced with. The result is:

Note – the above is just an example and will not match all credit card numbers!

Another simple example which would be difficult to achieve with standard Power Automate actions. The following string has too many space characters, and I’d like to replace them with a single space:

I love       Power     Automate, but I     really wish   that it    had support for Regular    Expressions.

Once again, it is very simple with the regular expression pattern \s\s+:

Conclusion

This blog demonstrates that it is possible to add regex support to Power Automate for free, without the use of external connectors. It also demonstrates the additional power that Office Scripts can add to Power Automate by providing access to JavaScript.

But I hope that support for regular expressions is added to the platform, string manipulation is a common requirement of many flows. An idea was submitted on the Power Automate Forum here in June 2019 to add Regular Expression support, but so far no support has been added. So please head over there and vote for it. I’ve also submitted an idea to allow execution of Javascript functions without the need for the Excel connector here, so I’d appreciate your vote on that idea.

The downside to this method is that the “Run Script” action is currently in preview, and it is limited to 200 executions per day. So depending on how frequently your flow runs, it might not be useful.

You can download my two Office Script functions from here:

• Regex Match
• Regex Substitute

Simply place the downloaded files into your OneDrive\Documents\Office Scripts directory and they will become instantly available to you. If you find this useful, I’d be interested to hear your feedback in the comments. What are you using it for?

I’ve written some other useful posts on Office Scripts which you might want to check out:

• Power Automate – How to sort an array
• How to Sum an Array of Numbers in Power Automate

There is no built in function in Power Automate to enable you to sort an array easily. But as with my previous post in which I demonstrated how to sum an array of numbers, this can be achieved very easily with an Office Script.

There is quite a bit of demand for the ability to sort an array in Power Automate, it has received a good number of votes on the ideas forum.

Here is the array I am going to work with, which is a list of Power Automate Legends, in no particular order:

[ "Paul Murana", "Damien Bird", "Tiago Mendonça", "Leon Tribe", "Ryan Maclean", "Fausto Capellan", "Jon Levesque", "Matt Beard", "Alison Mulligan", "Antti Pajunen", "Mark Stokes", "Azure McFarlane", "Sancho Harker", "Tomasz Poszytek", "Jon Russell" ]

This flow is going to make use of two Office Scripts:

• One which sorts an array alphabetically.
• Another which reverses the order of the array.

The Script Code

Below is the code which you can insert into your Office Scripts for each of the functions. Once you have added these functions to your Office Script repository, you’re good to build the very simple flow.

function main(
  workbook: ExcelScript.Workbook,
  sortArray: Array<string> = [],
) {

  sortArray.sort();
  return sortArray;
}

function main(
  workbook: ExcelScript.Workbook,
  reverseArray: Array<string> = [],
) {

  reverseArray.reverse();
  return reverseArray;
}

Sort Arrays with Power Automate

Here is a screenshot of the very simple flow which shows you how this all comes together:

The first script action will sort the array in ascending order, and the second will reverse the order. Here are the outputs of the flow:

[
  "Alison Mulligan",
  "Antti Pajunen",
  "Azure McFarlane",
  "Damien Bird",
  "Fausto Capellan",
  "Jon Levesque",
  "Jon Russell",
  "Leon Tribe",
  "Mark Stokes",
  "Matt Beard",
  "Paul Murana",
  "Ryan Maclean",
  "Sancho Harker",
  "Tiago Mendonça",
  "Tomasz Poszytek"
]

[
  "Tomasz Poszytek",
  "Tiago Mendonça",
  "Sancho Harker",
  "Ryan Maclean",
  "Paul Murana",
  "Matt Beard",
  "Mark Stokes",
  "Leon Tribe",
  "Jon Russell",
  "Jon Levesque",
  "Fausto Capellan",
  "Damien Bird",
  "Azure McFarlane",
  "Antti Pajunen",
  "Alison Mulligan"
]

Script Files

You can download the scripts I created directly from here, simply stoe them in your in Onedrive \Documents\Office Scripts folder and they will appear in your scripts function in Excel connector of Power Automate.

array_sort.osts
array_reverse.osts

Performance

I tested these functions with an array of 20,000 items and it finished each action in around 5 seconds. So compared to creating a loop and sorting within Power Automate, this is blindingly quick.

Conclusion

I’m pretty excited about what is possible with Office scripts to extend the functionality of Power Automate .

Let me know how you get on, and if you think of any other useful ideas for Office Scripts we can use in Power Automate!

If you are new to Office Scripts (as I am) check out the overview from Micrsoft.

Although it sounds like a very simple thing to do, summing an array of numbers in Power Automate can be quite difficult if you have a large list of numbers to sum.

The most common method is to use an “apply each” action to loop round an array of numbers and increment the value of a variable. Here is a basic example of this:

This method is completely valid, and works just fine if you are only summing a small list of numbers. The problem is that there is very little scope for performance improvement. I tested the flow above with 1,000 numbers and it took 10 minutes to execute, which is unacceptably slow.

Finding a better way

Fellow Power Automate lover Damien Bird and I were trying to come up with a better solution for this and he created a neat method to sum an array which he posted on his blog, which solves the performance issue.

I have come up with another method, which is both fast and works for integers, floats and negative numbers.

Fast and Simple Solution to Sum an Array of Numbers in Power Automate

My solution to this actually turned out to be very simple. It works by harnessing the Power of Typescript in Excel online to perform the sum calculation on behalf of Power Automate.

In terms of performance I tested it with a list of 20,000 numbers ranging in size from -500,000 to positive 500,000 and it took 5 seconds to complete, here is how it is done:

Create an Excel Workbook to Store Functions

The first step is to head into Excel online and create a new Workbook called Power-Automate-Functions. Once you are in the workbook do the following:

• Click on the Automate Tab
• Click All Scripts
• Click New Script

In the script window paste in the following code:

function main(
  workbook: ExcelScript.Workbook,
  numbersToSum: Array<number> =[],
  ) {

  let sum = numbersToSum.reduce((a, b) => a + b, 0);
  return sum
}

Rename it to SumArray, it should look like this:

The simple script accepts an array parameter called numbersToSum and has a single return value of sum.

Once you have saved the script, the Power Automate Flow is simple.

Execute the Excel function from the Power Automate Flow

Execution of this function is very easy, here is an example flow:

Simply choose the “Run Script” action from Excel online and provide your number array for the numbersToSum parameter. When you execute the flow you can easily see the return value in the output:

That is all there is to it! As I said above, I was able to process an array of 20,000 numbers in 5 seconds, so quite happy with the performance.

Conclusion

I’ve ignored the scripting functionality in Excel up until now. But I now realise that it gives Power Automate developer access to TypeScript which we can use to fill in lots of missing gaps in standard Power Automate functionality. I’m quite excited about it.

Being able to sum an array of values is just the first of many possiblities.

If you want to learn more about Office Scripts in Excel then check out this page from Microsoft.

Once again, thank you to Damien Bird for the inspiration for this idea.

This post explains how to update a Hyperlink Column in SharePoint Online using Power Automate. Unlike most field types, the hyperlink column has two components, the link address and the alternative text:

But when you try to create or update an item with a Hyperlink field in Power Automate, you are given only one field to populate:

The link text then becomes both the hyperlink and the descriptive text. If you have a long URL, this can ruin the look of your list and be confusing for users.

Create Hyperlink by using the Send an HTTP Request to SharePoint action

The solution is to use the “Send an HTTP Request to SharePoint” action to set both the URL and the Alternative text. This action would normally follow a Create Item action.

Select your Site Address and set the method to POST then set the URI to:

_api/web/lists/GetByTitle('Hyperlink Test')/items(ItemID)

Modify the above to contain your list name in place of “Hyperlink Test” and replace ItemID with the ID of your item, or dynamic content which references it.

Next set the Headers to the following:

{
  "Content-Type": "application/json;odata=verbose",
  "X-HTTP-Method": "MERGE",
  "IF-MATCH": "*"
}

You can copy and paste the JSON above if switch the headers to text mode:

Next is where the fun begins, an example HTTP Body looks like this:

{
	"__metadata": {
		"type": "SP.Data.Hyperlink_x0020_TestListItem"
	},
	"Link": {
		"Description": "Microsoft",
		"Url": "https://www.microsoft.com"
	}
}

But to make it easier for you, you can just type your list name, link description and Url into the form below and the correct code will be generated for you.

SharePoint List Name:

Hyperlink Column Name:

Hyperlink Description:

Hyperlink URL:

HTTP Action

Copy the code generated above into the HTTP Body and then replace the static values with your dynamic content. The complete action looks like this:

Give it a try and see how you get on. It is quite straightforward. I hope that in the future the standard actions will handle creating and updating Hyperlink fields better and there will be no need to do this in the future.

I recently discovered QuickChart and it is a fabulous tool for generating charts. It’s really easy to use from both PowerApps and Power Automate. In this post I am going to explain how to use it to generate charts with Power Automate using data from a SharePoint list.

Here is a sample of the SharePoint List:

Here are two samples of the Output Charts that are produced:

The charts are embedded into an email that produces a summary of the items in the list for that week. So let’s get on with it.

The HTTP action is used to make an API request to QuickChart, so a premium license is required.

The first chart above was created with a compose action and this HTTP Request:

So the complete JSON body looks like this when submitted:

{
  "backgroundColor": "white",
  "width": 500,
  "height": 250,
  "format": "png",
  "chart": {
    "type": "doughnut",
    "data": {
      "datasets": [
        {
          "data": [
            4,
            3,
            2
          ]
        }
      ],
      "labels": [
        "John Lilleystone",
        "Paul Murana",
        "Mohammed Islam"
      ]
    },
    "options": {
      "title": {
        "display": true,
        "text": "Issues by Assignee"
      }
    }
  }
}

The easiest way for you to generate the required JSON is to use the chart maker on QuickChart.io.

The most difficult part in this flow is reshaping the data from the SharePoint list, this is obviously highly dependent on what data you are trying to bring out into the chart so it’s difficult for me to explain, but this video should give you a good idea of how you can achieve it.

The other part you may get stuck on is embedding the image as Base64 into the email. I’ve got a separate post on how to embed an image in an email here.

I produced a weekly sales summary for a customer, which goes out to them every Friday:

I would be interested in hearing how you get on generating charts with Power Automate. Let me know if you come up with some cool use cases.