Get SharePoint List Image in Power Automate

Posted on by
103

Summary

UPDATE – FEBRUARY 2024
SharePoint Online recently made a change to the storage location of the Image Column type from Site Assets to Attachments. This post has been updated from the original Site Assets location to include Attachments. See ‘Step 10 with Attachments’

No-code/low-code platforms, such as Microsoft Power Automate, has transformed the days of centralized development teams to the hands of the end-user looking to increase productivity. The abstraction of code by connecting visual connectors and action boxes together and passing content between them makes it quick to automate repeatable tasks. However, when a connector does not provide expected content, we must pursue creative routes. That is the case for this post where a SharePoint List column of type ‘Image’, aka Thumbnail, does not appear in Power Automates SharePoint Connector content.

SharePoint List Item with Image column
Missing Image content in Power Automate
SharePoint Image column is internally typed as Thumbnail

The route to solve this scenario is to utilize the content we have from the list item and then add additional connectors and actions to pull the image from SharePoint itself via its API. As we have the list item content from the default SharePoint Connector, we can use its data to get the Image metadata by first parsing the SharePoint Column and then the Value to get it into a typed content format. Last step is to use the typed content to pull the image directly. This is achieved through a 4-step process as the Scope below illustrates.

Power Automate Scope of 4 actions
‘Photo’ is the column name containing the Image

Once we have the image content from the Photo column, we can use it for downstream actions such as sending it embedded in an email.
As the image depicts, we can also link to the Photo in which case the 4th step can be omitted as the typed metadata content has the information needed to link directly.

Email with linked and embedded image

The remainder of this post will go through the process step-by-step.

Prerequisites

Description

To start we need to create a SharePoint List that includes a column of type “Image”. In this example our SharePoint site is called ‘BBQTrails’ and the list is ‘BBQ Recipes’. These names will be referenced throughout the post. The columns for the list have been defined as follows with ‘Photo’ being the name of the Image column. Note: SharePoint uses ‘Image’ as the short description for the type, but the display name is ‘Thumbnail’ so these terms will be used interchangeably.

SharePoint List Columns

We then create a list entry for Texas Brisket, incl. a photo, that will be used in the Power Automate flow.

SharePoint List Item

At this point we are all set to create our Power Automate flow. We will us a manual triggered flow for demonstration that will send an email of the photo. The photo will be displayed both in a linked and embedded format. The advantage of the linked photo is that the email size will be smaller, but the recipient must have access to the SharePoint site where it is stored to view it. Embedding allows the photo to be viewed by recipients who may not necessarily have access to the SharePoint site as the email will contain the photo itself.

Let’s get started!

Steps

1. Using Power Automate, create a new manual triggered flow and add the SharePoint action ‘Get Item’. Configure the action with the target site, BBQTrails, list, BBQ Recipe, and ID of the list item to get. In this case 1.
Note: Id number, 1, is the value from the ID column in the SharePoint list for the particular row of interest.

SharePoint Get Item

Optional, but recommended, is to test as we work through the flow to verify we are getting the right outputs. In the below example we confirm that we are accessing the Texas Brisket list item.

Test Get Item action

2. Add a new flow action with the SharePoint connector ‘Send an HTTP request to SharePoint’. We will use this action to send an API request to SharePoint for getting just the Photo column value of our Texas Brisket item by passing the ID of the list item.

Get Photo column

We can also run the query directly in the browser to inspect the result. Replace the ID with the item ID, in this case 1.
Note; the space in ‘bbq recipes’ is URL escaped with a ‘%20’

https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/items(1)/Photo
API call for column

To help explore the SharePoint API further, here are a few additional queries to get information about the list and its data:

‘/’ gets list metadata

https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/

‘/fields’ gets metadata for all the fields

https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/fields

‘/items’ gets all the items

https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/items

‘/items(id)’ gets a single item by specified id

https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/items(1)

‘/items(id)/columnInternalName’ gets the value of a column. The ‘columnInternalName’ can be discovered by using the /fields query above and find the value of the ‘InternalName’ element for the desired column. 

https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/items(1)/Title

3. Test the flow for ‘Send an HTTP Request to SharePoint’. When the test is complete, copy the content of the outputs body as we will need that later to define our content schema.

Send HTTP Request test result

4. Add the Data Operation action ‘Parse Json’. For its content, select the body output of the SharePoint ‘Send an HTTP request to SharePoint’

Parse Json Data Operation

5. On the Parse Json action, add the Schema by clicking ‘Generate from sample’, and paste the output copied from step 3.

Generate Schema for Photo column
Generated Schema for Photo

