How to use RegEx in Power Automate

Intro

Extracting data out of emails, PDF invoices, Excel sheets, or other documents is a key 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:

1.1 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 rename the script as well 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.

1.2 Call the RegEx Office Script from Power Automate

Having created the Office Script in Part 1, the script can be called in 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 the place you want to extract data from. 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 dynamic content from the previous ‘Compose’. The RegEx pattern is filled in ‘pattern’ and likewise the flags.
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

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

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

2.1 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.

2.3 Use the Plumsail connector in Power Automate

Anders Jensen

RPA DEVELOPER, YOUTUBER & UIPATH MOST VALUED PROFESSIONAL 2021 Anders Jensen is the RPA Lead at Lessor A/S (Part of Paychex Inc) and an advanced certified UiPath RPA Instructor. Using his extensive experience in automating interfaces such as Windows, SAP, and browsers, Anders develops enterprise RPA solutions automating work for customers and colleagues one task at a time. In the evenings and weekends, Anders is passionate about teaching others RPA by making instructional videos on his YouTube channel.

Leave a Reply