Blog

How to use RegEx in Power Automate

Intro

Extracting data from emails, PDF invoices, Excel sheets, or other documents is a crucial element in the job of a Power Automate Developer. Unfortunately, there is no built-in free RegEx support in Microsoft Power Automate.

If the data is structured, the go-to solution is using Regular Expressions (RegEx). This article assumes you know have a basic knowledge about RegEx. If that’s not the case, check this.

Table of Contents

Office Scripts in Power Automate (The Free Solution)

Office Scripts is the “new VBA” and lets you automate everything in Excel on the web (Excel Online). The Office Scripts can be written in either JavaScript or TypeScript, which is almost the same.

Creating a simple TypeScript with RegEx support in Excel Online as an Office Script and then calling the Office Script from Power Automate is the solution for free RegEx support in Power Automate. So here is your 2-step cookbook:

Create the Office Script

Fig. 1.1 - Go to Office.com and click the Excel icon

Go to https://www.office.com, log in with your Microsoft Credential, click the Excel icon (Fig. 1.1), and open a ‘New blank workbook’.

If you don’t see the Excel icon on your office.com page, click the nine black dots in the upper left corner and find the Excel icon here.

Fig. 1.2: Rename Excel Book

(This step is not necessary but recommended) If you don’t have an Excel book for the Office Scripts you use in Power Automate, this is the opportunity to create one. Rename the sheet by clicking on the current name of the Excel book (Fig. 1.2:) and change the file name ‘PowerAutomateScripts’.

This Excel book can now be used as a container for Office Scripts in Power Automate.

Fig. 1.3: Create a new Office Script

To create a new Office Script, click ‘Automate’ (in the top menu), then ‘All Scripts’, and finally ‘New Scripts’ (Fig. 1.3).

Fig. 1.4: Default Office Script

This is how a default Office Script looks like (Fig. 1.4). Mark the entire script with Ctrl+a, and then it will turn blue. Press delete and watch the default code disappear.

You can also rename the script by clicking on ‘Script 1’. Call it e.g. ‘RegEx Match’.

Finally, copy and paste the following script into the editor:

				
					function main
  (
    workbook: ExcelScript.Workbook, inputString: string, pattern: string, flags: string
  ): Array<string> {
  let regExp = new RegExp(pattern, flags);
  let matches: Array<string> = inputString.match(regExp);
  
  if (matches) {
    return matches;
  } else {
    return [];
  }

}
Fig. 1.5: The RegEx Script

Your script should look like this (Fig. 1.5). This Office Script can now be called in Power Automate.

Call the RegEx Office Script from Power Automate

Having created the Office Script in Part 1, the script can be called Power Automate.

Fig. 1.6: Run script

Find the ‘Run script’ connector and fill in Location, Document Library, File, and Script (Fig. 1.6).

Three parameters need to be filled:

  • inputString: The input string is where you want to extract data. This could be dynamic content from a previous action.
  • pattern: Your RegEx pattern.
  • flags: Your RegEx flags.
Fig. 1.7: RegEx example flow

Fig 1.7 shows how to use the RegEx Office Script:

  • Manually trigger a flow. Simple attended automation where the user starts the flow.
  • Compose. The input string. It could come from all static and dynamic sources in Power Automate.
  • Run script. Fill in the settings/parameters. In ‘inputString’, we use the previous ‘Compose’ dynamic content. The RegEx pattern is filled in ‘pattern’ and likewise the flags.

My InputString:

This is a text script to test our RegEx script/connector. Of course, you can always reach me at anders@andersjensen.org, but if you want a guaranteed answer, please post your question/feedback in the comments below. Kind regards, Anders Jensen dontuse@thisemail.com

My RegEx pattern:

				
					[A-Za-z0-9]+@[A-Za-z0-9]+\.[A-Za-z]+
Fig 1.8: The result

Run the flow and then inspect the output under ‘result’ (Fig. 1.8). This is an array of strings and is our matches.

Fig. 1.9: Print the results

Fig. 1.9 shows how to print the results. That is, print each string in the array of strings. First, use an ‘Apply to each’ with the ‘result’ as input. Now Add a ‘Compose’ and in ‘Input’ use the dynamic output from the ‘Apply to each’.

Fig. 1.10: Print a specific item

Fig. 1.10 shows how to print a specific item of our matches in the array of strings by using the expression:

				
					outputs('Run_script')?['body/result'][0]

Plumsail - Unlimited RegEx in Power Automate

You’ll most likely find the Office Scripts Regex solution useless if you’re working in an enterprise setup. Because you’re only allowed to do 400 Office Scripts calls from Power Automate daily.

Plumsail is the go-to solution for enterprise RegEx support in Power Automate. It’s not free but reasonably priced.

Setup a Plumsail Account

Fig. 2.1: Create a Plumsail Account

Navigate to https://auth.plumsail.com/account/register and create an account (Fig 2.1). If you’re not logged in afterward, please do so.

Fig. 2.2: Go to Actions

Go to ‘Actions’ by clicking the icon (Fig. 2.2). If you don’t see it, find it by clicking the 6 “white dots” in the upper left corner.

Fig. 2.3: Create API key

Click ‘API key’ in ‘Create a new API key’ (Fig. 2.3).

Fig. 2.4: Add new

Click ‘Add new’.

Fig. 2.5: Create a new API key

Give your key a name (1), Choose the ‘Basic’ type in the drop-down menu (2) and click ‘Create’ (Fig. 2.5).

Fig. 2.6: Copy API key

Copy the created API key (Fig. 2.6). We’re going to use it in Power Automate.

Use the Plumsail connector in Power Automate

This Post Has 4 Comments

  1. John G 17 Apr 2022 Reply

    Excellent post, Anders. Thanks for sharing.

    The limit on the number of Office Scripts an individual can run per day has been updated from 400 to 1,600. This is a pretty big jump; probably has something to do with the fact that people are using a lot of Office Scripts (now that I’ve been introduced to them by your post, I’m sure I will!).

    Ref: https://docs.microsoft.com/en-us/office/dev/scripts/testing/platform-limits

    1. Anders Jensen 17 Apr 2022 Reply

      Hey John. That’s so awesome. 1600 might still be too less when automating in the enterprise space, but it’s getting there If you want, you’re very welcome at my developer community: https://discord.gg/d8KQpTUvFD And thanks a lot for the nice words Kind regards, Anders

  2. Power automate start 4 Jun 2022 Reply

    Hi, i am currently using AI builder to extract the invoice amount from invoices. The workflow is setup in a way that the data will be stores in an Excel file. The issue is that i want to remove characters like: €,£ and text: “vat” how can indo this with regex can you please help me i am lost searched for hours on internet then i found this which could be the solution but i am not familiar with regex

Leave a Reply