Power Automate

This post explains how to embed images inside an email sent from a Power Automate flow. This is a common requirement and can cause frustration depending on the requirements.

Generally, people want to include images stored within SharePoint, and this will be the basis of this blog post.

There are a few different methods, and I will explain the differences as we go through the post. If you prefer, here is a video explanation:

The file I am going to use for my test is in a SharePoint document library, it is called powerAutomateTestImage.png:

The full link to the file is:

https://accendo1.sharepoint.com/sites/PowerAutomateText/Images1/powerAutomateTestImage.png

Provide a simple link to the file in your email

This scenario is simple. On a client that is signed in to Office 365, you can specify the URL of the image in SharePoint and it should show in the email client:

This works well in the Outlook Web client, but not in other clients as the file is not reachable. So unless all of your clients are using Outlook on the Web, I do not recommend this method.

Embed the image content directly into the email with Base64

This method is much more reliable as the actual content from the image is sent within the email. So there is no need for the receiver to retrieve the image from SharePoint.

The file content is retrieved with the “Get file content using path” step. Then injected into the email as base64, with these expressions:

outputs('Get_file_content_using_path')?['body']['$content-type']
outputs('Get_file_content_using_path')?['body']['$content']

The complete image expression would be:

<img src="data:@{outputs('Get_file_content_using_path')?['body']['$content-type']};base64,@{outputs('Get_file_content_using_path')?['body']['$content']}" />

This method works really well, however some mail clients do not support base64 encoded images within the email body. For example, gmail does not show any base64 encoded images.

Another disadvantage of using this method is that the if the image is large, the email will large also. So be mindful of the size of the image that you are sending.

Use Power Automate as a HTTP Web Server

The most compatible way of embedding images in an email is to a publicly available image on the internet.

Uploading the image to a web server or CDN is best. But if you really want to use SharePoint as your source, it can be done. Its quite easily achieved by creating a flow which will serve image content via HTTP.

See this very simple flow:

This flow responds to a HTTP request with a reference to the Image in the URL. The picID in the URL refers to the ID of the image in the SharePoint document library:

The link to my HTTP Server Flow is:

https://prod-48.westeurope.logic.azure.com/workflows/59c5a92ccebe4f97adfd52eb6cf213a8/triggers/manual/paths/invoke/picID/{picID}?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=DNrMaouJY_Sifq8DRvbeWcuDl8TGKgeFpvP9NxDmOcQ

By modifying the {picID} to the ID of the image you want to serve, your private SharePoint Image will be accessible to the public. The test image used in this flow is ID 24, so this is the link to the dynamically served image. By changing the ID in the URL, a different image would be served to the client.

So now, getting back to our original email flow, we can modify it like so:

Now I can send the email to almost any client and it will be able to display the image. There are a couple of downsides to this method:

• It requires a Premium Power Automate license to use the “When a HTTP Request is received” action.
• The flow will execute every time someone looks at the email. If you are sending the email to many recipients, it will use a lot of flow capacity.

You could modify this flow to track if emails have been read, by embedding the recipient address in the URL. When the email is served to the client, you know the email has been read.

So what method should you use?

The method you should use to embed an image mostly depends on the capabilities of the recipient:

• Don’t use the first method, unless every one of your recipients is using Outlook Web Access.
• The base64 embedding method works well for the majority of clients.
• The HTTP method works well for all clients. But is more work to implement and not suitable if you are sending to many recipients at once.
• For transaction emails, I recommend using Sendgrid with Power Automate instead, it is much easier and produces a beautiful result.

Let me know in the comments how you get on and what method you choose!

Power Automate does not have a function to extract numbers from a string. I’ve seen this requirement come up a few times in the Power Automate community forums so thought I would post the solution here. Here is an image of the flow:

It works by creating a do until loop that looks at each character of the string that contains the number. In this case a compose step called “string”. If the character is equal to 0 to 9 or a period then it is appended to the variable called newString, otherwise it is discarded.

Most of the work happens in the append to string variable step, which contains the expression:

concat
(
if(equals(substring(outputs('string'), variables('index'), 1), '0'), '0', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '1'), '1', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '2'), '2', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '3'), '3', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '4'), '4', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '5'), '5', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '6'), '6', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '7'), '7', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '8'), '8', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '9'), '9', ''),
if(equals(substring(outputs('string'), variables('index'), 1), '.'), '.', '')
)

This sort of flow can work well inside a solution as a child flow. You should be able to copy the flow from the image above quite easily. But you can also download the example above here.

Hopefully a function will be added to Power Automate which allows you to extract numbers from a string. But for now, I hope this helps.

Sometimes strings in Power Automate need to be reformatted so that the first letter of each word is capitalized. This is know as “Proper Case”. There is no function built in to perform this common task. PowerApps does have this function so I expect it will arrive on Power Automate eventually.

In the mean time, here is an example flow and the required code to convert a string to Proper Case. The goal is to transform the sample string “this is a TEST” to “This Is A Test”

Example Proper Case Flow

