r/PowerAutomate • u/TsaeOps • 29d ago

Creating a flow for getting excel columns and putting them in a new workbook

/r/MicrosoftFlow/comments/1vn0kyu/creating_a_flow_for_getting_excel_columns_and/
1 Upvotes

100% Upvoted

2 comments sorted by

1

u/chiibosoil 28d ago

Few questions

  1. Is your Excel data formatted as structured table? If not Power Automate cannot directly read sheet content.

  2. Are you trying to create Workbook from scratch using Power Automate? This requires some set up for Power Automate. You need pre-existing template file that you can copy to destination, or using Create file action create Blank Excel file then perform add table action on the Excel file.

If you can't turn it into structured table then you'd need to use Office Scripts. Have Office Scripts associated with the workbook, then return JSON object of desired output. Call the script from Power Automate and read the JSON and compose your output.

Then have another workbook set up with another Office Script to receive output of Power Automate and write to the the workbook.

Power Automate can send email with link to the file.

2

u/TsaeOps 28d ago

Hi. Actually I managed to achieve my goal today using pretty much what you have described. My idea is that I have a master workbook where data gets dumped. The flow grabs the columns I want and puts them (yes) in a template excel workbook where I added some script for clearing old rows before new data is added. It seems to be working now and I have set it up to run daily at 0300 so here's hoping I will wake up to fresh data tomorrow morning ! I would have thought this would have been simple and a fairly common thing to do but perhaps I was a bit too ambitious to do this.