Best Practices with Sheets & AppSheet

[Music] all right well good morning good afternoon or good evening wherever in the world you're joining us from today my name is jennifer i'm a product marketing manager that works with app sheet and today i'm joined by two very special guests to discuss a very important topic on working with app sheet and google sheets uh christian would you like to introduce yourself really quickly sure thank you so my name is christian shulk i'm a google developer advocate i work primarily with workspace and cloud but more recently i've been spending a lot of time with appsheet and helping to integrate it better with our cloud and workspace products excellent and then we also have charles with us today charles would you like to provide a quick introduction hi everybody sure so hello i'm charles i am a developer advocate also on chris's team and i've been focusing on google workspace as a solutions platform for many years and i am a huge spreadsheet fan and so i joined the call today to debunk some of the cool things that spreadsheets can do to make them way more usable with app sheet awesome well thank you both so much for being here and with that we'll go ahead and get started next slide please christian yep all right so uh just a quick note of context some of you may be wondering why we have a webinar to talk about google sheets and app sheet so uh to help with that just a couple of things to call out app sheet is what's called a data driven no code platform what that means is your data is used to build applications the cleaner your data is the more successful you will be in your application development process and tabular data which christian and charles will talk about in a moment is really key for that sheets just happens to be a natural fit it also happens to be within our google family so we want to really highlight and emphasize how you can find success in your data structure to create really beautiful applications as you move forward cool thanks so much jen i'll go ahead and continue on here and and we'll dive into kind of like the core of our topics here what we're really discussing today is like how to take a google sheet and essentially transition it over to being uh very easy to work with and essentially ingested into app sheet with a minimal amount of friction so in this sense uh you'll want to like think about how to prepare your google sheet in in certain terms like for example um app sheet is looking for kind of your your very basic tables structure in a google sheet so you're going to have columns that have simple names you're not going to have a strange characters all over the place that's definitely not a a good pattern you want to essentially avoid lots of numeric columns um also repeating columns and having like data that's you know not necessarily set up in the most optimal fashion so charles is going to go through and actually walk through all of these steps of how to kind of clean up your data in a sheet and make it essentially into a more like a relational data set up now a few points on column naming and data types so app sheet is intelligent in the sense that it has these pre-designated data types that are intelligent in the sense that it has a data type for email image address photo and such and what it does when it takes the actual sheet data it's able to correctly decide what's the most uh best fit for the data type for the actual data that you have in your sheet so if it sees photos it will say okay that's that's an image if it sees emails so i'm going to assign that as an email and this actually dictates the behavior of the generated app for you so that means you don't have to actually hand code or hand set up a lot of these things it just comes out of the box for free and the app just does what you would expect it to be so just keep that in mind as you're having your data in your sheet you might want to make some of your column names to be a little bit more descriptive so that way those hints can be picked up by app sheet now keys is also an interesting topic as well so right out of the box app sheet is gonna give you or it's gonna predict uh essentially what kind of a unique uh underlying like unique identifier for each record because that's essentially typical databases have this concept of a primary key meaning like a key that essentially allows you to identify individual records so app sheet has the same thing but in the sheets world you don't necessarily have that you're going to have a lot of different rows that have different values and you can have rows that have the exact same values so this is why our app sheet has to decide how do i define what is a key to use in the data such they don't have like record collisions and such so the way that actually works is that it will automatically choose the leftmost column providing that each value in the rows are unique and we'll say okay this looks like a valid key i'm going to go ahead and designate this column as a key sometimes it doesn't have like the most unique values it will take a combination of the left two columns meaning like task and description could be an example where it says you know that way you have a higher uh degree of uniqueness i guess within the data um and then also you can specify specifically which um column you want to use as your key and you can then even ensure that it has a unique value whenever new values are created by uh setting its initial value uh to the function unique id again this is getting a little bit into the weeds but the main concept here to just remind yourself is that sheets and in general spreadsheets they don't have the same rules as a like a typical database and so this is the kind of transition into app sheet where it's treating the data more like a traditional sql database or you know essentially a database table in that sense all right so i'm going to hand it over to charles and he's going to start off like a fairly busy spreadsheet that has a lot of data it's got a lot of formulas a lot of like formatting things are going and he's going to walk you through how you would actually start to transition that sheet into something that is then very easy to be pulled into app sheet and generating an app with minimal effort okay so take it away charles sure and actually before we dig in um let's set up the application next slide chris by the way okay so occasionally what we want to do so we're going to take the very traditional use case the spreadsheets often get um sequestered to do for lack of a better word and that's actually project management in fact if you ask everybody what is their favorite project management tool unfortunately they may tell you it's a spreadsheet that we've seen everybody do that and i'm sure you've probably done that yourself as well i know chris and i have had these spreadsheets to run things in our business and so within the application uh we're gonna keep a real simple look at a uh a test tracking application or a project management scenario where we have many tasks that belong to a number of projects and those projects obviously have project managers as well too so i know this can be real simple we could have many project managers and uh having many to many but we're going to have a real simple example that we show here so with that let me flip over and show you my spreadsheet i'm going to share my screen for a second and we will start creating this thing so i'm going to pull this away and here just give me one second and hopefully now you'll see my spreadsheet beautiful we do okay awesome so um this is kind of the before version i needed test manager before and you've seen this type of spreadsheet undoubtedly you probably work with folks in your organization who have said hey fill this out and let me know what you're working on and let's work together and let's make clarity out of this it's going to be super hard right it could be you know a lot of data that's just kind of flown together and um it doesn't always transpire correctly into uh app sheet when you want to build a app immediately so let's take a little look what i mean by some of those things here i'm going to zoom out just to give you an idea what's going on and the first thing you'll start to recognize here is this spreadsheet has data everywhere right there's data in a big block there's data missing in columns there's data shoved way out on the window here sometimes in multiple tabs sometimes it's even in multiple sheets that people expect you to work and open and combine and do those things together and what we want to do and what's really clear here what uh chris and jen mentioned is if we can clean this spreadsheet up and structure it in a way that when it's actually pulled into app sheet your application will run a lot smoother right out of the box there'll be less work for you to do and therefore you can hit the ground running way faster in app sheet and so you'll convert this beast a lot easier by making it work better so let's take a look at some of the things that i like to do to get started here the very first thing i want to do is really understand my data and as i mentioned in our test manager application here we've got a couple of main things we're working with we've got entities that are tasks we have entities that are projects and we have entities that are project managers and logically that would tell you that if i'm going to normalize this data i'm going to have a table in this case a spreadsheet if i actually normalize it here inside of sheets that matches each one of these different entities and these type of fields and you'll notice again if you're familiar with normalization and making data fit better the whole goal is we don't want to have redundant data for a more optimized database we don't have redundant data and data repeating for maintaining and updating so say for example if i have an email address for a manager that works on a project and i have it listed multiple times not only is that more space but say for example somebody changes their email address i have to then fix that in a hundred different places if that person is associated with projects so the data gets a little bit sloppy and so not the best experience for the data not the best experience if you're trying to dig through it so what we're going to do is we're going to normalize this real quickly and make it work one thing i'd love to do real quickly is just figure out what is the current data i'm working with and understand it better so what i do a lot is i come down here and i say with the uh the con the command and the shift and the arrows i actually see what is the block of data that i'm working on in this case you can see this is the data that i need to do something with that data on the outside is an outlier so i'm going to want to get rid of that so we'll take care of that in a second but let me zoom back in for a second and also show you a few other things that kind of really go against the rules that chris mentioned when he was talking through some optimization techniques the very first thing is you want to give the sheets that you're bringing in uh really nice concise names if you notice down here i have a kind of long name of my sheet all project tasks forever since may 2020 a super long name that again if i pull that in it won't look great in the ui it's not very explanatory uh and we it's not really useful in the context of working with an app sheet app you'll also notice up here i've got my column names and again some of the rules are pretty good we've got tests we've got status we've got due dates and those can all be inferred but we've got one here where someone's trying to actually tell the user what to do while also mentioning the field type so in this case priority don't use one two or three scale use the critical high medium and low scale so we're going to want to clean that up as well too and again we've got some other note over here uh here's a field that doesn't make any sense it's partly filled out it's note data kind of looks like it's a junk column and again you've kind of seen uh things floating around the spreadsheet that don't make sense here's a great example of that as well too so let's clean some of this up i'm actually going to fast forward to a second to a spreadsheet that's a little bit more completed just to save some time and you'll notice a couple things i did the first thing that we've done here is we've cleaned up that task name so now you can see it's just tasks for the for the sheet name uh and then we've added two new tabs from each entity one called projects and one called managers again we don't have the data filled out in there yet we'll get that in a second but the neat thing is you can see it's starting to look like our data model of the three tables we want to build tasks projects and managers okay so now the next thing i want to do is a couple things i just like to do because i'm in sheets is make this a little more usable so i do things like let me just grab a thumbnail here and i'll make it so i can freeze the paint so i can scroll it i'm gonna format that just so again it looks a little better and i can maybe see that this is my data and my titles now one thing to note the formats that you do inside of sheets are not going to translate inside app sheet so i can format these and do things like that these are visual cues i'm doing here but you really don't need to do that there because again you'll do your formatting and your ui elements on the app sheet side this is just more for me in this case just so i can see what i'm working with okay so uh the next thing i want to do is i'm going to want to come in and i'm going to want to clean up the names here so again i'm going to jump out this don't use the one two three four scale uh that's fine and make all my column names way much easier to work with but i'm going to zoom out again just to show all the other data again and talk about what we want to keep and what we want to remove so again we don't want this information here the reason it was here by the way you'll probably recognize this if you've ever seen how folks can use spreadsheets where they're trying to use it like a database and we all know people try to but they shouldn't uh you'll notice that they're using this little table off to the side as lookups for the email address so you can see here i've got the manager name and they're using in this case the match and the index function and they turn it into a hyperlink some folks use vlookups or hlookups we don't want this data here because we're going to actually use it to look it up from the managers table also notice just like formats uh all the formulas that are inside a sheet are not going to be persisted when we go into the app sheet application this data would simply be flattened and it will not be a formula uh also sometimes for certain formulas like array formulas for examples uh they won't even be brought in to an app sheet app so you want to make sure that you turn all of your formulas into values i'll show you quickly to do that in a second but you want to make sure you do that so you don't try to uh pushing any formulas that won't get understood by the app sheet um conversion process okay so one thing we want to do is we know we can do here is just get rid of this column so we'll just delete that whole column out and i won't delete the manager field out yet because note that the manager field is being used to relate to the projects again those keys and the lookups and those relationships are being made so what we'll do in this case is we're going to take that little lookup table though i'm just going to copy it out and then while i'm here i'll just delete the rest of them out and then i'm going to go to the managers page and just push that into a brand new table and notice one thing i always do just to make sure there's no formulas is when i paste i paste special and i paste values only which gets rid of all those formulas for me so there we go now we've actually created the table of managers again it's normalized so it just has the manager information that we have available um we're going to do the same thing in a second for projects so we'll bring the project names uh and then we'll bring in the relationship so we can see who the project manager is that it belongs to but how are we going to do that let's take a look back at our data for a second so let me zoom back out and just go to the top here and make this a little bigger and look at the data we're working with now now the big difference is we still have our task status due date priority and project but again we don't want the manager field here and we don't want the notes field here but before we get to that we also need to capture again all the projects that are being worked on and we need to get a unique version of this list so one of the neat newer features introduced into sheets will allow us to quickly come in here and say data remove duplicates and then we'll allow us to quickly get a unique list you can see here it found that we've got a bunch of duplicates cut them out and then bring that information back as just a unique list now one of the problems though is we've just now removed the key project information from all these tasks so now we won't know what tasks belong to what projects so that is a neat feature but it's not going to work for this instance so what we need to do is actually come up with a better way of pulling it out and so i'm going to show you a really really really cool feature which i always say is your first homework assignment if you've never seen this before is to go play with this the next time you're in sheets and it's something called the explore feature so if i can have you vision and journey down with me to the very bottom right hand corner of sheets you'll see the explore button kind of hovers over it and it turns green and when i open it up this will allow me to do some amazing stuff to this data by using machine learning and automatically inferring some key things about this data this is a super cool feature if you're simply looking to do things like format or create pivot tables or look up information inside your data it works super well for doing those other features as well too but where i think it really shines is when you can come in and you can actually ask it questions that are just logical about your data and even ask it in natural language and this explore function will know how to respond even though you don't even have to train it on your data it kind of just learns about your data and again just like app sheet will do when we pull it into app sheet it's using some key syntax some logical data constructs to understand it so again those great task names up top will make it easier for the explore feature to understand what this data is so say for example charles a quick question for you though so just so the audience is aware we have not done anything with app sheet yet correct we are still in sheets we're not talking the app sheet we haven't connected this specific sheet to app sheet yet correct correct perfect right and remember our goal is to simply make this data as pure and clean and as friendly as possible so when we do pull up that sheet actually looks at and goes i know exactly what to do to this okay so in my explore feature i can ask you these very natural language questions so say for example i want to ask it how many of these tasks are actually completed so i can just ask them how many and notice i have not trained this at all how many tasks are completed and notice it can come back and already answer that for me and again i haven't told it where to look i haven't told it what to look at but again by knowing there's a task column and there's a uh status column it was able to infer that and look it up and notice how it returns the comment or the number there three in fact it even creates the kind of complex formula for me to show me how i could build that myself but now just quickly knowing my data like i do only knowing three tests were complete i recognize that something's a little weird in my data and i can visualize here that some people put completed and some people put complete and this is a challenge with spreadsheets right spreadsheets are great for collecting data but they're not really good for making sure people put the right data in the right places and so this is a great example of why this spreadsheet is way better inside of app sheet so now you can see when i said how many tasks are complete it gives me back the answer five so i know before i actually go into app sheet i want to fix and clean up this data and i'll show you how we can do that in a second uh this will show you a couple other cool things you can do with this explore feature again which is super powerful when i want to clean my data up so say for example i uh i want to check out the due dates of some of these things maybe i don't want to bring old tasks over with me maybe there's tasks maybe like last year aren't important because they're old and they're already done notice when i start to type due date it recognizes again because of that beautiful column name it recognizes this as a data type and i can just say due date um let's say before this here one slash one slash two o two one and notice it'll go back and automatically show me here's all the tasks and i can get again get that formula of all the tasks that are done before making it really easy for me to identify what's happened and cut through that data so again explore tool is super amazing to learn what your data is doing without having to train anything or or create any functions or formulas a great tool do please play with it but the real reason i came here if you remember was i wanted to get a unique list of projects that was the goal so how do we do that again since we have a great column name and we say all the different projects if you spell it right it helps again i've got the projects here i can say just simply i want a unique list again infers what i'm talking about i say unique and notice how it's got out and quickly shown me here's all the unique projects that i have and again it's giving me the formula which i just take that formula if i want copy it and then we'll go over to the project table and just paste those in so that was the fastest way of doing it again i explained a lot there but a fast way to do it if i came in and simply ran that and then said paste the values only and now you'll have it oh and again i didn't say face values only apparently i must have hit the wrong things notice there's a value in there and again if you ever want to take the entire spreadsheet for example and just make sure all all of your data is coming in as real data when you bring it into app sheet i can say copy and then i can just say paste special and paste special as values and again all those formulas are in fact gone and you don't have to worry about any of that getting lost in translation okay but back on task gear for a second notice i still need to go out though and get all the keys for all the projects let's just say project manager by the way so what the project manager unique key in here and again the project manager we've already determined we have email addresses and names now this is a great use case or a great example of how keys may or may not get inferred now if you think about talking about project managers or even people in your organization remember chris mentioned the value of a unique key now your organization may have you know two alicia williams or you may have two eric colitas and so how do you do unique keys the best way to do that is through email as chris mentioned though um a lot of times when we think of people or a project manager normally you think oh the name of the person you don't talk to somebody by their alias although maybe once in a while you do but for the most part you call them by their name um to do this you could obviously set this up later if you had an order where you had name first and then email but again to make it super easy and super valuable for app sheet to pick it up on the unique field if you just plot that unique field in the first column this is a great way of making sure that you don't have to go back and either correct anything in app sheet or actually have some of these columns brought together as a calculated column which again would make it you know harder when you don't need to when the email obviously is your unique field so a great use case right there anyway so i want to now figure out who are the project managers for each one of these projects so again we had all that in our denormalized table and so again i'd have to come down here and figure out what manager belongs to what project so again in some data you may even have use cases where you have multiple project managers uh working on the same project or vice versa multiple project managers with multiple projects but in this case what i need to do is figure out how do i build a lookup table to do this right again you've seen h lookups and vlookups and all these fancy ways of doing it but again we're going to go back and use a really neat function that allows you to build out a formula to look up who these people are and again the explore button could do some of this for you but i'm just going to show you how we can do it here by using one of my absolute favorite formulas inside of sheets and it's called the query function the query function allows you to do exactly what it sounds like you can come in and say equals query and what it will do is it will run using the google visualization api a natural query language query of any data sitting inside of your spreadsheet so in this case what we're going to do is we're going to say i want to go look up all the project managers and the projects so i'm just going to take that range and again remember we had the project range we also had this weird range in the middle we're gonna ignore it now we'll just click on it and include it um so i can have one big block of data but then we're also gonna have the project manager so when i enter that notice the query function did exactly what we told it to it went out and it queried and pulled back all the data for all those three columns now that's super neat and super easy but notice i also don't need the project name anymore i just need the project manager name and also notice i want to clean up these head the headers here so i'm just going to finish this formula do it first i'm going to do is i'm going to make the headers work you'll notice here you can see the help showing me how to do that by simply putting in a one or you can even put a zero in to make the headers work um and you'll see how that cleans that up but notice we still have this kind of weird column that we haven't got rid of yet so i want to actually just ignore that column for now and the way you can do that with this really brilliant query function is simply by putting a select statement a sql statement inside of this function and again if you've never written sql by yourself it's pretty straightforward the help function here if you hit learn more if you're going to help it'll show you all the great examples but for the most part it's pretty straightforward to do in most use cases or i can do things like say select in this case i want everything from column e and column g so to close that quote there and say when i say select e and g notice it goes out and it ignores that middle weird column and now i just have all the projects and all the managers so again in this use case we already know the project managers i'm sorry the projects so i don't even need that column anymore so i'm simply going to come in here and say you know what we don't need even the project name anymore so delete that out and notice now we have all the managers in place which is super neat uh just to give you a cool example here if i was actually to re-include that and put that e back in i can even do things like conditionally have like a where statement or or uh sort it by a certain thing so this case i could say where say for example i wanted to find out where every time i was actually one of the project managers i can just put again a nice sql statement in there so i select e and g where it's my project and i think i spelt something wrong ah i actually stipulate the column g equals and now in this case you can see it will come back and bring back all the projects i work on which happens to be just one in this case but again just a really neat powerful function to get things done but let's finish up here by cleaning this up we don't really need to do the sql statement we just need to bring the back column g again there's all about project managers but again we need it unique and if we remember when we used that feature before using the explore function and i q you do have the speller right um we can simply put another formula around the query function unique which will pull back all those unique records in order as they correspond to our projects and we've built it so again pretty fast once you know what you're doing with the query function pretty powerful let's just put it in the right place by doing the paste special values once again and then we'll get rid of our little workspace over there where we figure it out so that's how fast you could actually go through and mine some of your data out let's do one final thing and clean up the sheet so we can pass it over to chris so we can pull it in now that we've got our project table done let's fix up some of the data that's not consistent or regular and i'll show you what that means so first of all we've already added the manager in so we don't need uh that manager information anymore and we don't need this crazy notes column anymore so let's just delete that out and now we have just the project name which will help us figure out who the manager is with the different keys we had some anomalies on our beta remember for example we had status and then we had with complete and completed uh also note that we've got some data in the column c which looks a little weird um it looks like we've got some normal data so there's a great new tool as well which i can just come up here and say under data and scroll down a little to clean up suggestions cleanup suggestions will quickly come out again much like explore works it'll automatically look at my data and infer my data and look for patterns in my data and even find anomalies like it has here so for example if you hover over column c notice that it's gone out and recognized you've got some dates in due date you've got some strings in due date it looks like you have somebody here with a sense of humor putting in not even anything and also somebody thinking that time is not as valuable as money so they put it in as a number format um so what you can do is not just recognize these but notice when i hover over this cleanup suggestion feature it'll automatically show me the correction and allow me to make the correction right in line and make my data cleaner and this is super important because so when i import this into app sheet it'll look at this column and go oh this is all dates this is all date data i'll make that a date data type which is so much more powerful for your application okay so a couple of these dates need to be fixed of course and we could just fix that and change those later but let me show you how we handle this complete button any uh this complete error or completed error this little kind of anomaly that you see here so let me go in and add some data validation so quickly i'm going to select this column and under data under data validation i'm going to come in and say from a list of items and again i'm just going to paste these in i actually had these in the clipboard already i want to reject the input if it's not not started in progress or complete and when i hit save notice it goes out and it's automatically flagged and found the errors so when i bring these in they'll have the correct convention on it so i don't have a new data item or uh data that doesn't match and so now all my items will be complete and if anybody needs to go forward it adds any new records here if you want to do any records manually on this side you'll be ensured that you'll have the right type of data when you do this so data validation super powerful um real quickly i'm going to go out and do data validation on this field on the priority field again to make sure it's not that one two three four like we had in the beginning by simply running a macro and again if you've never used a macro macros are a great way of actually creating functionality to happen over and over again automatically so i just ran a macro that allowed me to put it on data validation and i also built one which allows me to go in here and use conditional formatting this is a neat feature i like that i can actually highlight and format content based upon the different values with inside it under format under conditional formatting i can come in here and say here's some rules based upon data elements which again is super powerful visually so i can see it so in this case i'm just going to run again a macro to show you how i can add some of these real fast and you can see how they bounce in with my macro so when i am done running those macros and again i could have set that up manually but i want to save a few steps here notice how visually my spreadsheet looks a lot easier to understand and making sure i get clean data now again these formats aren't going to show inside of app sheet but the neat thing is now i am really confident i am really comfortable that this data is the best that it can be so now let's bring it app sheet and we'll show you what it looks like chris take it away with my data and show me a great app sheet app okay thank you charles yeah that was very informative um just to kind of put a nice uh summary to what you just described uh and feel free to jump in on anything basically you did two steps first step was to clean up your data clean up any columns and then second part of that was to clean up your data by removing formulas and establishing data consistency such as the complete versus completed or the different date formats right and then the second thing you did was you normalized your data so you took that large flat spreadsheet and turned it into a more of like a relational database where you have different tables that then refer to each other is that fair to say that definitely does and chris let me just ask you a question if i would have gave you that original spreadsheet how much more work would you have had to done oh to bring it into app sheet yeah i mean it would have not made for a very good app generation because of the hints and stuff there was there's a lot of conflicting hints to be honest and so it would have been uh trial and error so uh i would have had to do what you did basically to go in and clean out a lot of these things and then remove formulas make sure the the columns make sure the data is consistent uh break it up into essentially a set of relational tables so to speak and then step through that process but yeah it what it has to happen uh one way or another but basically you mentioned a great point chris too i mean you don't have to get this perfect you can use a little bit of trial and error and see what works and you know when i started to work with app sheet myself you know i didn't understand exactly what i was doing with my spreadsheets and so i would try something see how it worked throw it away and start over uh until i kind of understood what i needed to do and frankly also read a little bit to see what i need to do but now i can turn a spreadsheet into an app sheet app really really fast okay cool so um as you can see i'm starting now with the spreadsheet that essentially charles has prepared with us so he's taken out all the formulas taken out essentially all the issues that crop up when you're trying to incorporate into app sheet and we also have some nice formatting going on uh like the status is now distinctly you know different uh types here for the status and then there's also some nice color uh data formatting that's going on and of course the big one is that he's able he was able to take uh the the data separate it into different tabs here and so all the stuff will link together very nicely um in the application now all right so let's take the next step and let's generate an app and so directly from sheets you'll see that i can do app sheet and then create an app so click on that and it's going to go ahead and set up sets up with a new application so we'll get a few seconds and then right out of the box we're going to have a perfectly runnable app now because i was viewing the tasks tab it gave us this one table here and we have all of the the columns available here so essentially mapped different columns to the different data types that is expecting from that it looked at from the the sheet right directly and so we have text date and enum is actually enumerated type this is because we have different uh statuses so like you know whether the status is not started or in progress and such and so it actually defined those enumerated types for us without us even having to do anything so if you see on the right side this is our ui in the preview window this is a perfectly functional application at this point so it's a crud application so create replace update delete and also search so i could search through all of the records very quickly um there are a few more enhancements that we can do as well so for example if i click to create a new record first off you can see that the status is now broken down into different options and via those buttons we have a due date of today but i might want to make that a little bit better and we have our drop down list which is already auto created for us so it gave us quite a bit out right out of the get go without having to do anything but let's go ahead and make it even a little bit better right so in this case what i'm going to do is take a couple of these columns and give them some initial values so status i'm going to go ahead and set it to i'm going to give it like a preliminary value i'm going to say not started so that way it will just start off with this not started value and evaluates gives you a little check mark to let you know that evaluated correctly and then for this today so that's whenever someone enters in a new task it auto encodes that of today's date so that's not entirely practical obviously so we want to do something that will give them or whoever is being assigned the task a couple of days to work on it so i'm using this handy function to call workday that will give me uh the next date that's five business work days into the future and so it's basically taking that those arguments and generating that data for us when we create new records and then finally for the priority we're just going to create a simple value of medium so i'll just say medium and leave it at that i'll click save once i get a confirmation that it evaluates correctly and so that's we're good to go at this point i will point out that different like compared to like google sheets or some of the other google products where it's auto saving every time that's not the case with app sheets so you actually see the save button here it is actually saving but it gives you a little bit more fine grained control on how and when you want to save it you can also roll back to specific earlier versions if you wanted to but just wanted to point that out there very quickly yeah that's a really good call-out question so thank you for mentioning oh sure yeah no problem um it's obviously people are coming from the google world they'll start using app sheet that's the first thing they'll notice in many cases so um also before i start building out the other tables for projects and managers i want to point out there is this project column here which is a simple type of text now watch what happens when i add the other tables that's actually going to create a relationship and it's going to change the data type to something else so let's go ahead and add a table for projects and so that will generate out the the necessary essentially data table in the background so we can do all the data operations that we want and while i'm at it let's go ahead and add one for managers so now we have these distinct three tables and they are all now related to each other as well so if we return back to our tasks table and take a look at the columns again you'll see that the tasks table in the the project column instead of text is now point is the type is ref that simply means that it's referring to another table of this name and regardless of whether it's plural single and this is the table that it's referred to so that essentially establishes the linkage between the two tables and we can also see it here on the ui so for example if i just click on any one of these tasks i can see the related project and such i can even drill down further and see the the projects and the actual tasks themselves so managers projects tasks it's all related now so it's a fully relational database app with very minimal effort at this point right and and typical like in the past if you wanted to build a fully relational database web application um that was you know not trivial but we've done it in just a matter of a few click it clicks i guess all right and i do want to add really quickly christian this is one of the biggest questions or most common questions we get is how to implement this type of relational table piece so thank you for highlighting that i know that's something that people are really curious about really often so thank you yeah so charles actually did the majority of the work for us in this sense like what he did on the spreadsheet was he just ensured that we have a column here that this column here that says project it actually matches with another table of the projects table or tab it doesn't really matter it could be external table or an actual tab in a spreadsheet and by virtue of creating it with the same name appsheet is able to recognize this and provide a reference and that's why it set the data type to type ref and so in app sheets kind of overall kind of thinking i guess it is now establishing the linkage between those two and the same thing happened for managers so we happen to have this table for managers and from a project standpoint it actually has this column of managers of the same same name essentially so that's how you can prepare your data if it's relational for it to be essentially integrated or ingested into app sheet and app sheet will automatically recognize those relationships for you i should also point out if you're working with a instead of a sheet but say a sql database and if you have foreign key references app sheet will do the exact same thing it will recognize these those foreign key relationships between the different tables and create the appropriate linkage within app sheet so it will also set up like a reference uh column that way the the tables are linked together from the standpoint and then the the resulting ui will also be linked together as well without really any effort okay hopefully that uh makes sense um incidentally like i was going to show you really quickly like if i click into these other tables it has this manager ref so it's pointing forward to the manager but also has related tasks and so this is kind of like in the middle of the relationship so project is kind of backwards pointing to the related tasks using this expression here where it joins the tables together and it has a reference to the manager table and likewise if i open up the columns on the manager table i can see that it has this related projects we're essentially using the same mechanism to join projects with managers okay so at this point my data is good to go i don't really need to do any further customizations i'm going to switch now to updating the ui because as you can see over in a ui although we have a a perfectly functional application from a task perspective we don't really have any options to go and edit or view the data directly on the projects and managers now of course i can drill down any one of these but i'm going to go ahead and provide some new buttons here on the bottom that gives us direct access all right so i'm going to i'm also going to do some updates we have this google map that was given to us for free just because we have some um i'll just go ahead and leave that alone because we actually have some address data so it's okay this user probably needs a a map and there's also some interesting statistics there that was given to us for free so i'm going to customize these a bit so to do that i click on ux and first off let's move our little statistics chart and instead of having it on the right side of the action bar on the bottom let's just go ahead and load it up on the menu right so that's going to disappear on the right on the right bottom right there you can see that it's gone and then for the map let's just shove that over to the right side of the action bar all right and we'll just click save everything's happy now we're going to add two more views for both projects and managers so if i go back to the top here i can say let me just close that down i'll click on new view and let's start off with this one i want it to do let's just add the measures one the managers view there we go so i give it the name that corresponds with it and then of course i can toggle the different view types but i'm going to stick with deck deck is good enough for me i think for the ui i might customize a few more things like maybe the rounded image and then the other thing i like to customize at least for this case is i like to change the icon so this is the icon that shows up on the bottom um of the bar there so you can see managers in this case so that makes sense let's go ahead and add another view for projects so i'll click new view and i want the data to come from the projects table and i'll just give it a corresponding name and for this one instead of dac i'm going to use tables a little bit more succinct and i think that's probably good for now although projects i'm going to change the the display icon and this one let's see it's one of the list icons that i tend to like i go for the clipboard uh there we go so now you can see we have this project icon that matches and so at this point as i walk through the app a bit you can see that i can click on the different uh projects tasks managers or likewise i can start with projects and drill down onto the associated you know click on a particular manager drill into the project and then go on to individual tasks and such so it all makes sense so pretty straightforward so i'm going to add a few more things to the ui so because we have uh some budgetary data in the projects table i'm going to add a new chart so let's create a new chart and this is going to be a chart type and i'm going to say let's go ahead and just stick it on the right side i think it's good and we're going to call it budget and instead of tasks i want it to go against projects and i want it to be a pie chart and now i just have to add a chart column in this case i'm going to have budget so that's the actual column and then i can just toggle what i want to show the actual value or the you know the key in this case happens to be the the project name but in this case i'm just going to go ahead with percent so now i have a little you know pie chart of the different uh budgets or the dollar amounts i guess for the different projects so that's basically providing me to some additional enhancement so i selected a con that seems to match and so there we go we have kind of a nice ui of managers projects tasks and our budget and then of course we have our map so i'll click save and so that's it from a general ux standpoint i'm going to add now a little bit more on the formatting or kind of like the the kind of the ui specifics only so first off just to kind of recall there is like these actually i didn't mention it but i wanted to kind of just walk through the branding feature in this sense like without really having to dive into any complex css or kind of advanced html it's very easy to just go through and start changing things on the ui so maybe i want to have like a a different app logo a different primary color and such maybe i'll go back to the light theme there there's a notion of a launch image there's a background image like this and just to kind of show you what the effect is of toggling these different uh features uh you can see that it's changing the look and feel of the app i can do this just very simply with a matter of a few clicks all right so i think i'm going to go with this look and feel it's it generally fits what i want so i'll click save oh actually i think i'm going to tweak the uh the the view type for the projects or not for the tasks let's see it's currently a deck i'm going to make it a little bit more compact and make it a table uh so now that's happy for that i have to be careful saying happy you're going to start to sound like a painter yeah so sorry you were saying i was going to say like we we mentioned this so casually like oh i just changed the table and oh i just added some colors um and and change you know the way it looks for the end user like it's it's so um quick and you know seamless to implement in this regard but you can really customize it to almost any need you have you know add your own special branding to it but this is kind of you know the beautiful part about no code right like you don't have to go in as you mentioned and write any type of custom coding you just input what you need and it performs what it's supposed to do so yep really cool yeah i mean take it from me i've i've been building these types of baths for years and and it's just like it's it's such a time saver with regards to all the complexity that you would have to get into back in the older days so let me go ahead and continue sorry as i clear my throat here so one of the other things that charles has done for us is he's actually applied some uh formatting of the actual data here based off of the values and so it applies like different colors and such and so although app sheet didn't automatically do that let me show you really quickly how you would actually set that up on your own and this is where you can use the format rules feature again it's under the ux tab here so in this case i can either start from scratch or just take one of the suggestions here so change font color so that's basically what we're going to do for any data in task we're going to change the font color and we're going to give it a specific condition so in this case we want the condition to be when the priority column equals critical so that's fairly straightforward and it evaluates perfectly fine and then now we just have to select which particular columns we wanted to apply that rule to so you can see it's already starting to work and now i'm just going to change the text color so down here i've selected the different text color and so there you go you can see like the different uh records there that have a priority of critical and now has that text color now i could set this up additionally for the other colors as well but you you get the idea like how i'm relatively straightforward you can just set up the basic formatting okay so i think for that i think that that more or less covers what i wanted to cover from an overall ui and ux standpoint um i think i'm going to add one more little demo or one little feature to the app that kind of really kind of brings it home and to do that i'm going to actually utilize a new feature called the automation capability sorry a new automation capability which is just launched earlier and what that in a nutshell it allows you to establish uh essentially a overall process which consists of an event as well as like a set of processes or a set of tasks encompassed within a process right and so and the way you do it is you configure a bot which will go through and help you define those processes and as you configure the the different components of the bot it will actually give you suggestions based off of your own usage patterns so so it's pretty cool feature and as i start to build out the different events and processes and tasks we can kind of follow along and do it fairly quickly right so first off let's create a new bot and so it's going to give us some suggestions and so in this case i'm just going to go ahead and select a new whenever a new task record is created send an email so that's kind of like a starting point but now i can go in and customize further so now if i were to look at the event that it created for me we have this new event and then we also have a process and in the side of the individual process we have all the steps and so and the steps themselves have these tasks so send an email so if i return back to the the overall bot i can also customize this further so for example maybe i want to do a little bit more than just send an email maybe i want to um let's see i can load the suggestions there we go i can check to see if a status is blank right so maybe if the status is blank i might want to do another operation right so maybe i want to set some of the data so in this case i can just set the due date to to today or i can even customize that so in this case let me just hit say really quickly just to make sure everything's kind of all synchronized so now that i've set the i've actually established a task for when a new record is created i can say that correctly so whenever i create a new task it's checking to see if the status is blank and if it is blank it's going to do a data operation so let's go ahead and change this a bit so i'm going to change first the step name so i'm going to say set the status i just write whatever i want here this is like a note to myself set status to say not started no i like the y the y is fun it gives a character that makes sense and i'm just going to kind of apply this on and so i'm actually redefining some of these tasks but i'm i'm preserving what i wanted to do i wanted to do a data change so this is the type of task and i want to actually set a row value so i'm going to actually just customize it all along the way here and i just wanted to add a note while you're typing some of this in so what's was on the right hand side of the screen the emulator which showed your application that is it's still there but right now you're looking at the bot building process essentially no pun intended there but you're you're looking at the different pieces that make up these bots to perform these actions to execute these processes and tasks et cetera so just know that your app didn't go away it's still there um you're just looking at more under the hood i said yeah yeah totally that that's totally good point because like if before this feature uh or i keep saying feature but essentially before this capability was rolled out you didn't necessarily have that little gear icon showing up and this gives you like expanded property editing capabilities exactly you can go walk into it and set all the different properties um so yeah so so here we go i have my my automation essentially complete but i'm going to add another little update i'm going to make it so this send an email it's not going to just be sending an email to a hard-coded email address so in this case what i'm going to do is let me just grab a bit of text here that i've saved off in a little buffer file instead of the hard coding it i'm going to set an expression and i pull that up and do the expression assistant and i paste that in and let me just describe what's going on here this is actually going to send an email to the task owner and so this is a a reference where it's going through from a task table perspective it's pulling up the project column and referring to the manager table and then within the manager table there's an email column and so that's how it derives the correct email address so it's no longer just hard-coded so it will it will definitely send an email to the task owner okay so i click save also for the content itself the email there is this kind of a nifty feature where it just says default content which all it is is going to do is send me send the task owner the values of the task record but if i wanted to i could actually add customized email content in this case this is where i can go in and start to use like uh individual like uh wording and and then i can refer to the data parts just using the the brackets and such and then there's even more interesting features where i can use like a body template where i could create a google doc and then using the simpler a similar templating write-up i can actually have all of that defined for me in a more kind of sophisticated email i guess but uh just to kind of step back from that let me just switch back to the um my original bot here and i'll go back to the send an email part and i will change this to just use the default content so just want to show that before i fired it off here so i think at this point my app is good to go i'm going to go ahead and do a test to make sure everything is functioning correctly so what i'm going to do now is create a new task so i'm just going to first i'll drill down from the manager's perspective and i'll pick this one guy over here and i know that he has this one project and then now i do is just add a new task so i click on the ad as you can see i have not started as the the status i can even change that to in progress i guess i can see that my date is five days work days in the future maybe give it another day and i might set it to critical and also notice like the critical is not even color formatted as well so pretty cool and so now i click save and we see that it's synchronizing now with our back-end sheet and then i can now go over to my email and i'll click refresh incidentally this email was created when i generated the app and then i should see my new email which is simply the the content of the task record there so as you can see it's critical i have the uh the new uh finish of the i meant to write finished demo but you got the idea i was in a mad uh rush to finish off the demo but you get the idea so so yeah hopefully this uh explains kind of the overall transition from taking a sheet uh cleaning up the data separating the data into different tables having them set up via relations and then bringing that into app sheet app sheets able to take those hints both from the data validation the data formatting as well as the relational uh essentially the relations between the data and give us a workable app right out you know from from the start and then i was able to customize it further and enhance the application to providing you know just some fairly sophisticated behaviors at this point um yeah so this is exactly it christian i have a question for you in terms of what you just showed so what happened in your sheet when you did all of this work in app sheet so what happened in the sheet oh yes so now the data is still the same so so there's not it's not going in and changing anything it's not adding formulas the data is the the data that you left it as is now the only thing that does change is when i interact with the app obviously i have a new record here that was inserted because i was using the app but as a as i'm editing editing formatting et cetera et cetera that's all independent of the underlying data sheet right so that is really critical to understanding this relationship right you can have your data where it lives in its own source have it structured how you need and then you also have your actually application and they too seem independent but they are speaking to each other the difference is one is more of kind of a front end piece of your data it allows people to interact with it based on what you provide or what you tell it to provide the end user with and then the the data ends in a different area so i really want to highlight that i think a lot of people are scared to get in and work with something like app sheet they're afraid it's going to break their data if errors are thrown that's not the case um it's we throw errors all the time and that's that's how you know you're learning and getting better is is by breaking stuff in app sheet so feel free uh to do that your data your data will be okay yeah totally and i would say that what you just said extends also to all the back-end data sources whether it's sheets or sql tables et cetera yeah really interacting with the app that you can actually add or delete or edit records themselves but not any of the other kind of uh built-in functionality of the sheets and back-end data sources really really good call out all right um so i think we have a couple of closing notes um for everyone would you uh would you like me to pull up the uh tips and tricks slide or would you like to do that christian what works best for you how about you go ahead and drive and i'll stop presenting and then um i'll just like hand it over to you um right let me pull this up i feel like we just covered so much but we did it so quickly but that also shows though that how once you get the hang of it how quickly you can you can build applications and automations with a no code technology all right i'm almost there almost well i could think i have the slides handy it should be oh there you go all right perfect i believe it's it's presenting now all right so a couple of uh sheets tips and tricks just to review charles gave us a lot of really really good information again as he mentioned you know a lot and and christian you mentioned this as well a lot of what happens in app sheet when you build actually starts in sheets uh so here's just a quick summary of some of his favorites that he pointed out um i know that the unique uh expression in particular function excuse me in particular was one that he highlighted a lot i really liked the explorer i knew that it did something like that but it was a good reminder that that was there so i feel like uh charles i owe you a debt of gratitude for reminding me of that um i was going to say feel free to rewind and even just play with the explore feature it's like it's it's pretty impressive what it can do so yeah yeah absolutely did you have anything else um you wanted to add on i think that's pretty much it i mean the those sheet functions are also quite powerful and you can experiment and plus there's plenty of examples out there but these are just tools allow you to kind of work with your data and continue to clean them up and then that unique function was very helpful in that he was able to take a bunch of like disparate different items and then just unify them and that was very helpful to app sheet because then actually we'll be able to read that so oh these are distinct sets of enumerated data so i'm going to create an enum data type and then give you these options like a pop drop down or a set of buttons so it really enhanced the integration experience absolutely all right uh so christian i'll actually have you cover this slide oh for this one yeah in general these are just the same links so these are all linkable and they'll be included in the the video right when it gets published so you can click on it but yeah in general app sheet you know as you guess appsheet.com blog.app sheet uh we have a community lapt that app sheet as well there's also some key uh things that are very helpful for these topics like the data and so obviously yeah you can click click on them within the the published video and stuff like that also just a plug on my video series that i started a couple months ago called building with app sheet we just launched a new video just the other day on automations and so you go to that uh building with app sheet you'll see the latest video feel free to check it out and i go into a little bit more detail on the types of automations you can do um but yeah feel free to have a look at that stuff yeah really great series christian um we reference it all the time i will also just i want to really double down on the community.appsheet.com i cannot emphasize enough how important or impactful that space is you get to connect with different app creators from all over the world as well as the app sheet team i know i personally am on there quite a bit um i read everything i mean i get to respond to everything but i do read everything and it's amazing what you all are doing on there so i highly recommend that space as well okay so a couple of questions we have that we wanted to address [Music] all right what's the advantage of app sheet versus a formula heavy sheet i feel like we covered this in some good detail but christian do you have any summary points you might take on it um so appsheet is it's an application development uh environment right so it gives you the end product is like as you can see in the screenshot there it's an actual mobile or even a desktop app if you want it and so that's that's catering towards a particular set of personas that may or may not have full comfort with working in a very complex sheet so in this sense yeah you can use the sheet whatever if you want to if you're perfectly comfortable but there may be people say on the shop floor that just want to go in and double check certain items or scan a tag or whatever and just do some data input quick data input for those folks they don't necessarily need to go into this massive complex sheet they just need to get in do whatever operation they need to do and get out and that's where app she provides that nice complement to your overall data so that it fits those types of personas in a much more efficient manner and one also thing to add you mentioned working on the floor or also if you're you're in the field app sheet has a really cool component it's the offline capabilities so you can be in the field where you don't have access to the internet and you can collect all whatever data data you need we have companies for example that work in mining um and that's that's one way they actually take their devices in collect information and then when they reconnect to uh wi-fi or internet whatever they may be um they're able to actually sync back to their data source so that is one really really cool feature that app sheet has that sheets i don't believe does have so something to keep in mind okay next uh is it possible to start an app sheet app without having the data sheet and no data fill into i just start to draw the screen and the button so the answer is and technically it's no the answer is no so you'll need some sort of initial data defining document so typically you would start with a sheet or it could be a sql table but that's kind of just the starting point because as you saw when i built the app it was actually reading deeper it saw all the hints within the data itself and it created those data types it created like the enum for example it created like a certain level of customization that it was only able to do that based off of the extra data that you provided so without a doubt um yeah you do need to have some initial document by which to base the data this is a little bit different from some other similar low code or no code word they actually provide like a a data modeling feature where you start literally from scratch but that's not the case here and i think there's actually a good point in this sense that you just cut to the chase point it to your data that you already have and then just quickly give you something that is workable and then you just customize at that point so it's a very efficient overall kind of uh user journey in that sense right and i would add too we have sample apps you can get started with um they're free of charge you just have to have an app sheet account which is also free of charge to get started with too um they actually have a data source built in and you can see what it looks like and when we talk about you know a data defining document it can just be the names of of columns you don't actually have to have those filled in so that that's also a helpful uh little tip to get started with there too yeah so if you start from scratch just open up a sheet add a few columns and then you're good to go you just do the tools menu and then generate an app and then yeah you're good to go yeah absolutely all right can i connect multiple google sheet files in one app sheet window or slice yes yes you can so if you recall in the demo charles had set it up where we have different uh tables in different tabs in a single sheet those could have easily been multiple sheets and then i could have also established relationships between those multiple sheets and in a similar fashion in a sql database environment i would have multiple tables and then i could just do the same thing so the answer is basically yes okay all right see if i uh choose multiple columns to form my key will it be similar to how it is handled in a sql database i think we covered this earlier yeah to some degree i mean it's like so in general to just to kind of uh discuss the key capability in app sheet and and how it kind of differs from a sheets world so like in sheets it's perfectly acceptable to have different records different rows in your sheet to have exactly the same values right but in a traditional database application that's not really doable because you could have data collision because it needs a way to identify every individual row or record and so that's where it has this concept of a row id or row key and such and so app sheet does the exact same thing it provides a key mechanism by which you can establish uniqueness among the different records even if the data itself is identical and so app she will go through define a key for you it will pick either the leftmost column or will actually concatenate the two columns that make the most sense it's all done to establish uniqueness and it does this by uh scanning the data to establish that uniqueness now you can also customize how and where you want to use a key like i could define my own column and just specify that i want this column to be my key and i'm also going to generate a unique value using a function called unique id and set that as my unique key now i could do that i could also set up multiple columns to serve as a key so i could just have a little checkbox so i said that's part of the key and that's part of the key it'll do that so either i can do it manually or even app sheet does this manually and so in your case if you're choosing multiple columns to form ikey which is something that i just said that you could do how similar is it handled in sql databases the answer is that it's exactly the same you can use it in the similar fashion where you have both the the different uh columns setting up as the key and it's even the same in the database world you could actually set up a foreign key relationship where you have like multiple columns establishing like that relationship for you as well but you basically the answer is yes you it's essentially the same all right that was a long-winded explanation for that no keys are important and there's also a lot of puns i like to make in there too but i'm going to refrain from making key puns because i think i do it too often uh okay i think it's our last question i am trying to build an informative app each column has info like name email phone do you guys suggest that each piece of info data be split between columns so i'm tempted to make a funny reference say no just stick them all in one column no yeah without a doubt you need to kind of keep well you totally need to keep the data separated so like you know address you know date all these things where they're distinctly different data types just as in a spreadsheet you would want to have your columns be distinctly different defining like what an email is different from a phone number an address yada yada so all that stuff needs to be split up into different columns from and that's just a recommendation from sheets or database tables and such and so naturally yeah that applies in the apg world as well i actually i want to try that now when we're done with this i'm actually going to try taking all of those lumps together to see what app sheet will do i think that would be really yeah you're probably right your birthdate address text phone numb column will just be text that's what it'll be crushing my dreams uh that'll be yes or no no whatever all right can you define unique values in a virtual column so technically you could start down this path i could say okay just to recap or just to remind folks a virtual column is basically a column that's derived with an expression right and so a lot of times virtual columns are created to well if you recall i had this like related tasks that was essentially setting up a virtual column with an expression to derive that related task so it just happens that um if you define a virtual column with a a function like unique id uh app sheet will then pause you right there and say no it's not allowed so you actually can't do that technically speaking you can't define a virtual column using the unique id function to derive unique values in this case and it's doing that to establish data consistency so there's essentially uh an understanding or a method to that particular behavior i guess gotcha okay so that is a wrap i want to quickly thank christian and charles for providing their insight and information today i think we all learned a lot or and we're reminded of some really cool pieces in both sheets uh and in app sheet as well so thank you everyone uh for joining us today greatly appreciate it and until next time stay safe and stay healthy and we'll see you in the community thank you as well jennifer it's been fun uh it's been a pleasure walking through this stuff so yeah yeah absolutely and we'll have you we'll have you both back on more in the future all right take care everyone thanks so much okay take care

Channel: AppSheet

Views: 34,579

Keywords: Mobile Apps, No-Code App Platform, Rapid Mobile Application Platform, Excel to App, Spreadsheet to App

Id: tnH3hPQ644I

Length: 73min 47sec (4427 seconds)

Published: Fri Apr 16 2021