Power Automate | Office Scripts | Send Charts via Email & Teams

[@BrettLangley]

Just brilliant, so many potential applications ! Just what I was looking for , thank you for sharing!

[@DamoBird365]

Cheers Brett,, would be great to hear what your use case is. 👍

[@ingridschiele1475]

Thats awesome! I`ve never get in touch with the excel script thing, but now I have several ideas how to use this for my projects. A big thank you for that :-)

[@MS-DianaMaganaNava]

Hi, great video! Sorry Is it possible do the same with chart created with pivot tables?

[@ngoctruong6208]

Thank you for the videos. May I ask why my outputs for Run Script step only to return to blank chart whose output only shows [[\"Name\",\"SalesAsVelue]"] and nothing else.I actually mimic your steps on my projects

[@DamoBird365]

Hi Ngoc, not very easy for me to work out. Take a look at the history output of a flow. You could share screenshots on the forum. powerusers.microsoft.com/

[@honeyVarBha]

How to setup the flow to post the tables in teams chaneel which received through outlook mail

[@ET-zf9qs]

Hi! I'm new to PowerAutomate, and I was wondering if there is a way to save these chart images into a SharePoint list as well as emailing the manager? Appreciate your help in advance, thank you.

[@DamoBird365]

You could save them as files to a document library or add them to a list as attachment or image 👍

[@ET-zf9qs]

@@DamoBird365 Perfect, thanks for sharing.

[@My1405NguyenThiThanh]

Wow, this is amazing. Thanks so much

[@cristiann.menajovsky9134]

Great video, thank you! As for the output expression @[7:36] I found out that you could use "outputs('Run_script').body.result[0]" it seems a little easier to understand than Outputs(‘Run_script’)?[‘body/result’]?[0]

[@DamoBird365]

Cool solution. Definitely many ways to achieve the same thing. You can also use first() if you are looking for the first ie 0 object. Also note, by removing the ? If the object doesn’t exist you’ll get an error. But by including ? in your expression will return a null.

[@FoxyGrandma915]

@@DamoBird365 I cant get Outputs(‘Run_script’)?[‘body/result’]?[0] to be accepted as a valid expression. It gives me an error everytime.

[@HarshavardhanGanesan]

Thanks for the proof of concept. In your example, the salesperson enters data exactly once a day, right? What if, in my case, they had to make multiple form entries in a day. What I wish to achieve is: to send Kevin (Manager - from your example) table with name of all his employees & their count of entries (number of form responses). Example - DamoBird365 : 1, XYZ : 0 etc. The table should also include the name of the employees with 0 entries for the day. Is that possible?

[@DamoBird365]

Have you looked at pivoting the data and summing by salesperson? I believe that this would be possible and you can experiment with the script recording tool.

[@don3482]

May I ask if there is a way to create multiple table images and send each table image to different people? I was able to create the table images for different managers, but not sure how to extract/filter and embed the image result in the email body...Thank you!

[@DamoBird365]

The easiest method might be to run an apply to each on the same script and email action with dynamic values for email and image. Each loop could generate the different image based on your filter,email or other requirements? It would also be possible to achieve this in a single script, but I’m not a programmer, so I would need time to investigate if I’m honest.

[@don3482]

@@DamoBird365 thank you!

[@badriruslan8797]

Hi, it me again, thanks for your content, I able to send and email containing images of chart & tables. however i would like to go one step further, is there any technique i can use to filter some content in the table using Script prior get the image of the table. currently my code only capture the whole table.

[@DamoBird365]

Use the recording tool and record the steps you describe. Add that to your script. You might need to recreate the chart based on the new filter, I am not sure how dynamic it all is and haven’t tested this before.

[@badriruslan8797]

@@DamoBird365 noted. The chart not required to be recreated. I only want to hide some unwanted rows.

[@inigosanchez-ostizchivite5441]

Thanks for your video. Is it possible to run the script from excel itself and create an outlook object and then send it. As it used to be in VBA. ?? I do not need the job part of power automate. Do you know if I can skip it ??

[@DamoBird365]

You can run the office script via a button in Excel. support.microsoft.com/en-us/office/-create-a-button-to-run-an-office-script-c686b2e7-62bc-4d76-9752-c94cdb625766

[@kristianwolthers8227]

Great video! I am wondering whether it is possible to populate these chart images into a Word file or to somehow create a PDF with the chart images?

[@DamoBird365]

Absolutely! You can save these image files and use them wherever you please. I've not done a video on this previously but you could embed them into HTML and convert them to PDF, or indeed convert the Excel File to PDF without premium actions. Word, I haven't tried without premium for images, albeit I have for text, watch here kzbin.info/www/bejne/gYesiHeIn7V-i80

[@kristianwolthers8227]

@@DamoBird365 Thanks a lot. I will try your suggestion to embed them into HTML and convert them to PDF :)

[@DamoBird365]

@@kristianwolthers8227 let me know how you get on, I may well do a video on this at some point.

[@PauloManinha]

Hi, what if you want to send one email per employee to each manager? Thank you.

[@DamoBird365]

Something like Transform Excel Data into Manager's Dream: Power Automate Solution kzbin.info/www/bejne/pqitaGage6qnerM

[@eswarprasadamirthi8982]

Getting Error while running the flow can anyone pls help? Error "Filter applyValuesFilter: The argument is invalid or missing or has an incorrect format."

[@DamoBird365]

I would suggest you try the forum powerusers.microsoft.com/

[@ShinichiroSatoshi]

Can you write the Excel script to automatically format a chart based on the way you want it to look?

[@DamoBird365]

There are limitations but the best way to explore this is to use the recording tool. Create your data, record a script and play it back on the same data. You will then be able to realise what is possible.

[@ShinichiroSatoshi]

@@DamoBird365 thank you for the quick response!

[@badriruslan8797]

Hi:-) thanks for your latest reply. I got the rows n column i need. However some of the content have warp text. When the script capture the image, i all the text appear in one rows (warp disable). I already try to enable warp before getimage. But still same result. Do you have any advise?

[@DamoBird365]

I’m afraid I don’t know the answer to this. Maybe try the Microsoft forum? powerusers.microsoft.com/

[@phlaviofranca]

Hello, @DamoBird365! Hope you're doing well! Apparently, your graphics are larger than 28KB. They are (lol)? I ask because I would like to know how you can send them via Microsoft Teams. I have a problem sending a small print from cell A1 to E14. Could you confirm this information? Thank you very much!

[@DamoBird365]

I’ve used the base64 encoding received back from the office script. It’s been a while since I’ve run this. Can you save the base64 to file? What are you experiencing?

[@phlaviofranca]

Hello again@@DamoBird365 ! Yes, I used base64. I did the test by reducing the range to A1:E5 and I was able to send the adaptive card without any problems. I checked the size of my json file through VS code and codebeautify when the range is A1:E15 and it actually exceeds 28KB. Thank you for your attention and your time!

[@seanconnors3356]

Can you access other workbooks besides the workbook you're currently in?

[@DamoBird365]

The script runs on the workbook, and allows you to access multiple sheets. A PowerAutomate Flow can run the script against multiple workbooks though. What's your use case?

[@seanconnors3356]

@@DamoBird365 want to use a data set where the information is collaborated on across multiple workbooks and keep it uniform.

[@DamoBird365]

@@seanconnors3356 I would suggest you log a case here powerusers.microsoft.com/ with more detail and hopefully the community can come up with the best option based on your use case and example.

[@GD-nl8fo]

This is great! You helped me so much with another video (Using excel to create planner tasks) and this is another tool I'd like to definitely use. My question is around potential functionality. In this example, you create the charts then capture the image. Is there a way to have charts already set up in the excel file and capture those images? I'm assuming the reason you create the charts is because the charts won't update on a regular cadence, hence why you need recreate it every time the data changes. Is this accurate?

[@DamoBird365]

That’s a good question. I don’t actually know if the chart updates or not. I would be interested to have you report back on your findings.

[@GD-nl8fo]

@@DamoBird365 I think i have it almost complete--I'm getting an error at my last step. The flow runs fine when I don't have the email containing the script outputs. When I add the output, I keep getting 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'.'--Any suggestions?

[@DamoBird365]

@@GD-nl8fo so an object is {} a single “row of data” if you like. An array [] is multiple rows or objects. The error suggests it wants a row or object but you’ve passed an array in f objects. You can get the first object with first(). Take a look here kzbin.info/www/bejne/fWS0Yoqrj7mKq7s

[@GD-nl8fo]

@@DamoBird365 Thanks! I'll take a look and try to troubleshoot. Thanks, again

[@mrbrushyy.]

Super helpful!

[@vikram8634]

Hello, Using the same process, but in gmail getting corrupt/blank image. Could you plz suggest how to fix it?

[@DamoBird365]

So many possibilities without knowing more info. I would suggest you start looking through the history output. Does the output from the script look good? Have you got it working on outlook?

[@JoshAutomates]

Can you save the chart as image in a SharePoint list? Basically I want to create an Office script that will create a chart, and that chart will be displayed in a power app. Is this feasible?

[@DamoBird365]

I believe it would be possible, but equally PowerApps has native charts?

[@JoshAutomates]

@@DamoBird365 Yes. But its not versatile enough based on the charts that I want to make. Combo Charts. But here's what I'm trying to do. 1. Flow to Update an Excel Online Table 2. Run a Script to create Excel Graph Online 3. Send the chart to a SharePoint List 4. Display that Chart as Image in Power Automate But there's a missing link : How do I get that chart and save it as an image in the SharePoint??

[@DamoBird365]

You would need to save the image to a file, use the add attachment action and optionally delete the image file.

[@JoshAutomates]

@@DamoBird365 What action can I use to do that? Sorry to ask you too many questions. Can't sleep thinking about how to execute this

[@DamoBird365]

It looks like you don’t need to save and delete a file. The “add attachment” action takes a list name, item id, file name and file content as input and so you should be able to construct valid file content using the output from the script.

[@badriruslan8797]

hi, how do i get an image of existing charts in the Excel sheets and send it to email?

[@DamoBird365]

Try getChart to retrieve a chart based on its name as follows: // Get an existing chart named "ColumnChart". let chart = selectedSheet.getChart("ColumnChart");

[@badriruslan8797]

@@DamoBird365 already inserted the code, however its return "Runtime error: Line 32: Cannot read property 'getChart' of undefined". please advise.

[@DamoBird365]

Here is a basic script with the active worksheet defined: function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let chart = selectedSheet.getChart("Chart 1"); let ChartImage = chart.getImage(); console.log(ChartImage) } Check your Chart Name is "Chart 1" as follows www.teachucomp.com/name-an-embedded-chart-in-excel-instructions

[@badriruslan8797]

@@DamoBird365 thanks for the reply. it works perfectly.

[@geralddahl9159]

@@DamoBird365 is the basic script you posted written as JavaScript?

[@NasenErlan]

How can I do it with SharePoint List?

[@DamoBird365]

Do you mean create a chart from data in a list? You could in theory load list data to an excel file? I’ve a video on that method using office scripts too.

[@NasenErlan]

@@DamoBird365 yes. I have SharePoint list, that has Person and his numbers (results). I need to send it as chart to email of this Person. Please advise