Power Platform

If you want to publish data stored in your Office 365 environment to your WordPress site, wpDataTables combined with Power Automate makes it very easy to do. The data source in Office 365 can be anything accessible to Power Automate (e.g. SharePoint list, Dataverse, Excel, SQL Server).

Check out the example below, which is being generated from a SharePoint list and nicely demonstrates some of the default functionality of wpDataTables.

Employees

wpDataTables can get table data from many different data sources. One possible source is a URL that contains a JSON array. Power Automate makes it simple to publish JSON data to drive the table, using the action When a HTTP request is received.

There are two steps required to generating a table like the example above:

• Generate the source JSON data for the table.
• Create a page or a post in WordPress that contains a wpDataTable.

Generating the JSON for the Table

There are two ways to generate the source data for the table, both methods have pros and cons.

The First Method uses the trigger action When a HTTP request is received.

• Pros
  • Provides always up to date data.
  • Very easy to configure the flow to provide the data.
• Cons
  • Requires Power Automate Premium
  • Request limits in Power Platform could prevent execution on a busy site.

The Second Method uses a recurrence flow to copy data to the WordPress server using the SSH Connector.

• Pros
  • Does not require Power Automate Premium
  • Does not expose your HTTP action to the client.
  • Number of page views will not impact your Power Automate Request limits.
  • Faster performance for the client.
• Cons
  • Data is refreshed on a schedule, so isn’t live data.
  • Requires you to have SSH access to your WordPress host.

Generate data using the HTTP Method

Here is the flow that generates the HTTP Response, it consists of only a few steps:

As you can see, it consists of only a few actions.

• The HTTP Trigger, which must have the method set to GET.
• A Get Items action, to retrieve the data from SharePoint
• A Select action to reshape the data and remove any columns that are not required.
• Finally a response action to return the results to the browser that requested the record set. It is important to set the Content-Type to application/json.

The URL for the HTTP Trigger is generated after the flow is saved. Click this link to see the JSON data generated for the example table in this post. Once the URL has been generated, it can simply be added as the data source in the wpDataTables table.

Generate Data Using the SSH Method

The core of this flow is the same as the HTTP method, but the trigger and method of delivery are different. Here is the flow:

As you can see from the image above, the flow is set to run every 30 minutes, which means it will execute 48 times per day. The HTTP version of the same flow would execute every time someone visits the page.

The create file action at the end of the flow connects to the the WordPress server and saves the output of the select action to a static file called employees.json. There are two advantages to storing a static file like this:

• No need for the browser client to lookup another host.
• The data is static so the response is near instant.

So if your list was a restaurant menu for example, then a lag in the data would be fine. But if it was displaying stock levels of fast moving products, then you’d use the live method.

Create the Data Table

Creating the data table is super easy and you can easily follow the video demo from the wpDataTables website. wpDataTables has loads of different display options and makes it very easy to expose data from your Office 365 environment when combined with Power Automate.

Wouldn’t it be nice, if you could trigger a Power Automate Flow for a selected message directly from Outlook? Well, now you can! People have requested this functionality but it hasn’t been made available, so I decided to make it myself.

There are two steps to making this work:

1. Creation of a small Outlook Macro and assigning that Macro to a button on the toolbar.
2. Creation a Cloud Flow that is triggered on the action When a new email arrives (V3).

In practice, it is super simple to use. You simply click on a message, or messages in the main Outlook window and click a button on the ribbon. The macro will:

• Generate a JSON string of information about the message that you selected.
• Assign a particular subject to the email as the basis for the cloud flow trigger.

An example of the JSON produced by the Macro:

{
	"from": "[email protected]",
	"subject": "Power BI Invoice",
	"internetMessageID": "<[email protected]>",
	"folder": "Inbox"
}

As you can see, it contains four pieces of information:

• The sender of the selected message.
• The subject of the message.
• The Internet Message ID
• Which folder the message was in.

These values are used by the cloud flow to find the exact message that you selected when the flow runs using the $search parameter in the Get Emails action of the cloud flow.

Build the Outlook Macro

I have written the Macro for you, all you need to do is paste it in to ThisOutlookSession. You can access the VBA editor by pressing ALT-F11, or enabling the developer tab in Outlook.

The Macro code is below, don’t be put off by the amount of code, you’ll only ever need to edit one small part of it:

