Create a new Excel File in Power Automate and dynamically populate with a Table and Rows

[@DamoBird365]

Want to see how to import SharePoint List or Dataverse Table data into a new excel file quickly and efficiently? 6 seconds for 1000 lines! kzbin.info/www/bejne/apibfZtmnN94pMU

[@cgbeale]

Thank you for such an excellent video. I was lost on how to dynamically create a new file, until I watched both of your videos. I love the pace and how you clearly explain everything especially showing the expressions.

[@DamoBird365]

Cheers Chad. Appreciate your feedback. Do let me know if there is anything else I could cover in future videos 👍

[@DamoBird365]

I have an updated version (Feb 2024) with both Graph API and Excel Office Scripts here 👉kzbin.info/www/bejne/naXPnJ-wnpuAesk 👀

[@jherschel]

Thanks! I got stuck trying to copy data from one excel table to another and creating a new file helped me through it! Also, I had 26 columns and didn't realize if you dynamically entered the row table Name in add row to table, I was able to copy ALL rows using the row field and current item!

[@xandros_74]

I can't believe it took me a long time to find this video. This is what I was looking for! Amazing. Thank you very much! This was helpful

[@DamoBird365]

Cheers Alessandro. This means a lot! If you’re ever looking for a solution, drop me a dm as I’m always looking for ideas. 🙏

[@Inno94709]

Thanks!

[@DamoBird365]

Cheers Jay, appreciated 👍

[@Inno94709]

@@DamoBird365 as a seasoned developer in vba, pad, pacf andl more, it's truly astonishing to me that this simple thing, create a dynamic xls file and then update it, is so ridiculously difficult. Without your vid, I was reduced to ludicrous measures to replicate this behavior...

[@DamoBird365]

@@Inno94709 it’s worth pointing out this video too 👉 you can create an excellent file using Rest API Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@Inno94709]

@DamoBird365 great! One other problem I can't solve, I do an oData GET with application/json, can dump results to email and see query is working, but PA filter, parse json all fail because it says ala 'must has array but u have object' 😞 . So I can't dump GET results to Excel file (which is the whole point)

[@DamoBird365]

@jaymumford3222 check your history. Have you used {} instead of []?

[@Kralnor]

Microsoft really should provide better support for creating a new Excel file, but your workaround is a great idea!

[@amaniramahi]

Cant thank you enough! I've been struggling with creating a table after exporting data from powerBI dataset using (Run query against a dataset). all tutorials were pushing to export it as a CSV file. this video is very clear and the jem i was looking for! Thank you

[@DamoBird365]

Thanks 🙏 Amani. Good luck with your automation.

[@XavierAponte-eq4hu]

This video helped me do exactly what I needed to! the flow was failing when I used dynamic values from power automate, when I used your item expression in its place it worked perfectly. Thank you

[@DamoBird365]

No problem Xavier, thanks for letting me know and glad to be of help 👍

[@mamadykonate8253]

Great Job! This just saved me from having a lot headache with Excel Thank you

[@rickcoterie5700]

I think you are the best teacher out there! Great stuff! Exactly what I needed as one of the steps. Going to look at your other videos for next steps. My needs and looking forward to finishing this fun one - 1 - Run Stored SQL Procedure 2 - Create Excel File(s) from the file 3 - Filter\Compare against SharePoint list that has the ID and email names of those needing the report 4 - Create charts required and add to template 4 - Create Word File 5 - Save as PDF 6 - Email that file to the user from SP list 😁

[@anhduong8484]

Thank you for sharing this video. It was very helpful.

[@yiubili1]

Great video, will you do an video that exactly show how to draw data from excel and paste to other excel with condition please? Would be great if the table range are also flexible as well...

[@quang110289]

this video save a lot of time for me. thank you

[@edjk6278]

I love your work mate. Keep teaching and I will keep learning...

[@DamoBird365]

Thank you.

[@nlsfrck2633]

