Unanswered

Need Correct Syntax for Excel Script - Chart Image

(0) Share

Report

Posted on by lardo5150 Microsoft Employee

I have an excel file with a table and chart.

The plan is to have power automate come in, remove any rows in the table, and then add fresh data to the table, which feeds the chart.

From there, I can't get the wright syntax for the script in Excel to grab the chart image.

The chart is called Chart3

sheet is just sheet1

i am trying to take the sample code from MS, and remove anything dealing with updating the table or the table itself, but I keep running into errors.

I dont care about the table since Power Automate will update the table, I just need the image of the chart. the chart will be larger than this sample also.

can anyone assist? I am trying to modify the below sample from the MS documentation.

function main(workbook: ExcelScript.Workbook): ReportImages {
 // Recalculate the workbook to ensure all tables and charts are updated.
 workbook.getApplication().calculate(ExcelScript.CalculationType.full);
 
 // Get the data from the "InvoiceAmounts" table.
 let sheet1 = workbook.getWorksheet("Sheet1");
 const table = workbook.getWorksheet('InvoiceAmounts').getTables()[0];
 const rows = table.getRange().getTexts();

 // Get only the "Customer Name" and "Amount due" columns, then remove the "Total" row.
 const selectColumns = rows.map((row) => {
 return [row[2], row[5]];
 });
 table.setShowTotals(true);
 selectColumns.splice(selectColumns.length-1, 1);
 console.log(selectColumns);

 // Delete the "ChartSheet" worksheet if it's present, then recreate it.
 workbook.getWorksheet('ChartSheet')?.delete();
 const chartSheet = workbook.addWorksheet('ChartSheet');

 // Add the selected data to the new worksheet.
 const targetRange = chartSheet.getRange('A1').getResizedRange(selectColumns.length-1, selectColumns[0].length-1);
 targetRange.setValues(selectColumns);

 // Insert the chart on sheet 'ChartSheet' at cell "D1".
 let chart_2 = chartSheet.addChart(ExcelScript.ChartType.columnClustered, targetRange);
 chart_2.setPosition('D1');

 // Get images of the chart and table, then return them for a Power Automate flow.
 const chartImage = chart_2.getImage();
 const tableImage = table.getRange().getImage();
 return {chartImage, tableImage};
}

// The interface for table and chart images.
interface ReportImages {
 chartImage: string
 tableImage: string
}

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

lardo5150 Microsoft Employee on at

Like (0)

Report
function main(workbook: ExcelScript.Workbook ) { let selectedSheet = workbook.getWorksheet("Sheet1"); let chart = selectedSheet.getChart // set image variables let linechartimage = chart.getImage(); // return linechart and piechart images return [linechartimage] }

I tried this as well, but getimage is giving me an error.

Was this reply helpful? Yes No
lardo5150 Microsoft Employee on at

Like (0)

Report
function main(workbook: ExcelScript.Workbook ) { let selectedSheet = workbook.getWorksheet("Sheet1"); let linechart = selectedSheet.getChart("Chart3"); // set image variables let linechartimage = linechart.getImage(); // return linechart and piechart images return [linechartimage] }

I changed my script to the above, but now get the following error in PA:

The API 'excelonlinebusiness' returned an invalid response for workflow operation 'Run_script' of type 'OpenApiConnection'. Error details: 'The API operation 'RunScriptProd' requires the property 'body/result' to be of type 'Object' but is of type 'Array'.'

Was this reply helpful? Yes No
lardo5150 Microsoft Employee on at

Like (0)

Report

Tried this, but the email I get just shows a white box with an X

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
I know this was an old post, but I got it to work for anyone looking for an answer:

function main(workbook: ExcelScript.Workbook ) { workbook.getApplication().calculate(ExcelScript.CalculationType.full); let selectedSheet = workbook.getWorksheet("Sheet1"); let linechart = selectedSheet.getChart("Chart 1"); // set image variables let linechartimage = linechart.getImage(); // return linechart and piechart images return {linechartimage} }

Change "Sheet1" to the name of your sheet where the existing chart is located.

for this:
let linechart = selectedSheet.getChart("Chart 1");
Change "Chart 1" to the name of your chart.

linechartimage is the object that will be returned to Power Automate

Then the HTML code for the email being sent:

@{outputs('Run_script')?['body/result/linechartimage']}
^That's the excel code for power automate in text version.
Change 'Run_Script' to the name of the action you made that runs the script. If you're not someone that changes every action name, then don't worry about changing anything here, it's probably right.

I would have put it altogether in a single code but I can't figure out how to make it accept the HTML code with the power automate code in it.

Furthermore, if you want to download the image to say sharepoint or to email it as an attachment then this is the content of the file:
@{base64ToBinary(outputs('Run_script')?['body/result/linechartimage'])}

Was this reply helpful? Yes No