Create flow from Excel and adding image/table

[Anonymous]

Hello Community!
I have encountered an issue.
So I need to be able to create a flow that would send a mass communication from Excel File.
So far I have been using commend "List rows present in tables":

This works just fine as sometimes I need to send emails to +100 recipients, and thanks to this solution I imply need to copy-paste addresses from another documents.

Yet, now my team came with a new obstacle.

There is a certain mass communication that requires us to embed either an image or a table in the body of the email (either I need to take a screenshot of the table or paste the table itself).

Until now I was trying to solve it using Dynamic content, where each if the table columns were separately introduced in the body of the email:

Yet, the final result was very disappointing as it was transformed in sheer text, and it simply did not look well.

Is there a way, to use earlier created excel file, to for instance create a column where you would paste images and then they can embed in the body of an email using dynamic content function, or doing it with a table? To put it simple: in my excel file I created column named "table", paste there screenshot with the table and then with Dynamic Content introduce this image in the body of my email in Send Email (V2) ?

Thank you in advance for your advices.
Kris

 

[Yutao]

Hey @Anonymous ,

Wondering if you would like to give Office Scripts a shot. The basic idea is:

• In Excel on the web, write some code to extract a table as base64-encoded image
• In Power Automate, add the "Run script" action to trigger this script against the workbook that contains the table you want to extract
• Add the send email action and embed the result from the "Run script" action in the email body

Here is the sample table in Excel on the web and the script in the Office Scripts code editor:

And here is the script code. Please note line#4 in the code below was change while I was posting this answer here because it complained that it contained invalid characters. So please adjust it based on the actual code in the above screenshot):

function main(workbook: ExcelScript.Workbook, sheetName: string, tableName: string): string
{
  var tableImage = workbook.getWorksheet(sheetName).getTable(tableName).getRange().getImage();
  var imageData = `data:image/png;base64,${tableImage}`;
  return imageData;
}

Here is the Power Automate flow that contains the "Run script" action and the send email action:

 

Please note in order to embed the image data in the email body, you need to switch the email body editor to "Code View":

And finally this is a sample email sent out by this flow:

If you're interested, here are a few other useful links about 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

Yutao

[Anonymous]

hi, i would like to ask how do i perform getImage() function for table with warp content in it?  here is the sample image i generated. 

this is the data from excel:

[Yutao]

@Anonymous - this looks like a bug on our side. I will need to do some investigation and get back to you later. Thanks for your patience!

[Anonymous]

Hello,
Thank you so much.
My sincere apologies, I haven't received any notification about your answer and in the end we came up with another solution which excluded sending image/table.
I have never heard about Office Scripts and I would need to read more about the tool, thank you for pointing that out!

[Yutao]

@Anonymous - sure thing! Please feel free to reach out to us if you have any questions on Office Scripts!

[iainoneill]

Hi there,

Awesome solution! I am afraid I had to make an visulaisation using conditional formatting and a drop down box and have run into problems.

I implemented a variation based on a named range, but it has gridlines, ignors borader formatting and won't show graduated cell shading.

Script:

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeName: string😞 string {

  var tableImage = workbook.getWorksheet(sheetName).getRange(rangeName).getImage();

  var imageData = `data:image/png;base64,${tableImage}`;

  return imageData;

}

Image I want:

Image I get:

Test flow:

Are there any script options to remove grid lines?

Are there any script options to respect boarder formatting?

Are there any script options to accept blended shading?

Any other throughts are welcome.

[Yutao]

Hello @iainoneill ,

I'm afraid gradient shading might not be supported at the moment.

But if you want to hide the grid lines, you can try:

const sheet = workbook.getWorksheet(sheetName);
sheet.setShowGridlines(false);

And can you describe a bit more what "border formatting" you were referring to?

BTW, I saw you also used some icons, which unfortunately are not supported, either. But I'm wondering if you can try replacing them with Emojis (used the same as text characters) in the cells. Of course they won't look as nice as the real picture icons but hopefully they can add a bit more visual elements to it. Here is an example of the rendered range image with grid lines hidden and Emojis used as icons.

[iainoneill]

Hi Yutao,

Some great Ideas!

By borders, I realised I had decided to turn boarders off and use shapes because I wanted rounded edges so I used the cell border formatting instead.

I also realised gradients probably were not supported yet, so I changed the colour scheme to Red Amber Green and came up with this which looks pretty smart.

I've taken your advice on using emojis and come op with this prototype for the images:

