How to Export a PowerApps Gallery to CSV File using Power Automate | 2023 Tutorial

hello everyone Michael back with another video in this video I'm going to show you how to send a powerapps filter gallery to a CSV file if you enjoy team SharePoint powerapps power automate videos feel free to subscribe to pull out more videos in those areas so let's get into it I am using my marketing SharePoint for the data source and I'll show that right here so this is my data I'm working with and as you can see my PowerApp just has a title a search field for the filter and a gallery so I'm going to be filtering this Gallery sending it as a collection from powerapps to power automate and then I'm sending the power automate link to download the file back to powerapps so I'm going to show you how to do that the basics of my PowerApp so I'm able to search the SharePoint list so if I want to search for an employee which is my search field right now I can search for employees and as you can see I type else and I get else and I'm doing this by having a in the items property of the gallery I just have a if statement if the input of the search field so this is input first name if it's blank or empty I just want the whole employee list but if it's not blink or empty then I want to filter the employee data list by what's in the search box and that is how I get those items so in the top right hand corner I have my icon for the user to download the CSV file and in the on selects I will show you how to send this filter gallery to over to power automate which we will be doing some work in so to start we want to create a collection that has all the filtered Gallery data on it so we want to set we'll do VAR collection actually I did that wrong we'll do coal and then we'll do gallery items so this will actually be the gallery name that's all items so this will get all items all the items in the gallery and all the comms with it and I'll show that in a second so if I clicked on this right now our gallery has Alice in it if I click on it so it ran and as you can see we have one record and that will be Alice if I remove the text here and I click on it again as you can see we have all the records from the gallery so you know that the filter is actually working so next thing we will do is go to our power automate and start creating the flow to connect it to powerapps this will be an instant Cloud flow and I will name it marketing CSV report and the trigger will be powerapps click on create so you we don't need to do anything for this step and the next step we want to collect The Collection data so this will be a compose and I want to rename it because what I rename it is what is going to be power-ups going to be looking for it so if it was just I just left it as compose uh and the PowerApp it'll show as uh for the parameter compose I want to show data so it's easier for the user to understand whoever's working on the app that hey this is the data field we're looking for so if we go back to the PowerApp I want to save the power automate flow so we can actually import that into the PowerApp and start working with it all right the power automate button on the left hand side add flow and we want to refresh as you can see it popped up now because we had the powerapps connector first so it looks for that we have the marking CSV report so it should ask us for one input and I want to go ahead and add some more functions to my button up here so I have the set action so I actually want to do a concurrent action as well so this is going to run more than one action at a time I just want to do this so I can send the notification message that hey the Blunt's been pressed for the user so they understand that you know they press the box you can click on it and sometimes you're like oh did I press Aaron out and then they spam click it we're just going to let them know that hey working on generating uh CSV file and we will just do information for the notification type if you aren't sure what that is that is you know like a different message at the top for a different color and for the second thing we want to do a we just want to run that flow to make sure it's working so we're just going to do marketing actually I need to put a comma there marketing PSP report run so it's going to look for one parameter and that parameter is going to be the whole gallery items and that is everything I want to run and it looks like we have an invalid argument all right so I'm getting this here it's expecting a text failure I actually have to sign this collection as Json so I forgot the function so let's go back in uh you want to put a Json function before your collection it'll be the collection Json and then you want to add a few formatting you're going to want to ignore the binary data option and then also we want to do unsupported types I don't really know why we do those two I saw another video and it works pretty well so something with the binary data I don't support types could screw up the power automate flow so we'll leave it as that we want to add another parenthesis right here just to close up the opening parentheses so it looks like it's running now if I clicked on this one it's going to send the data over as Json and as you can see it sends our notification for the user which is nice all right as you can see the flow RAM and we have all the data right here in Json so let's go ahead and go to edit new Step so I actually want to select what data I want from the collection and you do that with a select statement and I want to get it from the data so the data outputs and I want to start mapping what values I want and to do this I want to it's easy to do if you have the SharePoint titles because that will be the names of the columns you need also if I open of notepad plus plus and I paste and what we had before I can kind of find what values I'm looking for so title titles like the name and everything and I'm just doing this in the notepad plus plus because sometimes when you have spaces you can get a little kind of different formatting so you just want to be careful when you're mapping everything so this will be first name and that's the title so for the value of this I'm actually going to do a expression so I want to get the title field from the body of the data so it is item parentheses uh question mark and then it is the name of the column so in my case the title field is the first name so I'll go ahead and copy that so it's easier so I can work a little quicker press ok and I want the last name as well so if I go back to my notepad and just search for a last name as you can see the column name is just last name no space and I will capitalize first name just at least two match and I will paste in the first item and I will type in last name so I get these two Fields what other fields do we want we will do we'll do job title and then we'll do salary you can do as many as you want I'm just eliminating it to those fields for uh the video so we'll do job title we go back in here my notepad plus plus a job job title is labeled this I'll do my item format again question mark and job title last one is salary we'll do item just for a warning if you do more complex fields in SharePoint with like Choice Fields um multi-choice fields and like person fields it will you'll need to do different items so let's say if this was a person field it's an array so you have to do like let me see if I can do it here I think I do have a NRA so as you can see we have editor here and in this array we have the editor and then we get into the array there's claims department display name so if you want to get one of those secondary values it would be editor you can do a question mark question mark means it's an optional in Json I'm I believe so so if you do editor and then I did display name it will grab the display name that's how you do that format I don't not doing a person field in this one so save that for another video doing complex columns all right so if I save it so I can test it out we will run the flow again so if I go back in here and I uh we'll just edit something so we have two results right here Chris and Leslie so if I click on this button working on generating the CSV and we're just doing this attach the flow as you can see it failed the front property value and select action must be a type string the value must be the value must be an array so let's go ahead back into our select action and it says it needs to be it could set a need to be an array really must be an array so we'll go and we'll add the Json function in front of that so we'll go back to our select we will copy the outputs and we will go into the expression type Json and we'll do that on the outputs of the data so it should make it an array that power automate can use so let's go back in and test it with a recent trigger as you can see it ran successfully now and we're able to use the data we have Chris and we have Leslie and we have the values that we want right here all right it looks like job title is actually a choice field so I might have to do some more work on that all right so let's get into creating the CSV file so to create the CSV file we want to add a new step CSV and we can just choose it from our select output because it will work with that data so now I need to create the file for a user and we'll use the create file the SharePoint action site address we'll save it in my marketing I will just save it in the main directory to let me share documents the file name you can name the files whatever you want just be aware if you try to create a file with the same file name so you have to make it like a dynamic name not static because if a lot of users use it the download button and the app it's going to try to create the same file name if it does that the flow is going to fail so you have to create something unique you can either do like uh the like CSV report and then you can actually do like an expression for the UTC now let's do convert from UTC because I want to get the time in Eastern Time Zone so that will be UTC now to get the UTC time right now destination time zone so for me format is Eastern Standard Time your format might differ so look that up online what time zone you're in for the format we will do the year the day a month and I will also do a minute because I don't think you could have some users doing it at the minute so if you want to add seconds as well I'll do seconds now the file might look a little strange this should work so I will close up that bracket because it's invalid so I think I added too many parentheses and then after all that we want to dot CSV and the content of the file is going to be the output of the CSV cable all right so I actually want to have the user download this file in the browser so I need to get a shared hyperlink a shared link from the SharePoint site so if you do choose an operation create sharing link for a file or folder so this will actually create a sharing link that the user will download in the browser and I'll show you how to do the downloading so marketing Library name this would be the documents because the documents is where we're storing the file free the item ID for the ID the link type I want them to be able to view and edit and the link scope anyone uh we just only want people in our organization so now we need to get that sharing link and send it back to powerapps as a string and we can do this if we type powerapps respond to power up our flow we need to add an output this will be a text and we'll just do file URL and we will do the share link so this will just send the link over in text we want this to be downloadable so we will add a question mark download equal one so this will actually create a new tab and download the file there so let's go ahead and save this and we need to edit our PowerApp and re-import the power automate flow because if I try to run this now I added the SharePoint connector here and in our previous import of the flow it didn't have that connector so it doesn't know what to do when the SharePoint connector comes if I click on this it's going to fail and there's the failing let's check it out you get this an invoker connection override failed so what you need to do for that if it ever comes up is just remove the flow from the app and re-import it so now it's re-importing the flow with the right connectors aka the SharePoint connector we didn't have so we just need to edit our on select for the button now to actually launch the URL and also store the URL in a variable so after my notify statement we're going to add a set function on here and we're going to do CSE file URL comma then I want to put what we received back from from the flow so to do that we need to go to our DOT run and just add the file URL and that is actually the respondent Power Upper flow that is our label right here so just connect the match up the parentheses here and to launch that URL there's actually a launch feature in powerapps which takes in a string and we can use the the set variable recreated that stores the string and launch that a lot of work a lot get a little confusing but uh this should work now and let's test it out I will go ahead and we'll filter this so it should only send Joe in the CSV file click on download working on generating the CSV file pop-up blocked in the top right so we want to always allow pop-ups and redirects from this done so let's run it again because it blocked in the browser I'll click on run as you can see it download the file so let's open the file so for the file name it should be pretty unique we got the date per year something screwed up in there just check that out really quick it's going to bother me so I did the minutes before the month the months have to be capitalized minutes have to be a lowercase that's why my file name looks strange because it's supposed to be January 29 2023 we have first name last name job title and salary so I kind of screw up on the job title that that is a choice and choices get sent to power automate with arrays attached so to fix that really quick I want to go to my select or the job title let's copy that paste it and then I also want to add a question mark value let's press ok we will run it again let me close this out all right it doesn't want to close for some reason so we're just going to go ahead and run it again fix the title all right I just removed the question mark so I'll still get in the air so let's save it again and we will run it all right so it looks like it saved correctly now I just removed the question mark and to do that choice field I will show the code again so just the extra bracket with the array value you want so I hope that shows you how to filter a gallery and send the results in a CSV if you have any questions or any errors popping up feel free to leave in the comments I know this is a longer video but there's a lot of explaining and kind of figuring out a lot of Errors you have to go through to get this to work I left the years in so you guys know what to expect I hope you enjoyed the video and thank you for watching

Channel: Michael Alex

Views: 11,518

Keywords: Microsoft, Microsoft Forms, Forms, Microsoft SharePoint, SharePoint, Teams, Power Automate, Power Platform, Microsoft Flow, Flows, Automation, Tutorial, Guide, 2022, Microsoft Outlook, Outlook, Email, export gallery to csv, powerapps, powerapps tutorial, powerapps gallery, powerapps reporting, reporting, power apps, csv file, excel data, exporting, exporting powerapps, exporting data, powerapps to power automate

Id: WboI7-8vonk

Length: 18min 37sec (1117 seconds)

Published: Mon Jan 30 2023