This is amazing in its simplicity! Other instructional videos will have you running 2 scripts and importing them via Power Automate but this circumvents it and gave me a solution in 5 mins. Thankyou so much
Hi! thanks for this video. Do you know how to copy data from a SharePoint library to an excel template, and save this template as a new document in another library?
Hello Leila, thanks for watching and for your question. Before I can answer, I have some questions. What type of data is to be copied from the SP library and can you be more specific on the template in excel, is the template in a Table?
Hello @sofiaibarra1883 , thanks for reaching out and great question, as long as your cell range is set as a table you can copy its contents to other workbooks or worksheets.
Thanks for the video. Its very helpful. do you also happen to know what if i just want to update data in a row referencing from Excel 1 to Excel 2. Like if there are submissions with the same ID or unique row , it will trigger for it to update the existing row and not to just add data in a row? Hope you can read this! thanks again!
Hello @arahabigailbalgos6897 , excellent question, I've never setup a flow in Excel to not only allow appending data but also updating existing data, I've made a note to do a future video on it. In the meantime, you can visit the link below to see how the data comparison part would be done, I hope this helps for now and thanks for turning into my channel. sharepains.com/2020/11/11/compare-2-excel-files-power-automate/
Hello @tarshmidha5879 , thanks for your question, you can by using the OFFSET and SUBTOTAL formulas. Here is an example of the formula: =OFFSET('Sheet1'!A7,0,0,SUBTOTAL(103,'Sheet1'!$A:$A),37). 'Sheet1'A7 is the starting point and 37 is the number of columns in the table, those should be the only two values you need to change. Let me know how you make out and if you have any other questions.
hello, great video. Sorry noob question, but i do not see the x-ms-file-id from the dynamic content, what should I do? or can I use full path instead? thanks so much in advanced!
Hello @charisseaustria4711, thanks for reaching out with an excellent question, so it looks like I need to update my video due to recent updates in Power Automate. The trigger "When a file is created in a folder in SharePoint" is now deprecated and the new trigger is "When a file is created or modified in SharePoint (Properties Only)". For the Create Table action under the File property select the Dynamic Content from the SharePoint trigger called "Identifier". Try that out and let me know how you make out.
@@tw-waytek OMG! thank you so much for your reply. It still doesn't work. It says branching condition not satisfied :( I'm trying to get data within the excel table and update file property for one of the columns in document library. Do you think it's possible?
I followed your video and created a very successful flow. However, why this flow is only able to copy the first 256 rows from one Excel to another. My file actually had over 10,000 rows? Can you suggest why? Thank you!
Hello @shuchenlu3459, thanks for reaching out, there is a 256 row threshold limit for the Power Automate Excel action List Rows Present in a Table, to overcome this limitation, go to the List Rows Present in a Table action in your flow, go to Settings, go to Networking, turn on Pagination and enter the number of rows you need. Let me know how you make out with your flow or if you have any other questions.
Hello and thank-you for your question. Having multiple sheets in your Excel file will not be an issue since you must connect to a table in your worksheet. Each table can be connected to it's own flow.
I tried to build the flow nearly as same as yours (Change Onedrive in stead of Sharepoint). It allway crushes at "Create Table": Error Message: BadRequest. Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Bad JSON escape sequence: \U. Path 'message', line 1, position 73.' Any Idea what caused this problem?
Hello @sewaeria, sorry for the late reply but the message got blocked for some reason and I just noticed it now. I will review and respond after testing.
Hello @sewaerla, yes I did, you have to create the master data file and table first as the file and table information is required in the Apply to Each step. I hope this helps. Let me know if you have any other questions.
@@tw-waytek Update. Everything runs fine. My failure was that i had the data of the incomming file allready converted into a table (created the file for testing Step 2 directly. Thanky for your great tutorial and response.
This is amazing in its simplicity! Other instructional videos will have you running 2 scripts and importing them via Power Automate but this circumvents it and gave me a solution in 5 mins. Thankyou so much
@Steve-fs7ze, thanks for your feedback and glad I could help.
Great video! Very helpful.
Thanks Jonathan, I'm glad you enjoyed the video! There is more content in the pipeline.
Let me know if there is something else you'd like see.
Hi! thanks for this video. Do you know how to copy data from a SharePoint library to an excel template, and save this template as a new document in another library?
Hello Leila, thanks for watching and for your question. Before I can answer, I have some questions. What type of data is to be copied from the SP library and can you be more specific on the template in excel, is the template in a Table?
Hi! Do you know if it's possible to select a range like from A2 to C6 in your example, and then paste it to the other workbook? Thanks for the video:)
Hello @sofiaibarra1883 , thanks for reaching out and great question, as long as your cell range is set as a table you can copy its contents to other workbooks or worksheets.
Thanks for the video. Its very helpful. do you also happen to know what if i just want to update data in a row referencing from Excel 1 to Excel 2. Like if there are submissions with the same ID or unique row , it will trigger for it to update the existing row and not to just add data in a row? Hope you can read this! thanks again!
Hello @arahabigailbalgos6897 , excellent question, I've never setup a flow in Excel to not only allow appending data but also updating existing data, I've made a note to do a future video on it. In the meantime, you can visit the link below to see how the data comparison part would be done, I hope this helps for now and thanks for turning into my channel.
sharepains.com/2020/11/11/compare-2-excel-files-power-automate/
is there a way we can let Power Automate to determine the number of rows in a new file which is needed to create the table?
Hello @tarshmidha5879 , thanks for your question, you can by using the OFFSET and SUBTOTAL formulas. Here is an example of the formula: =OFFSET('Sheet1'!A7,0,0,SUBTOTAL(103,'Sheet1'!$A:$A),37). 'Sheet1'A7 is the starting point and 37 is the number of columns in the table, those should be the only two values you need to change. Let me know how you make out and if you have any other questions.
hello, great video. Sorry noob question, but i do not see the x-ms-file-id from the dynamic content, what should I do? or can I use full path instead? thanks so much in advanced!
Hello @charisseaustria4711, thanks for reaching out with an excellent question, so it looks like I need to update my video due to recent updates in Power Automate. The trigger "When a file is created in a folder in SharePoint" is now deprecated and the new trigger is "When a file is created or modified in SharePoint (Properties Only)". For the Create Table action under the File property select the Dynamic Content from the SharePoint trigger called "Identifier". Try that out and let me know how you make out.
@@tw-waytek OMG! thank you so much for your reply. It still doesn't work. It says branching condition not satisfied :( I'm trying to get data within the excel table and update file property for one of the columns in document library. Do you think it's possible?
I followed your video and created a very successful flow. However, why this flow is only able to copy the first 256 rows from one Excel to another. My file actually had over 10,000 rows? Can you suggest why? Thank you!
Hello @shuchenlu3459, thanks for reaching out, there is a 256 row threshold limit for the Power Automate Excel action List Rows Present in a Table, to overcome this limitation, go to the List Rows Present in a Table action in your flow, go to Settings, go to Networking, turn on Pagination and enter the number of rows you need. Let me know how you make out with your flow or if you have any other questions.
That's Actually Very Great. But What if I have More Spread sheets in My excel File
Hello and thank-you for your question. Having multiple sheets in your Excel file will not be an issue since you must connect to a table in your worksheet. Each table can be connected to it's own flow.
I tried to build the flow nearly as same as yours (Change Onedrive in stead of Sharepoint). It allway crushes at "Create Table": Error Message:
BadRequest. Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Bad JSON escape sequence: \U. Path 'message', line 1, position 73.'
Any Idea what caused this problem?
Hello @sewaeria, sorry for the late reply but the message got blocked for some reason and I just noticed it now. I will review and respond after testing.
@tw-waytek - Did you created the column names in Testfile two before the first input of Testfile one?
Hello @sewaerla, yes I did, you have to create the master data file and table first as the file and table information is required in the Apply to Each step. I hope this helps. Let me know if you have any other questions.
@@tw-waytek Update. Everything runs fine. My failure was that i had the data of the incomming file allready converted into a table (created the file for testing Step 2 directly. Thanky for your great tutorial and response.
@sewaerla , great news and great job!! I'm happy to have helped, reach out anytime.