6. Add the Data Operation action ‘Compose’. For its inputs, select ‘Photo’ from the Parse Json action.

Compose

7. Test the flow and when complete expand the Compose results to copy the outputs of the Photo.

Copy compose output

Using Visual Studio Code or a text editor, paste the results to inspect the Photo column’s value in detail. Notice it contains a ‘serverUrl’ element to SharePoint and a ‘serverRelativeUrl’ element to the image we added to the list item. This tells us that when we added the image to the list item, SharePoint, stored it as a file in its SiteAssets folder for the SharePoint Site, BBQTrails, under a Lists subfolder with the list’s internally unique identifier. It is these two (2) elements we are after to get the image.

Image metadata

7. Delete the ‘Compose’ action created in step 6. We only needed it to capture a sample of the data that we will use next.

8. Add another Data Operation action ‘Parse Json’. For its inputs, select ‘Photo’ from the prior Parse Json action (same as Compose in step 6).

Parse Json for Photo

9. On the Parse Json action, add the Schema by clicking ‘Generate from sample’, and paste the output copied from step 6.

Sample Image data
Generated Image schema

10. Add a new flow action with the SharePoint connector ‘Get file content’. We will use this action to get the content of the image file. Enter the Site Address to the SharePoint site, BBQTrails in this example. For the file identifier, we use the ‘serverRelativeUrl’ property from the Parse Json result, but we need do a couple of expressions to transform the data:

a) Using the ‘replace’ function we remove the redundant site information – /sites/BBQTrails
b) Using the ‘encodeUriComponent’ function we Url encode the remaining string

The resulting expression should look similar to the following:

encodeUriComponent(replace(body('Parse_JSON_-_Photo_Json')?['serverRelativeUrl'],'/sites/BBQTrails',''))
File identifier expression 1
File identifier expression 2

File identifier expression 3

UPDATE – FEB 24
10 with Attachments – with the update of SharePoint Online storing the image in Attachments instead of Site Assets we might not receive a serverRelativeUrl property value. In that case, only the filename will be provided, and we need to construct a different File Identifier to the new attachment location. The constructed Url should look similar to the following:

https://torbenp.sharepoint.com/sites/BBQTrails/Lists/bbq%20recipes/Attachments/1/Reserved_ImageAttachment_[12][Brisket][1][1].png

Let’s decompose these elements and where to get them:

  • https://torbenp.sharepoint.com/sites/BBQTrails – is the Site path and we specify that as the Site Address field in the Get FIle content action
  • Lists/bbq%20recipes/ – is the relative List path and we can read that from the Get Item action in the beginning and its Path property
  • Attachments/ – will be the new static relative path to the Attachments location
  • 1/ – is the ID for the list item and we read that from the Get Item action in the beginning and its ID property
  • Reserved_ImageAttachment_[12][Brisket][1][1].png – is the filename of the image that we obtain from the output of our Parse JSON – Photo Json action under the filename property

With this information, we replace the original expression in the File Identifier with the following expression:

encodeUriComponent(concat('/',body('Get_item')?['{Path}'],'Attachments/',body('Get_item')?['ID'],'/',body('Parse_JSON_-_Photo_Json')?['fileName']))

If you encounter a situation where you need to support both Site Assets and Attachments as file locations, I suggest creating a variable to hold the expression. Then add a condition after the Parse JSON – Photo Json action to check if the serverRelativeUrl is null. If true, set the variable to the updated expression, if false, use the original expression. Then add the variable as the File Identifier in Get File content.

11. Add a new flow action with the Data Operation connector ‘Compose’. We will use this action to compose an HTML structure of the email body to use for sending the embedded and linked image. For the embedded image we use the body output of the Get File Content action, but we must base64 encode it with an expression similar to the following: 

base64(outputs('Get_file_content_-_Photo_Content')?['body'])

For linking we concatenate the ‘serverUrl’ and ‘serverRelativeUrl’ obtained from the second Parse Json action in step 8.

Compose Email

12. Add a new flow action with the Office 365 Outlook connector ‘Send an email (V2)’. Add the recipient and in the subject we add the Title field of the get item from step 1. For the body we use outputs from the compose action in step 11.

Send email action

We have reached the end of the flow and can now test to see if it shows up in our mailbox.

Email with linked and embedded image

Next Steps

  • Microsoft is likely to release an updated SharePoint Connector in the near future. At that point, using the native content would be recommended over this method.
  • Building a custom connector that performs these actions may be of value for reuse if there is a delay in the native connector.
  • Hungry? Follow my BBQ journey on Instagram ‘bbqtrails’