Thank you very much ... that is brilliant. Have you ever been running into problems which seem as if the excel file is flushed/saved only after the flow ends? My flow (1) creates the excel, (2) populates it with some data from a sharepoint list, and (3) sends and email with the excel attached. The excel attachment from the email is not complete and even delays, check-out, etc. didn't help. It looks as even the excel on the sharepoint is completed only at the end of the flow.

[@cesarramos5597]

Gracias, gracias, gracias. Thank you so much. I was desperate because I didn't know how to fill out the content section of the file

[@apodostroiec]

Solución simple, funcional y elegante. Excelentemente explicado. Y mejor instrumentado. Muchas, muchas gracias.

[@DamoBird365]

Thank you 😍

[@btoz6237]

Thank you for this great video .. nice and clear steps

[@minzu9159]

Thank you so much! This tutorial video has solved my problem. Thanks heaps again!!

[@DamoBird365]

Amazing 🤩 good luck with the rest of your automation 👍

[@SaifUlislam-zq2qn]

Thanks God. Finally found this video. Great content. Actually I am doing little bit more advance things like I have big file for creating data into SharePoint list but it takes age's. So I was looking for something that splits the big excel file into smaller files then call the flow for each loop and providing the newly created excel files. Which reduced my creating data into SharePoint super fast

[@MitchTorres]

Thanks as always for these perfect demo :) this is just what I wanted to do :) Keep it coming :)

[@VaibhavSharma86]

Too good. Really helpful. Cheers.

[@JonDoesFlow]

Nice one, very helpful, thank you Damo !

[@elrevesyelderecho]

10:31 Hi. I have a small issue in this step. My data source is a Run Query against a PBI dataset. I have a columns with the entrydate. In all previous steps, I can see in the Output that the matching correctly the data ( Created table fine. Matching Header with data, etc., ) but in this steps all columns in the Excel table are populated with the exception of my date column. Any ideas with the Excel table doesn't populate this date column? Thanks

[@DamoBird365]

Did you write your own expression? If there is a typo, it will return null. Remove the ? And it will error if the field/key is not found in your array.

[@elrevesyelderecho]

@@DamoBird365 Thanks. You were right! It was the ? after the Item()

[@nandnandan4198]

exactly what I needed. Keep your work up and running.

[@ArkWorksCNC]

very helpful and to the point. Thank you

[@DamoBird365]

You might want to also check out kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@NouradineMahamatArabi]

Thank you! This video is all I've wanted for the past 2 weeks. But I don't know what you put in the first compose (filecontent) because I can't create a table and my excel file is unusable. I want to export the information from my PowerBI report to my Excel file

[@DamoBird365]

Ahhh... well you want to check out kzbin.info/www/bejne/naXPnJ-wnpuAesk and see two options and a SharePoint rest API to create an empty file.

[@aT0000my]

Thanks Damien, very helpful!

[@sebastianmartinezarenas3696]

Thank you for your content!

[@SuperGameBros0610]

A really nice tutorial! I'm quite new in power automate, and tutorials like this one helps a bunch. I have a question for you, that I can't seem to find the answer to anywhere. When automatically creating a new excel fille, like you have showed, is there a way where the data I want to populate the file with, can be dynamic? Say weekly sales reports, exported as an excel file. I can't quite seem to work it out.

[@DamoBird365]

I've two more recent videos that might help you with this kzbin.info/www/bejne/naXPnJ-wnpuAesk or kzbin.info/www/bejne/faHEYZ9_ash0qpo. Basically Office Scripts or Graph API would allow you to add in bulk and dynamically.

[@SuperGameBros0610]

@@DamoBird365 I'll have a look. Thank you so much!

[@prajaktadoshi9855]

Hey, You are awesome. thank you so so much... I was working on one of my company project and I was facing error while creating file even though i used dynamic field as file name. But your idea of adding unique file name of date, time formula really really helped...

[@DamoBird365]

Thank you for letting me know. I really appreciate the feedback. If there are any ideas for content, please drop me a message below.

[@prajaktadoshi9855]

@@DamoBird365 hey hi thank you for your response. About content, If you can create a flow for power app, to pass value from "one form" to "another" For example i want to pass customer_ID in all my "power app forms" which is connected to different share point list....

