Export Power Apps data to Excel in CSV format

hello everyone this is raza durrani in today's video i will show you how to export powerapps data to excel we will build a reusable power automate flow that we will call from our powerapps send data to the flow the flow will create that excel file in a csv format and send the link back to the power app so the user will get the excel file downloaded in powerapps so let's get started with the video but first my introduction [Music] in my power app i have a very simple screen here that has a gallery the data for which is coming from a collection and as part of this collection i have data related to devices in my case i have the id of the device the manufacturer name processor information storage title and type the gallery showcases all that information in the form of controls now in order for me to export this data to an excel file in a csv format i would need to take the help of power automate and in order for me to leverage power automate i would need to trigger a flow from power apps and for that i will first go ahead and insert a button i have called this button export to excel in order for me to call a flow from the button control if i head over to the action tab on the top we have an option here called power automate and this will list out all the flows that have a trigger associated with power apps in my case i will create this flow from scratch so i will click on create a new flow this now will open up the power automate experience for me and take me to the templates that are made available for us now in my case i want to create my own flow that triggers from powerapps and start from scratch so for that i will head over to my flows go to new flow and create an instant cloud flow one of the triggers on the instant cloud flow is power apps so i will select powerapps i will give my flow a name and i have named this flow export to excel i will click create this now will create the flow with the powerapps trigger aside now next step i would like power apps to send data to power automate so i can create that excel file so for that i will add a new step i will use the compose action which is related to the data operation i will rename the compose action to content and for the input i will head over to the option here to add dynamic content bear in mind i have the experimental feature turned on for power automate which shows these new expression options so on the top right if you see right here i can go to settings view all power automate settings and i have the experimental features turned on so in the inputs i will go to dynamic content and select this option called ask in powerapps now when i select this option it will create an input parameter for this specific flow and the name of that parameter will be related to the name of my action in my case here my compose action which i have called as content so i will click ask in powerapps and this now creates an input parameter when powerapps will call flow it will ask for that parameter now that i have this part of my flow built out i will go ahead and save this flow so my flow is ready to go now in order for me to test this flow this flow has to be triggered from power apps so back to my power app if i select my export to excel button and once again go to action and power automate this time my new flow would be listed right here so here's my flow called export to excel i will select this and this will now add the association of this flow to my button control and right here on the on select property of my button here is the association to that flow which is my export to excel flow dot run now this is expecting a parameter and this parameter is nothing but that input parameter that i created in my flow now this is where i need to pass my data that is my collection data in this case to flow a collection in powerapps is tabular information flow understands json javascript object notation the beauty about powerapps is that there is a function called json and as part of this function i can provide a data structure to it so that it converts it to json and that data structure could be tables records or any primitive value types in our case a collection is nothing but tabular data so i can directly provide that collection data right here and you can also define the format to be used in the json conversion now in my case i will go ahead and use the format include binary data i will close my function if i head over to data sources you will see the association of that flow in the data section in powerapps right here now if i go ahead and preview my app if i click on export to excel powerapps will trigger my flow and to confirm this if i head back to my flow and go to the flow details page i will see that my flow just got triggered if i select this this will now show me the details of my flow run so my flow got triggered from powerapps here is the input parameter that came in and here is the value that i stored in my compose action and this is the data that i received from power apps and this data is in json format if i look at show raw inputs the data is being represented right now as a string however it is in json format now in order for me to convert this to actual json so i can work with the data in flow what i have to do is this i will go ahead and add a new step search for data operation select the data operation and one of the actions here is creating a csv table so i will select create csv table and the first parameter is from now from expects an array of values my content is that collection data which is nothing but an array of values i have centered in json format but when it comes from powerapps to flow it comes in the form of string so i need to actually convert it to json so for that i will head over to the expression editor and write a very simple expression the expression is called json and the value in this function would be the output of my compose action which is called content so i'll select this click out and that's my expression right here which is converting the data in the content compose action into json that i can leverage so that flow can create a csv table for me right here so let's go ahead and test this flow select automatically with a recently used trigger and i will rerun the flow directly this time and test it in power automate now when this flow reruns once again content is going to give me the data in that string format however right here i have converted it first into json so if you look at the input the input here is now converted into actual json so here is that array of data that i have right here which is all my data in that collection from powerapps now if you look at the output here the output is nothing but in csv format you will note that all the properties of my json object device id manufacturer processor and so and so forth they are provided right here as the header and then i have all my data points in individual roles all of these in csv format all i need to do now is create an excel file and to create a file i would have to define its location where i would like to create this you can utilize any location of your choice in my case for this scenario i will use onedrive so i will use the user's onedrive to create that excel file that the user just requested create a new step search for the action create file in onedrive for business i will select this and this now gives me the option to create a file now it's asking me for the folder path if i select the folder option right here and select root this will now create the file at the root level in the user's onedrive who would be running the power app please note when if flow is called from powerapps all the actions on the flow would run under the context of the user who is running the power app so even though right now when i'm creating this flow and configuring it against my onedrive when a user runs the app all of these actions in the flow would run under their account now my folder part is the root so in the root which is under my files it will go ahead and create that file if you would like this to be created in its own folder so you can even do that by defining a folder path so in my case right here i can actually type the name of the folder as well and here's the path that i have defined export excel powerapp that means when it goes to create this file if this folder exists it will add the file in this folder if it does not it will create a folder with this name next step is the name of the file now here i would want to ensure that the name of my file is unique so that whenever the user executes this flow it does not go and replace the previous file so for that if i head over to expressions i can define an expression here called utc now which basically gives me the current date and time in utc format this is guaranteed to always be unique and if i click out this has put that expression right here so here's the name of my file the extension is dot csv you can add any pattern that you want to your file name right here and next step is the file content the content of the file the dynamic content value will come from the output of the create csv table action and this will go ahead and create that file for me in my onedrive next step once the file is created i need the link of that file because i would like to open that file directly in powerapps when user clicks that button to export to excel the flow should get called the flow should create the file and the flow should launch that file in powerapps so i need to send that link of the file directly back to powerapps right here in my flow for that i can go and add a new step and in the onedrive for business connector there is an action here called create share link so i'm going to select this the first property it is asking for is the unique identifier of the file so if i click here and under dynamic content the create file action has that unique identifier and that's the id property so i will select this the link type i will create a direct link to that file and the link scope i will change this to organization so this will create the link directly to that file that just got created in my onedrive and now finally i need to send this link back to powerapps so for that i will go to new step and search for respond to powerapps there is an action called respond to a powerapp or flow i will select this i will add an output property of type text i will call this file link and the value for this output property under dynamic content from the create share link action we get the web url which is the url that points to that file that just got created and this now completes my flow so i will go ahead and click on save now if i was to go ahead and test this flow once again from a previous run you will note that you will receive this error and the error says that it could not find any valid connection for connection reference name shared underscore onedrive for business the reason for this is i'm trying to use a previous flow run to re-trigger my flow but as part of that flow run there was no connection to onedrive for business i just added the onedrive for business actions to my flow now if i head back to powerapps and if i click on export to excel and if i go back to the edit mode of the app i will receive an error again and once again it's that same error that i saw in flow now the way to fix this is go back to the data tab in powerapps remove the association of your flow go back to your export to excel button you can make sure that you copy your code so i will copy the on select function action and go back to power automate and now once again click on export to excel so it reassociates it back with your power app now that it reassociates it back you will see that the function is played out again so you have to again enter the input parameter that's why i copied it before so i can just paste it back again this time if i preview the app and select this button if i head back in edit mode i do not receive any error if i go to the flow run history and if i refresh i will see that my flow got executed if i select this this will show me the details of my flow run right here and if i go to my one drive i have a folder created right here which says export excel powerapp that's the name of the folder that i created and if i select this i get that csv file right here the name for which is utc now because that's the expression i used and if i select this this will be an excel file which has data in csv format right here the device id manufacturer name basically all the information that i passed from my power app collection directly to power automate now as part of my flow i'm responding back to powerapp so in my powerapp when i trigger this flow i will need the response back when this flow is triggered so for that so for that i can create a very simple variable i can call this where file link so i can set a variable trigger this flow and at the end of the flow run action if i just put a dot intellisense will guide me through this if there are any output properties it would be exposed right here my property is called file link so i can select this and i can close this next step would be for me to launch that excel file right here in powerapps and for that we have a function called launch so i will select the launch function and all i need for this is the address and that is stored in that variable that i just created called verify link now if i go ahead and preview the app and click on export to excel this now triggers the flow the flow creates that file sends the response back so right here as you can see at the bottom it is downloaded that file that csv file experience for me right here and if i select this file here is that excel file that got created for me now this flow that we just created is actually dynamic in nature because no matter what data i send to it as long as that data has a collection of records or an array of records or a table and power apps basically and that too in json format the flow will be able to handle it so let's try and test this out with different scenarios now so here's my export to excel button so i'll just go and copy this now as part of this app i have a home screen here that shows me data from a sharepoint list so here's a sharepoint list called work progress tracker and this gallery has certain filters so i can even filter data here so show me all my work progress tasks that are completed so if i select completed i only have two tasks that are completed so it's showing me only the completed tasks so now if i want to export this data dynamically as the user is filtering what if i want to export this to excel so for that let's say i just paste that button that i copied from the other screen right here let me place it right here at the bottom so export to excel now all i have to change here is the information of that collection that i'm passing now this gallery is a gallery of tasks and this is connected to my data source which is my sharepoint list and if i look at the data here it's it's in tabular format and this is all the information that is provided right here now one of the simplest options here is i will copy the name of my gallery and in my json function my data would be the gallery tasks dot all items now the moment i put the function gallery tasks dot all items i get an error that says the json function cannot serialize tables objects with a nested property called rectangle 2 of type control now as part of this gallery i have certain controls and the json function here is not able to serialize those controls into json format so for this all we have to do here is along with include binary data can add an ampersand and include another format which is json format dot ignore unsupported types so any type that is unsupported just ignore that and now i don't receive any error now if i go ahead and preview this app and click export to excel once again my flow will get called i will have that csv file available right here it's downloaded it for me so if i click open file here is my file with all the data which is filtered as part of my gallery now if you look at this i have the assigned to column which is like the people picker column and sharepoint that's the format in which it returns the data if i look at the category value this is a choice column in sharepoint it is giving me the data but it is giving me the results in the format in which sharepoint returns the data now although the data is available the format is not quite right for text fields i do get the data accurately however there are many other columns here that maybe i would not want as part of my exported excel file so for this we have to do a little bit of work back to my export to excel function now right before this action we can go ahead and write the following function which is for all that means for all the data in gallery tasks dot all items go ahead and collect this information in a new collection which is my collection of data exported that's the name of my collection i'm providing here and now i need to provide the record and right here is where i have to provide the information of my record so i will open the curly curly braces and let's say in my case i would like to pass certain pieces of information maybe i would like to have a column called title the value for which i will put the formula this record dot title next maybe i would like to pass the created by information so i can create my own column here called created by and i will use this record dot created by dot display name and i can do this for every property that i would like to pass to my excel file and lastly i will pass the progress information of my data from sharepoint so i'll add a title here called progress and this will come from this record dot progress which is a choice column in sharepoint so i have to do dot value and now i can close my item i can close the collect function and i can close the forall loop now when this bottom is clicked this will loop through all the items that are available in the gallery which are already filtered and collect that data in a collection and this time the collection will only have three pieces of information and the column names would be title created by and progress next i will pass this collection to the json function right here and once the flow is called and the file is launched i will go ahead and clear this collection so i'll go to clear and clear this collection now if i go ahead and preview the app let's say this time i picked the status in progress so i have three records that are in progress let's go ahead and click on export to excel here is the file that got exported if i open this file here are my three records and here are those three column names that i passed so i can choose which columns i want to pass from powerapps to flow now let's say i head over to a completely different app in my case here i have a power app here that tracks help desk information ticketing information now let's say i would like to add that export to excel feature right here well for that i will go ahead and insert a button i have called it export to excel can even add an icon here and use the download icon so when the user selects this or the user selects the button it will go ahead and download the data now for the button here if i head over to action and power automate once again this will list out all the flows that have the power apps trigger my case here's my flow export to excel it's reusable i can reuse this across all my apps so i will add this to my power app here and once again once the association is made with your app it's asking me to provide the input in my scenario here i have a gallery called the tickets gallery so i will once again execute those same steps i have my collection created now when the export to excel flow will run i need the data which should be in json format so i will call the json function pass my collection of data close my function the link that comes back from flow i can even avoid using the variable i can use the launch function directly here and from the run action the variable that is returned from flow is called file link so i can just directly launch that right here and i will apply the same formula to this download icon on select as well so if i go ahead and click on download once again the same flow that i created earlier gets called it creates that excel file and if i open the file right here i have my data available right here and these are all my tickets if i filter my data in my gallery so let's say i would like to look at all the tickets related to the marketing department where the request type is business card now if i go ahead and export this data to excel as well and here is my exported file which just has that one record information right here watch out for my next part in which i will show you how you can export the data in a pdf format and once we launch the pdf file the user can go ahead and print the data as well if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 154,208

Keywords: power apps export to excel, powerapps export to excel, powerapps export to excel button, powerapps excel spreadsheet, powerapps to excel, powerapps export data to excel, powerapps export csv, powerapps export collection to csv, flow create csv table, flow create csv file, export gallery to excel powerapps, power apps, power automate export to csv, powerapps json to excel, powerapps json parse, excel, export, how to, powerapps export collection to excel, reza dorrani powerapps

Id: tQCBWMR7T64

Length: 26min 8sec (1568 seconds)

Published: Thu Mar 18 2021

Reddit Comments

[u/Geek1Geek]

I'm looking to lookup value from SharePoint list to an external Application outside SharePoint, is that possible?

Thank you