• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

Power Platform

Secure the HTTP Request Trigger in Microsoft Power Automate

September 21, 2020 by Paulie 1 Comment

Flows created with the “When a HTTP request is received” trigger can be executed by anyone that knows the URL. This post covers details how to secure the HTTP trigger by using a trigger condition.

I have created a simple two step flow that sends me an email when the flow is triggered:

Image of a simple flow in Power Automate that sends an email after the a HTTP request is received,

Using a condition within the flow to compare a value passed in the HTTP works, but will consume a flow run.

Secure the Trigger with a condition to evaluate if the flow should execute

A better method is to use the Trigger Conditions functionality to test if a flow should execute. Trigger conditions must evaluate to true or false, there is a good introduction to trigger conditions here. I will demonstrate the basic premise with an example.

First, access the trigger settings by clicking on the ellipses of the HTTP Trigger:

Image showing how to access the settings of the HTTP Request trigger in Power Automate

Set a condition for the trigger, if this condition does not evaluate to true, the flow will not run:

Image showing Trigger Condition checking the value of a HTTP header to decide if the flow should execute.

I am passing the header “runKey” to the HTTP Request and testing to see if it matches a random string. Here is the code:

@equals(triggerOutputs()['headers']['runKey'],'FSgWPsAEBDP6epQZ')

The advantages of trigger conditions are:

  • It does not execute at all if the condition is not met.
  • The flow executes faster as there is no condition within the flow.
  • Your run history is cleaner (it only shows if the condition was met).
  • The flow is less complicated.

Build the required trigger expression

Instead of writing the trigger condition manually, let Power Automate build it for you. Create a compose action in your flow that evaluates to true or false and copy the code into the trigger condition.

Image showing a compose action being used to generate code for a trigger condition to secure a HTTP request in Power Automate.

Testing the security with PowerShell

I tested execution of the HTTP trigger in Powershell with the following code:

$flowURI = "https://prod-31.westeurope.logic.azure.com:443/workflows/..."
$messageSubject = "Testing HTTP Security Trigger"
$messageBody = "Execution test from Powershell"
$params = @{"messageSubject"="$messageSubject";"messageBody"="$messageBody"}
$headers = @{'runKey' = 'FSgWPsAEBDP6epQZ'}
Invoke-WebRequest -Uri $flowURI -Method POST -Headers $headers -ContentType "application/json" -Body ($params|ConvertTo-Json)

In my run history I can see all of the data that PowerShell transferred:

{
  "headers": {
    "Expect": "100-continue",
    "Host": "prod-31.westeurope.logic.azure.com",
    "User-Agent": "Mozilla/5.0,(Windows NT; Windows NT 10.0; en-GB),WindowsPowerShell/5.1.18362.752",
    "runKey": "FSgWPsAEBDP6epQZ",
    "Content-Length": "115",
    "Content-Type": "application/json"
  },
  "body": {
    "messageSubject": "Testing HTTP Security Trigger",
    "messageBody": "Execution test from Powershell"
  }
}

I hope this helps, if you have any clever ideas to add to this method, please let me know!

Filed Under: Power Platform Tagged With: Power Automate

How to send awesome emails with Power Automate and SendGrid

August 19, 2020 by Paulie Leave a Comment

There are many ways to send emails from Power Automate. This post will demonstrate how to build a flow to Send email from Power Automate with Sendgrid using dynamic templates.

There are many advantages to using SendGrid:

  • Ability to send from any alias in your domain.
  • Dynamic Templates are easy to maintain and build with SendGrid editor.
  • Simple to track email delivery.
  • Great solution if you are sending a high volume of emails.
  • SendGrid has comprehensive testing tools to ensure emails are deliverable.

The first part of the process is to create a dynamic template in SendGrid. I implemented a flow to send customer balance statements and the resulting email looked like this:

Image of a sample email template in SendGrid
Right-click save as to download the SendGrid Dynamic Template.