[@DamoBird365]

@@prajaktadoshi9855 do you want to drop me a bit more detail / description to [e-mail] with a use case? Thanks

[@prajaktadoshi9855]

@@DamoBird365 yes sure will do

[@dmstar101]

Hi, This was a brilliant video and It has helped me heaps to get around the create Excel dilemma I was having. I guess that this type of flow can take the excel data and populate a Json script to be uploaded into an API?

[@DamoBird365]

Hey Darren, most things are possible. Ai don’t understand your need to create an excel file first to the create a json array? I do have lots of videos on json and select though 😉

[@ngunghuong6938]

Thank you so much. This is really helpful for me.

[@vijaymunavalli335]

Well explained🎉

[@sudarshanreddy6969]

Thank you so much damobird, I am looking for exactly the same topic

[@financiallyfreegenz]

Thanks for this video. Very helpful in my projects

[@DamoBird365]

Make sure you check out the latest video pinned in comments too. Would love to hear from you with other use cases.

[@wigglwrrm]

Massively helpful. Thanks!

[@testingaccount-x9b]

Thank you very much for creating this

[@lakshyasharma1715]

Thanks this was a huge help

[@lucapiu]

Thank you so much, just what I needed!

[@Sudharaaj]

Its almost an year, but super helpful. Thank you!

[@weizhang429]

Thanks for this. Exactly what I'm looking for. spend hours trying to figure this out!

[@AmySavage-z3g]

Thank you so much, very helpful. Question- instead of using a blank excel book in get file content, could you use a populated or formatted excel file? I'm trying to have it create file from a template and then populate data based on the power automate workflow and trigger. Any help would be appreciated!

[@DamoBird365]

Absolutely. Get the file content, create file as a copy and then populate the template. I’ve a few examples on my channel. Check out kzbin.info/www/bejne/eYupfZ2vbc14rpIsi=03qZYhH3E6DG4xjZ

[@notonurnelly]

Really useful Damo, this use case with a few tweaks will be really helpful on a project I am about to start.

[@rhodrithomas6040]

This has been super helpful, thanks! Trying to build a flow for creating distribution lists for documents in a SharePoint library and this is getting me a long way down the line!

[@johnfromireland7551]

Fantastic! Creating an Excel file using Power automate. I think there are size limitations to take into account, however. 10MB? One thing that would be super cool, in the future, is if Microsoft would create a "Add to Data model" action. So, for large data files (millions of records) the Flow would add that huge array/data source into an Excel or Power BI Data Model. I suppose the question, here, is "Does Power Automate have Action(s) or methods that allow for very large data sets to be handled?"

[@DamoBird365]

That’s interesting John. I’ve read that there is an inter action limit of 100MB but MS have extended this to 1GB with “chunking”. I’ve read of 25MB limits here docs.microsoft.com/en-us/connectors/excelonlinebusiness/ albeit in the above proof of concept I am not pushing these by any means, I’ll admit.

[@rupikayelamarthi7219]

Such a great video, the content I'm looking for, would like to see how to fetch the email ids from the excel created like above and send a mail to all the recipients...

[@DamoBird365]

Hi Rupika, glad to hear this has helped. If the emails are already part of your data, you could use that to create a semi colon separated list of email addresses. Check out kzbin.info/www/bejne/fWS0Yoqrj7mKq7s

[@videobewerker111]

Beautifull video Damo. How can I send excel to a contractor with expired requests and provide 2 columns (1 yes/no extend and if 2) 'Yes' until witch duedate? If I get the excelfile back I want auto-update the records in the request-list and re-approve the extended requests. Is that possible?

[@DamoBird365]

It’s possible, definitely. But it’s open to going wrong, if someone changes the document layout for instance. But you could do this when an email with attachment arrives.

[@florian1054]

thank you for your video, really appreciate it !

[@JONODO12]