Go Docker: Hello-Go with multi-stage build

Posted on by
Reply

Summary

This post covers a Hello-Go web application written in Go and hosted in a docker container. The solution will use Docker multi-stage builds to create the container image and display ‘Hello-Go’ on web requests.

Prerequisites

Description

Go is an open source project from Google that is based on the C programming language. It is a compiled language with multi-platform support across Linux, Windows, macOS and more. With the Go runtime performance and succinct syntax of its programming language, Golang, it is well suited for making a lightweight web application. The compiled application will be published as a Docker image so it can run on a container platform.

A Docker multi-staged build process will be used to separate the build and runtime images. Separating the build and runtime provides the benefit of removing non-essential runtime files and applications to reduce the image size. Removing the unrelated applications is also a security hardening component to reduce attack vectors for when the container is running.

The following process represents the subsystems involved in building the Hello-Go web application.

Process for Go source code to Docker image via multi-stage build

In this example we’ll build the image for the Linux kernel using Windows10 WSL2.

Steps

1. Using Windows Terminal, open a WSL Linux terminal (such as Ubuntu), create a source folder, hello-go, and open the folder in Visual Studio Code

Windows terminal with Ubuntu creating a source folder and open VSCode.

2. Create a source file named ‘hello-go.go’

VSCode with a hello-go.go source file

3. Enter the following code for the hello-go web application

package main

import (
	"fmt"
	"log"
	"net/http"
)

func main() {
	http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
		fmt.Fprintf(w, "Hello-Go")
	})
	log.Fatal(http.ListenAndServe(":8080", nil))
}
Hello-go source code in VSCode

4. Create a docker file for the multi-stage build named ‘Dockerfile’ (no extension)

Dockerfile for docker image build

5. First section of the Dockerfile, we define the base image that will be used for the runtime image and expose the web application port the hello-go application is listening on, port 8080.

FROM alpine:latest as base
EXPOSE 8080

6. Next we define the build image that contains the Go compiler we can use to compile the .go source. We’ll create a /build folder and compile using the ‘go build’ command.

FROM golang:1.15.2-alpine3.12 as build
RUN mkdir /build
ADD . /build
WORKDIR /build
RUN go build -o hello-go .

7. With the application compiled, we can add the final stage by copying the build output to the runtime image defined at the beginning of the Dockerfile. We create an /app folder in the runtime image, copy the build to it, and specify the command to execute the application when the container runs

FROM base as final
RUN mkdir /app
WORKDIR /app
COPY --from=build /build .
CMD ["/app/hello-go"]
Complete Dockerfile entered with multi-stage build configuration

8. In Visual Studio Code, go to the Terminal menu and select New Terminal

9. Enter and run the following docker command to build and tag, hello-go:latest, the docker image

docker build -t hello-go:latest .

10. Verify the docker build completed successfully

Compilation success output in terminal after running Docker Build

11. Run the container and publish port 8080 on the host so it is accessible

docker run -d -p 8080:8080 hello-go:latest

12. Call the web application using ‘curl’

curl http://localhost:8080

13. Verify that the curl response displays ‘Hello-Go’

VSCode terminal window used to request the url with curl and displaying the Hello-Go output

14. List the build and runtime images to see the size differences

docker images | grep 'hello-go\|golang'
VSCode terminal output shows image sizes of the golang build and hello-go runtime images

15. Notice the size difference of 300MB for the build image, golang, and 12MB for the runtime image, hello-go.

VIP notifications on Android with Outlook Mobile

Posted on by
Reply

Summary

This post covers how to create a VIP notification for emails on Android with Outlook Mobile.

Overview over VIP messages

VIP notifications is available with Gmail (and iOS Mail) that allow notification customization when emails arrive from very important people (VIP). This is convenient when preferring silence, except for VIPs. Unfortunately, it is not available in Outlook on Android at this time. In Android, turning on the Notification feature, ‘Do not disturb’, will cause all notifications to be silent, but it has exceptions for certain types such as calls and text messages. The text message exception will be used for this solution by identifying emails from VIPs and then sending a text message that allows the notification to break through ‘Do not disturb’.

Prerequisites

Description

To begin the solution it helps to start with a baseline on how emails are send to the mobile device to identify integration points.

VIP email send to Android Device via a Mail Server

Outlook Mobile receives all emails from a mail server and although there may be other systems in use before the VIP email arrives to the mail server, we only have two (2) direct integration points; (1) Mail Server and (2) Android Device w/ Outlook Mobile.

For VIP notifications to break through ‘Do not disturb’ the following text message process will need to happen.

