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!
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.
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!
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
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! czcams.com/video/4g8Lh0gzEnc/video.html
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
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...
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 😁
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
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.
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?"
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.
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.
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 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....
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 ?
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.
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
@DamoBird365 This is a perfect solution for a problem of mine. I used this method and implemented it for collecting monthly data through forms (recurring). Instead of Array, I just used Forms output. whole data is processed and stored in SharePoint. Thanks a lot for the insight! keep it coming :)
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 :)
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
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?
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 😉
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!
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 czcams.com/video/CZFLjy8hEx0/video.htmlsi=03qZYhH3E6DG4xjZ
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?
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
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.
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 ideas@damobird365.com.
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!
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.
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.
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
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?
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 czcams.com/video/gtlklzi6MDg/video.html
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!
Hi, I see that your video is showing inserted text file with the data as output. Would like to know if I'm to copy data from my data in another excel table that has more than 1K line item how can I input them in the output?
Hi, you could use list rows and then select to create an array in the correct shape, before passing that to compose. Office scripts and Graph api enable you to load data into excel too. Each with their own pros/cons and learning.
Thank you so much for this video!!! I have a question - is it possible to format the excel table automatically? For example, is it possible to highlight a few columns? Or merge and center several cells?
Yes, with office scripts. I’ve a few videos on the concept but you can use the recording tool to create a script that will do the formatting for you. What’s your use case?
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?
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 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!
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 ?
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.
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.
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.
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.
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...
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 czcams.com/video/G3Q1WuZTWuY/video.html
@@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...
@@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 czcams.com/video/gtlklzi6MDg/video.html
@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 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 ?
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 👍
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?
Hello while adding the table name from a variable or from the created file it does not take the name for the table. It takes a default name like "Table1" how to fix it. please guide
Great video! I am trying to do a similar thing, the only difference is from an excel I am trying to create multiple excels based on some filtered data. My flow is running fine till except the last line where I am adding row to the table. Its failing with the error "message": "The request entity's media type 'text/plain' is not supported for this resource." Any idea what this can be about?
Because you are creating a file dynamically, I wonder if you need to supply an object {} to the rows parameter? If it’s identifying the object as text, maybe there’s a formatting issue?
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?
It will become very inefficient as the size increases. You could try and explore office scripts czcams.com/video/4g8Lh0gzEnc/video.html and send the data as an array.
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
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.
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)
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?
@@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
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 ?
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 czcams.com/video/p26sL3qHmfs/video.html
Hello Damien, Appreciate your tutorials! They are so clear and helpful. I'm trying to create a flow that automatically (1) creates a new subfolder (2) Copy an excel template into that subfolder (3) populates this excel file from another. I really can't figure get the first part of the process to work. Any hints?
If you watch my latest video, I show you how to create folders using PowerAutomate. czcams.com/video/ySAZqtC6mXg/video.html. Hope this helps, please let me know.
@@DamoBird365 Thank you for your prompt reply. I am not trying to create a folder, but a new subfolder that goes into an existing subfolder that is already located in an existing folder (if that makes sense?). I want the trigger to be when a row is selected. Thank you for any advice.
I do folders and subfolders in the video 😉. You can try the forum powerusers.microsoft.com/ or drop me an email ideas@damobird365.com and I’ll try to help.
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.
I’ve got a basic demo for a vertical html table here near the end czcams.com/video/LQumCR1B-q0/video.html and that technique could be used to transpose the data. You could do it direct with an excel office script though.
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?
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.
@@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.
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
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.
Hi Damo, I am replicating your script but am getting an error "The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Compose')' is of type 'String' - Getting this in the "Apply to each". The result must be a valid array." Im not sure what to try. can you suggest anything?
You can’t easily create an expression using this method alone. You could use office script or graph api. Otherwise sum the values in power automate and add a final row. I’m planning a video on using graph api for excel updates. In the next few weeks.
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.
I’ve done a split workbook into multiple worksheets and also a newer video on populating an excel file here czcams.com/video/gtlklzi6MDg/video.html. What’s your exact use case? Feel free to reach out with more details.
I have an updated version (Feb 2024) with both Graph API and Excel Office Scripts here 👉czcams.com/video/gtlklzi6MDg/video.html 👀
Excellent video - very detailed and easy to follow - thank you for taking the time to share
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!
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
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. 🙏
Thanks as always for these perfect demo :) this is just what I wanted to do :) Keep it coming :)
exactly what I needed. Keep your work up and running.
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.
Cheers Chad. Appreciate your feedback. Do let me know if there is anything else I could cover in future videos 👍
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!
Thank you for sharing this video. It was very helpful.
Really useful Damo, this use case with a few tweaks will be really helpful on a project I am about to start.
Another great video 👍. Please keep making these, I think there very helpful 😀
Great Job!
This just saved me from having a lot headache with Excel
Thank you
Thanks for this. Exactly what I'm looking for. spend hours trying to figure this out!
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
Thanks 🙏 Amani. Good luck with your automation.
Thank you so much, just what I needed!
Nice one, very helpful, thank you Damo !
Microsoft really should provide better support for creating a new Excel file, but your workaround is a great idea!
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! czcams.com/video/4g8Lh0gzEnc/video.html
Thank you for your content!
Thanks Damien, very helpful!
I love your work mate. Keep teaching and I will keep learning...
Thank you.
Thank you so much! This tutorial video has solved my problem. Thanks heaps again!!
Amazing 🤩 good luck with the rest of your automation 👍
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
No problem Xavier, thanks for letting me know and glad to be of help 👍
Massively helpful. Thanks!
Thank you so much. This is really helpful for me.
Too good. Really helpful. Cheers.
Damien as always is so helpful!
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...
Thanks this was a huge help
this video save a lot of time for me. thank you
very helpful and to the point. Thank you
You might want to also check out
czcams.com/video/gtlklzi6MDg/video.html
Thank you so much damobird, I am looking for exactly the same topic
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
😁
thank you for your video, really appreciate it !
Thank you very much for creating this
Thanks for this video. Very helpful in my projects
Make sure you check out the latest video pinned in comments too. Would love to hear from you with other use cases.
Solución simple, funcional y elegante. Excelentemente explicado. Y mejor instrumentado. Muchas, muchas gracias.
Thank you 😍
Well explained🎉
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
Its almost an year, but super helpful. Thank you!
Awesome presentation.
very useful case ， thanks a lot
It help me a lot, thank you very much!
Cheers Eduard.
Brilliant video.😀😀😀
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.
Thanks man !
Thank you so much !
No problem at all.
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?"
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.
Excellent :-)
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.
You would need to look at office scripts if you want to automate the width of the columns.
goood! thanks!
Great !!!
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...
Thank you for letting me know. I really appreciate the feedback. If there are any ideas for content, please drop me a message below.
@@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....
@@prajaktadoshi9855 do you want to drop me a bit more detail / description to ideas@damobird365.com with a use case? Thanks
@@DamoBird365 yes sure will do
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 ?
Definitely. I have a video on a brief overview of json. But it is worth being able to visualise json, understand arrays and object.
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.
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
@DamoBird365 This is a perfect solution for a problem of mine. I used this method and implemented it for collecting monthly data through forms (recurring). Instead of Array, I just used Forms output. whole data is processed and stored in SharePoint. Thanks a lot for the insight! keep it coming :)
Thanks Chandrasekar. I really value your feedback. Let me know if there are any ideas you would love me to solve.
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 :)
I’ve not tried to be honest. Might be worth logging a call on the support forum powerusers.microsoft.com/
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
Ahhh... well you want to check out czcams.com/video/gtlklzi6MDg/video.html and see two options and a SharePoint rest API to create an empty file.
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?
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 😉
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!
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 czcams.com/video/CZFLjy8hEx0/video.htmlsi=03qZYhH3E6DG4xjZ
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?
Take a look at Excel Office Script Tutorial - Populate an Excel File Dynamically from Power Automate czcams.com/video/Q7GLQnvJJF0/video.html
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
ca you do this using one drive instead of share point, the setting in share points are different than in OneDrive
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.
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 ideas@damobird365.com.
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!
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.
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.
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
Great stuff. What video software do you use for creating these videos?
These days camtasia, albeit I haven’t created a video for a while as I’ve been fairly busy ☹️
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?
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 👍
thanks for the video .. one question: concurrent in apply to each is not applicable for inserting row in ordered sort right?
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
czcams.com/video/gtlklzi6MDg/video.html
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!
You’ll need to get the file content. In my latest demo of something similar, I show you how czcams.com/video/gtlklzi6MDg/video.html
Hi, I see that your video is showing inserted text file with the data as output. Would like to know if I'm to copy data from my data in another excel table that has more than 1K line item how can I input them in the output?
Hi, you could use list rows and then select to create an array in the correct shape, before passing that to compose. Office scripts and Graph api enable you to load data into excel too. Each with their own pros/cons and learning.
Great video! I have to extract data from an aggregate excel file on a daily basis. I wonder if this flow can help.
This might help too czcams.com/video/4g8Lh0gzEnc/video.html
Thank you so much for this video!!! I have a question - is it possible to format the excel table automatically? For example, is it possible to highlight a few columns? Or merge and center several cells?
Yes, with office scripts. I’ve a few videos on the concept but you can use the recording tool to create a script that will do the formatting for you. What’s your use case?
@@DamoBird365 Thank you so much. I tried script and it worked very well!!
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?
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?
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!
Have you seen or heard of office scripts? This method is far more efficient czcams.com/video/4g8Lh0gzEnc/video.html
@@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!
@@jackrealdrummer nice one. Let me know how you get on. The Microsoft docs are very good for office scripts.
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 ?
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.
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.
Hi, can you share with me a bit more detail to ideas@damobird365.com
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.
You could just get the file content and create a new file straight from that 👍
@@DamoBird365 Thanks. If that's the case, how do I map the column names with the 'Get Items' values?
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.
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...
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 czcams.com/video/G3Q1WuZTWuY/video.html
Thanks!
Cheers Jay, appreciated 👍
@@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...
@@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
czcams.com/video/gtlklzi6MDg/video.html
@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)
@jaymumford3222 check your history. Have you used {} instead of []?
@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 ?
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 👍
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?
Could you use SharePoint rest to get the drive id? _api/v2.0/drives?
Hello
while adding the table name from a variable or from the created file it does not take the name for the table. It takes a default name like "Table1"
how to fix it. please guide
Great video! I am trying to do a similar thing, the only difference is from an excel I am trying to create multiple excels based on some filtered data. My flow is running fine till except the last line where I am adding row to the table. Its failing with the error "message": "The request entity's media type 'text/plain' is not supported for this resource." Any idea what this can be about?
Because you are creating a file dynamically, I wonder if you need to supply an object {} to the rows parameter? If it’s identifying the object as text, maybe there’s a formatting issue?
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?
It will become very inefficient as the size increases. You could try and explore office scripts czcams.com/video/4g8Lh0gzEnc/video.html and send the data as an array.
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
You can change the colour 💯but it sounds like you are creating a table on top of another table, which isn’t possible.
@@DamoBird365 yes so how to approach it could you please share some details how to do so?
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.
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.
You’ll have to share me a screen shot. Ideas@damobird365.com.
How do you handle columns with spaces in the title? For some reason only my single word columns are getting any data through.
I think it’s a limitation of excel. Alphanumeric names only.
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)
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?
@@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
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 ?
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
czcams.com/video/p26sL3qHmfs/video.html
It is ok also to copy records from sharepoint list to anew excel file?
Sure 👌 this works with any data source.
Hello Damien, Appreciate your tutorials! They are so clear and helpful. I'm trying to create a flow that automatically (1) creates a new subfolder (2) Copy an excel template into that subfolder (3) populates this excel file from another. I really can't figure get the first part of the process to work. Any hints?
If you watch my latest video, I show you how to create folders using PowerAutomate. czcams.com/video/ySAZqtC6mXg/video.html. Hope this helps, please let me know.
@@DamoBird365 Thank you for your prompt reply. I am not trying to create a folder, but a new subfolder that goes into an existing subfolder that is already located in an existing folder (if that makes sense?). I want the trigger to be when a row is selected. Thank you for any advice.
I do folders and subfolders in the video 😉. You can try the forum powerusers.microsoft.com/ or drop me an email ideas@damobird365.com and I’ll try to help.
@@DamoBird365 Thank you! You are so kind. I'll check it out and reach out if I can't figure it out!
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.
I’ve got a basic demo for a vertical html table here near the end
czcams.com/video/LQumCR1B-q0/video.html and that technique could be used to transpose the data. You could do it direct with an excel office script though.
Thank you. I will re-watch and keep this as well as your other tips in mind. Much appreciated.
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?
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.
@@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.
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
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.
@@DamoBird365 Thanks. You were right! It was the ? after the Item()
Hi Damo, I am replicating your script but am getting an error "The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@outputs('Compose')' is of type 'String' - Getting this in the "Apply to each". The result must be a valid array." Im not sure what to try. can you suggest anything?
Did you build an array in a compose? Most likely it’s not valid and is being treated as a string. Use a json beautifier to check its validity.
would there be any way to be able to add a total at the bottom of a column doing this?
You can’t easily create an expression using this method alone. You could use office script or graph api. Otherwise sum the values in power automate and add a final row. I’m planning a video on using graph api for excel updates. In the next few weeks.
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.
I’ve done a split workbook into multiple worksheets and also a newer video on populating an excel file here
czcams.com/video/gtlklzi6MDg/video.html. What’s your exact use case? Feel free to reach out with more details.