cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos

Create a Flow to insert a chart from excel into the body of an email

I am looking for a flow that I can take a chart from an excel file in my one drive or sharepoint and share within a weekly email set to a recurrence event.

Status: New
Comments
Microsoft

I'm wondering if you can accomplish this by using the new "Run script" action of the Excel Online (Business) connector. This action allows you to execute Office Scripts against the specified Excel workbook.

 

There are an API called Chart.getImage in Office Scripts that can return the base64-encoded image data. Then in Power Automate, you can pass that image data from the "Run script" action to a send email action, where you can embed that chart image in the email HTML body.

 

Below is one sample flow that uses this "Run script" action to get images of a chart or table from a workbook and pass to an email:

ChartImage.png

 

 

Please note you'll need to first write up the necessary script in Excel Online.

 

Here are a few links that might be helpful:
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

Helper I

Hi @Yutao @Yutao

Can you elaborate on the script on excel online to get the chart. i already have the chart on excel online and just need to grab that chart to my flow and send email regularly - because the chart has updated automatically. thanks.

Microsoft

Hi @yustinus79 ,

 

Firstly to learn more about the new Office Scripts for Excel, please start from here:

https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel

 

For the particular ask (getting an image of the chart), here is one possible piece of script that can do the job:

 

 

 

 

function main(
  workbook: ExcelScript.Workbook,
  sheetName: string = "Sheet1",
  chartName: string = "Chart 1",
  chartWidth: number = 512): string {
    const chart = workbook.getWorksheet(sheetName).getChart(chartName);
    const chartImage = chart.getImage(chartWidth);
    const result = `data:image/png;base64,${chartImage}`;
    return result;
}

 

 

 

(note: Please replace `data:image...` with `data : image...` (no spaces). The post editor somehow changed my original script code).

 

As we can see, this script grabs a chart name "Chart 1" from the worksheet "Sheet1" and exports its image in the form of embedded PNG picture encoded in base64, which can be later used in an email. (Please note, the worksheet name, chart name, and the width the image should be passed in as parameters to the script. Here "Sheet1", "Chart 1", and "512" are just default values for those parameters.)

 

Once you've created and saved it through the Office Scripts Code Editor in Excel Online, you should be able to see it from the "Script" drop down list in the "Run Script" action of Excel Online (Business) connector in Power Automate.

 

Then you can do something similar as shown in the screenshot in my previous post - specify the workbook, name of the worksheet, name of the chart, width of the exported chart image, and pass the exported chart image in base64 format to the send email action.

Frequent Visitor

Hi @Yutao 

 

Thank you for the code above.  I have added and run the script which appears to work however when I run the flow the image is coming through as text.  As per below example.

 

data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAGUAAAAu………

 

I have looked for solutions however all are VBA based or similar.

 

Are you able to provide any solutions or guidance on how to edit the script to produce an image?

 

Thank you

 

CB

 

Microsoft

Hello @ClintRB ,

 

The post editor somehow didn't like colons in my sample script code and insists to change it into `:`. So when you type in or paste the script into the editor, please make sure to replace `data:image...` with `data : image...` (no spaces):

Yutao_0-1607821629191.png

 

And on the Power Automate side, while you're writing the email body, please make sure to compose in the HTML source mode (click on the Code View button).

Yutao_1-1607822054824.png

 

You'll need to use an "img" tag in the HTML source and insert the Run script result as the "src" of it:

Yutao_2-1607822315622.png

 

Hope this helps!

 

Yutao

New Member

Hi

Thanks for this! Is there a way to improve the quality of the image? My chart is hard to read.

Microsoft

@Aleejack -

 

You can try tweaking the chartWidth parameter of the script. My sample script uses 512 as the default, but you can experiment with some larger numbers like 1000, 1500, etc. You can also try tweaking the settings of the chart itself, e.g. title font size, axis font size, etc.

 

Yutao

Frequent Visitor

Hi @Yutao 

 

Thank you for the solution with the code button.  The emails are working great now.

 

CB