Intro
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.
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
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.
(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.
To create a new Office Script, click ‘Automate’ (in the top menu), then ‘All Scripts’, and finally ‘New Scripts’ (Fig. 1.3).
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 [];
}
}
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.
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 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]+
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 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 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
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.
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.
Click ‘API key’ in ‘Create a new API key’ (Fig. 2.3).
Click ‘Add new’.
Give your key a name (1), Choose the ‘Basic’ type in the drop-down menu (2) and click ‘Create’ (Fig. 2.5).
Copy the created API key (Fig. 2.6). We’re going to use it in Power Automate.