SendGrid will replace the values between the double curly braces with values supplied from Power Automate.

Once you have created your template SendGrid will assign a template ID. Copy the Template ID for the next step in Power Automate:

Image of Dynamic Template in SendGrid which will be used to send an email from Power Automate

Next, generate a SendGrid API Key for access from Power Automate and then you can build your flow. Here is my sample flow (click to zoom):

Image of Flow in Power Automate

Here is my JSON code:

{
  "from": {
    "email": "[email protected]",
    "name": "Tachytelic Accounts"
  },
  "personalizations": [
    {
      "to": [
        {
          "email": "[email protected]"
        }
      ],
      "bcc": [
        {
          "email": "[email protected]"
        }
      ],
      "subject": "Statement from Tachytelic.net",
      "dynamic_template_data": {
        "Current": "5,890.08",
        "Days0_30": "5,118.00",
        "Days30_60": "0.00",
        "Days60_90": "0.00",
        "Days90plus": "12,384.00",
        "CustomerName": "Sample Customer",
        "Total": "23,392.09"
      }
    }
  ],
  "template_id": "d-420f62e9c0074c899610f056b0141d11"
}

In Production flows like this one are used to:

  • Send large batches of customer statements.
  • Send PDF Invoices which are generated from a word template
  • Notify customers of delivery of goods.

The SendGrid template tools make it easy to create consistent branded emails from Power Automate. You can get a free account on their website to try it out.

Filed Under: Power Platform Tagged With: Power Automate

Address Lookup in PowerApps with Power Automate and loqate

May 14, 2020 by Paulie 10 Comments

This post covers using Loqate API to auto-fill address details in PowerApps. Manual entry of key customer and supplier details is error prone and time consuming.

The Loqate API is really simple, so you can develop a solution in a couple of minutes.

Watch the video for a demonstration of how I was able to develop a Power App that enables address search:

Address Lookup in PowerApps with Loqate

The app in the video uses two flows to get address information from Loqate.

The first flow uses the Interactive Find API method to get a list of possible address matches. Each result contains a unique ID which is used to drive the second flow.

The second flow uses the Retrieve API to get the details of the selected address from the first result set.

First Flow: Interactive Find

Image showing details of a Flow in Power Automate used to Interact with the Loqate API

The flow is quite self explanatory. There are two inputs

  • The country code.
  • The search query.

These variables are inserted into the the query string and submitted to loqate using a HTTP action.

The interactive find returns a JSON array, which looks like this:

[
  {
    "Id": "GB|RM|A|53530442",
    "Type": "Address",
    "Text": "Microsoft, 2 Kingdom Street",
    "Highlight": "0-9;0-6",
    "Description": "London, W2 6BD"
  },
  {
    "Id": "GB|RM|A|55068353",
    "Type": "Address",
    "Text": "Microsoft Reactor, 70 Wilson Street",
    "Highlight": ";0-6",
    "Description": "London, EC2A 2DB"
  }
]

The Id column, which is highlighted above provides the input for the next flow.

The addresses are loaded into a collection which is used as the data source for the gallery of addresses:

Image showing the creation of a collection in PowerApps that stores address data from loqate
ClearCollect(Addresses, TestAddressLookup.Run(txtAddressLookup.Text, dropCountry.Selected.Value))

Second Flow: loqate reteive

The second flow uses the Unique ID retrieved in the first flow to get all the details of the selected address:

Like the first flow, the results are stored in a collection, with the following code:

ClearCollect(AddressDetails, loqateRetrieve.Run(ThisItem.Id));
UpdateContext({searchVisible: false})
Image showing the execution of a flow in PowerApps which collects address details from Loqate and puts them into a colleciton.

I used a context variable called “searchVisible” to set the visibility of the address lookup controls.

Back in the actual form I just set the default property of the dataCardValue text controls to:

First(AddressDetails).Company
First(AddressDetails).Line1
First(AddressDetails).Line2
etc

