Power Apps Export data to Excel with Flow - Collections to CSV file

in today's show until I exporting your power apps data over to a CSV file that you use an Excel thanks to our little friend power automate aka flow there nothing too exciting but as this one's concepts I want to explore a little bit about sending collections of data from power apps over to flow and then once you've got it there parse it and then you can do whatever you want with it which is really the whole idea this video but first here's our intro hi my name is Shane Young with power apps 9-1-1 those guys in today's show we're going about exporting a file to CSV and what we're gonna do is we're gonna start with some power apps data we're going to write gallery we're going to select the different records we want and we're gonna press a button and Boop we're gonna magically get a CSV file but really what we're gonna concentrate on because that's not always the most exciting scenario but I want to really concentrate on just the mechanics of it alright so how do we get the data over and power app selected and then export it so we can send a collection of data over to our friend flow and then once the questions in flow how do we work with it because now that you've got a collection of data in flow you can spit on a CSV file you can make your own excel file you can make an HTML table you can import it into sequel I don't care what you do with it but really what I wanted to concentrate on us that mechanics of getting the data over there in the byproduct is a pretty popular topic which is exporting a CSV so soundly caught of fun of me let's switch over to my desktop and take a look ok so over here my desktop here's the quick little app that I threw together one of our galleries you've probably seen this one once or twice before and I just made it so we could select a different records Boop so I'm gonna go get four records and then we're gonna say export to CSV this is going to fire off a flow and after a couple moments we should be able to go over here and we should see there's our file click on the file and boom there you go we've got our four records exported into CSV so that's we're gonna do we're gonna build that we're gonna have a little fun but remember really what I want to talk about it's the mechanics of this because that's I think the big win for you here so let's go back over and let's look at our power app real quick so to populate this gallery it is using a collection called employees and so if you look here all I'm doing is taking my normal SharePoint list called employees and I'm putting it in a collection and I'm adding a column called is chosen and I'm setting it to false and so then if you look at my little check boxes here you know if I uncheck I am just taking this chosen setting in the false and if I check it is setting is chosen or true and so I covered that in great gory detail in the for all video so I'll put a link to that somewhere around here down there up there somewhere that will if you want to go back and watch that kajak whoa I've never seen that but my assumption is you've kind of I seen that video and you're right I know how to select records so then what we do is once we've selected records we need to send those over to flow right we want to send a collection of flow because that's that's the fun here and sort of do that it's a multi-step process so you can see here that the first thing I'm going to do is I need to drop some columns and so the reason for that is that in order to send a collection from Tower apps to flow you have to use the JSON function to get it in the right format and the JSON function does not like record columns so in this case where I was using SharePoint I have those record fields like face author created by editor all that stuff all of those fields they were breaking the JSON so I had to drop those out now this video doesn't have to be about sharepoint though you can be exported from sequel or CVS so just keep in mind that you might have to shape the data a little bit to make sure that it has the right pieces and then it's not getting up well so my case I had to drop some columns and these were all my record columns right and the easy way to tell there are record calls you click on collect employees author his record and his record right so face is an image column favorite colors a record so those are the ones that I figured out I had to drop okay that's the first drop all those secondly then I run I just create another variable called var format JSON and all it is doing is it's using the JSON function which we've covered in several videos and it is putting it into the ident for format and the nice thing about that well it doesn't preview well here but it puts it in a nice little like tabbed out format when it sends it over we had some debate I got to work without it one time I was required another time so I'm just gonna go ahead and say it's included and you should always put it here but I don't think you actually have to have this but it made it easier for me and then we send the data to flow so on the flow side what are we gonna do well instead of just walking you through the flow side I want to I want to actually build the whole flow with you guys so instead of minutes we're about to build a CH what we're gonna do is we're gonna build around some overhead of flow I'm gonna say new and I'm going to say I want to do an instant and then we'll call this video export to CSV like that and then we will say power apps is our trigger and say create now keep in mind remember if you are a curated subscriber you can download this video and you can also then download these files so that we don't have to rebuild all this yourself but if you're not no worries I'm going to show you how to do it all so first thing you need to do so when you're trying to send the data over from a collection you have to figure out the format before we can actually use it so what we will do is we put a compose action right here just like that and so then for the compose action we're going to say I want to ask empower apps and so then it's going to ask for the compose inputs and believe it or not at this point you're actually you need to stop because what we're going to do is we're going to use this to figure out the next part of the puzzle so you say save okay so now that is save what we're going to do is we're gonna go over here to our flow and where we name that we named it video export to CSV we're going to add a new button so insert a button I'll pull it down here and so what we're going to do here in this button is we're going to say alright I need to add that flow so actions and then power automates and somewhere in this hot mess and it is a hot mess video export a CSV I have a lot of these will pull this over and so that's gonna say I've got one input that I want there it is and so then what we want to do is we're going to just pass all of this stuff right so I'm just gonna copy these two lines out and then we're gonna put in a bar format a JSON because we know that that's the power outside of getting all the data ready but we don't want to go through all that so we're gonna take advantage of it just like that okay so then now if I press this button some hold on the Alt key press the button it's sending the data over and so in my flow here if we look it succeeded shew I'm gonna click all in the succeeded flow and the reason we did this hey I like baby steps but more important than me and like you my baby steps is that you need this output so we'll put my cursor right here I'm gonna say control a and control C to copy it because now we're going to edit the flow and now you know that's just really a big old text string so what we want to do is we need to parse that JSON so we're gonna type in the word parse and parse JSON and for the content section we're just going to use the compose output easy enough and then for the schema we're gonna say generate from sample we're gonna paste in all that craziness this is the reason we had to do that test run but then at this point flow just figures out all that so if you're like Shane what's JSON and how do I understand and I don't know who cares it did it alright it's happy now so then now we can say save and then we'll do another test but this time I don't have to go at the power house we just test from here and so we should see oh look at that it out put it in is actual fields that we can use whoo-hoo so now we're in business now we have a proper table if you will or an array over and flow and you can do whatever you want with it so I think I promised you guys we are going to you know turn it into a CSV file but you can do it dude you see fit so let's do this let's they edit again so now we've got the data over here I'm gonna say new step and so then I'm going to type in CSV create CSV table right there in the middle who knew and so the from this is a little trickier I owe I did not mean to do that I'm sorry I almost delete that and let's delete that and delete that as well so we're just gonna delete everything I'm not gonna mess that up OOP all right so who do see a new step CSV create a CSV table and so what I was trying to do is scroll down and over for you so what you're looking for is any from your parse JSON action you do not want one of these specific fields necessarily but it's important to note they're here so you could build a solution piece by piece but in the our case because we're trying to export a CSV we want the whole thing so we're gonna take the body so you say body and then you don't get a pot each you know you got the right feel when you don't get a part each under show advanced pieces here you see it creates its own columns so it's gonna figure it out good job flow and then last but not least we're gonna go in here and we're going to type in create file and so look I can create a file in onedrive for business I can create a file on SharePoint I don't care what are you doing so I was just a crate in SharePoint I created the last time with onedrive so create and share points or create file and then it asked me where I'm gonna choose my power apps videos and then what folder do I want which means what document library I want the one called documents and then we're gonna call it demo dot CSV and then for the file content from the create CSV table action here you just take the output and I'll do it so then now we're gonna hit save now I'm gonna I'm gonna break something here I'm gonna break it on purpose cuz you guys run into this all the time you never know what it is so all right cool let's do a test so you go up here you say tests oh yeah I just use that again do this and so then this test is gonna run and then it says flow run failed and you're like what happened you get real angry you know how I know you get real angry cuz all I get real angry when this happens and it says hey I can't find the SharePoint connector you know you you know share points there what is it so what it is is that when you did the run when you connected the power apps you had not made this sharepoint connection so it doesn't know how to associate that power apps data with a data set that has the sharepoint connection it gets real wampie all you got to do this is the beauty of this go back over here why not all grab this will copy all this control see I'm going to click on data sources and we're going to say video export a CSV No thank you we're gonna remove the flow that we just added remove and so the buttons mad fair enough so now we're gonna add it back I'm gonna scroll the bottom I hope it's at the bottom there it is we're gonna put this in when we add it the whole reason I copied the code watch is it's gonna get rid of my code here just a second ah no problem to read that out paste it in so everything is exactly the same but because we deleted the data connection and reinitialized it it went ahead and syncs up back up so then now if we hit play and we got our forward down here wit button again this sends the data over we'll go back to the run history the one we just did is running it succeeded nice job and we can go in here and see everything is happy and so then now at this point if you were to continue to edit you could test but so the key was if you add a new data connection like we did any time you add a new data connection you've got to go over to power apps and start the cycle over again and let's just go back out to SharePoint real quick ok so here under documents and we should have our lovely little CSV file oh man you know what I put the file in the wrong location mad flow is hard let's go back over here O's editor flow again I meant to create the file not in documents but in shared documents let's try skin there you go so the it is is all you can have to do is we run my test now alright because I can run the test over and over again because you know we got everything sunk back up so let that finish real quick okay so now we'll go back over here and then now and here we should see we do a refresh there's our demo CSV now if we click on the file there's our data I mean who doesn't enjoy putting a file in the wrong spot okay but that is honestly it right we've done and we done all these things so if you want at this point you could email that a link for that file off you could send that file in an attachment you could send a link back to power apps with that you could know the world is your oyster at this point if you get this you've mechanically got what you need you know because we won't go all the way here but let's just do one more little thing so then remember also that if you go down here like alright well that was great but what I really want to do is go create something in like a share point so you might say all right I want to create an item and so we go say create an item we would choose a SharePoint site you know back over here maybe it's a different perhaps videos it's a different employees list but we're just use the same one just make your lives easy so when you choose the list when you go for title here if you go over to the right and then kind of down here if you grab out of the parse JSON remember there's the title field we sent over in the collection if you choose this I just want to remind you guys what happens so it's choose title right all of a sudden then you get the apply to each right why do we get the applied each because the parse JSON action that's a table of data and so this is saying hey for every record that comes back so I think we sent over for records right for every record that we send over do create a new item and use the title as the title so not a big deal but you know between those two things now either you can use the whole table to create a CSV or you can piece-by-piece do this and apply to each it's automatically gonna go to push your data in here here's I've got your SharePoint collection or your power apps collection data into Flo yeah all right so hopefully that helps you guys hopefully you enjoy that you know you kind of walkthrough understand mechanically how all those pieces work because there's a lot going on there that took to make this simple little demo but this comes up enough that I thought you guys would enjoy it there's always forgetting ideas on future videos we've got comments or questions about this one even below I respond to all the comments it's times it takes me a week but I get them all in there also remember to check out our training resources I am diligently working on re-recording our big class right now so that's our power apps and flow for app builders this demo is just a small subset of a larger section in there so I just kind of pulled this out for you guys because I'm a nice guy but you know the bigger class has more details more fun this in full fidelity so whoa all right I think with that I'm gonna say thanks and have a great day

Channel: Shane Young

Views: 113,648

Keywords: Shane Young, powerapps911, powerapps export to excel, powerapps export data to excel, powerapps export collection to excel, powerapps export csv, powerapps export collection to csv, flow create csv table, flow create csv file, microsoft flow csv, power apps export to excel, export gallery to excel powerapps, power apps, power automate export to csv, power automate tutorial, powerapps json to table, powerapps json to excel, powerapps json parse, jon levesque power automate

Id: imhxy3REgXc

Length: 16min 27sec (987 seconds)

Published: Tue Jun 23 2020