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.
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.
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.
The remainder of this post will go through the process step-by-step.
Prerequisites
- Microsoft 365 subscription (requires SharePoint, Power Automate, and Outlook)
- Visual Studio Code (optional)
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.
We then create a list entry for Texas Brisket, incl. a photo, that will be used in the Power Automate flow.
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.
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.
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.
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
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)/Title3. 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.
4. Add the Data Operation action ‘Parse Json’. For its content, select the body output of the SharePoint ‘Send an HTTP request to SharePoint’
5. On the Parse Json action, add the Schema by clicking ‘Generate from sample’, and paste the output copied from step 3.
6. Add the Data Operation action ‘Compose’. For its inputs, select ‘Photo’ from the Parse Json action.
7. Test the flow and when complete expand the Compose results to copy the outputs of the Photo.
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.
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).
9. On the Parse Json action, add the Schema by clicking ‘Generate from sample’, and paste the output copied from step 6.
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',''))
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 actionLists/bbq%20recipes/– is the relative List path and we can read that from the Get Item action in the beginning and itsPathpropertyAttachments/– will be the new static relative path to the Attachments location1/– is the ID for the list item and we read that from the Get Item action in the beginning and itsIDpropertyReserved_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 thefilenameproperty
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.
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.
We have reached the end of the flow and can now test to see if it shows up in our mailbox.
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’
Awesome! Thank you.
In case anyone else is reading, I wanted to take an image from a SharePoint List and use it for Word document automation.
The output from Step 10 is the content you need to feed into a Word image control, so you don’t need the Step 11 Compose – instead, use the ‘Populate a Microsoft Word template’ action and feed the Body of step 10 into the template’s image control.
Sweet tip, thank you!
Thank you for the information; it is really clear and easy to follow.
I’m trying to achieve what Neil did by using the image to populate a Word template.
All the steps seem to work just fine, but in the “Populate Word” step, the error is: “The image is not of type PNG or JPG. Please provide an image of type PNG or JPG.”
I have tried downsizing the image and changing it from JPG to PNG, but it doesn’t seem to work.
I don’t understand why. The “Get File” step seems to be working just fine; the file identifier ends with .png.
What can I try?
How come I don’t have the ‘Photo’ from the Parse Json action?
Hi Rigel, I would troubleshoot step 2 to verify that the column request is succeeding both manually in the browser and afterwards in Flow after executing the Send HTTP Request. Hope that helps, but feel free to elaborate on what you see.
the Jpeg files are not being embedded in the email
Hi Radhika,
A few suggestions to try and troubleshoot:
1) Try and save the ‘Get File Content’ retrieved in Step 10 to a file in SharePoint or OneDrive and verify the image is correct and not corrupted
2) If a valid image, check step 11 for the expression that performs a base64 encoding to embed it in the email.
3) Verify HTML is supported for the email.
Hope that helps,
Torben
Thank you for the quick response!!
even though the file was visible in SP list, the base64 conversion was giving error…I tried to upload picture from my list app. However, all the uploads from computer for jpg, jpeg and png are successful. Thank you again!!
Hi,
I keep getting an “The expression is invalid.” error when I try to set the URI in the Send an HTTP Request action with Get function. Basically, I am trying to retrieve an image from a SharePoint List and display it on a PowerPoint slide.
Hi Kamala,
I am assuming you are referring to step #2 and suggest adding a hard-coded ID value and see if it is successful. If it is, you may not have the retrieved the right list item. You can debug it by testing your flow to see the input/output values of the ID. See step 1 for the test results.
Hope that helps,
Torben
Hi Torben
I have tryed to add image to Word documents for a while and now I found your sulotion that looks greate. But I have got a problem at step 10.
“Get file content” adding this “File Identifier”:
encodeUriComponent(replace(body(‘Parse_JSON_-_Byggsignatur_Json’)?[‘serverRelativeUrl’],’/sites/Entreprenader’,”))
I get this error :
-Correct to include a valid reference to ‘Parse_JSON_-_Byggsignatur_Json’ for the input parameter(s) of action ‘Get_file_content’.
Can you help a beginner wiith this?
Hi Bengt,
That error is likely due to the name ‘Parse_JSON_-_Byggsignatur_Json’ not matching the Parse Json internal name from step 9. If you typed it manually in the expression dialog, one option is to have the Dynamic Content dialog write it out. For example, if you typed ‘encodeUriComponent(replace(‘ in the Expression function textbox, click over to Dynamic content and then select the ‘serverRelativeUrl’ attribute and it should write out the full internal path in the function textbox. You can then add in the relative url replacement or compare it to what you had previously.
Best of luck!
Torben
I am trying to use this method to get a sharepoint image and populate a word document with the image itself. Is this possible using this type of flow?
Hi Cole,
I have not tried so not sure if the Word action would support embedding an image as a field. If image embedding is supported, then this should work.
Best of luck,
Torben
Thanks for posting this solution Torben!
It works great, however I have a SharePoint list where the image column isn’t always populated, and I’m getting an error on the first Parse JSON action if the image column is empty. “Invalid type. Expected String but got Null.”
How would I go about resolving this?
Hey Alan,
There are likely various ways, but one example would be to use the ‘Condition’ action and then check for ‘null’ directly after the HTTP request action so you can fork the code based on the image being null. Here’s a partial condition definition to show the expressions for getting to the Photo column and checking for null before the Parse JSON action.
"operationDefinition": {
"type": "If",
"expression": {
"equals": [
"@outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?['Photo']",
"@null"
]
},
"actions": {
"NoImage": {
"type": "Compose",
"inputs": "NoImage",
"runAfter": {}
}
},
"runAfter": {
"Send_an_HTTP_request_to_SharePoint": [
"Succeeded"
]
},
"else": {
"actions": {
"Image": {
"type": "Compose",
"inputs": "Image",
"runAfter": {}
}
}
}
}
HTH,
Torben
How can we delete a photo from the image column? I used these steps to obtain the Id, serverURL, and relativeServerUrl but no luck. I thought the relativeServerUrl would do it since it appears to point right to the file, but I received an error stating “The response is not in JSON format” (using the Delete files action on each try)
Hi Torben,
thank you so much for this tutorial. I have followed the instructions and I am getting the data to the Outlook email. However, Outlook does not display the photo. I know this is not the intention of this post but didn’t know if you had a potential solution. Even if I hard code an image location into an Outlook email, the image is not displayed. This only happens for images that are stored in a SharePoint online list. I have on-prem SharePoint, and other web server image locations, and all of these function fine. Only images from SharePoint online are affected. Any Ideas?
Hi Phil, I am assuming that it is only the linking that fails for SharePoint Online images. If that is the case it could be several things that is blocking the user from being able to pull the image, including permissions to the SharePoint site/content area. To avoid a permission issue or other blocking intermediaries, are you able to embed the image instead of linking?
Torben
Hey Torben,
Yes, the issue is using the img tag. If I put serverurl and relativeurl in an anchor tag, the link comes through and users can click the link and wire the image without any issue. I did try to embed, but could never get it to work. I am not sure but the only thing different from your code and mine was on the replace function. When I use ‘/sites/…. It gets changed to ‘sites… without the forward slash. The serverurl and relativeurl from the second JSON are correct so I assume I am good to that point. But nothing I try in the outlookv2 email will render the image. Any steps I should check to make sure the data returned is correct? If I look at the source from the email, I see what I would assume to be the image data, but it just does not display.
I looked at my example email in outlook.office.com (web client) and noticed that it showed as a broken image. Are you seeing the same that the image placeholder is present, but the image is broken?
To make it show, I did a right click on the broken image and selected ‘Copy Image Link’ to capture the url. The url was pasted into a separate browser tab where it authenticated me to SharePoint and rendered the image. After that step, going back to my email on outlook.office.com the image showed up correctly.
If your experience is similar, it appears due to Outlook not being able to authenticate the user automatically to SharePoint in order to display it. However, once you force the authentication directly it will show up based on the active session. I will make a note on the post to this behavior as I was using embedding instead of linking after writing it which isn’t ideal based on what you found. If you can tell me what step you got to on embedding, we can try and see if we can get it working as it will likely be a better solution.
Torben,
I have finally got this working. my testing was a little flawed. When I attempted to embed the image, it did not work when viewing the email with the Outlook client, but it did work when viewing with the web client. This lead me to believe the issue was specific to the Outlook client. I went over the flow step by step again using your post as a reference and saw that I had done all of the configuration properly with one exception. When I put the code to embed the image, I did not add the height and width tags. This is because I did not want to shrink or expand an image to fit a specific area but rather wanted the users to see the raw image in its native state no matter the size. Once I added those tags, the image now displays properly in Outlook. Really doesn’t make sense to me, but it is working. Thank you again for your very helpful post.
Great to hear and thanks for the update Phil!
Hey Torben, I was incorrect about having this working. Not sure why I thought it was, but it is not. I have done significant testing to determine why this is not working but cannot seem to come up with a solution. In my flow, I have validated that all of the JSON steps and the Get_file_content outputs look correct. In fact for linking and creating an anchor tag, the serverURL and serverRelativeURL are correct and work for adding the linked photo. This works only when using Outlook via the web as you mentioned earlier due to authentication. In my email I set up three different ways to deliver the image to my users to see the result.
1. Embedded – this does not display the image at all. in fact if I look at the source of the email, the area that this image should be shown has only the tag in the code without any image info.
2. Linked – This displays correctly on Outlook Web Access but not on the Outlook client due to authentication issues.
3. Anchor – This displays a link that works just fine with either email client since it opens a web browser to O365 and authenticates the user.
It seems like the only way to make an image appear in the Outlook client is by embedding, but I cannot seem to get the image to embed even though the output of the Get_file_content step clearly shows it is getting the image fine. See my code for the Send Email Step below
embedded:
The only thing that I can see that seems weird to me is that the output of the Get File Content shows that the file type is .jpg. Does this make a difference? I just wonder since we are calling out image/png in the tag.
Any insight would be most appreciated.
Hey Torban,
I have figured something out but not sure how to fix it. I have been able to have some images embed as they should. Most will not. What I have discovered is that when a flow works and embeds an image correctly, the output from the Get_file_content step lists the following:
{
“$content-type”: “image/png”,
“$content”: “iVBORw0KGgoAAAA…the rest of the picture data…….”
}
but when the flow fails to embed the image, the output from the Get_file_content step is not displayed but rather a link is generated to download the output. The output is very different when viewed. Below is the output from that step for a picture that does not embed properly.
{
“statusCode”:200,
“headers”:{
“Vary”:”A whole bunch of header information…….”
},
“body”:{
“$content-type”:”image/png”,
“$content”:”iVBORw0KGgoAAA..the rest of the picture data…..”
}
}
The only thing I can see that is different between the files is the file size. The ones that work are all less than 700KB in size. All larger files fail. I have now been able to make png and jpg files embed, but only if they are small. I know this is not an outlook setting since when I view page source on an email that does not embed the image, only appears and nothing else whereas the ones that work, show the entire tag and not just .
Any idea how to troubleshoot this to make sure that all photos will embed properly?
Hi Phil,
I think you may be hitting up against limitations of embedding the image inline vs. a multipart email. Inline the image content will be part of the email body where in multipart it is attached for inline usage and then referenced by its content id (CID) in the body. This separates the email body and image into 2 parts.
You can verify that the image content is valid for both sizes by using the output of the ‘Get File Content’ as the email attachment content to see if it comes across for both image sizes.
If that is the case, I’m not certain the Send an Email action allows customizing the attachment to be used inline with a content id instead of a regular attachment. I’ll explore and update if I find a solution to it.
Torben
Thanks Torban,
I just tested your theory and you are correct. Simply adding the file content as an attachment works no matter the size of the email. Honestly, this was the original intent anyway but I couldn’t figure out how to do it. I stumbled across your post and then started to try to embed it. So much simpler this way anyway. FYI, I did run across another post where someone was having an issue copying a file from one location to another. The process was the same for getting the file content, but they had the same issue I was facing with larger file sizes. They ended up using Base64ToBinary to change it to binary and then creating the new file. I tried this and it did convert to Binary, but I could not figure out how to convert it back to Base64.I found an article that explained that you could do this but I really did not understand it. Below is the text from that article. In any case, thank you so much for your assistance with this.
For example you are reading a file from one drive and want to convert the binary file into Base64 string for further processing. You actually don’t need to. This is because when Power Automate will handle the incoming bytes, it will automatically convert the incoming bytes as Base64 with content type as “application/octet-stream”.
“$content-type”: “application/octet-stream”
And to get the content in Base64 format, you just need this expression – triggerBody()?[‘$content’]
That’s great news, thanks for sharing!
Torben
Hi. this is fantastic, but at step 3 i get an error. It states it cannot find resource. here is a screenshot of my step 2 and 3 (step 2 passed test). i am using the word Artwork at the end since that is the column title. i tried changing it to photo as well, but didn’t work.
https://prnt.sc/vo4HXBe5IJui
Error Details
Error
Action ‘Send_an_HTTP_request_to_SharePoint’ failed
Error Details
Cannot find resource for the request Artwork.
clientRequestId: 24dd8b20-e765-43c4-8788-70603f2f7d74
serviceRequestId: 3441b0a0-c085-3000-98cf-c106de5ca0fc
Hi James,
It looks like the ‘Artwork’ column is not found in the list. It uses the internal name so if the column has been renamed, it may visually show Artwork in the Title but have the old name as its internal name. I would suggest first verifying in a browser that calling the url directly fails with the not found:
https://comcastcorp.sharepoint.com/sites/cqcadvancedmedia/_api/lists/getbytitle('Stunt%20Gun%20Promotions‘)/items(2)/Artwork
Assuming it fails, you can use the following url in the browser and search for the Artwork column to find its internal name. It would be in an attribute such asArtwork
https://comcastcorp.sharepoint.com/sites/cqcadvancedmedia/_api/lists/getbytitle('Stunt%20Gun%20Promotions‘)/fields
Try replacing Artwork with the internal name found in the ‘fields’ query.
HTH,
Torben
Hi! Thank you thus far. I was able to resolve it. now i’m on step 10 but get this error after running at test.
Route did not match
clientRequestId: fa4223c4-705b-4725-a6de-163c5e87d2e3
serviceRequestId: fa4223c4-705b-4725-a6de-163c5e87d2e3
Here is the expression that was used. Screenshot is the underlying field name.
encodeUriComponent(replace(body(‘Parse_JSON_-_Screenshot_Json’)?[‘serverRelativeUrl’],’/sites/CQCAdvancedMedia’,”))
Hi James,
The encodeUriComponent looks right so I would check the Site Address on your ‘Get File Content’ action is set to:
https://comcastcorp.sharepoint.com/sites/CQCAdvancedMedia
If that doesn’t work, perhaps review the json payload in step 9 and verify the serverUrl and serverRelativeUrl looks something like:
serverUrl: https://comcastcorp.sharepoint.com
serverRelativeUrl: /sites/CQCAdvancedMedia/SiteAssets/Lists/…
Best of luck!
Torben
actually the relativeURL payload looks a little different.
{“type”:”thumbnail”,”fileName”:”Screenshot 2023-05-07 at 8.09.26 PM.png”,”nativeFile”:{},”fieldName”:”Screenshot”,”serverUrl”:”https://comcastcorp.sharepoint.com”,”fieldId”:”66f902d3-cea9-47a0-9af5-6a7fe7e8605e”,”serverRelativeUrl”:”/sites/CQCAdvancedMedia/SiteAssets/Lists/e0a116a7-dbcf-4569-b0a8-488a67eb9263/Screenshot%202023-05-07%20at%208.09.26%20PM.png”,”id”:”48d50c15-f5c1-44bb-85ba-58eb91ef7c65″}
here is the screenshot of it as well. the site address looks correct. do i have to add in all the json payload after the /list?
image here: https://prnt.sc/uaVhf3rRMGWd
json payload from previous step.
{“type”:”thumbnail”,”fileName”:”Screenshot 2023-05-07 at 8.09.26 PM.png”,”nativeFile”:{},”fieldName”:”Screenshot”,”serverUrl”:”https://comcastcorp.sharepoint.com”,”fieldId”:”66f902d3-cea9-47a0-9af5-6a7fe7e8605e”,”serverRelativeUrl”:”/sites/CQCAdvancedMedia/SiteAssets/Lists/e0a116a7-dbcf-4569-b0a8-488a67eb9263/Screenshot%202023-05-07%20at%208.09.26%20PM.png”,”id”:”48d50c15-f5c1-44bb-85ba-58eb91ef7c65″}
Hi James,
You are very close! The serverUrl and relativeServerUrl are correct. The challenge is most likely in the input for the ‘Get File Content’ action.
For the Site Address, I recommend removing ‘Advanced Media -‘ so it looks as follows:
Site Address: https://comcastcorp.sharepoint.com/sites/CQCAdvancedMedia
For the File Identifier, you need to convert it to an expression by clearing out the current content, clicking in the field should display an ‘Add Dynamic Content’ link in the lower right of the input box. Click that and select the ‘Expression’ tab. Then paste in your encodeUriComponent string and accept.
The File Identifier input should now be replaced with a box that has an fx icon similar to the following:
https://i0.wp.com/torbenp.com/wp-content/uploads/2022/07/flow14.png?ssl=1
HTH,
Torben
WOW! I got it to work! you’re a genius. This is what my issues were:
1) i didn’t know i had to rename the Parse JSON step title.
2) i didn’t realize i had angled quotations mark instead of straight ones for expression.
another question, does this only work for that unique single sharepoint entry? i want it to email me image anytime there is a trigger, like a new entry or a status value choice change. right now it only sends me the image for ID value 1.
i got it to work! i just added a “if sharepoint item is created or modified” in the beginning instead of manual trigger. super stoked thanks.
Excellent!
Hi Torben,
This tutorial was great and very easy to follow.
If I create the item, adding images from my computer, the email sends and all the images show up in the email, but whenever someone else creates the item then the images don’t show up in the email.
I’ve checked that the images aren’t corrupt and even sent someone images that I know work to add to a new item and they still don’t show.
Is this something you’ve come across before and do you know of a fix?
Thanks
Hamish
Hi Hamish,
Thank you! You may be running into a SharePoint permission with the Run Only User of your flow. I believe the default is the user identity who started the flow will be used to access your connections (e.g. SharePoint). You can solve that in one of 2 ways.
a) Change your flow settings under ‘Run only users’ and have the SharePoint connection use your identity (instead of Provided by run-only user)
b) Grant user access to your SharePoint list
HTH,
Torben
Hi Torben
Fantastic guide, got everything working except the embeded excpression from section 10
You use the following expression:
base64(outputs(‘Get_file_content_-_Photo_Content’)?[‘body’])’
And i assume that Photo = Field name of the list and _Content is the content of this, is that correct?
My entry looks like this, since the field name in my list is Udl_x00e6_g1Bilag
base64(outputs(‘Get_file_content_-_Udl_x00e6_g1Bilag_Content’)?[‘body’])
However i get the following error:
Correct to include a valid reference to ‘Get_file_content_-_Udl_x00e6_g1Bilag_Content’ for the input parameter(s) of action ‘Compose’
Hi Danny,
Much appreciated. I see I wasn’t consistent in my image/naming convention so need to update it to make it better.
The outputs(‘Get_file_content_-_Udl_x00e6_g1Bilag_Content’)?[body] is referencing the internal Power Automate name it gave to your action ‘Get File Content’ where you pull the image. It takes on a naming convention based on what you name the action in Power Automate and is not tied to the SharePoint column name. In your example, I would expect to see an action titled ‘Get File Content – Udl_x0036_g1Bilag’ in your flow which probably doesn’t exist.
I recommend using the ‘Add dynamic content’ window to help build the expression and have Power Automate insert the action name for you. Once you are in the flow to insert the expression, click the Add Dynamic Content and select the Expression tab, in the fx textbox type ‘base64(‘ and it should show ‘base64()’ with the cursor placed between your parentheses. Keeping the cursor there, click on the ‘Dynamic Content’ tab and find your ‘Get File Content’ action that pulled the image, then click on ‘File Content’.
That should place the output reference similar to outputs(‘Get_file_content_-_Udl_x00e6_g1Bilag_Content’)?[body] but with the correct name between the parentheses and you can click OK.
Hope that gets you going!
Torben
Hi,
That didn’t seem to work.
One thing I’ve also noticed is that .jpeg images don’t pull through into the email, but .png images do when submitted by someone else.
I’ve tried changing step 11 to /jpeg instead of /png but that hasn’t worked.
Thanks for the help so far and any idea how to fix this?
Hamish
I tried embedding the image in outlook but it only shows in outlook online, failed to show in outlook app and gmail as well. I followed your steps to the letter, no mishaps but still. Please help.
Hi Mohamed,
You may be running into a security or size issue. Most mail clients will block embedded images, particularly from untrusted sources. However, if you are sending to other users within your Microsoft tenant it should be trusted and show up in the Outlook client. If it doesn’t, try and see if a smaller size image works and if that is the issue. For non-Microsoft tenant clients, the best option is to attach the image to the email. You can do that in two ways, as a regular attachment and as an inline attachment. I would start with trying as a regular attachment and see if that works. An inline attachment requires the ability to set a content-id to the attachment header and then reference it in your email body HTML using:
<img src='CID:[content-id name]' />I have unfortunately not been successful in getting the Power Automate Send an Email action to accept an inline attachment with a CID, which makes a regular attachment the only option for a flow.
Hopefully it gets you a little closer to what you need.
Torben
How to do if an item has multiple ‘image’ column?
Will I have branches of similar JSON parse for each ‘image’ column in the item?
Hi Arish,
Yes, if you have multiple image columns you will need to repeat steps 2 thru 10 for each column. You can utilize the following URL to get the list fields and find the names of the image columns you need (replacing to match your site/list)
https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/fieldsIf you need to do it many times, one suggestion is creating a sub-flow for the repeated steps and then create the loop in the main flow.
Best of luck!
Torben
Hey there!
I got this error in ‘get file content’ step:
{
“status”: 404,
“message”: “File not found\r\nclientRequestId: 842217dd-c3ae-4368-822f-6db5fa4d2346\r\nserviceRequestId: 842217dd-c3ae-4368-822f-6db5fa4d2346”
}
Hi Isaac,
That likely means the encoded Uri path to the file is incorrect. I suggest looking at the prior step output (Parse Json) to verify the server URL is coming across correctly and then look at the uri encoding and replacement functions to ensure they construct a valid url that points to the image file.
HTH,
Torben
Here’s the content of my second ParseJson:
-Input:
–Content:
{“type”:”thumbnail”,”fileName”:”102ccd97-bcef-4157-9a4b-623415f99b2c.jpeg”,”fieldName”:”Comprovante”,”serverUrl”:”https://simpress.sharepoint.com”,”serverRelativeUrl”:”/sites/TrocaSuprimentos-Residente/SiteAssets/Lists/be7bc389-60e6-4762-b05a-1131285629d9/102ccd97-bcef-4157-9a4b-623415f99b2c.jpeg”,”id”:”bd736713-670b-47ae-9431-7ce41fe9cd76″}
–Scheme:
{
“type”: “object”,
“properties”: {
“type”: {
“type”: “string”
},
“fileName”: {
“type”: “string”
},
“fieldName”: {
“type”: “string”
},
“serverUrl”: {
“type”: “string”
},
“serverRelativeUrl”: {
“type”: “string”
},
“id”: {
“type”: “string”
}
}
}
-Output:
–Body:
{
“type”: “thumbnail”,
“fileName”: “102ccd97-bcef-4157-9a4b-623415f99b2c.jpeg”,
“fieldName”: “Comprovante”,
“serverUrl”: “https://simpress.sharepoint.com”,
“serverRelativeUrl”: “/sites/TrocaSuprimentos-Residente/SiteAssets/Lists/be7bc389-60e6-4762-b05a-1131285629d9/102ccd97-bcef-4157-9a4b-623415f99b2c.jpeg”,
“id”: “bd736713-670b-47ae-9431-7ce41fe9cd76”
}
Hi Isaac,
That looks good! I would try the following:
1) Combine serverUrl and serverRelativeUrl in a browser and verify that the picture is displayed:
https://simpress.sharepoint.com/sites/TrocaSuprimentos-Residente/SiteAssets/Lists/be7bc389-60e6-4762-b05a-1131285629d9/102ccd97-bcef-4157-9a4b-623415f99b2c.jpeg2) Assuming #1 succeeds, in your ‘Get File Content’ action, select the File Identifier field and click the ‘add dynamic content’ link to bring up the dynamic content dialog
3) Click the ‘Expression’ tab and type
encodeUriComponent(Replace())4) Place the cursur in the middle of the parenthese of the
encodeUriComponent(Replace(CURSOR_HERE))function5) Click the ‘Dynamic content’ tab and select the ‘serverRelativeUrl’ field from the Parse Json output
6) Go back to the ‘Expression’ tab and type the following after your dynamic content:
,'/sites/TrocaSuprimentos-Residente, ''The full string in the dynamic content will be something similar to:
encodeUriComponent(replace(body('Parse_JSON PLUS UNIQUE NAME HERE')?['serverRelativeUrl'],'/sites/TrocaSuprimentos-Residente',''))7) Click OK and the Get File Content action should show a fx icon for the input.
Good luck!
Torben
Hi,
I have multiple images column. How can I get all those images and get into a table in send an email action?
Thanks
Hi Sachk,
If you have multiple image columns you will need to repeat steps 2 thru 10 for each column. You can utilize the following URL to get the list fields and find the names of the image columns you need (replacing to match your site/list)
https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes‘)/fields
If you need to do it many times, one suggestion is creating a sub-flow for the repeated steps and then create the loop in the main flow.
Best of luck!
Torben
Hi Torben,
Amazing Guide and works perfectly. My question however relates to environments. I have 3 environments- Development, Testing and Live. Is this still possible using environment variables, rather than using hardcode URLs?
Thanks!
Hi Ben,
Supporting multiple environments should be possible but it will need some changes to the current logic. You would likely want to create variables for holding the site/list id’s as they will change as you promote through your environments. It will then be a matter of constructing the URLs based on the changing IDs. The actual IDs could be parameters to the flow itself such that they are injected from a parent flow that contains the trigger per environment to make the main logic flow reusable across all environments. You will potentially also have to adjust/change some of the action boxes to an alternative that supports referencing the SharePoint location via a variable.
Best of luck!
Torben
Hi Torben,
First of All thanks for the awesome tutorial.
I have to get the exif info from sharePoint and populate it into crm ,
will this flow help me to get the exif info of photo present on sharePoint,
if yes then at which steps I need to make change?
Hi Bob,
Much appreciated! If the exif image is stored in an Image column in the SharePoint list then you should be able to retrieve it through Step 10. After step 10, you will need to add the extra logic to upload to CRM.
Best of luck,
Torben
Dear Mr Pedersen,
thank you very much for the great description above.
I tried to insert several images into the html text by repeating the steps above. But I did not have any success. Do you know a method how to do this?
Best regards
Orhan
Hi Orhan,
Thank you! Are you able to share any error information or describe what happens? One suggestion to troubleshoot is to verify that you have the image after step 10 by inserting the output into the email attachment content and verify it comes across as an attachment. If that succeeds, it’s likely part of step 11 for embedding it.
Hopefully that gets you further,
Torben
Thanks for the post. If I have 2 columns (Type images) Photo 1 & Photo 2. I want only to get photo 1 in the email not both photos. How I do this?
Never mind i figured it out. My question is how I will add this code before Parse JSON
Here’s a partial condition definition to show the expressions for getting to the Photo column and checking for null before the Parse JSON action.
“operationDefinition”: {
“type”: “If”,
“expression”: {
“equals”: [
“@outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[‘Photo’]”,
“@null”
]
},
“actions”: {
“NoImage”: {
“type”: “Compose”,
“inputs”: “NoImage”,
“runAfter”: {}
}
},
“runAfter”: {
“Send_an_HTTP_request_to_SharePoint”: [
“Succeeded”
]
},
“else”: {
“actions”: {
“Image”: {
“type”: “Compose”,
“inputs”: “Image”,
“runAfter”: {}
}
}
}
}
Hi Leila,
Great you got it working and thanks for sharing! If the columns are fixed, I believe you could also use the /fields call to get the internal column name for Photo 1 which could remove the condition by accessing the Photo 1 column directly.
https://torbenp.sharepoint.com/sites/BBQTrails/_api/lists/getbytitle('bbq%20recipes')/fieldsBest,
Torben
Excellent tutorial. I would like to ask a question: Is it possible to send the photo when a new item is created in sharepoint? What should I change in this example you made?
Thank you very much.
Hi Marcelo,
Thank you, appreciated! Yes, you should be able to change step 1 to a different trigger action such as “When an item is created” (SharePoint) and that will help provide the ID for the list item to use in later steps.
Hope that helps,
Thanks for the idea!
I would need to build a report using around 200 columns, mostly text but also images – would that be technically doable with this approach?
Hi Peter,
Yes, that should work, and the text columns will be a lot simpler as you should have them available in the trigger or get item action. You will only need to do the additional requests for the image column(s). If you find yourself with several images (3+) it may be beneficial to externalize the logic of getting the image in a sub-flow and then call that for each image in a primary flow.
Best of luck!
Torben
Hello Torben,
I found your tutorial very useful, thank you.
I have an issue though, instead of posting the Output of step 11 in an email (which works perfectly i tested), i want to put this in a Post message in a chat (Teams).
While the flow runs without error and the message is posted. The picture is not, neither linked nor embedded. In the Teams message only the “not able to display” picture icon is shown.
This happens in both desktop Teams as web Teams.
Right clicking on it does not give any option (like copy/paste etc).
Do you have an idea what this could be ?
Hi Kenneth,
Thank you, much appreciated! I was able to post an image to Teams using an Adaptive Card and linking. I created a Compose action that concatenated serverUrl and serverRelativeUrl that was retrieved from step 5 of parsing the photo json. This new compose action effectively provided a full Url that can be used in the adaptive card for linking.
I added a ‘Post card in a chat or channel’ action to the flow with the following adaptive card:
{
"$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
"type": "AdaptiveCard",
"version": "1.0",
"body": [
{
"type": "TextBlock",
"text": "@{outputs('Get_item')?['body/Title']}",
"id": "Title",
"size": "Large"
},
{
"type": "Image",
"url": "@{outputs('Compose_Image_Url')}"
}
],
"spacing": "None"
}
Here are a couple of pictures for reference of the result:
Best of luck!
Torben
Is there a way to incorporate this approach into the “Compose HTML Table” action? I am trying to pull a list of Standard Operating Procedure instructions from a SharePoint list, some have an attached image which I want to use as a Figure in the SOP. The body of the SOP is being sent to the requestor in the body of an email. I can pull text without issue but having an image populate a column in the table is causing me grief.
Hi Amanda,
Yes, with a little bit of html tag decoding/replacement it should be possible. Assuming you only have 1 SOP, one approach is to create the array for the table that includes the image column as below. (If you have a list of SOPs instead of a single record it will be a bit more challenging in order to pull the image for each SOP)
Create a Compose action and add the record with the image similar to Email example:
[
{
"Title": "@{outputs('Get_item')?['body/Title']}",
"Image": "<img src='data:image/png;base64,@{base64(outputs('Get_file_content')?['body'])}' width='150px' height='100px' />"
}
]
Use the output of the Compose action directly in your Create HTML Table action.
Then in your Email body, you will need to do a replace of the Create HTML Table output to convert the encoded tags <, >, and ‘.
replace(replace(replace(body('Create_HTML_table'),'<','<'),'>','>'),''','"')
Here are the actions for these 3 steps:
{
"id": "0e6bd03b-bdc4-49be-825d-d5259c904160",
"brandColor": "#8C6CFF",
"connectorDisplayName": "Data Operations",
"icon": "https://content.powerapps.com/resource/makerx/static/pauto/images/designeroperations/dataoperationedit.2c8a4d5e.png",
"isTrigger": false,
"operationName": "Compose_Body_ArrayV2",
"operationDefinition": {
"type": "Compose",
"inputs": [
{
"Title": "@{outputs('Get_item')?['body/Title']}",
"Image": "<img src='data:image/png;base64,@{base64(outputs('Get_file_content')?['body'])}' width='150px' height='100px' />"
}
]
}
}
{
"id": "0429bb32-3eb8-40a2-b80e-ba9b88921587",
"brandColor": "#8C6CFF",
"connectorDisplayName": "Data Operations",
"icon": "https://content.powerapps.com/resource/makerx/static/pauto/images/designeroperations/dataoperationedit.2c8a4d5e.png",
"isTrigger": false,
"operationName": "Create_HTML_table",
"operationDefinition": {
"type": "Table",
"inputs": {
"from": "@outputs('Compose_Body_Array')",
"format": "HTML"
}
}
}
{
"id": "6f6f08cf-cf35-4ec5-beef-942166a53809",
"brandColor": "#0078D4",
"connectorDisplayName": "Office 365 Outlook",
"icon": "https://connectoricons-prod.azureedge.net/u/laborbol/partial-builds/390724148/1.0.1641.3327/office365/icon.png",
"isTrigger": false,
"operationName": "Send_an_email_(V2)",
"operationDefinition": {
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_office365",
"operationId": "SendEmailV2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365"
},
"parameters": {
"emailMessage/To": "torben.pedersen@outlook.com",
"emailMessage/Subject": "Recipe for @{outputs('Get_item')?['body/Title']}",
"emailMessage/Body": "@{replace(replace(replace(body('Create_HTML_table'),'<','<'),'>','>'),''','\"')}",
"emailMessage/Importance": "Normal"
}
}
}
}
A few images that may help as well:
Best of luck!
Torben
Hi,
How can I know the ID number of the column that contains the image? In your case it indicates that it is “1”, but where do I see that information in my list?.
Thanks
Hi Francisco,
The number 1 is the first number assigned to the default ID column in the list. The ID column may be hidden in the default list view, but you can edit the view to make it appear. You can also use the Get Items action and then loop through each row in the list to get the IDs.
HTH,
Torben
Hello Torben,
I spent 3 hours trying to figure it out, changing everything i could and i cannot come up with solution. I have medium experience with power automate and thought maybe You could help me with my issue.
I am constantly getting an error and my flow fails :
{
“status”: 404,
“message”: “File not found\r\nclientRequestId: 010a1882-d534-47f5-8589-dc980ce788d4\r\nserviceRequestId: 010a1882-d534-47f5-8589-dc980ce788d4”
}
Here are screenshots of my flow:
https://prnt.sc/n1Sr7DUt8T1V
https://prnt.sc/VCzIzdcB1O-3
and file identifier for ‘Get file content’
encodeUriComponent(replace(body(‘ParseJSON_’)?[‘serverRelativeUrl’],’/sites/workflow’,”))
Hello everyone, does someone know why I do not get the URL relative parameter after doing the step 3 Send an HTTP Request to SharePoint? I only receive the fileName as an output so I does not creates the correct schema after.
This is the only output:
{“fileName”:”Reserved_ImageAttachment_[4]_[Foto][29]_[foto][3]_[641].jpg”}
The column is Picture (thumbnail), it is reading the correct item, everything seems to be correct but I only get that output
Thanks!
Hi there,
Did you ever figure this out?
I’m having the same issue
Thanks!
Hi Chirstie,
I have added an update to the post that includes how to transition to Attachments instead of Site Assets. Please see Step 10 with Attachments section for the updated expression if still interested.
Best,
Torben
Hi Axel,
Thanks for the feedback and I suspect you have worked through it by now. However, I have added an update to the post that includes how to transition to Attachments instead of Site Assets. Please see Step 10 with Attachments section for the updated expression if still interested.
Best,
Torben
Similar but different function. I am trying to get the image from this to also be added to a different sharepoint list. For example the list I’m using to get the image is my main list. When i create a new item from a different list. i want it to pull the image from this list and add it to that new item. Then send the photo in a email as shown in your example basically confirming the new item was created with the details.
I am struggling trying to figure out how to take a photo from one list and add it to another.
Thank you
Hello Torben,
I followed your tutorial and I must say it worked flawlessly and served me well, until recently. Presently, I am encountering an InvalidTemplate error whenever I try to process any newly uploaded photos. It seems other people encountered this same issue as well, as shown in the following Microsoft Power User Forum link: https://powerusers.microsoft.com/t5/Building-Power-Apps/SharePoint-Image-URL-quot-Reserved-Image-Attachment-quot/m-p/2497624
After conducting some research, I discovered that SharePoint Lists no longer uses the dedicated folder for attachments in the “Site Assets” folder. Instead, it uses its folder under the list, where pictures are also renamed. Although I assume that a change in the link reference should help, I discovered that the issue arises from having the old set of photos under one link and the new set under another.
Old link: …sharepoint.com/sites/xxxxxxteam/SiteAsset/Lists/2456e11b-0e32-45be-837d-xxxxxx5e8ef8/Picture.png
New link: …sharepoint.com/sites/xxxxxxteam/Lists/…List/Attachments/22/Reserved_ImageAttachment_[5][Photo][11][Picture][2]_[22].png
Although disabling attachments is one of the workarounds to this issue, it won’t work for me as we need the option of submitting attchements.
Have you encountered this issue? If so, do you have a solution that works with both the old and “new ways” of referencing the photos in the SP list?
Cheers
Hi Patrick,
Thanks for the feedback and I suspect you have worked through it by now. However, I have added an update to the post that includes how to transition to Attachments instead of Site Assets. Please see Step 10 with Attachments section for the updated expression if still interested.
Best,
Torben
I used your steps and till december, it was working completelu fine. but now when I tried to send HTTP request
using for eg:
_api/lists/getbytitle(‘Inventory%20Stock%20Images’)/items(1)/FrontLeft
I am only getting ‘fileName’ and not getting serverrelativeurl!
{
“d”: {
“FrontLeft”: “{\”fileName\”:\”Reserved_ImageAttachment_[9]_[FrontLeft][36]_[4f1fbb82-ed9a-4ee0-8c58-0f74026b5e8f][1]_[1].jpeg\”}”
}
}
Can you help me on this!
Thanks in advance!
Hi Deepthy,
Thanks for the feedback. I have added an update to the post that includes how to transition to Attachments instead of Site Assets. Please see Step 10 with Attachments section for the updated expression.
Good luck!
Torben
Thanks for the detailed write up. I keep getting an error that states ‘File not found’ each time it runs the ‘Get File Content’ step. Also, whenever I setup the JSON steps, both of my outputs look like this:
{
“d”: {
“Photo”: “{\”fileName\”:\”Reserved_ImageAttachment_[5]_[Photo][6]_[SCTest][2]_[10].jpg\”}”
}
}
I have tried to replicate by typing in what you have but I still end up with the same results. Any thoughts on this?
Hi Eric,
I recently updated the post to account for the new Attachments location instead of Site Assets. Based on your image name, you will need the adjusted logic in Step 10 with Attachments. You should be able to test your image path by changing the appropriate site/list information to match your list
https://[COMPANY].sharepoint.com/sites/[SITENAME]/Lists/[LISTNAME]/Attachments/[LISTID]/[5]_[Photo][6]_[SCTest][2]_[10].jpgBest,
Torben
What about the JSON steps? The issue I seem to be facing is that the file cannot be found.
I have several “photo” thumbnail columns for a list, I’m assuming that I would do steps 4 thru 9 for each image?
Hi Steven,
If you are getting multiple image columns, you will likely need to start at step 3 for each column. In step 3) you would need to replace the last part of the request “/Photo” with your respective thumbnail columns.
Hope that helps,
Torben
I was able to follow, however:
I get this in the email body:
{“fileName”:”car.png”,”serverRelativeUrl”:”/sites//SiteAssets/Lists/a857ff2f-acd8-432a-babb-1ed472fd5eeb/car.png”,”id”:”9daae25a-fb30-46ce-bc44-bdc8770e80e0″,”serverUrl”:””,”fieldName”:”Image1″,”type”:”thumbnail”,”fieldId”:”0a3cefd9-4bf5-4274-a386-8a7c1ae68b2a”}
not the image, what did I do wrong?
Hi Steven,
That looks like the output from Step 9) Parse JSON – Photo Json. I suggest testing the flow and seeing the outputs from each step if you might have selected an incorrect Compose result in a later action.
Best of luck,
Torben
I cannot get the BASE64 to work, and I have at least 10 or more Photos for a word document.
Same error issue:
Connect to include a valid reference to ‘Get_file_content_-_Parse-JSON-Photo1_Content’ for the input parameter(s) of action ‘Compose’
“”
my Get_File_Content is pointed to:
Parse JSON-Photo1JSON
Which Parse are we to use?
First Parse title is
Parse JSON-Photo1
Second Parse title is
Parse JSON-Photo1JSON
I even tried the column name “vehicleImage1”
None of these seem to work, and we have NOT upgraded to the Attachments item yet.
how do I get this to work?
Hi Steven,
If you are on step 11 for base64 encoding the file content, your input to the function should be from
Step 10 - Get File Content, which is the SharePoint action that reads the content of the file in SharePoint.Hope that helps but if you still get an error feel free to post back. It will help if you can mention the last step that succeeded and what step you are getting an error on with error message.
Good luck,
Torben
Hey torbenp,
I have having many issues trying to get beyond step 6. I believe the site I am using has the new ‘attachments’, however, following your guide, I get to step 6 and the output from the ‘compose’ action is ‘Click to download’ and takes me to a ‘untitled’ tab with no data.
The output from the step earlier, where you parse JSON is:
{
“d”: {
“Buyer_Signature”: “{\”fileName\”:\”Reserved_ImageAttachment_[15]_[Buyer_Signature][36]_[6ed76de5-6916-4800-970a-e7b19bc2d413][1]_[5].png\”}”
}
}
with the schema:
{
“type”: “object”,
“properties”: {
“d”: {
“type”: “object”,
“properties”: {
“Serial_Number_Image”: {
“type”: “string”
}
}
}
}
}
Any help would be appreciated! It annoys me so much how Microsoft makes simple things sooooo hard. I am a one man team for all M365 apps and want to try uplift simple processes in the business.
Thank you!
Hi Lachlan,
I would check if you may be crossing 2 columns in your list “Buyer_Signature” and “Serial_Number_Image”. Based on your result, you appear to be using the new Attachments location and I would expect the output and schema property value to match. Suspecting your intent may be Buyer_Signature in which case your schema should look similar to:
{“type”: “object”,
“properties”: {
“d”: {
“type”: “object”,
“properties”: {
“Buyer_Signature”: {
“type”: “string”
}
}
}
}
}
You can also check the attachments by using the following url (replacing the all cap stubs)
https://[COMPANY].sharepoint.com/sites/[SITENAME]/Lists/[LISTNAME]/Attachments/[LISTID]/Reserved_ImageAttachment_[15]_[Buyer_Signature][36]_[6ed76de5-6916-4800-970a-e7b19bc2d413][1]_[5].pngBest of luck!
Torben
What do we do when one or more of the image columns is null or blank, as I have 12 image columns. Some will have images, and some will not. Sometimes all will be blank.
How can we get this to run if there are nulls for the various columns?
Hi Steven,
You could use a condition to check for null in the response after getting the metadata in step 2. The condition would be something like:
"type": "If",
"expression": {
"equals": [
"@outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?['Photo']",
"@null"
]
}
HTH,
Torben
thank you for the code, sorry for the delay on this I have these questions:
however, confused on how to get this into a Condition?
Do I put this code before the HTTP Request or after the Get Item?
How do I put this into a condition?
I found an article that suggested to get the Schema to accept “null”
was to put this
“anyOf”: [
{ “type”: “string” },
{ “type”: “null” }
for the Second JSON I have
“type”: “object”,
“properties”: {
“Vehicle_x0020_2_x0”: {
“anyOf”: [
{
“type”: “string”
},
{
“type”: “null”
}
]
}
}
}
and it doesn’t throw an error.
However:
I now get an error on the get file content step:
encodeUriComponent(replace(body(‘Parse_JSON-Photo2JSON’)?[‘serverRelativeUrl’],’/sites/’,”))
Error:
Unable to process template language expressions in action ‘Get_file_content2’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘replace’ expects its first parameter ‘string’ to be a string. The provided value is of type ‘Null’. Please see https://aka.ms/logicexpressions#replace for usage details.’.
the issue is, there is an image in that column, so it should not be Null.
Any ideas as to why other than the caused the error:
“anyOf”: [
{ “type”: “string” },
{ “type”: “null” }
Hi Steven,
The replace function requires the string value to be present, e.g. not null, as you described. I would look through the test output from Parse_JSON-Photo2JSON and verify the serverRelativeUrl attribute is present with a value.
Best of luck,
Torben
it is in the schema, but there is no output value:
this is all I get for output value:
{
“d”: {
“Vehicle_x0020_2_x0”: null
}
}
Hi Steven,
It looks like Step 3 is not returning any value from your list. In that case I would go back to Step 2 and test it in your browser directly to see if it returns values from the column (assuming the list item has an image in the Vehicle 2 column). Your url should look something like:
https://YOURTENANTNAME.sharepoint.com/sites/YOURSITENAME/_api/lists/getbytitle('YOURSITENAME')/items(YOURLISTROWID)/YOURCOLUMNNAMEYOURCOLUMNNAMEshould be something like “Vehicle 2”.HTH,
Torben
Hi Torben, I followed the steps but when calling the link of the json origin it appears like this
{“fileName”:”Reserved_ImageAttachment_[22]_[Foto_x0020_certificado][36]_[e73f2d7f-2a7f-439a-a49e-931bba898f29][2]_[15].jpeg”}
instead of and all its set
“serverRelativeUrl”: “/sites/TrocaSuprimentos
Hello, I applied what was mentioned on February 24 and it worked, thank you very much.
encodeUriComponent(concat(‘/’,body(‘Get_item’)?[‘{Path}’],’Attachments/’,body(‘Get_item’)?[‘ID’],’/’,body(‘Parse_JSON_-_Photo_Json’)?[‘fileName’]))
Super!
Hello Eduardo,
I have struggled with that for sometime now. I still get an error message when I use the updated code. Did you change anything in that code?
Hello Torben,
Thanks for the detailed tutorial. I have followed through till I got to the part of capturing serverRelativeUrl which appears to be omitted maybe due to Microsoft’s new update. I realized you updated that on February 24. However, the update only addresses the File Indentifier in the “Get file content” section. I think you didn’t address the Schema in “Parse JSON – Photo Json” in the update. Since a successful running of “Compose” didn’t give any serverRelativeUrl, what should be in the Schema.
This is my Outputs from Compose
{“fileName”:”Reserved_ImageAttachment_[5]_[Image][64]_[pngtree-blue-bird-vector-or-color-illustration-png-image_2013004][1]_[1].jpg”}
Best,
Francis
Hi Francis,
Thanks for the feedback!
If you are using the original schema it should work for both pre-and-post Feb update (it should include attributes for both fileName and serverRelativeUrl). The old result will look similar to –
.
If you get this data, then following the steps and using the ‘serverRelativeUrl’ should work (old way).
The new way with the February update is to check if ‘serverRelativeUrl’ is empty/null. If so, only ‘fileName’ will be populated as is seen in the example output you posted. In that case, use the February update and ‘fileName’ field value instead of serverRelativeUrl.
I had a list that included both options so the way I worked around it was to add a Condition action after the Parse JSON and check if serverRelativeUrl was null. Then use the true/false branches to get the proper field value (fileName/serverRelativeUrl) into a variable I could use later for retrieving the file.
Hope that helps and best of luck!
Torben