Found how to insert emojis ( in 2016 insert Icon will not do it, you need Windows key + . to bring up the picker, or write them in unicode I suppose).

I learnt that the emoji system is really buggy; for example, the question in a box for provision should be VR headset emoji and displays correctly on the sheet but not in the base64image. The Computer Emoji really wanted to look significantly different after I copied it from one cell and pasted it into another so I had to use a laptop.

It also looks like the known issue of text not wrapping properly has also reared it's ugly head for the organisation name (redacted).

Would GetShape(Key) help?

[Yutao]

@iainoneill 

Unfortunately I don't think getShape would work either.

Yes "WIN + . " is a nice trick to input emojis! Although the rendering of emojis could be quite tricky. I noticed the same emoji sometimes would look totally different on different platforms (web/phone/desktop), or even inside different apps on the same platform.

Back to the issues in Range.getImage() . We are aware of the rendering issues when running the script in Power Automate. But this kind of "headless" rendering (rendering content on the server side without opening it into an interactive client like the Excel desktop app or Excel Online) has always been a challenging problem. Finding the best solution to them may take a while. Thanks for your patience!

[mimart]

Hi Yutao, 

I followed your suggestions/steps above but get the following error:

How to work around this? 

Thanks

[Yutao]

@mimart - Based on the error message, I am guessing the script is probably trying to read the cells on a non-existent worksheet.

Sometimes this would happen with relative references (e.g., getActiveWorksheet) when running scripts in Power Automate. Normally you can workaround it by using absolute references like getWorksheet("Sheet1"). Please find more information here: https://docs.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#avoid-rel...

If this is not the case, please feel free to share your script so we can do some further troubleshooting.

[JPSNV]

Is there any update to this text wrap issue?

[iainoneill]

I am not sure. It is still in the documentation for Get image, but I got round it by making the length of the box as long as the longest possible organisation name.

[Danny288]

Hi Yutao,

May I ask about this error when running your script on Excel web?

I copied your script (exactly same code) but when I run a test script, it showed: 

Line 3: Workbook getWorksheet: The argument is invalid or missing or has an incorrect format.

Could you help me explain on this error and how to fix it? I'm new to this Automate.

Btw, thank you so much for your solution on this issue.

[Yutao]

Hello @Danny288 ,

Unlike running in Power Automate, when running the script in Excel for the web, it won't ask you to provide the arguments for the worksheet name and table name. So normally for testing purpose, you'll need to provide some default arguments for `sheetName` and `tableName`. Something like this:

function main(workbook: ExcelScript.Workbook, sheetName: string = "Sheet1", tableName: string = "Table2"): string
{
   // ...
}

Make sure both sheetName and tableName exist in your current workbook.

Later when running the script in Power Automate, the default values you defined in the function will automatically populate for you in the Run script action, but you can always override them with whatever makes sense in your actual scenario.

[Danny288]

Hi @Yutao ,

Thank you so much for the solution provided. I made it.
Btw, I have another question about reset the data in excel table daily.

I have to send this table as information to specified emails, but everyday I want to delete (reset) data in column 3 and 4 (Title and How to do) at 12pm which other people will go to this excel file to update on new information daily at 1pm.
Is there any way that I can reset this data table daily using Power Automate?
Thank you and I'm looking forward to hearing from you.

[Yutao]

@Danny288 -

In order for your flow to run daily at a certain time, you will need to use the "Recurrence" trigger:

And to clear the content of some cells, you can refer to the following script sample that clears the contents (keeping the format) of F9:F12:

function main(workbook: ExcelScript.Workbook) {
  workbook.getActiveWorksheet().getRange("F9:F12").clear(ExcelScript.ClearApplyTo.contents);
}

So if you want to reset the contents of some cells daily, you can create a flow with the Recurrence trigger and use a Run script action to run a piece of script that does Range.clear.

[Danny288]

Hi @Yutao,

Thank you very much for your help.

I made the flow base on your guide, and work perfectly.

[Danny288]

Hi @Yutao ,

Today I have another issue which I would like to ask for your help.
I'm still using the same file as above discussion, but my excel file have many sheets base on the day of week (Mon, Tue, Wed, Thu, Fri, Sat, Sun)

Is there any additional script that I can add to generate the table from corresponding sheet?
I'd like to run this script automatic base on the day of week.
e.g. If today is Mon, then I'd like to generate table from Mon sheet. Or today is Tue, I'll generate table from Tue sheet, etc.

Thank you.