Hi there, this is a brilliant video- the only issue I am having is when I want to introduce Environment Variables. The Document Library input seems to be the issue; I selected the "Documents" option from the dropdown; after testing the flow (in my dev env) it changes from "Documents" to a drive id. When I have saved the drive ID to an EV the flow fails. Oddly, the drive ID for my Documents folder seems to be the exact same in my Dev and Test env despite referencing different SharePoint sites and lists. Any ideas/help?

[@DamoBird365]

Could you use SharePoint rest to get the drive id? _api/v2.0/drives?

[@powerbi9048]

Great! How to create excel file when new data in SharePoint List created ? I need to create excel file one by one of data in SharePoint List. Thank you.

[@DamoBird365]

Hi, can you share with me a bit more detail to [e-mail]

[@JacobPierce-e3z]

Hi @DamoBird365! This video was really helpful. Thank you! Quick question, if I wanted to attach the contents of the file to an email, how would I do that? I've been unable to figure out what the file content should be. I've tried a lot of the dynamic content, but I get an error that that the file cannot be opened. One of the dynamic contents worked, but it was the blank file. Any help is appreciated!

[@DamoBird365]

You’ll need to get the file content. In my latest demo of something similar, I show you how kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@naderhayek3310]

Excellent video - very detailed and easy to follow - thank you for taking the time to share

[@franknielsen3219]

Brilliant video.😀😀😀

[@Gwill1983]

Hi. I love these videos and am learning so much about automation. One thing that I am struggling following this is how to format the new excel file / table in the flow? Some of my data requires a wider column, but I can’t work out how to automate any adjustments to column width etc.

[@DamoBird365]

You would need to look at office scripts if you want to automate the width of the columns.

[@vincentDev84]

thanks for the video .. one question: concurrent in apply to each is not applicable for inserting row in ordered sort right?

[@DamoBird365]

You could be right. Have you seen office scripts or graph? Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@seemunwong9364]

Hi Damion, thank you for sharing the video. It has been a great help for me, a newbie in using Power Automate. Unfortunately, my column names are excessively long and the flow failed with 'bad request'. Any chance I can use an excel template containing the headers (with long names) at the step of 'Get File Content' and paste its JSON under 'Compose'? Is there a different to go about this problem that you can suggest please? Thanks again.

[@DamoBird365]

You could just get the file content and create a new file straight from that 👍

[@seemunwong9364]

@@DamoBird365 Thanks. If that's the case, how do I map the column names with the 'Get Items' values?

[@eduardvaldivia]

It help me a lot, thank you very much!

[@DamoBird365]

Cheers Eduard.

[@zineddine]

Great video and I like especially the way you used Json to create and populate the excel file. To become good in Power Automate, should JSON be my priority of things that I should learn ?

[@DamoBird365]

Definitely. I have a video on a brief overview of json. But it is worth being able to visualise json, understand arrays and object.

[@jackfreudenheim4846]

Very useful, Thanks! Is there a way to populate a bunch of rows, for example from the output of a sql query, where I use a different action? I'm guessing this Add Row action is going to open and close the excel file with each insert. I'd use an Apply to Each but would prefer it not open/close the file for each operation if possible. Thanks!

[@DamoBird365]

Have you seen or heard of office scripts? This method is far more efficient kzbin.info/www/bejne/apibfZtmnN94pMU

[@jackrealdrummer]

@@DamoBird365 I just watched that video and it's brilliant, thank you! I've never done scripting in Excel but I had the pleasure of being one of the early adopters of Visual Basic way back in the day, so this is all quite familiar. In my case I'm calling a stored proc in Sql Server, but it looks like your dataverse example will provide me with what I need. I'm going to try it now. Thanks again!

[@DamoBird365]

@@jackrealdrummer nice one. Let me know how you get on. The Microsoft docs are very good for office scripts.

[@lfish894]

Damien as always is so helpful!

[@2007pradipta]

hi I am creating same with Solution and Environment variables. At 3:40 where you are putting the Document library name I putting the Environment variable for it ..I getting error like The provided drive id appears to be malformed, or does not represent a valid drive.. It because it does not take the GUID of Library instead it take some id of the drive... any help to make the Document library dynamic ?