Download the flows to import into your environment here:

Flow 1 – Loqate Interactive Find
Flow 2 – Loqate Retieve

And that is how easy it is to add Address Lookup functionality in PowerApps! Any questions, just ask.

Filed Under: Power Platform Tagged With: Power Automate, PowerApps

Combine SharePoint API & Flow Parallel branches for Performance

April 19, 2020 by Paulie 5 Comments

This post covers how to configure a Power Automate Flow to get items from a SharePoint list at maximum speed. Super useful if you have an interactive process and you do not want to keep users waiting.

This post is part of a three part series of blog posts, before proceeding please read this post which describes the objectives, pros and cons of each method. It also provides links to the exported flows and sample data which I used to build the flows:

The many ways to retrieve more than 5,000 items from a SharePoint list using Power Automate

If you have not used a parallel branch before, this blog post is worth reading.

Before getting into the technicalities, have a look at the complete flow. If you have already built the more SharePoint API version detailed in this post, it will make sense.

Image of an entire Power Automate Flow that uses Parallel Branches to get records from a SharePoint list

General Premise

This flow retrieves the SharePoint items using the same method as the flow provided in this post. But the work of collecting data from SharePoint is split over four parallel branches, which execute at the same time. Instead of waiting for a single batch of 5,000 to be collected before proceeding to get the next batch.

Changes from the SharePoint API Flow

Several changes were made to the SharePoint API flow:

Variable Declarations

There are some extra values declared and defined at the start of the flow which control how the four branches choose which section of data to return:

Get the Lowest ID from the SharePoint list using the REST API

The lowest ID from the list is retrieved using the HTTP request to SharePoint action and uses the following code:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID asc
Image showing Sharepoint API being used to find the lowest ID of a row in a SharePoint list

Get the Highest ID from SharePoint list using the REST API

Same as the above flow step, but repeated to get the highest ID, uses the following code:

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID&$filter=ID gt 0&$top=1&$orderby=ID desc

Gets the Total Item count from the SharePoint List using the REST API

The total item count of the list is returned by using the ItemCount method of the SharePoint API:

_api/Web/Lists/GetByTitle('5000 Item Test')/ItemCount
Image of the SharePoint API being used to get the total item count of a list.

Declare the control variables for each parallel branch

Because there are four parallel threads, all of the variables which operate within each branch are declared four times.

In the previous example, there was a single integer, array and boolean variable to control the do until loop that was retrieving SharePoint data. Now those variables need to be declared for each parallel branch:

Image showing four arrays being defined in Power Automate, ready for parallel execution.

The main control block for the Parallel Branches

In the control block for the parallel branches, a compose action is used to divide the total item count by four to determine the amount of data each branch should retrieve:

div(variables('itemCount'),4)

The control mechanism for the do until loops was modified to be more efficient, by changing the step which checks if there were no results from SharePoint

if(
	empty(body('HTTP_to_SharePoint_to_get_First_Quarter_of_Results').value),true,
	if (
		less(length(body('HTTP_to_SharePoint_to_get_First_Quarter_of_Results').value), 5000),true,false
	)
	
)

This removes an iteration of the loop, by checking if either of the following conditions are true:

  • The result set from SharePoint was empty
  • The number of results returned from SharePoint was less than 5000

SharePoint API Query for each branch

The SharePoint API query is different for each parallel execution of the data collection.

Each branch collects data according to the batch size defined by dividing the total item count by four and begins at the lowest ID in branch one. ?Next, branch two begins at the end of branch one and so on, so that all items are collected.

Branch 1

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{variables('intID')} and ID le @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 1))}&$top=5000"

Branch 2

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID gt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 1))}  and ID gt @{variables('intID2')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}&$top=5000

Branch 3

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID ge @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}  and ID gt @{variables('intID3')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 3))}&$top=5000

Branch 4

