Export filtered gallery records to CSV file from PowerApps | Export to CSV | Power Automate FLOW

hey guys welcome back to powerapps learning channel in today's video we will be looking at how we can export the gallery records from the powerapps to a csv file you have a gallery and you have some filter options provided so when user applies filter you you get the filter records in the gallery and user wants to export it to csc file or excel file so we are going to look at how we can do that and what are the limitations of the different or approaches we are going to use so before getting into the detail let's quickly have look at the demo this is my sample app and i have some filters here i have a tenants and i just want let's say i want tenants with six months contracts and the status is in progress so these are the records i want these to be exported uh so i've just clicked on export button and it is it has triggered a flow which will generate the csv file and you can see it already the csv file is generated we can see that if we check the record you can see that this is all six months contract tenant with the status in progress i'll just close this files now let's just see how we have implemented it and what are the options we have now we we of course there might be multiple options but as far as i know there are two options which you can use to export the data from powerapps gallery to a csv file before getting into the detail you can generate excel file also you can use flow you can use the excel online business connector first kind of complex flow you have to write you have to store the excel file as a template you have to add the rows in the table in that excel you have to create the excel file template with the table and all that so it's a bit complex here in this approach creating csv file is really straightforward and user can anyway that it opens the excel client itself user can save it as a excel file when it opens at his end so this is pretty much accepted approach by the end users they are okay with the csv file also now coming back to the options we have two options we wherein first option is you can gather the data from the gallery and send it as a json string to the flow and flow will create a csv file and the second option is you just send the filter parameters like you the way we we just saw you send the filter values of project status and all that you send the filter parameters and in the flow you get the records from your data source using that filter parameters and generate a csv file and send it back now with the first approach the limitation is user need to keep on scrolling the gallery in order to load all the records because in the gallery only hundred records are pulled from the data source and if you applied a filter and the filter is supposed to give you 500 records so user need to keep on scrolling till it loads all 500 records and then you can generate the data from the gallery and send it to the flow so this is like dependency on the user to load all the data in the gallery so whatever data is loaded in the gallery only that will be exported with the first approach okay so if the data is not loaded in the gallery then it won't be part of your exported excel file or csv file so in order to handle that or how will you i will make sure that the the applied filter and the data is not mis missing in the exported file so to avoid that you can go with the second option that is what we are going to look at in this video is you just send the filter parameters like project is so and so the contact dates and so on so status and so on so whatever user selects from your filter you just send this filter parameters and you generate the filter query in the flow and general csv table create a csv file and respond back with the url of the file so let's just jump into the actions what we need to do so as i said we'll be using the second approach which is you don't have limitations over here you will be able to export all the records from your data source which are matching with the filtered what user have applied in the powerapps so we need to send the filter parameters trigger the flow generate the filter query get the items create csv file generate this generate the csv file and respond back the url let's just look at the so first we'll look at the app app is straightforward we you just have a gallery control here we have some filter drop downs and in here if you look at the items property of the gallery uh filter function this is my data source and i have filtered on three different columns or three different fields which is project contract and status so first i'll be looking at if the drop down value is selected if it has to match here if it is not selected and if it is set to all then many of you might not not be aware of this approach uh how to get all the records if you apply this if you apply the filter so to to do this you have to add all or anything whatever text is you think that this convenient for your requirement or for your application you can add that here let's say i want to show all projects all contracts all status so i've just added all when it is all we are not going to apply the filter and when it is actual value other than or all then we will apply the filter so how to handle this is you just add or condition here and you can see that so project is so answer if it is selected or the value in the drop down is all so it will return true and when it's true it will return all the records irrespective of the filter value so now this is uh the part of how you can filter in the gallery and on this export button this is i have called a trigger trigger to flow and you can see that this is my flow which is with run parameter and we are sending just three values here depending on how many filter you want to apply in my case it's just three filters project contract and status so i'm just sending the selected values to this flow and the response is the file url which i'm setting in the variable and once it is done all this is done i'm launching that url the exported file url i'm using opening it using launch function now the main part let's switch to the flow wherein how we have created that flow and how we have created the query the filter query so you have to create a flow with of course powerapps as a trigger and then depending on how many filters you have you just create the variables so in my case i have three filter parameters project contact and status so this is pretty straightforward you just go ahead initialize variable give a name to variable and the value you just click on as ask in powerapps okay so this will create an input parameter so i'll just delete this well you have the input parameters now all the filter parameters which are needed now next main part is creating a query before getting into this let me just take you through all the actions this is these two actions composion query value is where we are generating the query next is the gate items as my data source is sharepoint list we are getting the items from that list and as you can see here i have passed the filter query parameter here i've generated a query and i'm send i've sent it or passed it to this particular action so i'm getting the tenant records from my data source then you have another action which is create csv table so see this create csv table what you need is just pass the output of your previous action within items values and in the columns if you if you keep it automatic it will return all the columns from your list including hidden columns also so make sure you use custom and you just provide like okay i want 10 and name then just pass in the the column name i want contract contract value so likewise like this you can let me just put it here so like this you can add all the columns whatever you want it in your export or exported csv file and this will generate a csv table now next action is creating a csv file so we have used onedrive action of course so this will create a file csc file in onedrive create file onedrive for business create file choose your path you can create a separate folder or you can create this file in the root folder also give a name usually i use timestamp and file content would be the output of this action which is create csv table now if you look at my actual action which i have already configured is i'm creating a csv file in the root folder as i said the timestamp format time function utc now ddm this is like a regular i assume that this these are the basic things you must know it already and extension you have to add as dot csv and this is the output of previous action i'll just delete this action so till now we have got the items from the list we have created a csv table just delete this action also and then we have created a file in onedrive now the next action is you have to create or get the url of this file which is created which you can respond back to the powerapps so this is like you just add a string variable name it and respond back the url web url of the created file now to get the url what you have to do is use create share link by path action you can find it easily create share link by path and just get the output from the previous action which is create file you can get the path of the file and link type you can choose view or edit you want user to view that file or edit the file and make it organization because you of course you don't want to share it outside the organization just keep it our organization this will give you the url of the generated file which you can send it back one tip here i have added a download is equal to one this will download the file actually if you don't add this or if you don't append this parameter it will open the excel csv file in the browser in the new tab in the browser but most of the user like they expect it to be downloaded locally on their machine so you can add this parameter download is equal to one now let's just delete this and let's just get back to the query how you can generate the query now if you look at this filter query parameter here if you look at the the tooltip here which is given you this is like the basic odata format you know to provide the column name let's say a contract is my column name equal to greater than less than whatever operators you are using and in the single quote you need to provide the value now this is just one part or one parameter of the query if you have complex query like in my case i have three parameters to be passed either you can do and or or and then you have another parameter or column which you need to pass so here i have three parameters and i want to and all the parameters now here writing it directly is pretty straightforward you can do it when it is a static part or you want to hard code the query this is fine but when you want to generate it uh dynamically depending on the values which you are getting from the powerapps you're getting the project value you're getting the contract while you're getting the status value and you want to push this or embed these values into a query now to do that i have written a function here in the compose i've used compose action in that composition i've written a concat function because you don't know whether user have actually selected a value like this a six months or user have kept it all so you have to decide you have to get that understand that also whether the value is actual value or it is all depending on that you you will decide whether to include that particular column or parameter in the query or not so what i'll do i'll just this i have already copied it in a notepad to elaborate in detail so you can see this this is my concat function so i'm using making use of not function equal function this is my variable which is we are getting as input from the powerapps we'll be checking if it is all or not so this is equal and then not so we are checking if it is not equal if it is not equal then we will include this particular uh parameter in the query so we will include this project equal to single quote the value which we have got from the powerapps okay so this is your first part of your query now as of as i have three parameters to be passed i need to end it so you can see it here this is my first if condition which is checking if project is selected or not if it is selected we are using concat function and getting it with the and also at the end now then we then i have next parameter to this main parent concat function which is another if same for some next column or next parameter then i you have contract then your status is same now here you let's say you have a project selected specific project and contract and status you mentioned all so when it is all you nothing will be added in the query right because we want all the records irrespective of contract and status but we want project which is selected but then this and will get added at the end so then i have added next function to remove this and if it is there at the end of this particular compose output if it is there then we will need to remove it so again i have used i first checked it if it is there if it is there we will remove it so we are checking if and is there if it is there we are just using substring function and passing the start index and the end index now get back to the flow the first i'll put these functions uh this code in the description so you can refer it and you just modify it depending on your columns how many columns you have depending on the condition also you want to apply and condition or condition so this is first compose function and then then i have a query value variable where we are removing the the and which is getting added extra if it is there then we are removed if it is not of course we are using the as is output of the composite action now this value is being passed here in the filter query which will generate so if i show you the if i just let just go back and see here the the already ran instance of the flow to see how we get the so if you see here this is how the actual output of the compose action would be contract is equal to six months approval status is equal to in progress now if you see i had selected project all that's why it was not part of the query okay and in the query value again it is same because there is no and here so it will not remove anything it is as is and this is what it is passed uh to the gate app gate item action this query this will give you all the the records matching to this filter filter query now the one more thing to tell you here is in this gate item action if you go to the settings you just enable this pagination and you can add here 5000 10 000 depending on you think that much is expected you can add that so this will this will make sure that you will get all those 5000 records from your data source for the matching filter query now if you look at the the earlier approach which we discussed in the presentation this won't be possible to get all the 5000 records in the powerapps in in your gallery and then you export it you send it to the floor it will be like user need to keep on scrolling and scrolling to load all that data in the gallery first and then click on export that doesn't make sense so you just want to apply the filter and click on export so that he can uh play around it on there in the excel file so make sure this is important step pagination enable and add the threshold value here i'll just cancel this now let's just make sure everything is fine and we'll just do one more round of testing okay you can see here i have created five columns and passed in the as this is my choice column so you need to pass the value here project value apartment value and we have used it here in this create file create share link by path and responding back with the download parameter so i'll just save this i hope you have not made any unexpected change in this i'll just save this and let's just go back to the powerapps just refresh it let's reset everything and let's select particular tower annually and i want all approved record so let's say there are four of course this is my dummy data i just have limited data here in actual scenario it could be hundred and thousands of records where this approach will really help you so let's just click on export and let's wait for the csv file to be generated and downloaded automatically so you can see it it's already here let's just open it now for the excel file if you have any other simple option rather than going with the template and adding the rows in the table in the excel file if you have any better approach you can put it in the command i would love to hear about it but this is a simple approach what i found is creating a csv file you can see that this already here and user can easily save it as excel file so that should not be a problem with the end user yeah that that's it i guess in this video so i hope this will help you guys you know i'll put all the functions in the description so you can refer that and yeah thank you so much for watching and let me know in the comments if you have any questions or if you have any better approach you can put it in the comment i would love to know about it and i'll try to answer your questions thank you so much for watching again have a good day and uh we'll try to upload another video soon so try to like share subscribe and yep thank you bye

Channel: Power UP with Sarvesh

Views: 12,007

Keywords: Export to Excel, Export to CSV, PowerApps export data to excel file, Export gallery data to CSV file, PowerApps, Power Automate, FLOW, CSV File, Excel File, Development, Microsoft, power apps, business app, build an app, Power Apps, if this then that, tasks, task, steps, office, office.com, flows, power, automate, platform, CSV, export, template, Microsoft flow, action, trigger, schedule, build, develop, sharepoint, share point, database, microsoft 365, help, guide, how to, tutorial, 365, list, sql, code, app

Id: 95sPDky0bU0

Length: 22min 36sec (1356 seconds)

Published: Thu Mar 24 2022