Reza - thank you for all the content over the years. Your work is greatly appreciated and, in my opinion, has helped with positively changing peoples lives and getting people to think about various solutions that would never have been delivered without your content. Thank you again.
Thank you so much for your kind words and thoughtful message. It means a lot to hear that my work has had a positive impact and has helped spark new ideas. I'm truly grateful for your support, and I'm glad to have contributed in a way that’s meaningful.
Hello Reza, this is really a flow that has helped me a lot because I can integrate it into almost all of my applications.... super helpful, super efficient. I can only say.... great work... many thanks for that!!!👏👏👏
Great Video. My biggest takeaway was the use of the Graph API to perform the copying into Excel. Was also nice to see you prefer using the Select action over Apply to each to significantly reduce API calls making the flow more efficient. Tested it in my environment and it works perfectly. Thanks for sharing. 😄
Hi Reza , I just wanted to take a moment to thank you for the excellent video on how to export large datasets from Power Apps to Excel using Power Automate. The content was fantastic and incredibly helpful! The explanation was clear, and the approach was very practical, which will definitely help streamline my processes. I’d like to make a suggestion: it would be great if you could create a similar video, but using Dataverse as the data source. Additionally, it would be really helpful to see how you handle Choice, Lookups, and other data types within Dataverse. I’m sure many of us working with this data would greatly benefit from a detailed explanation. Once again, congratulations on the great work, and I look forward to more amazing content from you!
Thanks so much. My rule for topic suggestions is set - "Every 6-8 months I ask for topic suggestions on community tab of my channel. Make sure to post this idea there. If it gets max votes, I will add the topic to my backlog"
Thanks so much - I had spent hours on ChatGPT and PowerPlatform forums trying to achieve exactly what you showed in the video. Your explanations are thorough and clear making it easy to understand and apply. I would next like to adapt the FLOW to allow the email recipient to APPROVE the list sent for deletion: (I added a filter to the trigger which allows only certain records to be included in the saved files. These archived files should be removed from the main SP list once checked)
Most welcome! I do not have a video reference on the mentioned scenario and would have to try it out to know the options. I will recommend checking or posting your query on forums in case someone has done something similar community.powerplatform.com/
@@RezaDorrani Thanks once again. I managed to create a separate approval flow that does the Delete action on submission of "Approve" but it fails to Apply to Each when I add it at the end of your flow. I will post a reply if I get it to work.
@@RezaDorrani I added a START and WAIT for Approval step, then simply followed it with Sharepoint Delete Item, using the Sharepoint Library address and the ID from my Get Items step. It created a FOR EACH loop and worked when I submitted Approve in my email. I did not realise it would take quite a while to delete 300 records - I learned to be patient!
Thank you so much for this video, really interesting method. I have some flows that do the opposite (I periodically receive an excel file in my inbox, the flow triggers to save it to my sharepoint -> convert to table -> add table rows to a list). Do you have any idea if this method could be used in this order of operations? (using the select method with graph api to add table items to a list)
Thanks for the share. Avoiding the apply to each is a great way! 👏 Can we bulk update a SharePoint list using graph api similarly? Any links that you can share?
Great video and straight to the point as usual Reza. Many thanks for sharing your knowledge with the community. One little question if I may... Could you please be so kind to post a video using the same approach but using Dataverse as a final backend with all 4 CRUD actions? I am struggling when using the HTTP connector (premium). Again, many thanks for your continuous support! 🥇
Thx Reza Useful as always. Any suggestions on how to use this to populate the gathered data to SharePoint list rows without iterating through every record?
@@RezaDorrani Looking forward to it! If I can still influence your script for that video… I often need to bulk delete and bulk add items to sharepoint lists with a couple of hundred rows. Iterations are just very resource consuming. Keep up the good work!
Great tutorial Reza. Thanks for sharing. What account permissions are required for accessing Graph API? I have several use cases to improve my existing flows.
Thanks! The logic shown in video is specific to exporting. You would need to check graph api documentation to see if there is any api to create or update.
Hi Reza, Great and very informative video. Quick question, can you please let me know if instead of one drive as placeholder if we can use excel file in SharePoint location? Will be awaiting your reply.
Hi Reza, Great, Great video as always!! I know this solution should work however, when I run the flow, it errors out when sending the HTTP request. Wondering if you can shed some light on this issue. InternalServerError Encountered internal server error. Thank you in advance for any guidance you can provide!
I have not experienced the mentioned issue, hence not sure whats the cause for your issue. You can try downloading the solution shown in video (members only - check video description for details)
I truly appreciate for the sharing fabulous knowledge! Can you show how to export over a million records from a large table? directly to the local drive in the laptop...
Thank you for this video, it was very helpful. I am working with SQl tables and view with very large datasets. I have a gallery that filters from multiple combo boxes and search field. The gallery only shows a few columns. is it possible to export all selected items in the gallery and include all my columns from SQL view?
I have done no work with SQL, hence not sure of the options there. I would recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com/
Thanks, very useful. Wondering if there is an equivalent endpoint to do the same for SharePoint List. I mean bulk insert or update of a SharePoint list.
In select action where column values are placed - you can write an expression for the choice value. If null or empty then put value empty string else the actual value. I do not have a specific video reference for this, hence can only guide.
Hi Reza, Thank you for all the amazing videos you share! :) Regarding the Graph API approach, I used it last month and found that the JSON payload can be a maximum of 4 MB when posting data to an Excel file. Do you have any ideas on how to handle JSON payloads larger than 4 MB?
I am not aware of the max payload size. I was able to export around 50k rows with a scenario for a customer. There get items thresholds itself is 100k.
Hi Reza, this is a very helpful video, and this trick can be used in many situations. However i can see from the Send HTTP action details provided by Microsoft that these segments are supported: 1st segement: /me, /users/ 2nd segment: messages, mailFolders, events, calendar, calendars, outlook, inferenceClassification. I don't know why it is working for the "drive" segment, but should we except some day this endpoint will not work anymore?
Thank you for this great video. I have a question, though. I created this flow using Power App(V2) instead of a manual trigger, and I would like to know if other users can run this flow. I have tried, but an unknown error occurred for another user during the Create Table action, stating "The request is forbidden by Graph API. Error code is 'accessDenied'. Error message is 'Access denied'." If you have a moment, would you please respond? Thank you.
@@RezaDorrani I think I understand his comment, as I am also looking for a solution for the same, what if we are creating the Excel file in a SharePoint document library
@@RezaDorrani I used your method, and it was ultra-fast. As a workaround, I decided to first create the file on OneDrive and then move it to the document library and delete the instance in the OneDrive, due to information security reasons. I read the comment thread, would you be able to give me a hint, if I can do this in one go through Send an HTTP request to SharePoint, Rest API? I couldn't find the right end points though.
Hi Reza great video! I got this built for a use case but ran into a brick wall on the create table step as other users who hit the button to export to excel fail as it is using my credentials to create the table on the excel connector and doesn’t have access to their OneDrive. Any way to delegate those permissions?
@@RezaDorrani security-wise it’s probably going to be an issue as the excel connector needs access to the user’s OneDrive unless your organization has a service account that can access them all. I’ll think up some different way I guess
@@jamespenna5452 big question is how is the flow being triggered? if flow is scheduled or automated - then flow runs under flow owner account. You could always download the file and send as email attachment. If flow is run manually, then it would use the run only users creds.
@@RezaDorrani yes I am using the power apps trigger like in your video with the button but the step to create the table will error unless the account that created the excel connection has access to that users OneDrive. Unless I have it configured wrong LOL . The OneDrive connectors use delegated model where the excel ones do not
@@jamespenna5452 this video is on flow only :) If using power apps to call flow, then flow runs under users context running app which is what you want. I dont see any security issues there.
Hi Reza, thanks for the video. but small Q why didn't you used "ID" in HTTPS from Create File dynamic content the way you used in create table and then you parsed from create Table step? Thanks again
@ Actually I was trying insert records to file stored on sp document library but I realised it will not work. So I copied from it to one drive and then inserting but getting invalid request. Any clue or exception you remember. Thanks for quick response.
Thanks Raza, it works very well when Excel document is created in One Drive. Would it be possible for you to let me know what I need to change if documents are saved to SharePoint document library? I am getting 'Send_an_HTTP_request' failed" Invalid request.
Hello Reza, very good content, it is very useful for learning, I need to do the same thing but with add rows, I tried to do it but it didn't work, I would appreciate it very much
That would be a lot more complex as first you would need to check if row already exists and according add or update. I'm not sure if this technique would work there.
Your original data prior to exporting to Excel had color formatting for the region. Is it possible to preserve this formatting when exporting to Excel? Can Power Automate do this conditional formatting? I.e. South region had a green color on SP list so we set it as a green cell in the Excel export ?
Thanks for this video, can you create a video on pulling multiple attachments from SharePoint list and save it to the documents library with naming conventions to understand the underlined file from the list .....
I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
Hi Reza, I'm trying to create this flow however want the file to be created in SharePoint instead. I'm struggling on how to apply the HTTP part of the video for this scenario. Do I use Send an http request to SharePoint for this case? And if so, what is the Uri?
I dont have a video reference with sharepoint and not sure of what the steps would be. I would suggest posting your query on forums powerusers.microsoft.com/
In the Select statement, how would I represent a comma separated display name from a people picker field that allows multiple selections? Also, an enhanced rich text multiple line text field? Any tips?
I would have to try this out to know the options. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Amazing video. Working fine but running into a "redirect issue code 302" if the json contains more than 20,000 rows. The flow will fail. Is there a workaround for this please?
Hi reza, im new for this platform and trying to copy what u do. but, at create table, specifically at document library, none of it called onedrive. is it a default choices or u create the library by ur own? thank you reza
Thanks for the flow it works fine when export 500 items, but i am trying to export 3500 items and its failing to do. It gives me 302 error. code "message": "The response is not in a JSON format.", "innerError": "Cannot read server response." Not sure if there is a limitation on using this action ?
Have not experienced the mentioned issue, hence not sure what's the cause of your issue. You can try downloading the solution shown in video (members only - check video description for details)
I am facing an issue with the http post approach. For 5k records it works fine. But the moment is pass 70k records, it throws 302 error with message as the response is not in a json format and with internal error as cannot read server response. Had anybody faced similar issue. I am trying to figure out the reason it works for 5k records and not for huge list of records makes me wonder if it's the api limitation.
@@sandeepadode1 I am also facng the same issue , my table consists of 200 columns and 50k rows, tried all the approaches from my end, @rezadorrani could you hep us in figuring it out ?
Works like a charm!. But I can't get the output of a query on a PowerBI dataset in the body of the HTTP Post: { "values": outputs('Query')?['body']} does not work ! What reading material do you suggest?
@kirane7000 Not sure if I get this, but I can say what we did. We have a powershell script that is scheduled to get flows every 30min for failures and send an email to the development team take a look at it and fix any issues with the failed flows.
@@RezaDorrani 15000 rows done in total flow time 30 sec, this is amazing. Yes the top count worked as you mentioned. I think I saw it limiting the records with the Excel connector but this is the SP connector. The HTTP request action took 5 sec only. Perfect for many scenarios. Looking forward to your next for writing to SP.
Reza when can we expect a video an end to end video of integrating Gen AI with PowerApps with lot of noise that's around regarding the same a video will help us
I have not heard any noise from anyone :) I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
Hi I need some urgent help on creating a schedule flow that will execute an oracle query at the beginning of each month and export the result of the query to an excel table. Any help would be really appreciated. Thanks
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, I see you used OneDrive throughout the process. What if I wanted to only use SharePoint. Could I follow a similar process using the Invoke Http action and populate the excel stored in SharePoint via the Graph API?
Hello Reza, Thanks for this video. Unfortunately I'm getting the following error, "unable to process template language expression in action 'send an http request' inputs at line 0 and column 0. The template language function split expects it's first parameter to be of type string. The provided value is of type Null. Can you pls advise what could be an issue.
I want to make the table read only and add 2 columns (extend 'yes/no' and 'due date' if yes) that are editable for the receiver. Group the excel for every firmname and send it to the email of responsible firmcontact. How to ...
Damn, I just spend 24 hours learning how to do the exact same task, though with data from power apps, even created a custom graph connector, and had to wrangle with text to json etc....... Should've just checked in with Reza
I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
@@Copy-kodadam-mama I don’t have any blog references for this. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
Not something I have tried, hence not sure. I will recommend checking or posting your query on forums in case someone has done something similar community.powerplatform.com/
I don't get the part with split(actions('Create_table')?['inputs']?['parameters']?['file'], '.')?[1] In my case the value for actions('Create_table')?['inputs']?['parameters']?['file'] of this sharepoint file is "%252f5_0_SharedDocuments%252ffoldername1%2bB2B%252ffoldername2%252freport.xlsx" splitting the string and choosing the second part with "[1]" results in "xlsx". Any Ideas!?
Have not experienced the mentioned issue, hence not sure. You can try downloading the solution shown in video (members only - check videos description for details)
Flow working fine in my account but Im getting following error when the flow run from user end. "Action 'Create_table' failed: The resource could not be found. clientRequestId: df41ce0e-5a49-419d-ac73-7415e9534013 serviceRequestId: 11ea0a46-a7a9-4348-8b77-8f1e1e0df534"
Thank you very much Reza ! With your method, I was able to reduce a 40 mn long flow to only 3 mn (delays included). You're simply the best !!! 👍
Fantastic!
Reza - thank you for all the content over the years. Your work is greatly appreciated and, in my opinion, has helped with positively changing peoples lives and getting people to think about various solutions that would never have been delivered without your content. Thank you again.
Thank you so much for your kind words and thoughtful message. It means a lot to hear that my work has had a positive impact and has helped spark new ideas. I'm truly grateful for your support, and I'm glad to have contributed in a way that’s meaningful.
Hello Reza, this is really a flow that has helped me a lot because I can integrate it into almost all of my applications.... super helpful, super efficient. I can only say.... great work... many thanks for that!!!👏👏👏
Great to hear! Thanks so much for watching
Great Video. My biggest takeaway was the use of the Graph API to perform the copying into Excel. Was also nice to see you prefer using the Select action over Apply to each to significantly reduce API calls making the flow more efficient. Tested it in my environment and it works perfectly. Thanks for sharing. 😄
Glad it was helpful!
Hi Reza ,
I just wanted to take a moment to thank you for the excellent video on how to export large datasets from Power Apps to Excel using Power Automate. The content was fantastic and incredibly helpful! The explanation was clear, and the approach was very practical, which will definitely help streamline my processes.
I’d like to make a suggestion: it would be great if you could create a similar video, but using Dataverse as the data source. Additionally, it would be really helpful to see how you handle Choice, Lookups, and other data types within Dataverse. I’m sure many of us working with this data would greatly benefit from a detailed explanation.
Once again, congratulations on the great work, and I look forward to more amazing content from you!
Thanks so much.
My rule for topic suggestions is set - "Every 6-8 months I ask for topic suggestions on community tab of my channel. Make sure to post this idea there. If it gets max votes, I will add the topic to my backlog"
Thanks a lot Reza, another amazing video from your side, This is what every Power Apps developer need.
My pleasure
I now have to rewrite several flows in my organization but that will save me a lot of actions ;-)
Great video, thank you, Reza
Glad it was helpful!
Always top quality content. Very straighforward approach. You're the GOAT Rezza.
🐐
Thank you Reza! Excelent aportation to the community.
Most welcome!
Great Reza, very clear explanation, works like a dream and is very fast!
Great to hear!
Fast and efficient
I wish the new Flow designer worked as well for me as it seems to for you 😅
Great video!
You got to handle it with care
A great video that showcases an important (and under used) technique - Thanks for sharing.
Glad it was helpful!
Love this video. Very helpful.
Thanks for watching!
So helpful! Thanks for sharing!
Most welcome!
Thanks Reza for sharing the video!
Just gave me the idea to replace the trigger from manual to Run from PowerApps
Hey, I have another video coming where I mix this with run from Power Apps and make it super dynamic. Watch out for that one.
Hello Reza, once again It’s awesome vid. You really want us to get addicted. Thank you. You make me want to invest much on graph
Graph is KING
Very helpful, i just follow the step and get the output i want. Thank you very much Reza
Glad it helped
Grateful for this video, thank you very much. Alone I'd never been able to do it.
Glad I could help
@@RezaDorrani I've got a dataverse table to export actually, is the way similar?
@@francescofornasiero5071 pattern would be similar
This is really awsome trick..thanks Reza🎉❤
Most welcome
You are amazing Reza !!❤
Thanks
Excellent!!!
Thanks
Thanks so much - I had spent hours on ChatGPT and PowerPlatform forums trying to achieve exactly what you showed in the video. Your explanations are thorough and clear making it easy to understand and apply. I would next like to adapt the FLOW to allow the email recipient to APPROVE the list sent for deletion: (I added a filter to the trigger which allows only certain records to be included in the saved files. These archived files should be removed from the main SP list once checked)
Most welcome!
I do not have a video reference on the mentioned scenario and would have to try it out to know the options. I will recommend checking or posting your query on forums in case someone has done something similar
community.powerplatform.com/
@@RezaDorrani Thanks once again. I managed to create a separate approval flow that does the Delete action on submission of "Approve" but it fails to Apply to Each when I add it at the end of your flow. I will post a reply if I get it to work.
@@RezaDorrani I added a START and WAIT for Approval step, then simply followed it with Sharepoint Delete Item, using the Sharepoint Library address and the ID from my Get Items step. It created a FOR EACH loop and worked when I submitted Approve in my email. I did not realise it would take quite a while to delete 300 records - I learned to be patient!
Thank you Reza.
Most welcome
Hellon Reza thak you for this explanation, is this works also for files in sharpoint ?
I have not tried with files in SharePoint. There may be a graph api call for that. Best to check the documentation.
Great video I like it❤
Thanks
Magician Reza!
Thank You!
Thank you so much for this video, really interesting method.
I have some flows that do the opposite (I periodically receive an excel file in my inbox, the flow triggers to save it to my sharepoint -> convert to table -> add table rows to a list).
Do you have any idea if this method could be used in this order of operations? (using the select method with graph api to add table items to a list)
I have ideas for that also. Im planning videos for both SharePoint and Dataverse. Waiting to see the response for this video first.
Reza Thank you!!
Most welcome
Thanks for the share.
Avoiding the apply to each is a great way! 👏
Can we bulk update a SharePoint list using graph api similarly? Any links that you can share?
Yes, there is batch api for SharePoint as well. Il plan a video on that and Dataverse in future.
Finally I'm getting a taste of how to use APIs, thanks to your 'simple explanation' 🤘
Great to hear!
Great video and straight to the point as usual Reza. Many thanks for sharing your knowledge with the community. One little question if I may... Could you please be so kind to post a video using the same approach but using Dataverse as a final backend with all 4 CRUD actions? I am struggling when using the HTTP connector (premium). Again, many thanks for your continuous support! 🥇
Thanks!
I have plans for bulk action videos related to Dataverse and SharePoint. I’m waiting to see the response to this video.
Thx Reza
Useful as always.
Any suggestions on how to use this to populate the gathered data to SharePoint list rows without iterating through every record?
Im working a video idea for SharePoint and Dataverse.
@@RezaDorrani
Looking forward to it!
If I can still influence your script for that video…
I often need to bulk delete and bulk add items to sharepoint lists with a couple of hundred rows. Iterations are just very resource consuming.
Keep up the good work!
@@jamesbergmann4322 Depends on how much I can squeeze in within 20 mins
Great tutorial Reza. Thanks for sharing. What account permissions are required for accessing Graph API? I have several use cases to improve my existing flows.
The API shown in video uses delegate permissions. Basically uses the existing access of the user.
Great❤
Thanks!
Hi Reza, amazing video>However just one query can use this logic to create/update items to a SharePoint list from an excel file?
Thanks!
The logic shown in video is specific to exporting. You would need to check graph api documentation to see if there is any api to create or update.
Thank you for this video. Can the reverse of it possible? Like entering 150k rows of data from Excel file to the Sharepoint list?
I have not explored capabilities in graph for the reverse scenario.
Hi Reza,
Great and very informative video.
Quick question, can you please let me know if instead of one drive as placeholder if we can use excel file in SharePoint location?
Will be awaiting your reply.
I have not tried with SharePoint. Like onedrive, there would be an api for SharePoint
Hi Reza,
Great, Great video as always!! I know this solution should work however, when I run the flow, it errors out when sending the HTTP request. Wondering if you can shed some light on this issue.
InternalServerError
Encountered internal server error.
Thank you in advance for any guidance you can provide!
I have not experienced the mentioned issue, hence not sure whats the cause for your issue.
You can try downloading the solution shown in video (members only - check video description for details)
@@RezaDorrani thank you for your response. I will keep troubleshooting 🙂
I truly appreciate for the sharing fabulous knowledge! Can you show how to export over a million records from a large table? directly to the local drive in the laptop...
I don't think a million records is supported. I don't have any video references on storing info on local drives. Not something I have tried.
Thank you for this video, it was very helpful. I am working with SQl tables and view with very large datasets. I have a gallery that filters from multiple combo boxes and search field. The gallery only shows a few columns. is it possible to export all selected items in the gallery and include all my columns from SQL view?
I have done no work with SQL, hence not sure of the options there. I would recommend checking or posting your query on forums in case someone has done something similar
powerusers.microsoft.com/
Thanks, very useful. Wondering if there is an equivalent endpoint to do the same for SharePoint List. I mean bulk insert or update of a SharePoint list.
Not a graph api but there is a batch api. I have plans for a video on it in future.
@@RezaDorrani very nice. You're the best. Have a wonderful weekend.
Thanks for the video! How do you handle empty values in a choice field? I’m getting error when field is empty and trying to trap and insert null.
In select action where column values are placed - you can write an expression for the choice value. If null or empty then put value empty string else the actual value.
I do not have a specific video reference for this, hence can only guide.
@@RezaDorrani thanks I’ll add and post back results
Hi Reza,
Thank you for all the amazing videos you share! :)
Regarding the Graph API approach, I used it last month and found that the JSON payload can be a maximum of 4 MB when posting data to an Excel file. Do you have any ideas on how to handle JSON payloads larger than 4 MB?
I am not aware of the max payload size. I was able to export around 50k rows with a scenario for a customer.
There get items thresholds itself is 100k.
Hi Reza, this is a very helpful video, and this trick can be used in many situations. However i can see from the Send HTTP action details provided by Microsoft that these segments are supported: 1st segement: /me, /users/ 2nd segment: messages, mailFolders, events, calendar, calendars, outlook, inferenceClassification. I don't know why it is working for the "drive" segment, but should we except some day this endpoint will not work anymore?
I followed the process as per the official documentation + the action is GA.
I cannot comment or predict on wether something will change.
Thank you for this great video. I have a question, though. I created this flow using Power App(V2) instead of a manual trigger, and I would like to know if other users can run this flow. I have tried, but an unknown error occurred for another user during the Create Table action, stating "The request is forbidden by Graph API. Error code is 'accessDenied'. Error message is 'Access denied'." If you have a moment, would you please respond? Thank you.
I've not encountered such an issue before, so I'm not sure how to solve it.
@@RezaDorrani ok ,thank you for giving me your valuable time
Awesome video! Please could you a video on how to do the http action on a SharePoint file? 🙏🏼non premium if possible
SharePoint file? What type of file?
@@RezaDorrani I think I understand his comment, as I am also looking for a solution for the same, what if we are creating the Excel file in a SharePoint document library
@@Y0urN3m3sis I'm sure there would be a graph api endpoint for that as well. I don't have any plans for that video as of now.
@@RezaDorrani I used your method, and it was ultra-fast. As a workaround, I decided to first create the file on OneDrive and then move it to the document library and delete the instance in the OneDrive, due to information security reasons. I read the comment thread, would you be able to give me a hint, if I can do this in one go through Send an HTTP request to SharePoint, Rest API? I couldn't find the right end points though.
@@Y0urN3m3sis I would have to explore that to know if there is a one shot rest api
Hi Reza great video! I got this built for a use case but ran into a brick wall on the create table step as other users who hit the button to export to excel fail as it is using my credentials to create the table on the excel connector and doesn’t have access to their OneDrive. Any way to delegate those permissions?
The create table action requires properties for onedrive id. I believe that can be made dynamic but not something I have explored.
@@RezaDorrani security-wise it’s probably going to be an issue as the excel connector needs access to the user’s OneDrive unless your organization has a service account that can access them all. I’ll think up some different way I guess
@@jamespenna5452 big question is how is the flow being triggered? if flow is scheduled or automated - then flow runs under flow owner account. You could always download the file and send as email attachment. If flow is run manually, then it would use the run only users creds.
@@RezaDorrani yes I am using the power apps trigger like in your video with the button but the step to create the table will error unless the account that created the excel connection has access to that users OneDrive. Unless I have it configured wrong LOL . The OneDrive connectors use delegated model where the excel ones do not
@@jamespenna5452 this video is on flow only :)
If using power apps to call flow, then flow runs under users context running app which is what you want. I dont see any security issues there.
Hi Reza, thanks for the video. but small Q why didn't you used "ID" in HTTPS from Create File dynamic content the way you used in create table and then you parsed from create Table step?
Thanks again
I don’t remember why.
@
Actually I was trying insert records to file stored on sp document library but I realised it will not work. So I copied from it to one drive and then inserting but getting invalid request. Any clue or exception you remember. Thanks for quick response.
I don't recollect to be honest
@@RezaDorrani Thanks, Can we use any alternate way to perform same when file is stored on SP document library.
@AutomationDose4U You could store in onedrive and then use that file to create file in SharePoint. I remember someone posted a comment doing that.
Another way can be to use Office Scripts to Create Excel in One Go, using it we can generate complex excel exports.
Yes
Thanks Raza, it works very well when Excel document is created in One Drive. Would it be possible for you to let me know what I need to change if documents are saved to SharePoint document library? I am getting 'Send_an_HTTP_request' failed" Invalid request.
SharePoint would be a different process and not something I have explored
Can't believe you're replying to nearly every comment! ✨✨
I am! You will have to believe it 😊
Hello Reza, very good content, it is very useful for learning, I need to do the same thing but with add rows, I tried to do it but it didn't work, I would appreciate it very much
Thanks!
Add rows to?
@@RezaDorrani excel file, it is the same thing you did here but instead of creating the file I want to update it
That would be a lot more complex as first you would need to check if row already exists and according add or update. I'm not sure if this technique would work there.
Your original data prior to exporting to Excel had color formatting for the region. Is it possible to preserve this formatting when exporting to Excel? Can Power Automate do this conditional formatting? I.e. South region had a green color on SP list so we set it as a green cell in the Excel export ?
I don't think it can. Probably require excel scripts or other mechanisms to get that working.
Is there any chance to use similar approach to load / modify / delete data in sharepoint list?
Future video planned!
Thanks for this video, can you create a video on pulling multiple attachments from SharePoint list and save it to the documents library with naming conventions to understand the underlined file from the list .....
I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
Hi Reza, I'm trying to create this flow however want the file to be created in SharePoint instead. I'm struggling on how to apply the HTTP part of the video for this scenario. Do I use Send an http request to SharePoint for this case? And if so, what is the Uri?
I dont have a video reference with sharepoint and not sure of what the steps would be. I would suggest posting your query on forums powerusers.microsoft.com/
In the Select statement, how would I represent a comma separated display name from a people picker field that allows multiple selections? Also, an enhanced rich text multiple line text field? Any tips?
I would have to try this out to know the options. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Amazing video. Working fine but running into a "redirect issue code 302" if the json contains more than 20,000 rows. The flow will fail. Is there a workaround for this please?
I'm not aware of a workaround for this
Hi reza, im new for this platform and trying to copy what u do. but, at create table, specifically at document library, none of it called onedrive. is it a default choices or u create the library by ur own? thank you reza
It's a default choice for all. Video description has link to download entire solution (members only - check description for details)
Is this solution also possible for updating/patching an existing excel table? Obviously with a patch/put api
Have not explored that
Thanks for the flow it works fine when export 500 items, but i am trying to export 3500 items and its failing to do. It gives me 302 error. code "message": "The response is not in a JSON format.",
"innerError": "Cannot read server response." Not sure if there is a limitation on using this action ?
Have not experienced the mentioned issue, hence not sure what's the cause of your issue.
You can try downloading the solution shown in video (members only - check video description for details)
Its possible we can use upsert using the graph. Beacuse simple create not work in my cases.?
I have not explored that option
I am facing an issue with the http post approach. For 5k records it works fine. But the moment is pass 70k records, it throws 302 error with message as the response is not in a json format and with internal error as cannot read server response. Had anybody faced similar issue. I am trying to figure out the reason it works for 5k records and not for huge list of records makes me wonder if it's the api limitation.
Video itself shows 10k+ records.
I have not tested with 70k+ so not sure if there is any limit there.
Logically it should handle up to 100k.
@RezaDorrani Thought so too, but unfortunately it won't work for bigger set. Also, I think it has something to do with excel service limits.
@@sandeepadode1 I am also facng the same issue , my table consists of 200 columns and 50k rows, tried all the approaches from my end, @rezadorrani could you hep us in figuring it out ?
@@RezaDorrani It is not working for 50k rows
@@Ashba822 Probably depends on how many cols included.
Works like a charm!. But I can't get the output of a query on a PowerBI dataset in the body of the HTTP Post: { "values": outputs('Query')?['body']} does not work ! What reading material do you suggest?
Thanks!
The values must be an array of arrays as shown in video. You would need to ensure the power bi dataset output matches that pattern.
@@RezaDorrani Fantastic, thanks much for your tip. Works fine....all one needs to do is FOLLOW INSTRUCTIONS! 😊
It is possible to create a text file (.txt) instead an excel file? (.xlsx)?
Not with this approach. Not sure about options for txt file.
Whats the maximum number of items excel will support. Or whats the maximum excel file size will it support 😊
I am not aware of what that size limit is
Not related to video. Is it possible to get notifications from trigger failures of power automate ? Which managed table would give you those details?
Not sure which managed table has that information.
@kirane7000 Not sure if I get this, but I can say what we did. We have a powershell script that is scheduled to get flows every 30min for failures and send an email to the development team take a look at it and fix any issues with the failed flows.
From where can i download a copy of thr flow?
Video description has details on how to download entire solution (members only - check description for details)
Hi Reza. With the Top Count set to 5000, and pagination set to ON, how does it return more than 5000 items? Isn't Top Count going to max it?
No. Pagination will load data in batches of 5000. Try it out and test it.
@@RezaDorrani 15000 rows done in total flow time 30 sec, this is amazing. Yes the top count worked as you mentioned. I think I saw it limiting the records with the Excel connector but this is the SP connector. The HTTP request action took 5 sec only. Perfect for many scenarios. Looking forward to your next for writing to SP.
Reza when can we expect a video an end to end video of integrating Gen AI with PowerApps
with lot of noise that's around regarding the same a video will help us
I have not heard any noise from anyone :)
I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
Hi I need some urgent help on creating a schedule flow that will execute an oracle query at the beginning of each month and export the result of the query to an excel table. Any help would be really appreciated. Thanks
I do not have a video reference on this scenario and would have to try it out to provide guidance. I recommend checking on the forums at powerusers.microsoft.com/ in case someone has done something similar.
Hi Reza, I see you used OneDrive throughout the process. What if I wanted to only use SharePoint.
Could I follow a similar process using the Invoke Http action and populate the excel stored in SharePoint via the Graph API?
Maybe you just add a new step to copy the newly created file to your Sharepoint Group?
The graph api also has option to point to SharePoint.
In the video, once I build the file - I save it in SharePoint library as well.
I see you tried posting a link which gets blocked by my channel settings. I wanted to let you know that.
@@RezaDorrani 👍
Hello Reza,
Thanks for this video. Unfortunately I'm getting the following error, "unable to process template language expression in action 'send an http request' inputs at line 0 and column 0. The template language function split expects it's first parameter to be of type string. The provided value is of type Null.
Can you pls advise what could be an issue.
I want to make the table read only and add 2 columns (extend 'yes/no' and 'due date' if yes) that are editable for the receiver. Group the excel for every firmname and send it to the email of responsible firmcontact. How to ...
Does excel support making table read only and 2 columns that are editable?
Damn, I just spend 24 hours learning how to do the exact same task, though with data from power apps, even created a custom graph connector, and had to wrangle with text to json etc.......
Should've just checked in with Reza
Sorry for giving you the trouble to rebuild the flow :)
@@RezaDorrani All good 🙃
Hi reza, could you please make a video on the number to word conversion.(ex:123.60 ,one hundred twenty three dollars and sixty cents)
I make videos when I receive multiple requests on a topic. Every 6 months I ask subscribers for topic suggestions on Community Tab of my channel. Most voted idea gets added to my backlog. Make sure to post this idea whenever I post the next topic suggestion post.
@@RezaDorrani first of all thank you for your response,could you please share a blog with me ,if you have number to word convertion
@@Copy-kodadam-mama I don’t have any blog references for this. I will recommend checking or posting your query on forums in case someone has done something similar powerusers.microsoft.com
How to write URI in Http request, when Excel file is stored on SharePoint? I cannot figure it out.
Not something I have tried, hence not sure.
I will recommend checking or posting your query on forums in case someone has done something similar
community.powerplatform.com/
I don't get the part with
split(actions('Create_table')?['inputs']?['parameters']?['file'], '.')?[1]
In my case the value for
actions('Create_table')?['inputs']?['parameters']?['file'] of this
sharepoint file is
"%252f5_0_SharedDocuments%252ffoldername1%2bB2B%252ffoldername2%252freport.xlsx"
splitting the string and choosing the second part with "[1]" results in "xlsx".
Any Ideas!?
Have not experienced the mentioned issue, hence not sure.
You can try downloading the solution shown in video (members only - check videos description for details)
@@RezaDorrani Thanks for answering. I'll have a look. Keep up your great videos!
Flow working fine in my account but Im getting following error when the flow run from user end.
"Action 'Create_table' failed: The resource could not be found. clientRequestId: df41ce0e-5a49-419d-ac73-7415e9534013 serviceRequestId: 11ea0a46-a7a9-4348-8b77-8f1e1e0df534"
Check response on this to some recent comments.
Idea would be to make the create table action dynamic using graph api.
@@RezaDorrani Not working. showing same error only
@@Srinisha28 I did not give a fix. I made suggestions on what you could try.
I tried with dynamic also but it doesn't work.
@Srinisha28 I'm not sure what steps you tried to make it dynamic.