[@DamoBird365]

Hey Pradipta, I’ve not done env variables with this particular solution. Could you use the SharePoint rest api and a filter, as I do in my latest video kzbin.info/www/bejne/pmOZpH9ppq2gnNU

[@SiyamandRashid]

You are aStar , can i use get file action to create an excel file from a template which i used several formatting and colors in it?

[@DamoBird365]

Don’t see why not 👍

[@DamoBird365]

Or write an excel script that formats the file for you?

[@SiyamandRashid]

@@DamoBird365 thank you very much for you always support, yes I tried what you explained in your video on my template and it worked! Any resource you recommend on how to write the script for learning purpose ?

[@DamoBird365]

@@SiyamandRashid here are some options kzbin.info/www/bejne/gmeanmutnMlnd80 or Office Scripts kzbin.info/aero/PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM

[@SiyamandRashid]

@@DamoBird365 million thanks

[@croti5391]

Great video! I have to extract data from an aggregate excel file on a daily basis. I wonder if this flow can help.

[@DamoBird365]

This might help too kzbin.info/www/bejne/apibfZtmnN94pMU

[@jl1399]

Would this method include having live links from powerapps going to excel?

[@ericabrunetti6043]

It is ok also to copy records from sharepoint list to anew excel file?

[@DamoBird365]

Sure 👌 this works with any data source.

[@KarthikeyanBalasubramanian]

A great video indeed... Thank you so much.. I am looking for a similar solution, but to update the existing table with new records without appending (deleting old news and updating with the new ones) and maintain the same file name. How to achieve it ?

[@DamoBird365]

You could explore office scripts and graph - both of which would allow you to batch update an excel file. I would suggest you also ask on the forum to get more ideas. I have videos on both Graph API and Office Scripts to populate an excel file fast.

[@MohammadAli-ts2ev]

Thank you for the informative video. In Power automate/Flow, how can we set the value of cell A2 of a table (of Excel Online for Business) to a variable?

[@DamoBird365]

Take a look at Excel Office Script Tutorial - Populate an Excel File Dynamically from Power Automate kzbin.info/www/bejne/h2iqfYSkq699fJI

[@Con-zt4vm]

Hi! I followed your example. It was going smoothly until the Add Rows into a Table action. It won't let me save my flow because if an "Invalid JSON" error. Apparently, It does not accept an array enclosed with just open brackets, but it lets me save when enclosing it with closed brackets and open brackets or [{}]. Will it matter?

[@HotamSingh-m2c]

@DamoBird365 am facing one issue when concurrency control is enable . Lets suppose there are 100 records and Degree of Parallelism is set to 30 then in my excel there are only 30 rows . If I change the Degree of Parallelism to 50 then I will get 50 records. Seems like it is due to some locking while inserting in multiple threads. Have you also face this issue ?

[@DamoBird365]

The main problem with concurrent Excel actions is that each one can lock for up to 6 minutes. You are better looking at my videos on using Graph API or Office Scripts to populate an excel file fast 👍

[@murti82]

Thank you DamoBird. I prepared an excel template with some formatting (font size, column with, wrap text etc.) and applied your "Split workbook into multiple worksheets based on key column" video. The result is great, I get splitted workbooks with nice formatting. However a template should not include a table, power automate does not like that :) because powerautomate cannot paste one table onto another.

[@DamoBird365]

You might want to explore office scripts, I have a few videos but here’s an idea learn.microsoft.com/en-us/office/dev/scripts/resources/samples/copy-tables-combine

[@shubhamgupta-sd9kd]

Great video!! Can we change the table layout from blue to any different color using the same template. Do we have to add table with different layout in template file, I tried and got error like "cannot overlap table"? Any advice! Thanks

[@DamoBird365]

You can change the colour 💯but it sounds like you are creating a table on top of another table, which isn’t possible.

[@shubhamgupta-sd9kd]

​@@DamoBird365 yes so how to approach it could you please share some details how to do so?

[@bc67]