Proper case is simple to achieve, take a look at this sample flow:

Explanation:

• The phrase to be transformed is added to a compose step.
• An apply to each is started and the input is a split expression which splits the phrase into an array.
• Each word is then passed into an expression which makes the first letter of the word uppercase and the remainder lower case.
• The final step of the flow uses a join expression to put the phrase back together again with spaces between each word.

The expression in the flow step “Capitalize each word” is:

if
(
  equals(length(item()),1),
  toupper(item()),
  concat(first(toupper(item())),toLower(substring(item(),1,sub(length(item()),1))))  
)

Explanation of the expression:

• If the length of the word is only 1 letter, just output the uppercase representation of the word.
• If the word is longer than 1 character then uppercase the first letter of the word and lowercase the remainder.

I decided to write this up after someone asked how this could be done on the Power Automate community forums. He was following a blog post written by Elaiza Benitez that performs the same function.

My flow is mostly the same, but also works when one of the words has only one letter.

Hope this helps you out until the Power Automate team add a Proper Case function.

In this post I am going to demonstrate how to collect data from a SharePoint list using Power Automate and publish it in a jQuery grid on any website. Many businesses store data in data sources that are easily accessible by Power Automate that might be useful to share externally. For example, stock levels, upcoming events, price lists etc.

This is easy to do with a small amount of code and saves you having to create guest access to your Office 365 environment. Power Automate can compile a JSON array as a data source to a jQuery grid.

Take a look at the following SharePoint list:

Using the above data, the parts list is going to be exposed to the public web, but the cost and margin columns will be excluded, and the list will be filtered so that only items where “Show on Web” is set to yes.

This data source could be anything that is accessible to Flow:

• Excel Data
• SQL Server
• CDS
• Oracle
• MySQL
• etc

The table below is a live working example of what this flow will demonstrate, as you can see:

• It’s very fast and easy to browse.
• Searching is virtually instant.
• It’s possible to group items easily.
• Filtering is simple.

How to build the flow

The flow is really easy to build and requires only a few steps, the trigger action is “When a HTTP request is received”.

Filter the data

The first action in the flow is the “Get Items” from SharePoint. This has an oData filter query so that it only retrieves items where “Show on Web?” is set to yes:

Reduce the JSON data from Get Items

The JSON returned by SharePoint is quite heavy, some of the the columns are not required and none of the metadata is needed for the grid. So a select action is used to reshape the data and create new column names:

You can really see the value of this when you compare one record from the “Get Items” action to one record from the select.

A single record from the select action looks like this:

  {
    "Part": "716/27600",
    "Description": "FUEL SENDER UNIT - P12",
    "Price": 67.75,
    "Stock": 25,
    "Product Group": "Standard Product"
  }

As you can see, it contains only the information required to build a grid in the browser.

Send the JSON response to the browser

The final step is just a HTTP response configured in the most basic way. It simply sends the output of the select action.

The entire flow consists of four steps and looks like this (Click to Zoom):

Build the Browser page for the grid

The table above was built using the DevExpress DataGrid, but the method is very similar for many jQuery based datagrid components. The amount of code is minimal.

The html for the grid consists only of a couple of DIVs:

<div class="demo-container">
  <div id="gridContainer"></div>
</div>

Everything else is done from jQuery. Three lines in the head section:

<link rel="stylesheet" href="https://cdn3.devexpress.com/jslib/20.2.3/css/dx.common.css">
<link rel="stylesheet" href="https://cdn3.devexpress.com/jslib/20.2.3/css/dx.light.css">
<script type="text/javascript" src="https://cdn3.devexpress.com/jslib/20.2.3/js/dx.all.js"></script>

And one script on the page that pulls the data from Power Automate:

<script>
jQuery(function(){
    jQuery("#gridContainer").dxDataGrid({
        dataSource: "https://prod-03.westeurope.logic.azure.com:443/workflows/...",
        columns: ["Part", "Description", "Price", "Stock", "Product Group"],
        showBorders: true,
		paging: {
            pageSize: 10
        },
        pager: {
            showPageSizeSelector: true,
            allowedPageSizes: [10, 25, 50, 100, 200]
        },
        remoteOperations: false,
        searchPanel: {
            visible: true,
            highlightCaseSensitive: true
        },
        groupPanel: { visible: true },
        grouping: {
            autoExpandAll: false
        },
		filterRow: { visible: true },
        allowColumnReordering: true,
        rowAlternationEnabled: true
    });
});
</script>

The datagrid component has many more options that I did not configure, and could have been setup with even less code than I used.

This is an easy to implement, but quite powerful use for Power Automate. If you only want to provide a small subset of data to users outside of your organisation then you don’t need to setup guest access to your environment, so it really is an easy solution.

Something to consider is that if you are going to do this on a webpage that gets many visitors per day then you are going to go through flow runs quite quickly. An alternative would be to run the flow on a schedule and output the JSON to a file which is publicly reachable. This will produce a faster response to the browser and save your flow usage.

If you would like to copy the source code, go to this demo page and view source in your browser.