_api/Web/Lists/GetByTitle('5000 Item Test')/Items?$select=ID,EmpNo,Title,Last,Furloughed,Telephone,Email,Brand&$filter=ID ge @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 2))}  and ID gt @{variables('intID3')} and ID lt @{add(body('getLowestID')?['value'][0]['ID'], mul(outputs('DivideTotalByFour'), 3))}&$top=5000

Compile all results and send back to PowerApps

The response step simply uses a Union action to bring all the SharePoint data back in a single step:

union(variables('arrItems'),variables('arrItems2'),variables('arrItems3'),variables('arrItems4'))

Summary

Using Parallel branches to fetch SharePoint data can provide a huge speed benefit over a standard do until loop at the expense of creating a more complicated flow but it can be worth it to provide the best possible user experience.

It may be easier to just download the exported flow and modify to your requirements than build it from scratch.

You can download the flow and sample data from this post.

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

Solutions to the SharePoint 5000 item limit using Power Automate

April 19, 2020 by Paulie 16 Comments

If you need to get more than 5000 items from a SharePoint list, there are a number of different approaches. I’ve written up three different ways to solve the problem, which return exactly the same result, but have very different execution times. To get an overall understanding of this problem, check out the YouTube video I produced:

My requirement is to return 17,000 SharePoint list items to a PowerApp. I used Power Automate to get the data but found the standard “Get Items” action to be too slow. A good user experience is essential for application acceptance.

Eventually, I was able to reduce the amount of time that the Flow takes to retrieve the items from 2 minutes down to 8 seconds! That’s 13 times faster!

Since I originally wrote this post, I have figured out a method that is even easier and faster! Check it out here.

Links to my sample data and all the flows created are at the end of the post.

My example data looks like this:

Image showing data from SharePoint list which is going to be used as a DataSource for a flow

It’s 7 columns wide, 17,000 rows of data. Here is how long it took Power Automate to execute the flow with execute each method:

Method Time to Execute
Standard “Get Items” 111 Seconds
Standard “Get Items” with Select Action 67 Seconds
Retreiving same data with SharePoint API 31 Seconds
Retreiving same data with SharePoint API and two parralel queries 19 Seconds
Retreiving same data with SharePoint API and four parralel queries 13 Seconds
Same as above with optimised flow 8 Seconds

The table shows the results from six variations on three different themes. The fundamental principal behind each method is a “Do until” loop which collects the entire contents of a SharePoint list into an array. Each method has some pro’s and cons.

Which method to use?

The right method to use, depends on your circumstances. Here are some suggestions on what method may work best for you:

  • If your flow is not time sensitive, just use the standard “Get Items” action with a “Do Until” Loop in Power Automate. The flow is simple and easy to build.
    Get more than 5000 items from SharePoint with Power Automate
  • If you are already using the “Get Items” method but need it to be a bit faster, enhance the speed of it by adding the “Select” action.
    Get more than 5000 items from SharePoint with Power Automate
  • If the performance gained from the “Select” action still does not meet your requirements, switch to using the SharePoint API. It is almost the same as using “Get Items”, but drastically faster.
    Use the REST API in Flow to get over 5000 items from SharePoint
  • Finally, using parallel execution to retrieve chunks of SharePoint list data simultaneously increases speed further. But it does make the flow more complicated.
    Combine SharePoint API & Flow Parallel branches for Performance

Why is the SharePoint Get Items action so slow?

The “Get Items” from SharePoint in Power Automate is very slow. The primary reason is the size of the payload that it returns from SharePoint. In addition to returning the columns defined in your SharePoint view, it also returns many other columns and metadata.

A single row of the sample data above results in this from SharePoint:

{
	"@odata.etag": "\"1\"",
	"ItemInternalId": "1",
	"ID": 1,
	"Title": "Terry",
	"EmpNo": 1.0,
	"Last": "Carter",
	"Furloughed": 1.0,
	"Telephone": "(462) 723-7695",
	"Email": "[email protected]",
	"Brand": "PowerBI",
	"{Identifier}": "Lists%252f5000%2bItem%2bTest1%252f1_.000",
	"{IsFolder}": false,
	"{Thumbnail}": {
		"Large": null,
		"Medium": null,
		"Small": null
	},
	"{Link}": "https://accendo1.sharepoint.com/sites/DocumentManagementTest/.....",
	"{Name}": "Terry",
	"{FilenameWithExtension}": "Terry",
	"{Path}": "Lists/5000 Item Test1/",
	"{FullPath}": "Lists/5000 Item Test1/1_.000",
	"{HasAttachments}": false,
	"{VersionNumber}": "1.0"
}

That’s a huge amount of extra fields, which results in a massive payload. 5,000 rows of my sample data created a JSON response which was 3,545 KB.

The same 5,000 rows of data when passed through the “Select” action produces a JSON response which looks like this:

{
	"Id": 1,
	"Title": "Terry",
	"EmpNo": 1.0,
	"Last": "Carter",
	"Furloughed": 1.0,
	"Telephone": "(462) 723-7695",
	"Email": "[email protected]",
	"Brand": "PowerBI",
	"ID": 1
}

For the same 5,000 records, the JSON response in flow was only 772 KB – A massive reduction. So passing the response from Get Items through the select action can make a big difference to how quickly your flow will run, and it is super easy to do.

Image of output of Sharepoint Get Items action in Power Automate being reduced down with the select action.

Use the Sharepoint API for maximum speed

Although the “Select” action in Power Automate described above improves the speed of a flow within a do…until loop, the “Get Items” action is still retrieving a lot of unwanted data from SharePoint.

Using the SharePoint API allows you to get exactly the data you want without retrieving any unwanted columns or metadata.

Fortunately, this is very easy to do using the SharePoint API using the “Send an HTTP request to SharePoint” action. Compare these two flow actions:

Standard “Get-Items” action.
The same request using the SharePoint API

These two actions, essentially perform the same operation, but the second one returns only the requested columns. Here is the difference in execution time:

Image displaying the difference in execution time between Power Automate Get Items from SharePoint Action and the Same request to the SharePoint API.

Use Parallel Branching to get batches of 5000 items simultaneously.

Using the SharePoint API to get list data into Power Automate makes a huge difference to the speed of the operation. But it still gets the data in batches of 5000 blocks. With my 17,000 row sample the do-until loop has to execute four times to collect all of the data, all of which takes valuable time.

By using the parallel branch actions in Microsoft Power Automate we can engineer a solution which works much faster.

I tried collecting SharePoint data with two and four parallel branches. With four, I was able to get all 17,000 items in 8 seconds. The downside is that the flow is more complicated.

Conclusion

The standard Get Items action in Power Automate is fine for basic operations, but if you need more control, or speed, then there are better options.

I’m happy with the performance improvements I managed to create. Retrieving data from SharePoint is key to many applications and SharePoint handles the simultaneous requests well.

Example Data and Flows

You can download both my sample data and the flows discussed in these posts from here:

  • Test Data to upload to SharePoint list
  • v1 – Using the Get Items Action
  • v2 – Using the Get Items Action with Select action
  • v3 – Using SharePoint API to retrieve items
  • v4 – Using SharePoint API and Two Parallel Execution Branches
  • v6 – Using SharePoint API and Four Parallel Execution Branches

Adapting the flow to return SharePoint data to a jQuery grid

In the YouTube video above I also provided a demo of a jQuery grid that drew data from the SharePoint list using Power Automate, you can view that demo here:

Demo of jQuery based grid retrieving SharePoint data via Flow

You can easily copy the source code from that page, the flow has been left unsecured so that you can try it out.

Filed Under: Power Platform Tagged With: Power Automate, PowerApps, SharePoint Online

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Go to page 5
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile
Go to mobile version