This looks super helpful, but I'm receiving an, "Enter a valid JSON" error in the compose action that contains the file content. Have you run across this before?

[@DamoBird365]

It will be a typo. If you validate your json, you will probably find an error. You can try something like jsonformatter.org/ if you’re ok sharing the data.

[@bc67]

@@DamoBird365Thanks for your quick response! That's strange. I copied it directly from the file content. Parse error on line 4: ...AFgDNBQAAI2YAAAAA"}{ "$content-type": ----------------------^ Expecting 'EOF', '}', ',', ']', got '{' Thanks in advance if you can quickly identify the problem.

[@anupamtamrakar8630]

8:41 In the same scenareo, my external data source is a Form processing model, that is extracting tables from pdf. Any guidance on that? Thanks.

[@anupamtamrakar8630]

Detail: In the new compose action, I'm creating a jason array similar to what you have as an external data source. But my compose action is wrapped in ApplytoEach with names and valus are dynamic values from Form processing model. Its throwing "Bad Request" Error on the "Create file" block.

[@DamoBird365]

You’ll have to share me a screen shot. [e-mail].

[@GregDolan]

Another great video 👍. Please keep making these, I think there very helpful 😀

[@llssssll]

Hi Damion, thanks so much for your video! I found this can be applied with Update a row but I stuck at this, do you have any video for the update a row? My flow tested OK with the first few steps, created a new file with existing file content, thanks for your advise in advance.

[@DamoBird365]

Hi Sulamita, I don't understand your question. This flow creates a new excel file, do you wish to update an existing row in the new file? This might be a question for the forum as I don't have a video for that scenario powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@Ratheesh_G_Nambiar]

Hi, My requirement is to automate the process of handling emails with CSV attachments from SFDC. Upon receiving such an email, the system should automatically create a new Excel file based on the attachment's content. Subsequently, a new worksheet should be generated within the existing Excel file, and the data from the newly created file should be populated into this worksheet. Any assistance with implementing this requirement would be greatly appreciated.

[@snooki9]

Thanks man !

[@PhilHughes-qh4nv]

Hi. Do you have a video going into more detail on how to do this when your original data is already in an excel file (and you want to create a new excel containing your filtered data from the first one) ? You mention it at 12 mins and 25 secs but didn't go into any detail on how to do it and I got lost. Thanks in advance.

[@DamoBird365]

I’ve done a split workbook into multiple worksheets and also a newer video on populating an excel file here kzbin.info/www/bejne/naXPnJ-wnpuAesk. What’s your exact use case? Feel free to reach out with more details.

[@ShyamYempelly]

Hai Dam, Can you please tell me from where you copied the $content type in compose (file content)

[@DamoBird365]

I’ve got an easier method for you to try: Create Excel File in SharePoint via Power Automate Cloud Flow kzbin.info/www/bejne/naOZY6aKZ76qmpo

[@ShyamYempelly]

@ currently i am receiving an email with the attachment in the CSV format but i wanted to convert it as xlsx format using power automate I have gone through your videos but somewhat i was missing something Could you help me, how exactly we can covert csv to xlsx format without loosing content when new email triggers with an attachment.

[@DamoBird365]

@ office scripts? kzbin.info/www/bejne/b3uZhZuGjKyUfZIsi=LPq5-SYi89kTmesy

[@ErikNordblad-pt3to]

Hello! Thanks a lot for making this video, I am sort of a rookie in Power Automate but this helped me a lot. My case is we have an excelfile contatining data regarding 13 different customers. I have been able to extract all rows from one of the customers and presenting them in a new excel file. How would you go about dealing with the rest of the rows? The goal is to create 13 new excelfiles containing only rows of data from a specifik customers. Is there a smarter way than adding 13 different filter arrays, one for each customer after an "List rows present in a table"-action? And then 13 different Create a table-actions?

[@DamoBird365]

You can populate files fast with this method : Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@DamoBird365]

And combine it with something a bit like Transform Excel Data into HTML Table and Email: Power Automate #PowerAutomate #Outlook kzbin.info/www/bejne/pqitaGage6qnerM