VIP text message send to Android Device via a Wireless Carrier

Android receives all text messages from a wireless carrier and similar to emails there may be other systems in use before the VIP message arrives to our Wireless Carrier, but we only have (2) direct integration points; (1) Wireless Carrier and (2) Android Device w/ Messages.

With the email and text message flows as a baseline, we can define a conceptual integration between the two (2) processes and their subsystems. As the process starts with an email, we need a solution that can check a condition if the email is received from a VIP sender and if true, send a text message to our device. This solution can be conceptualized by bringing the baseline flows together with a workflow.

Conceptual diagram of Mail Server and Wireless Carrier integration.

The conceptual design introduces a couple of new subsystems in the process, (a) Mail Event Receiver and (b) Workflow. The purpose of the Mail Event Receiver is to watch for incoming emails and initiate the Workflow subsystem. The workflow will then be responsible for the conditional check to see if the email sender is on the VIP list and if true send the email and/or message to the device via the Wireless Carrier.

The workflow subsystem is the integration hub between the mail server and the wireless carrier. We can use no-code / low-code workflow tools for these type of subsystem integrations. A great tool for this purpose is IFTTT (IF This Then That) which comes with connectors to various subsystems. For the purposes of this post, we will cover a similar tool called Power Automate with integration to Exchange Online acting as the mail server. For the mail event receiver between Exchange Online and Power Automate, we will indirectly be using a Graph subsystem that both Exchange Online and Power Automate is integrated with to complete the mail server to workflow integration.

That covers the input to the workflow, so lets turn to the workflow output and its integration to the Wireless Carrier. Most carriers support the ability to send emails as text messages through the use of the wireless phone number as the recipient on the carrier’s mail server (aka realm). This means we can send text messages to ourselves via emails by using the notation <my.phone.number>@<wireless.carrier.realm>. This is supported by multiple carriers including the following US carriers:

Wireless CarrierEmail Recipient
Verizon5551234567@vtext.com
AT&T5551234567@txt.att.com
T-Mobile5551234567@tmomail.com
Wireless Carrier email to text message

Plugging in the identified solutions for each subsystem creates a completed logical design that we can implement.

Logical design diagram of the email to text message integration.
Logical design of the Email to Text Message integration

Note; variations can be made to this design such as substituting Exchange Online with a different mail server and Power Automate with IFTTT.

Next we’ll cover the steps for creating the workflow condition using Power Automate.

Steps

1. In a browser, navigate to Power Automate and login with an Office 365 account.

2. On the navigation menu, select “My Flows”, “New”, “Automated – from blank”

Power Automate screen for creating a new automated flow from blank template.

3. Enter a Flow name, such as “VIP Notify”

4. Type “Email” in the flow trigger search box and select the trigger “When a new email arrives (V3)”

Searching and selecting the trigger event for the workflow to be based on arrival of an email.

5. Select “Create” and a workflow canvas should appear with a trigger step.

6. Click the trigger step titled “When a new email arrives (V3)” and it should expand to see all configuration options.

7. In the ‘From’ field, enter the sender email of the VIP person. Multiple sender emails can be entered by adding a semi-colon (;) between each sender.

Entering sender emails of VIPs in the Power Automate trigger task to filter emails to only VIPs

8. Select “+ New step” at the bottom of the workflow canvas to enter a new step.

9. Search and select the action titled “Send an email (V2)”

10. Click the step titled “Send an email (V2)” and it should expand to see all configuration options.

11. In the ‘To’ field, enter your wireless carrier email address using the <phone.number>@<wireless.carrier.realm> notation to send an email as a text message to yourself.

12. In the ‘Subject’ field, enter a description that identifies the text message, such as “VIP Email Received”

13. In the ‘Body’ field, enter a description for the text message, such as “Check email for details”.

Configuring your wireless carrier phone number to send an email that should be converted to a text message.

14. Select the ‘Save’ button on the workflow canvas to save the workflow.

15. The VIP Notify workflow is now configured and should be active if the Status field displays ‘On’

VIP Notify power automate flow summary with status set to 'on'.

16. If the VIP sender list needs to be updated, return to this ‘VIP Notify’ flow and select ‘Edit’ to return to the canvas and repeat step 7 for adding or removing VIP sender emails in the ‘From’ field.

Next Steps

  • In the configuration of “Send an Email (V2)” step 12 & step 13, additional information can be added to the Subject and/or Body fields for the text message. For example, the Subject could include a dynamic field that contains the ‘Sender’ or ‘Subject’ of the email that triggered the workflow. This would provide specific details in the text message of which VIP and subject was received without having to check emails.