Option Explicit

'Duplicate this routine to create different trigger subjects.
Sub ExampleFlowStart()
    'Modify this line to create a different trigger subject.
    Dim triggerSubject As String: triggerSubject = "ExampleEmailTrigger12345"
    
    'Leave everything below this line as is.
    Dim myOlExp As Outlook.Explorer: Set myOlExp = Application.ActiveExplorer
    Dim myOlSel As Outlook.Selection: Set myOlSel = myOlExp.Selection
    Dim olItem As Outlook.MailItem
    Dim x As Integer
    For x = 1 To myOlSel.Count
        Set olItem = myOlSel.Item(x)
        If olItem.Class = 43 Then
            Call triggerFlowForSelectedMessages(olItem, triggerSubject)
        End If
    Next x
End Sub

'Main routine that sends the email to fire the Power Automate trigger
Sub triggerFlowForSelectedMessages(olItem As Outlook.MailItem, triggerSubject As String)

    Dim strFolder As String, JSON As String, sender As String
    Dim messageFolder As Outlook.Folder
    Dim oPA As Outlook.PropertyAccessor: Set oPA = olItem.PropertyAccessor
    
    Const PR_INTERNET_MESSAGE_ID As String = "http://schemas.microsoft.com/mapi/proptag/0x1035001F"

    'Find and convert the folder path
    Set messageFolder = olItem.Parent
    strFolder = Replace(Mid(messageFolder.FolderPath, (InStr(Mid(messageFolder.FolderPath, 3), "\") + 3)), "\", "/")
     
    'Definition of JSON that will be sent to trigger the flow
    JSON = "{" & vbLf & _
    "'from': '{{from}}'," & vbLf & _
    "'subject': '{{subject}}'," & vbLf & _
    "'internetMessageID': '{{internetMessageID}}'," & vbLf & _
    "'folder': '{{messageFolder}}'" & vbLf & _
    "}"
        
    'Replace JSON values
    JSON = Replace(JSON, "{{from}}", GetSenderSMTPAddress(olItem))
    JSON = Replace(JSON, "{{subject}}", olItem.Subject)
    JSON = Replace(JSON, "{{internetMessageID}}", oPA.GetProperty(PR_INTERNET_MESSAGE_ID))
    JSON = Replace(JSON, "{{messageFolder}}", messageFolder)
    JSON = Replace(JSON, "'", Chr(34))
    'Debug.Print (JSON)

     
    'Send the message that Triggers the flow
    Dim objMsg As MailItem
    Set objMsg = Application.CreateItem(olMailItem)
        
    With objMsg
        .To = GetUserEmailAddress()
        .Subject = triggerSubject
        .BodyFormat = olFormatPlain
        .Body = JSON
        .Send
    End With
     
End Sub

'Function to get the sender address of an email. Required for exchange accounts.
Function GetSenderSMTPAddress(mail As Outlook.MailItem) As String
    Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
    If mail Is Nothing Then
        GetSenderSMTPAddress = vbNullString
        Exit Function
    End If
    If mail.SenderEmailType = "EX" Then
        Dim sender As Outlook.AddressEntry
        Set sender = mail.sender
        If Not sender Is Nothing Then
            'Now we have an AddressEntry representing the Sender
            If sender.AddressEntryUserType = _
                    Outlook.OlAddressEntryUserType.olExchangeUserAddressEntry Or _
                    sender.AddressEntryUserType = _
                    Outlook.OlAddressEntryUserType.olExchangeRemoteUserAddressEntry Then
                'Use the ExchangeUser object PrimarySMTPAddress
                Dim exchUser As Outlook.ExchangeUser
                Set exchUser = sender.GetExchangeUser()
                If Not exchUser Is Nothing Then
                     GetSenderSMTPAddress = exchUser.PrimarySmtpAddress
                Else
                    GetSenderSMTPAddress = vbNullString
                End If
            Else
                 GetSenderSMTPAddress = sender.PropertyAccessor.GetProperty(PR_SMTP_ADDRESS)
            End If
        Else
            GetSenderSMTPAddress = vbNullString
        End If
    Else
        GetSenderSMTPAddress = mail.SenderEmailAddress
    End If
End Function

'Function to get the Primary email address of the current user
Function GetUserEmailAddress()
    Dim outApp As Outlook.Application, outSession As Object, curr
    Set outApp = CreateObject("Outlook.Application")
    Set outSession = outApp.Session.CurrentUser
    Set outApp = Nothing
    GetUserEmailAddress = outSession.AddressEntry.GetExchangeUser().PrimarySmtpAddress
End Function

Lines 3 – 19 are the only part of the code that you need to be concerned about. In particular line 6 defines the value of the subject that is going to be used to trigger the flow. You can take this whole section of code, duplicate it and rename the subroutine to setup trigger buttons with different subjects (to produce different results). For example, you could duplicate it like this:

Sub StoreSupplierInvoices()
    'Modify this line to create a different trigger subject.
    Dim triggerSubject As String: triggerSubject = "TriggerStoreSupplierInvoices"
    
    'Leave everything below this line as is.
    Dim myOlExp As Outlook.Explorer: Set myOlExp = Application.ActiveExplorer
    Dim myOlSel As Outlook.Selection: Set myOlSel = myOlExp.Selection
    Dim olItem As Outlook.MailItem
    Dim x As Integer
    For x = 1 To myOlSel.Count
        Set olItem = myOlSel.Item(x)
        If olItem.Class = 43 Then
            Call triggerFlowForSelectedMessages(olItem, triggerSubject)
        End If
    Next x
End Sub

The key is to use a subject line that you are unlikely to receive in your normal stream of emails. Next you need to assign the Macro to a button on your ribbon. On my ribbon, I have a button that looks like this:

If that button is clicked, you will receive a plain text email, back into your own Inbox that looks like this:

That is it for the Outlook part, when we have built the flow, it will be triggered by the new incoming email with the identifiable subject and begin to process it. During the processing it will also delete the trigger email.

Build the Cloud flow to process the Trigger

The cloud flow is really very simple to setup, I’ve also put all of the required actions into a scope that you can easily copy. Here is a screenshot, the only part you need to modify is the Subject filter in the first action:

The scope itself can be simply copied from the code below, directly into your flow:

{
	"id": "793da215-6ea6-4ef0-b570-bc9f-ca19188a",
	"brandColor": "#8C3900",
	"connectionReferences": {
		"shared_sharepointonline": {
			"connection": {
				"id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
			}
		},
		"shared_office365": {
			"connection": {
				"id": "/providers/Microsoft.PowerApps/apis/shared_office365/connections/shared-office365-9474202e-bf2b-4861-96d8-aa8f94183a13"
			}
		}
	},
	"connectorDisplayName": "Control",
	"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
	"isTrigger": false,
	"operationName": "Process_Email",
	"operationDefinition": {
		"type": "Scope",
		"actions": {
			"Get_emails_(V3)": {
				"type": "OpenApiConnection",
				"inputs": {
					"host": {
						"connectionName": "shared_office365",
						"operationId": "GetEmailsV3",
						"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365"
					},
					"parameters": {
						"folderPath": "@json(triggerOutputs()?['body/body'])['folder']",
						"from": "@json(triggerOutputs()?['body/body'])['from']",
						"fetchOnlyUnread": false,
						"includeAttachments": true,
						"searchQuery": "from:@{json(triggerOutputs()?['body/body'])['from']} AND subject:@{json(triggerOutputs()?['body/body'])['subject']}",
						"top": 25,
						"importance": "Any",
						"fetchOnlyWithAttachment": false
					},
					"authentication": "@parameters('$authentication')"
				},
				"runAfter": {},
				"description": "Find closely matching emails"
			},
			"Filter_array": {
				"type": "Query",
				"inputs": {
					"from": "@outputs('Get_emails_(V3)')?['body/value']",
					"where": "@equals(item()?['internetMessageId'], json(triggerOutputs()?['body/body'])['internetMessageID'])"
				},
				"runAfter": {
					"Get_emails_(V3)": ["Succeeded"]
				},
				"description": "Match the email array against the Internet Message ID"
			},
			"Parse_Email_JSON": {
				"type": "ParseJson",
				"inputs": {
					"content": "@first(body('Filter_array'))",
					"schema": {
						"type": "object",
						"properties": {
							"id": {
								"type": "string"
							},
							"receivedDateTime": {
								"type": "string"
							},
							"hasAttachments": {
								"type": "boolean"
							},
							"internetMessageId": {
								"type": "string"
							},
							"subject": {
								"type": "string"
							},
							"bodyPreview": {
								"type": "string"
							},
							"importance": {
								"type": "string"
							},
							"conversationId": {
								"type": "string"
							},
							"isRead": {
								"type": "boolean"
							},
							"isHtml": {
								"type": "boolean"
							},
							"from": {
								"type": "string"
							},
							"toRecipients": {
								"type": "string"
							},
							"attachments": {
								"type": "array",
								"items": {
									"type": "object",
									"properties": {
										"@@odata.type": {
											"type": "string"
										},
										"id": {
											"type": "string"
										},
										"lastModifiedDateTime": {
											"type": "string"
										},
										"name": {
											"type": "string"
										},
										"contentType": {
											"type": "string"
										},
										"size": {
											"type": "integer"
										},
										"isInline": {
											"type": "boolean"
										},
										"contentId": {
											"type": "string"
										},
										"contentBytes": {
											"type": "string"
										}
									},
									"required": ["@@odata.type", "id", "lastModifiedDateTime", "name", "contentType", "size", "isInline", "contentId", "contentBytes"]
								}
							},
							"body": {
								"type": "string"
							}
						}
					}
				},
				"runAfter": {
					"Filter_array": ["Succeeded"]
				},
				"description": "Parse the Output of the Filter"
			},
			"Delete_email_(V2)": {
				"type": "OpenApiConnection",
				"inputs": {
					"host": {
						"connectionName": "shared_office365",
						"operationId": "DeleteEmail_V2",
						"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365"
					},
					"parameters": {
						"messageId": "@triggerOutputs()?['body/id']"
					},
					"authentication": "@parameters('$authentication')"
				},
				"runAfter": {
					"Parse_Email_JSON": ["Succeeded"]
				},
				"description": "Delete the email that triggered the flow"
			}
		},
		"runAfter": {},
		"description": "Finds the message that was selected and Parses"
	}
}

To copy this into your Flow, in the Flow designer, simply copy the code above and then paste into your flow like this:

The dynamic content of Parse Email Action will now contain everything you need to manipulate the email. This is the process the flow takes:

1. The flow is triggered on the arrival of a new email, which is generated by the Outlook Macro.
2. The trigger has the unique subject filter applied to ensure it only runs in response to the Macro.
3. The mailbox is then searched for matching emails based on the folder, the sender and the subject.
4. These results are then filtered further to match emails with a matching Internet Message ID.
5. Finally the result is put into a Parse JSON step to make the dynamic content available.

In my example I have a button labelled “Store Supplier Invoices”. This takes the attachments from a selected email and then puts them into a document library on SharePoint. When new items are added to the Supplier Invoice library, a new flow begins.

Let me know how you get on with the flow and if it is useful to you.

Office 365 has good form and survey functionality provided by Microsoft Forms. This post is details a completely different method to collect form data into Microsoft Power Automate, using a jQuery library provided by SurveyJS, which provides greatly enhanced functionality.

So why the need for an alternative method? There are a number of features, which are considered basic form based functionality missing from Microsoft Forms, such as:

• Ability to upload a file with the form submission (10k Votes)
• Basic functionality to be able to manipulate fonts etc (4K Votes)
• Support for additional controls, checkboxes etc (3K Votes)
• Conditional Logic
• Timer for questions to create a quiz
• Regular Expression Support for field validation

And many more. I suspect that Microsoft have to draw a fine line between functionality, ease of use and treading on the toes of business partners.

The method described in this post, satisfies many of the wishes on Microsoft UserVoice and many more besides. Microsoft Forms is incredibly easy to use, but that ease of use does seem to come at the expense of functionality. Check out the video demo where I build this flow from scratch:

Building better form functionality for Office 365 with Power Automate

Before getting into this solution in detail, It is worth pointing out that to build this solution you must have a Power Automate Premium license. That, and a little patience and you will have amazing form functionality in no time at all. Essentially this solution consists of two Power Automate Flows:

• One that delivers the form or survey to the browser.
• One that collects the data from the form submission.

This solution leverages the Power Automate trigger “When a HTTP Request is received” to serve a form. The form is really easy to build using the form creator on SurveyJS.

Click here to see an example form I built in no time. Note, that form is not being delivered via this blog, but served directly from Power Automate. It is worth noting, that although it is being served from Power Automate, the survey itself could be hosted anywhere, it is the data collection that has to be directed to Power Automate.

Using the form creator on SurveyJS takes care of the majority of the work for you. You simply need to put it together in a HTML page. The source of my HTML form looks like this:

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Power Platform Survey</title>
	<style>
	</style>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
	<script src="https://unpkg.com/[email protected]/survey.jquery.min.js"></script>
	<link href="https://unpkg.com/[email protected]/modern.css" rel="stylesheet" type="text/css">
</head>
<body>
	<div id="surveyContainer"></div>
	<script>
	   $(document).ready(function() {
        Survey.StylesManager.applyTheme("modern");
        var surveyJSON = {title:"TachyTelic Power Platform Survey Demo"...}

        function sendDataToServer(survey) {
            //send Ajax request to your web server.
            console.log("The results are:" + JSON.stringify(survey.data));
            $.ajax({
            url: 'https://prod-137.westeurope.logic.azure.com:443/workflows/...',
            type: 'post',
            dataType: 'json',
            contentType: 'application/json',
            success: function (data) { console.log(data.msg);},
            data: JSON.stringify(survey.data)
            });
        }

        var survey = new Survey.Model(surveyJSON);
        $("#surveyContainer").Survey({
            model: survey,
            onComplete: sendDataToServer
        });
	   });
	</script>
</body>
</html>

As you can see, there is very little code. The form is simply added to a div called surveyContainer. I have highlighted and truncated two sections above:

• Line 17 : This JSON is the entire form definition (The full text is pretty printed below).
• Lines 22-30 : This is where the form results are submitted to the flow that collects and processes the results.

Both of these things deserve a bit more consideration…

The form definition is just a JSON string

Before talking about this point any further, let’s view the form JSON in a neater format:

{
	title: "TachyTelic Power Platform Survey Demo",
	logo: "https://www.tachytelic.net/wp-content/uploads/Logo-TTNet-blue-small.png",
	pages: [{
		name: "PowerPlatformSurvey",
		elements: [{
			type: "text",
			name: "emailAddress",
			title: "Your email address",
			isRequired: true,
			validators: [{
				type: "email"
			}],
			inputType: "email",
			autoComplete: "email",
			placeHolder: "Please enter a valid email address"
		}, {
			type: "text",
			name: "twitterID",
			title: "If you have one, what is your Twitter handle"
		}, {
			type: "text",
			name: "youTubeChannel",
			title: "If you have one, what is your YouTube Channel",
			requiredErrorText: "Please Enter a Valid URL",
			validators: [{
				type: "regex",
				text: "Please Enter a Valid URL",
				regex: "^(http:\\/\\/www\\.|https:\\/\\/www\\.|http:\\/\\/|https:\\/\\/)?[a-z0-9]+([\\-\\.]{1}[a-z0-9]+)*\\.[a-z]{2,5}(:[0-9]{1,5})?(\\/.*)?$"
			}]
		}, {
			type: "rating",
			name: "powerAutomateEase",
			title: "Overall, how easy do you find Power Platform to work with.",
			description: "1 = Very Difficult, 10 = Very Easy",
			rateMax: 10
		}, {
			type: "imagepicker",
			name: "powerPlatformFavourite",
			title: "What is your favourite Power Platform Product",
			choices: [{
				value: "PowerAutomate",
				text: "Power Automate",
				imageLink: "https://www.tachytelic.net/wp-content/uploads/powerautomate.png"
			}, {
				value: "PowerApps",
				text: "Power Apps",
				imageLink: "https://www.tachytelic.net/wp-content/uploads/powerapps.png"
			}, {
				value: "PowerBI",
				text: "Power BI",
				imageLink: "https://www.tachytelic.net/wp-content/uploads/powerbi.png"
			}, {
				value: "PowerVA",
				text: "Power Virtual Agents",
				imageLink: "https://www.tachytelic.net/wp-content/uploads/powervirtualagents.png"
			}],
			choicesByUrl: {
				titleName: "Power "
			},
			showLabel: true
		}]
	}]
}

If you take a look at line 38 for example, you can see the definition for the picker. Because this is being delivered via Power Automate, you can generate choices to be displayed on the form dynamically from a data source sitting in your Office 365 Environment. Which is another highly requested feature for Microsoft Forms! This would be simple to setup and you could create some very cool functionality with this.

Submission of the data to Power Automate

Line 22 of the code listing is where the code is submitted to second Power Automate Flow, designed to receive the data from the form. But that small section of code shows you how simple it is to submit data from a browser based client capable of running jQuery to Power Automate.

Build the Flows

As stated at the beginning of this post, this solution consists of two flows. One which delivers the form to the client, and another which receives data from the client.

The second flow, which receives the data must be hosted on Power Automate. But the first one can sit anywhere you like. But if you want to change the form contents dynamically as described above, then run it from Power Automate.

First Flow – Deliver the Form to the Browser

In its not dynamic form, this flow is very simple. In my example just a few steps:

The Important bits to note:

• Ensure the method in the first action is set to GET.
• Ensure the headers in the response action are configured to provide Content-Type set to text/html.

The second action in the flow can either be “Get file content using path” as I have done, or a simple compose action. The file that I am retrieving is the HTML source from above. This is probably slightly slower, but makes editing the HTML easier as you can use a proper editor, such as visual studio code.

Second Flow – Receive the results from the client

The second flow can be as simple or as complicated as you choose to make it. The important part is making sure you receive the data. In this simple example, the data is received and added to a SharePoint list:

My flow looks like this:

The important part to note in this flow, is the method of the first action, must be set to post. The video demo at the top of the page makes this step much easier to understand.

Conclusion

Microsoft Forms is a great, easy to use product. If you have more sophisticated requirements, you can get the functionality you need with very little effort by combining Power Automate and SurveyJS. It’s got some incredibly functionality and it is very easy to use.

I recently wrote a blog post that showed how to sort an array in Power Automate using Office scripts. That script works well for simple arrays, but it is unable to sort an array of objects.

Using the following array as an example:

[
  {
    "Ranking": 1,
    "Player": "Novak Djokovic",
    "Age": 33,
    "Points": 11873
  },
  {
    "Ranking": 2,
    "Player": "Daniil Medvedev",
    "Age": 25,
    "Points": 9850
  },
  {
    "Ranking": 3,
    "Player": "Rafael Nadal",
    "Age": 34,
    "Points": 9490
  },
  {
    "Ranking": 4,
    "Player": "Dominic Thiem",
    "Age": 27,
    "Points": 8615
  },
  {
    "Ranking": 5,
    "Player": "Stefanos Tsitsipas",
    "Age": 22,
    "Points": 7860
  }
]

There are no in-built actions in Power Automate to sort simple arrays, or arrays of objects. So I wrote a small Office script that does this. It accepts three parameters:

• The array to be sorted (Passed as a string).
• The property you want to sort on. (Age, Points, Player etc).
• The direction to sort in (Either ASC or DSC).

And the output is a nicely sorted array. Screenshot of the flow:

Note that the array, which was defined in the first step, has to be converted to a string before being passed to the script. I did this with the code:

string(outputs('playerRankings'))

After the script has executed you can easily retrieve the results of the sort from the result property of the Excel action.

Here is the code for the Office Script which you can copy and paste into your environment:

function main(workbook: ExcelScript.Workbook, strArray: string, property: string, sortOrder: string): string {
  let array = JSON.parse(strArray);
  if (sortOrder === 'ASC') {
    return array.sort((n1, n2) => {
      if (n1[property] > n2[property]) {
        return 1;
      }

      if (n1[property] < n2[property]) {
        return -1;
      }

      return 0;
    });
  } else {
    return array.sort((n1, n2) => {
      if (n1[property] > n2[property]) {
        return -1;
      }

      if (n1[property] < n2[property]) {
        return 1;
      }

      return 0;
    });
  }
}

Alternatively, you can download the Office Script from here and place it into your Documents\Office Scripts folder in OneDrive.

Conclusion

Although it would be great if this functionality was a native action in Power Automate – this post demonstrates how you can sort an array of objects in Power Automate using Office Scripts.

Very short post, but hopefully adds useful, much needed functionality to your flows. Let me know how you get on in the comments. Office Scripts have all kinds of uses, check out my other posts to see what I have used them for:

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

Office scripts used in this way are a simple and low cost method of getting access to a JavaScript engine, to fill in the gaps in standard Power Automate Functionality. Just be mindful that there is a limit of 200 executions per day on Office Scripts.

If you haven’t used Office Scripts before, check out the Microsoft getting started guide.

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