Help with excel scripts and power automate for exporting excel charts to a word template

Question

Hi Community,

I have the following power automate (PA) flow I was able to build following partially the instructions from Awesome! Create Charts via Office Scripts - DamoBird365

I have adapted the excel script to export existing charts in excel (instead of creating them with the script) to a word template. My question will concern the script in the "run script" action and probably also the "compose action".

The script code is the following which comes from the microsoft doc to illustrate the getImage property. I have just adapted it to include a second chart:

The PA workflow works very well when a single image is used. However, I have not been able to include the second image in my workflow. I am not expert at coding so I just tested crating 2 "return" as you can see at the end of the previous script, but only the first image is really taken into account in the PA workflow.

The other thing I tested is returning an array :

but in this case I get an error when running the PA workflow:

I have also tried creating and interface at the end as I have seem in other examples of code in the microsoft doc :

but in this case I get another error :

Do you know how I can correctly return my two images from my script to then be able to use them in the rest of my PA workflow ?

Thanks for any help,

Julian

Answer 1

Hello there,

Sorry to hear you're experiencing issues with Excel and hope the following solution helps solve your issue:

1. Returning Multiple Images from the Script: 1.1)Returning multiple images from the script requires a slightly different approach. Instead of using return, you can store the images in an array and then return the array at the end of the script. 1.2)Here's an example of how you can modify your script to return an array of images:

function main(workbook: ExcelScript.Workbook) { let charts = []; let sheet = workbook.getWorksheet("Sheet1"); let chart1 = sheet.getCharts()[0]; // Assuming the first chart is located on the first sheet let chart2 = sheet.getCharts()[1]; // Assuming the second chart is located on the first sheet

// Export chart images and add them to the array charts.push(chart1.getImage()); charts.push(chart2.getImage());

return charts; }

2. Handling Multiple Images in Power Automate: 2.1)Once you've modified your script to return an array of images, you need to handle these images in your Power Automate flow. 2.2)You can use the "Compose" action in Power Automate to store the array of images returned by the script. 2.3)Then, you can iterate over the array to process each image individually. 2.4)Ensure that you're correctly accessing each image in the array within your flow to avoid any errors.
3. Error Handling: 3.1)If you're encountering errors in your Power Automate flow, carefully review the error messages to identify the root cause. 3.2)Make sure that your script is properly configured and returning the expected output. Check for any syntax errors or inconsistencies in your script code. 3.3)Test your flow step by step to isolate any issues and troubleshoot them effectively.

I hope the following solutions help solve your problem and should you have anymore problems/concerns please do not hesitate to contact me again.

Thanks,

Parth P.