[@GabrielAGilO]

ca you do this using one drive instead of share point, the setting in share points are different than in OneDrive

[@mariushernandez6112]

Your guide is great! I'm just struggling with a couple things I hope you can help me with: 1. I'm making this flow inside a loop where I create different Excel files and send them to different recipients through e-mail... the issue is that although the file is generated and filled with all the rows that it should contain the file is being sent with only the first row 2. It's awful to see the Excel table created with fixed column width, is there a way to autofit the column width so it can be better at sight?

[@DamoBird365]

Not sure about your single row. With regards to the auto fit columns, there is a function. Try using the recording tool and copy the code 👍

[@Scott_OD]

Hi Damien, is it possible to get a JSON array from an excel file using the Sharepoint HTTP request? Thanks

[@DamoBird365]

You could use office scripts or graph api. A bit like kzbin.info/www/bejne/i6TTmnyin5ubibM. Did you see my demo on populating via both? kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@Scott_OD]

@DamoBird365 yes I had seen those thanks, but they didn't cover getting a JSON array using the Sharepoimt HTTP request

[@mattkoch3117]

I cant seem to get it to accept thecarray i typed into the "add a row" field. Is there anything that could be tripping it up? It looks the same as yours with the colon and the quotes

[@berouk1955]

Impressive video! I'm trying to figure our how to extract form data from surveys that are emailed and have the results put in an excel table with a predefined set of columns. I got it to work only if all the text fields in the survey are filled in. If a few are not required and not filled in, there's no data element, null string or empty separator in the array which messes up the order of the elements when the row is appended to the excel table. Any help would be appreciated!

[@DamoBird365]

Might be one to post on the forum powerusers.microsoft.com/ as it will be easier to share further details and screenshots. It’s not clear what format the form data is but most logic is possible.

[@tranbangviet]

Thank you so much !

[@DamoBird365]

No problem at all.

[@yojie120]

Thank you so much for this tutorial. I do ran into a bind, whereby the data that i am trying to add into the Excel column is a multi-line text column from a Sharepoint list. It currently only shows the first line but not the rest. Would you have any recommended steps on how to move the full content of this column? Thank you in advance for your reply :)

[@DamoBird365]

I’ve not tried to be honest. Might be worth logging a call on the support forum powerusers.microsoft.com/

[@pgk511]

Great video thanks, We got a requirement to email dataverse table as excel on daily basis. Do you have any thoughts on how we can accomplish it. Any help is much appreciated

[@DamoBird365]

This is actually a request on the forum, powerusers.microsoft.com/t5/Power-Apps-Ideas/Export-Dataverse-view-to-Excel-using-Power-Automate/idi-p/858333. I would suggest you use the Dataverse list rows action and whilst I used add rows to a table, it’s actually more efficient to use excel scripts. I might do a video if you think it’s worthwhile?

[@DamoBird365]

Hi GK, take a look at my most recent video kzbin.info/www/bejne/apibfZtmnN94pMU 6 seconds for 1000 Dataverse rows. Please let me know how you get on.

[@pgk511]

Thank you so much that was pretty awesome. But do you have excel script somewhere that I can copy. Thanks again

[@pgk511]

Also based on this video I came up with something to export data to Excel and send as an email attachment. I am happy to share

[@DamoBird365]

@@pgk511 you can drop me details to [e-mail], share your idea but also let me know which script you want a copy of. I am yet to blog about it.

[@thiagolopes1132]

Thank you so much for this amazing tutorial. I learned a lot. I would like your help if is possible. I used to receive lots of excel files from different people. They usually post these files into a SharePoint library and it is always in the same layout/format, of course with different information. I always need to pick those files and copy the information into a new standard layout. I'm trying to figure out I way, using as a trigger “When a file is created in the folder”, Power Automate read this file content, identify a table, get all data, and after that start to populate the new template. I believe this tutorial provides a halfway to do that. So Where do you recommend I can study and find a way to do what I described? Thank you so much again. Sorry about my English. It is not so good.

[@DamoBird365]

