Add image in to excel table from base64

[Anonymous]

Hi,

Scenario

SPO list "Covid19_Compliance" has 3 column names 'PayLoad01', 'PayLoad02', 'PayLoad03'.

The data in these columns contains stringified JSON of user inputs which includes Texts, Camera captured images(base64).

SPO list is updated from PowerApps.

The Problem:

I am able to add all data from those columns into excel tables using Parse JSON & Excel Business connector.

But I am unaware how to add base64 encoded images into cell of excel table.

Also suggest me if I need to make changes at PowerApps level, like some alternatives instead of converting images to base64.

I need help 🙏

[fchopo]

Hello @Anonymous 

Could you try to convert your base64 image to binary and send it to excel by using dataUriToBinary() function?

Have a look at this thread: https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerApps-to-Flow-DataURIToBinary/td-p/156177

Hope it helps!

Ferran

[Anonymous]

Hi @fchopo ,

I have tried with the reference article you mentioned but it is giving following error-Excel Connector

InvalidTemplate. Unable to process template language expressions in action 'Add_a_row_into_a_table' inputs at line '1' and column '2784': 'The template language function 'dataUriToBinary' expects one parameter: the data URI that is to be converted to binary. The function was invoked with '0' parameters. Please see https://aka.ms/logicexpressions#dataUriToBinary for usage details.'.

 The function is

dataUriToBinary(items('Apply_to_each')['p5'])

[Anonymous]

With the help of JSON parse connector, successfully converted image data URI to binary and updated in cell of table.

But in cell it shows something like ?PNG

So should I conclude that images can't be inserted to excel via flows.

[Anonymous]

@ChristianAbata Hi, Can you help in this regard?

[ChristianAbata]

hi @Anonymous  shure. 

So if you want something like this

I'm afraid my friend is not possible with PA, because and image is not part of a excel row, and image is like a frame inside excel, and until today this is not suppported by PA.

Another scenario is this

inserting data in base64 into excel to use it in Power Apps and show images by reading the data in excel.

Please let me know what scenario you are needing.

[Anonymous]

@ChristianAbata 

First of all I would like to congratulate you for being elevated as Super User 🎉🎉🎉

When user fills and submits information from PowerApps, a new item will be created in SPO list.

Then a flow will be triggered which will first parse JSON from PayLoad1, PayLoad2, PayLoad3 of SPO list item.

Then excel's Add Rows connector will fill up three tables from above data in a existing excel templete.

Excel sheet has 3 Tables(Table1,Table2,Table3).

All 3 tables' column names are same- SrNo, Area, Point, Status, Remark, Image(Optional). (Total 6 columns & 3 tables)

Then an email will be sent attaching that excel file to preset user email addresses.

After that all rows from all 3 tables will be deleted using another excel connector.(To make it ready for future flow trigger as a template).

Everything is running fine except image inserting issue in excel excel sheet table.

The need is to insert image in excel's Image(Optional) column. This is required.

(Off the topic question: How can I create my signature so that it added automatically when creating/replying to a post in this community forum? just like like outlook has that feature)

Best Regards,

Ankit Shah

Surat-India

Twitter: https://twitter.com/AnkitShah55

[ChristianAbata · Accepted solution]

@Anonymous  thanks my friend, it's a pleasure to help here.

---

@Anonymous wrote:

The need is to insert image in excel's Image(Optional) column. This is required.

---

It's a shame but unfortunally we can't do this with PA this is not supported yet.

And to answe your second question see it's in profile setting

I don't know if this is available for all users but is there jejeje

[Anonymous]

@ChristianAbata 

Thanks friend for clarifying that, now I have no doubts and I can move forward.

What I am thinking to create a zip file containing that excel file and image files.

Every image file name will be copied to ImageName column in excel table.

Is this good practice? Or if you have idea?

(PS: I am convincing my senior to generate reports in PDF instead of excel, I can do it by using HTML)

[ChristianAbata]

@Anonymous  excellent way my friend. You can create .rar file and then in excel add the url to that file.

Pd: If you can convince your superior to use pdf is better, because as you said you can use HTML, I don't know if you saw this video but I give you anyway https://www.youtube.com/watch?v=2rfAQe1kK8M, is a Shane Video

