Learn to connect: PowerApps Excel Spreadsheet hosted in OneDrive

in today's show we're going to talk about using power apps with Excel so we use excels our data source we'll build our first app and then we're so there's a couple little tricks to help you make a better app instead of foundation so we can have future videos with much more complicated stuff but first here's our intro hi my name is Shane Young with bold zebras those guys and today's show is all about power apps in Excel alright this is one of our getting started videos when I use this to set some foundational stuff up so other videos can build on it and we can get into more complex things later what we're going to do is we'll take an Excel workbook we're going to store on our onedrive for business location we're going to then pull it down and access that from power apps and just have that as our data source as we write back and forth now when it comes to excel in power apps right it doesn't have to be stored in onedrive you could store it and you have something like Dropbox or Google or you know onedrive the other one Drive you know whatever you want to do there's different places you can do it but for this example we're gonna use onedrive for business the key with that excel workbook is we're gonna need to make sure it's shared so let's just jump over to my desktop and take a look at this so on my desktop here I've logged into a browser you can see here I'm at the power apps home page and what we're gonna do is we're gonna sign in now if you don't have an account you can sign up for free I didn't want to go through that whole process because I've got way too many accounts already so we're gonna use one of my existing accounts so say sign in after 15 seconds or so here's the home page for my power apps and what I'm going to look at over here is where we go to apps so these are all the apps I've built right there's a video right here this one's using SharePoint is a video source there's a link below if you want to watch that video but you know here are the different apps I have available now we're on the write that we're going to take advantage of creating that so when you choose create an app now you're going to see we have some different options so we start with your data and this is what we're going to use we're gonna tell you Vantage of the onedrive for business you can start with a blank app or you can start with one of their app templates which are really great especially in the excel scenario or a lot of fun to use because they use Excel workbooks for most of the template apps so you can kind of get in there and see how they're doing the more complex things you might be interested in but for today we're gonna choose just onedrive for business and the phone way out now when you click this what you might find you might not have onedrive for business set up over here so you might have to authenticate the first time for it mine's already set up so it was no problem already had a connection but if you don't then go ahead and set up the onedrive for business connection all you do is put in your username and password and you don't link everything up for you right now I don't have any excel file so let's fix that so I'm reach over here and grab Excel I'm gonna drag it over so this is the Excel workbook that I created very complicated stuff employee directory I typed in a bunch of random stuff real fast all right the first thing you need to learn about using excels a data source is your data has to be set up as a table so what I'm gonna do is I'm gonna grab all this I'm gonna select it all I'm gonna say format as a table and I'm gonna pick an option that is hopefully not too hideous my table does have headers so that works alright that looks good and then what you want to do is you want to make sure you name your table so I'm going to say employees because when we reference this over in power apps we have to do it by name so better off doing that if you switch over cheat - I have another table set up and so we're not gonna use this one for this video but I want to have two so format as a table and we'll do this pretty blue I'll say okay and then we'll call this departments minutes and are there alright and so in a later video we'll make it we'll pick up from there and we'll take this it will make this actually a selection so we have a drop-down with a selection set of people typing in the department but that's for another day alright so departments looks good go over here employees looks good perfect so let's hit save and then we'll get rid of Excel so I'll drag this back over here and so we want to put this over in our onedrive now I realize that you probably have your onedrive all set up so that you don't have to or you just safe safe Excel but I have so many different accounts this accounts not hooked up that way Yatta Yatta Yatta something's gonna grab the file and drag it over here so there's my file and it will open it up just to make sure I grab the right one that looks like that data that looks my pretty blue all right we're in good shape so let's close this up go back over to power and so now we have our data we'll hit the little refresh icon over here there's our employee directory so we'll click on that and so the now it's gonna prompt us to choose a table and you can see I have both departments and employees and we now we're just using employees but if you have multiple tales you're gonna use all of them what you want to do is whichever table is your primary table right where you're gonna read and write the data to so like in my case employees we're updating and maybe departments and titles and those type of things those ones would all be supporting tables then you'd want to choose for your first one the table you want to write to so in our case employees that way power apps kind of sets that up as a default for you does a lot of work for it to see just a second so quick employees will say connect and this is one of the magics of power apps it's gonna go build us a fully functional app based on that data it's gonna figure it out so look at it twist dududududu so we didn't have to do any work and now we have this awesome app let's zoom in a little bit alright and so to take a look at this the first thing you want to do after you come in here is you want to hit the play button right up here this lets you preview the app so you can see that alright I've got my employees listed here if I click on Luke and finance here view his item details there it is I've already got edit screen built in here it's pretty cool stuff right if we take Luke for example a set it Luke and let's just change his department favorite yeah let's change this title from kid to kid number two I don't know I'm not very creative sometimes but we'll say check let me see the old dots going by we'll go back to the home page here and we'll close out shoots us back there and if we go over to our onedrive and click on employee directory we should now see sheet 1 there's Luke he's now kid 2 right so we're all reading and writing data back and forth our Excel workbook and we did nothing pretty cool right close it out the rest of waiters had to trust me it's happening I'm not gonna keep showing you that it's actually doing it so if we go back to our power apps we can see all right they set this up for us pretty nice we could customize it though right so employee someone employee details I don't just easy well type in boom now if you look at this though it's not really showing that data I want right so we've got things like executive and purple and I don't know I don't like the way they did this so no problem pretty easy for us to fix because this data is being displayed by a control called a browse gallery we click on browse gallery you can see up here where the items are coming from we'll talk about that a second but you can see that over here on the right and price should be my face I don't block you more so we alright I moved but so now if you click on layout here you're actually gonna see that it shows us where is it getting the data from which of the layout options it's using there's several different templates you can choose from but let's stick with a default and then you can choose what shows up as title subtitle and body and so for title in my in this case right I think I want something a little more personal than apartment how about their name subtitle can be email and will make the body favorite color right so just like that alright now it's Nicola Nicola at bold zebras calm purple pretty good oh but perhaps pretty easy to customize so let's take this a step further right we need to call his full name she's not Nicola she's Nicola young I went through a lot of effort to make her last name young so we should reflect that here so what I'm going to do is I clicked on the field and you can see that the text in that field is represented by this item so the current one we selected the gallery and then the first name field alright but it's really easy for us to add onto that right we just add ambrus and do a quote space quote another ampersand and then we'll do this item dot last name right I'm using the keyboard there to select things that I want to type too much and so with that just like that with few clicks of the mouse and the keyboard now we've got a little more of a proper right Nicole Young Nicole that bold zebras so let's let's do something cooler so right now you can see her favorite colors purple so we could just update this and say something like we won't put in quotes just read your text will say favorite color like so oh let's put a space though let's do this do : a space like that I look for mad make it nice and then we'll put our amber sand so this item favorite color so that's nice but you know what colors color I want to format this thing I want to make it pretty no problem so that's our text so over here though on the left I'll put this drop down this is all of the different properties for this particular control and we can set these kind of as we need so we could scroll through these I encourage you to scroll through them check them out see what settings are there but right now if we click on color we can see that right now color is just set to color RGB a bla bla bla bla bla bla which turns out pretty much a black right well I don't understand RGB a but I do understand colors and I know that the color that we want is the color that is her favorite color right well nobody kind of neat so what we could do is something a little different and we can say you know what we want do a color value so color value is a function and it returns a color based on the string you provide it and for the string that I'm going to say this item dot favorite color do something like that and look at that let's preview and so now favorite color purple blue red and we've done the coloring for it alright probably not something you've ever actually do but this is just to kind of get your brain thinking around two concepts I want you to think about one is that with power apps I don't want you to rebuild the boring stuff that you had before right we had boring Excel or like my InfoPath users had these InfoPath forms that since you would write pieces of paper don't build that build an experience right so use all the fancy controls we're not going to cover them today but there's a lot of different controls especially if you just go here to insert you know we've got media controls we've got all types of different controls like sliders and toggles and things like that use those to create an experience that your users want to have right you're going to need a lot more design skills to make really awesome power which is good you like warning new things so learn some design stuff and make things cool so that's where something like you know getting your head wrapped around hey I've got the color here while I change the screen color to it you know or all these options you know we can put in static data like it was there the color or if we don't want to do static data we can always do things like if statements right so if if the title was boss you know make the person's name and bold or you know there's just any type of crazy ideas you can have that makes it a better experience for users those are available and power up so I encourage you as you learn power-ups to think outside the box all right okay so there you go there's my little sideshow trick for the day we made that colored so let's go another layer deep so one of the things that also is very confusing when you first start with power-ups is how to navigate the screens so my best tip to you here I open up the preview alright we're happy this screen yay go into this screen alright well this one's still showing legacy fields that we don't care about it's in a weird order I want to edit this screen if you navigate to the screen and then X out and we get back to the design pane we're on that screen so I don't have to figure out where it was I can see now it's highlighted over here so it is actually detail screen one and so then there is a detail form one that is the part that is showing here and controlling what we see so I clicked on that over here on the right now under layout I have different layout options so where the data come from here's all the different layouts vertical or horizontal and what fields show up so what I might do is I might say you know what I don't let that legacy field at all so I'm gonna select that so I gets rid of it and then now I want to kind of do some sorting here so my new first name and last name kind of pushed those up will bring dept down so that looks a little better now we're smart kids though right we know how to customize this stuff and make it a little nicer so let's let's do some simple customizations and then we'll just a little more advanced so let's take this one now first thing noticed a little card and it's locked so go to advanced you can see that by default and it form like this all the controls are locked it's up to us to say alright I really do want to edit that I want to make I the only edit I do want to edit it so I'm gonna unlock that and then I can change all this stuff so for example I can come in here and say instead of first you know % X whatever that is name I just changes the first name boom so just like that now the form looks a little nicer also what you might do here you got snapped to column so changes to two so here you can see that auto formatted everything into two columns that's good but her email address doesn't fit into columns no problem it's a snapper grab this guy slide it over it automatically snaps to feel filling two columns so now I got first name last name email and so we've kind of done a little sorting for ourselves also if you wanted to if you want to combine first name and last name into one card again right we showed you how to do that previously you could do that okay now let's fix last name here real quick cuz that looks real bad so last name boom you know employee right and we could change this to employee details I don't know something that state but there you go so with a couple of quicks couple of this all that we were able to do it you know the later video I'll show you how to senate but add a button to email this information over to someone alright we'd add a button on this page no problem because these pages are all customizable so do a preview that looks good go back let's check out my record all right so we're looking good we're making our app little nicer you know it worked is fully functional we're just trying to make a little better experience so now let's close out of this I don't know what's not close on this what's going here and let's edit this so now if we added this item oh man we're back to another ugly screen again no problem hit the X if we scroll over here we can see edit screen ones are selected for us this one's also made up of an edit form and the Edit forms interesting though because the Edit form actually has two purposes one is for editing data but also if we create new data that's going to be done here as well so when you do things like when you're thinking about what's goes up here in the title text maybe this is where we have an if statement it says if the form is an edit mode then say edit Shane right and if if the form is new modes a crew add an employee you know we have those type of flexibilities right because we can insert functions up here in the text field I'm not gonna do it we could here will click on the Edit form again we'll go back to the properties and so then here's our layout and then we'll do the same type of drill again right we'll drag first name and your last name up we're drag Department down we will get rid of legacy field because we didn't need that in our app anyway yeah why can I do this with SharePoint I get rid of you know I don't want you can't edit the modify and create data anyway so I get all those off the screen there by default boom so we could do the same process again around cleaning up first-name and lastname you know we could do the two columns we could do four columns whatever we want to do there but I think that gets you through the mindset of what it takes to create an app and just to kind of show you the last little step so we'll say play what comes out of that oh I'm it's you go here was exhaling shame we go back to the home page now the last little thing I want to do here was the fact that this page is sorting but I don't really know what it's sorting by or how it's sorting so let's take a look at that and so if we go here and you click on browse gallery one you can see the items is actually being presented by using the sort by column and the sort by columns is actually using the search function to take advantage of what you type in this box okay so that's what's getting the list of data we're gonna skip over that part for the moment so all of this and then the second parenthesis or second item here in the commas is going to be what field are we sorting by so we see we're sorting by department that is not what I want I want to sort by first name so we can start typing in first and then now we typed in that we can sort or scroll down and there's first name just like that minimize out and boom now we are sorting all of our data right we've got multiple data here we're sorting it by the first name field it's very easy change for us to make in a future video we're gonna go over kind of more details about how that works there's already a video it's linked down below the explains how this will this button just changes the sort order from ascending to descending which to me was like oh yeah that's probably really simple turns out there's like three new concepts that you learn when you go in and break down how this form works and that's really what I like to do right is either take these power apps these default ones and I just go figure out how these different buttons work and that's how I learn about new functions and procedures and power apps pretty cool okay so let's finish up and so to finish up we need to save our power app so we're gonna click over here on file and we're gonna app settings first I'm gonna name this Shane's excel video okay let's name my video I know name of the app the icon here I'll make this one red and we'll make it a house Wow so real easy for us to give account a custom feel you could add a description and we're these descriptions and icons Austria will come to plays in a minute we look at the fact that you have an app on your phone that's where the you want these to show up and be your users figure out what it is it does so all that done we also have a screen here for screen size and orientation probably a bad idea to change now because we designed for the phone fat the phone form factor but you could you can also design for web page form factors or mobile device like you know surfaces and those type of things so but this is all been designed so we will leave all this alone and now we're gonna hit save and so we're gonna save it to the cloud Shayne's excel video and hit save and save it's really important right because now that I've saved my app going forward it will autosave every few minutes for me but until you save it the first time manually there is no autosave so really when I first started this video step one should have been hit save right you should save early and save often to keep yourself out of trouble alright now it's done saving we're going to click on share this app and so then this is where I have an interface for sharing and publishing this app out to other users so I want to publish it out to my boss Nestor just like that he has the ability to use it we'll hit save and so you get the nice cool working on it message and it's going to save so where this works out really good is so if Nestor has power apps app installed on his phone for example the next time he logs in this app is just gonna show up and so then from there he can go in and use the experience right there on his mobile app and have a lot of fun and be like oh I guess I should give that Shane guy race he did a good job making me an app and actually so you know what we'll do is I will log into it here on my phone and then anywhere after you know three or five minutes or so it'll show up here on my list of apps just by logging in it's already on my phone and then I will do some screen recording here and give you guys a nice little you know show a demo of how the app works on my phone so that's what you're watching right now on the show right so I'm going here I swipe over okay I got that and so then now my app is loaded so I can scroll with my finger I can click on chewy young there's his details I could do it edit type in pretty cool go back go back and hit the plus create a new employee so lots of cool things that we could take advantage of here with the the apps alright so I think that gets you started with Excel for power-ups hopefully you've enjoyed this if you have any questions you want to learn about different things you wonder why did this or that or you have suggestions of places you want me to go I see a rabbit hole right go down that rabbit hole leave me comments below always happy to help and if I do anything to help you just reach out to me through the comments or you can always hit me up on Twitter hit subscribe button you know how you know the drill I well thanks and have a great day hey it's me again just a reminder if you don't mind click the old subscribe button over here that always helps me out or if you want to work together you can always hit me up through the bold deborah's or if really what you want is some more of these power out videos which is probably what you want then the playlist is somewhere on the screen here alright thanks have a great day

Channel: Shane Young

Views: 244,575

Rating: 4.9360528 out of 5

Keywords: PowerApps, Learn PowerApps, PowerApps Excel, PowerApps Excel Spreadsheet, PowerApps Excel Data, PowerApps excel tutorial, power apps excel, power apps, powerapps excel example, connect to excel, powerapps onedrive, powerapps app excel, powerapps excel data source, shane young, shane young powerapps, powerapps in office 365, powerapps tutorial, microsoft powerapps, microsoft powerapps tutorial, microsoft powerapps examples, powerapps training, learn power apps microsoft

Id: -Z_13J02RPU

Length: 21min 59sec (1319 seconds)

Published: Tue Jan 09 2018