Hi Thiago. What you’re describing definitely sounds feasible. There is an action to get table(s) in an excel file. I’m happy to help if you want to drop me a message to [e-mail].

[@claytonstephenson3985]

This was helpful, but wondering if this will break when Microsoft makes an update to the excel file type. If they do, I guess I will have to update the content in "compose".

[@DamoBird365]

It’s always a possibility. Equally a native action could be made available but for now it doesn’t exist.

[@DamoBird365]

Try kzbin.info/www/bejne/kJbNpKeshc55kNk

[@ankitsharma1943]

In my scenario i need to add the title in the first row in the merged cell and then add the data. how we will do that?

[@44bthoj44]

Awesome presentation.

[@geralddahl9159]

Is it possible for Power Automate to transpose table values from the source? ie attendance xlsx has student, date and p or a (present/absent) value. I'm envisioning a final sheet with each student's name descending down the left column and a series of 1 - 31 column headers stretching left to right across the top such that the corresponding a/p value might rest in the placeholder created as a result of transposing the original data. Thank you for any thoughts, tips you or others might wish to share. Cheers to you from Canada.

[@DamoBird365]

I’ve got a basic demo for a vertical html table here near the end kzbin.info/www/bejne/goLYnnaIZqdgp5I and that technique could be used to transpose the data. You could do it direct with an excel office script though.

[@geralddahl9159]

Thank you. I will re-watch and keep this as well as your other tips in mind. Much appreciated.

[@marshapage1739]

I have a file with 4 tables and I am filtering each by the same key value and adding the rows to a new Excel file. Flow is taking over 2 hours to run. If I run concurrently, I get files ending up in the wrong folders. One of the tables has 3,000 rows/17 columns. Is there a way I can make this faster/avoid looping through each line row?

[@DamoBird365]

I’m assuming that you are adding rows to a new file? Take a look at graph or office scripts. I’ve videos on both methods.

[@ColinBaconLabs]

Excellent :-)

[@adrianojnn]

What should I use in the compose of the content file?

[@DamoBird365]

It’s the file content which you can get from get file content of a blank file. I demo a method using an api here Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@adrianojnn]

@@DamoBird365 I see... and how can I get excel content file to sendan e-mail with this file?

[@venkatakrishnagolla4056]

Thank you, Damien. Video was very helpful. But I see the limit of Apply to each control is 5000. Can you please explain how to add more than 5000 rows into Excel table? I am using Paging Cookie method to get more than 500 rows and when used apply to each control, it is adding the last iteration of Do until. How can I overcome this to add more than 5000 rows?

[@DamoBird365]

It will become very inefficient as the size increases. You could try and explore office scripts kzbin.info/www/bejne/apibfZtmnN94pMU and send the data as an array.

[@jeanegerber9775]

Damion! You are freaken brilliant!! Thank you so much for this. I am using your approach and tweaked it with the trigger "When a new Form is submitted" - getting the response details and dynamically opening up an Excel file for each response and populating the data submitted into a table. I however keep on running into the same problem and cant figure out how to resolve the error = "Another table already exists with the name 'DemoForm Submission' or the name is invalid. Error Code 400". Any guidance on this would be super. have been at it for 3 days now (face in palm)

[@DamoBird365]

Does your template include the table name? Or do you create it once the file has been created? Are you using ‘enter custom value’ on the add row to table action?

[@jeanegerber9775]

@@DamoBird365 Thanks so much for replying! My Flow at the moment is: [Trigger] When a response is submitted -> Get Response Details -> {Compose} your trick to input the File Content -> Create File with Filename using FormatDateTime and File Content linked to the Compose Output -> Create Table {File* linked to dynamic SharePoint Id - as you had it} -. Add row to table {As you had it - the only difference is my row data is populated from the dynamic Forms submission data. I would like the workbook to generate upon submission (Doing this) and then to also generate a table and populate it with the form inputs for further analysis. I made my table name a dynamic input [Company Name] which info is received from the form submitted... I have also just made it a normal text as you did but makes no difference as I still get an error. THANK YOU SO MUCH