[Anonymous]

@ChristianAbata Yes I have watched that video, in fact I follow all videos and updates from Shane, (He is like a guru)

Well, Thanks for all help friend 👋

[ChristianAbata]

@Anonymous  you are welcome my friend. Please consider to mark an answer as solutions to guide other over your question 😉

[Yutao · Accepted solution]

Hey friends,

Wondering if you'd like to try out the new "Run script" action of Excel Online (Business), where you can actually run Office Scripts to:

• Retrieve images from Excel as base64 encoded content then pass to the next action in Power Automate
• Or pass in some base64 encoded image content to create an image in Excel

For example, this is an action to add an image (encoded in base64) to cell "E8" on "Sheet1" in Excel:

You'll need to first create the "Add image" script in Excel Online. Here is one sample script that can be used here:

function main(workbook: ExcelScript.Workbook, sheetName: string, address: string, base64ImageString: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange(address);
  let image = sheet.addImage(base64ImageString);
  image.setTop(range.getTop());
  image.setLeft(range.getLeft());
}

Here are a few links that might be helpful to learn more about the new Run script action and Office Scripts:

• Office Scripts in Excel on the web (overview )
• Announcing support for Office Scripts in the Excel Online (Business) connector
• Run Office Scripts with Power Automate
• Call scripts from a manual Power Automate flow
• Pass data to scripts in an automatically-run Power Automate flow

And a few Office Scripts APIs that might be helpful here:

• Worksheet.addImage(base64ImageString)
• Shape.setTop(top)
• Shape.setLeft(left)

Hope this helps!

Yutao

[Ganeshkumar_R]

Guys,

Can you explain how we can add in this below format

@ChristianAbata  - Need to take the SharePoint attachment content and add that in excel table which is in OneDrive. I can able to take the attachment content using get attachment and get attachment content action from SharePoint. By passing the value to Update Row action is not working. Can anyone help this out it emergency 

Thanks

[Eulopezp]

Hi @Yutao ,

This is not working for me, I get the following error:

This is the excel file: 

Any ideas about what could be wrong?

Thanks in advance,

Eugenio.

[Eulopezp]

The problem was solved, it is needed to remove the "data:image/jpeg;base64,".

This is a very common problem in Power Apps, just in case someone get it 🙂

[RameshMukka]

@Eulopezp @Yutao 

The script works great and images are show in excel. However the images are not aligned in the excel rows and they overlapped over other. How do I fit the images in the column designated for the image. Is it even possible to fit in the image in the cell of image column?

[Yutao]

@RameshMukka - to properly align an image to a range, you will need to specify the image's top, left, width, and height to match with the respective top, left, width, and height of the target range.

For example, in order to achieve the same result in this screenshot:

You can write your script like this:

function main(workbook: ExcelScript.Workbook) {
  const base64ImageString1 = "iVBORw0KGgoAAAANSU...";
  const base64ImageString2 = "iVBORw0KGgoAAAANSU...";
  const sheet = workbook.getWorksheet("Sheet6");
  const range1 = sheet.getRange("B2");
  const image1 = sheet.addImage(base64ImageString1);
  image1.setTop(range1.getTop());
  image1.setLeft(range1.getLeft());
  image1.setWidth(range1.getWidth());
  image1.setHeight(range1.getHeight());
  image1.setPlacement(ExcelScript.Placement.twoCell);

  const range2 = sheet.getRange("B4:E6");
  const image2 = sheet.addImage(base64ImageString2);
  image2.setTop(range2.getTop());
  image2.setLeft(range2.getLeft());
  image2.setWidth(range2.getWidth());
  image2.setHeight(range2.getHeight());
  image2.setPlacement(ExcelScript.Placement.twoCell);
}

Using `ExcelScript.Placement.twoCell` can make sure Excel will automatically move and resize an image when you move and resize the range below it.

[LucaNeedHelp]

Hello, i get always a new image from the MS Forms in my OneDrive. Is it possible to get this images also in the excel? 

[MarkAnthony_]

Hi! Do you still have the codes and flow's steps?