Show a User only their data in AppSheet (Custom User Permissions)

welcome everyone to another webinar hosted by actiontraining.com my name is clark james i'm going to be your host today i am also joined with none other than landon cormont the most amazing person in the whole world lynn please say hello to your how's it going guys there he is uh well guys i'm gonna be honest we got a good one today it's gonna be a lot of fun we are going to be going over some ux stuff uh but it's going to be for individual users we're going to be able to hide and show data based on user permissions and who's going to be able to see them is defined by uh who is actually signed into that account so before we jump in i want to talk about uh some things that we have available um i believe we have two new paths one new path landon can you confirm i believe i know we have one new path on azure training.com um but we might even have two and i'm pretty sure they're both still on sale so you definitely want to check those out if you haven't checked out our new chrome extension or our expression library they're both free for anybody you just have to sign up and make an account no credit card or anything like that it's uh just very very helpful atlanta can attest we both use this every day it is so helpful so yeah i would definitely check that out um but you can see from these screenshots we've got one showing the google sheet and how that looks and then we have another that's actually showing our app so landon if you want to take it away i'll be fielding questions for the entire webinar so please feel free to post in the chat i'll be checking that and relaying those questions to landon so go ahead landon take it off absolutely all right guys so we do have a couple objectives today um want to cover a basic refresher on conditional expressions because that is really going to help us get into gear with our main topic which is how to curate a user experience around a user table that you would develop within your app sheet application and so the conditional expressions is going to be the backbone for that we are going to show you how to build a robust user table and what are the uh primary criteria that you need for that um we're going to talk about customizing the user experience and based off of that user table and the end result is you're just going to feel like a boss no but in all sincerity it really is a crucial topic because everybody who's creating an application and most application experiences are predicated on the the personality of it it's whenever you log into facebook you don't want to see everybody's data you just want to see your data right and so today we're going to be talking about how to as a developer ensure that everybody is getting the most relevant user experience for them so uh with all those things in mind i will go ahead and share my screen and we will jump in all right let's jump in here okay so uh for starters i want to demonstrate uh kind of the end product um we're gonna be covering a couple of different things today um and there are so many different areas within app sheet that can be customized through a user a user experience curation based off of this this user table that we're going to be talking about and so i don't know that we're going to cover all of it but i hope to give you the most um prominent areas where i'm i'm you know continually making changes in various apps that that i'm building on a day-to-day basis and and hopefully you can extrapolate to some of those more peripheral use cases that would require user experience filtration okay so um for those of you that weren't on the last webinar that i led and we basically covered api workflows and in order to demonstrate that we made this time card application uh the premise of which was um whenever you're creating users we wanted to essentially pre-populate a bunch of time cards for those users so we created a handful of users and an app sheet just generated a couple hundred records for every single user uh for i think about a year of time so it should have been january 1st to december 31st of that year for the time cards um so that's kind of the uh the background going into this uh just to give the the users the ability to edit their own time cards and for administrators to see um what was going on there okay so i want to start by kind of showing you three different user cases so we've got a super admin and the purpose of this user role is basically to show everything the super admin should see everything be able to edit anything so on so forth we then have a second tier of user role and that is going to be a just what we're calling an admin so the purpose of an admin is that they're kind of like a team leader and they're going to see information from their team in their territory but they shouldn't be able to see other team leaders information so we've got that role um and then we have just kind of your standard user which we're calling non-admin and the purpose uh or within a non-admin role you should really just be able to enter your own time cards you shouldn't be able to see anybody else's edit anybody else's so that is kind of the experience that we're developing here um so i'm going to jump in and just show you from the super admin what's going to be seen so i've got app at greece.solutions and we'll just preview the app here to demonstrate this again super admin should see everything so we've got three different views here we've got a user view so they can edit user information we see our different uh different admin super admin and non-admin so we've got uh crew is is the uh current account that i'm previewing as so that's a super um and then we have cj who is an admin and then uh cameron clark and landon are non-admins so did you actually zoom in on this uh google account absolutely i appreciate that i do have a big monitor and i'm actually zoomed out anyways so there we go um let's see how does that look maybe 125 yeah that's 125. that work yeah this should be good okay excellent all right so um like i was saying we've got these three different user roles and i was just explaining who's who so right now we're just demonstrating the super and so we've got uh visibility on this user view we've got a time cards view which should allow us to see everybody so if i scroll up here to the top i'll see cj and then as i go down i should see other users so i think that was yep that's landon and then i would obviously see my own time cards um okay so that is the time cards view and then we have the schedule which we were using to kind of pre-populate all the time cards as users were added so that's that um okay moving onward i want to show you uh briefly the uh the admin user role so this will be cj greece.solutions and for cj uh because he's an admin he only has access to one view but what he will see is um he will see time cards for him and his team so anybody uh who is a non-admin on his team he will also see here and i think uh the only person that is would be landon so we'll just scroll down to verify that uh once i get to december and then back to january okay yeah so we should see landed right there okay so that is a cj and then we'll look at landon who is a non-admin and uh so same thing here just gonna see the time card view and i'm just going to see my time cards i hit this edit button i could just edit edit those so there you go that is the uh kind of the user experience that we're going for here so briefly i want to mention the different things that we're going to be covering today so uh objective of showing you how to create a user table um with attributes to validate it against for this user experience secondly we're going to again recover some of these conditional expressions so we'll cover that second thirdly we're going to talk about column constraints and how you can apply user filtration to who can see or edit certain columns or when certain columns should be required employing security filters based off of user table will be number four five is going to be creating slices based off of user table six is going to be conditionally showing views based off of user table and lastly we will cover conditionally showing actions based off of the user table so that's a lot to get through we'll see how much we can tackle and you know the the 30 more minutes that we have remaining so uh if if things are going too quickly and you need some clarification uh please just reach out over the chat and uh clark ken can let me know if i need to re-explain anything all right so without any further ado let's go ahead and jump into our user table and right here i just want to highlight the columns that you are going to need all right so uh the quintessential columns for a robust user table is going to be this we need an email column the reason for this is because every uh secure application within app sheet requires that users log in with an email and so we want to match up whatever email they're logging in with uh as a record in this user table so we've got stefan agrees dot solutions app agree stat solutions clark so on so forth uh so this is a quintessential column and this will likely be your key column in app sheet and it works out well because all emails are unique so then we have uh and the roll column so this is going to be another quintessential column if you're going to filter data you don't have to call it roll but the idea is that you are specifying uh different user types or different roles with within their app to help curate that user experience this will allow us to validate against these values later on in the process and then i've added an additional column here sometimes you will run into cases where you're having to maybe have another filter parameter than just a roll and so maybe you're building a crm for your sales team and you've got a territory or a region that you're dealing with so in this case i added an additional column so that we could also filter off of this this is going to primarily impact the admins user experience because the admin will not necessarily transcend a region they would only be able to see non-admins data within their region so for example here cj is an admin in the south and earlier we saw that landon uh he could see landon's information who was a non-admin in the south okay so that is our user table and these are the columns that are going to be essential for us again it's primarily email in some sort of role or user type or whatever you want to label that as so now that we have seen an example of a user table we're going to hit again a quick refresher on conditional expressions because this is the backbone of everything that we're going to be doing and conditional expressions are expressions that will result in either a true or a false value or a yes and a no at the end of the day so i have panned over here to a tab that i had to open um from app sheets documentation on yes no expressions and i just want to cover a couple different things so there are really three different types of comparisons that we're going to be making in these sorts of uh validating circumstances so the first is going to be whenever you are comparing values to values so i have a single value could be a name could be a number whatever it is and i'm trying to match that with a different value and whenever we're doing that type of comparison we have these sorts of operators we have equals two not equals two and if it's numbers you know greater than less than so on so forth um and you also have things like uh contains um is is blank essentially you know validates whether or not a column is has any values in it those are for values matching or not matching um so that's comparing two values together then we have comparing a value with a list that's the second type and we're primarily going to going to use one expression for this and that is the in expression so a quick reminder on syntax for this you're going to have the single value that you're trying to evaluate whether or not it's within a list and then you're going to have a comma and then your list so uh just to show that right here um so i always think about this uh same with contains is which is kind of a similar expression and you have your smaller smaller piece and then you have your bigger piece so with contains you may or may not have a list that you're searching through but there should be something that is bigger than whatever you're trying to figure out uh is contained so and and then in the list you have the single value and then you're comparing it with the list of values okay so that is comparing a value with a list lastly we have comparing two lists together which is kind of challenging and so maybe you have two enum list columns that you're comparing and you're wanting to know if there are overlapping values so for this appsheet actually has a different expression that will help us validate the in this type of circumstance it's called intersect and what intersect does is it return it returns a resulting list based off of two two separate lists that you're trying to compare to one another so in this case we've we're intersecting this list at red blue and green with the list orange and blue and the resulting the result from this expression returns a list that has one value and that value is blue so the way that we turn this into a conditional expression because again app sheet is going to require that rather than having a list and we we need to have a true or false value at the end of the day so the way that we're going to turn this into a true or false value is by wrapping this in a count function so the count will count the number of values that exist in a list and we know that if there were any overlapping values that the count would be greater than zero so if we're interested in knowing if there is an overlap and two list we would say count intersect and we would put our two lists in there and that should give us a yes or no value at the end of the day okay so that's our brief refresher on conditional expressions and this will help us as we move into conditional logic to kind of explain these these different areas of user filtration all right so we've got our user table and the first uh the first use case that we're going to try to tackle is constraining column constraints and the reason i want to tackle this first is because oftentimes you'll add a table into your app sheet app and the first thing that you do is you go into app sheet and you try to make sure that all of your data types are correct and you you know start populating any initial values or app formulas things like that you may also be interested in conditional show logic and so we're going to go ahead and jump directly into that so i've got our old application this is prior to any changes that were made earlier that i demonstrated at the beginning of the webinar and i'm going to show you here what our what our user table looks like all right and i actually need to regenerate the structure here because we did add that column for the region okay all right maybe this is going off of a different table so let me just quickly look through here and see yes it is okay so what i'm going to do is copy our user table that we had earlier and i'm going to replace it so the schema reflects what we had in the other application all right okay so now i can come in here and i can regenerate my structure and it should have that region column okay and we're just going to quickly give these values should just be north or south are the two options there's a only two territories that we have okay great and we're going to put a column constraint on our hourly rate okay so um there are a couple different ways that we could go about this we could either go in the detailed editor you'll find that with the pencil icon right next to the column name and just below the column name you'll see our show section so if you're just interested in showing it uh you know kind of universally you've got this little check box here you can check or uncheck but what we're interested in is actually filtering it based off of user information so what we want to do is only show the hourly rate if the user is a super admin okay again that's that's our top tier of user type so what we're going to do here we know that we only want this to return true if the current user's role is admin so the first thing i'm going to do is i'm just going to put admin because i know that's what we're setting things equal to all right and because we are comparing a value with a value we're going to use an equals operator all right and sorry this should actually be super that is the type all right so super equals and then the next question is how do we get the current users role so in order to do this we are going to do a a look up or in any select which does a query on the user table so i'm going to go ahead and select from the user table and again the column that i'm interested in is whatever column is going to have a matching value with this or at least could have a matching value and so that is going to be our roll column from that point and the next portion of our select expression is going to filter which rows we're interested in and we're only interested in one row and that is the current users row the way that we can define that is where the email call the yeah sorry the email column equals the current user's email which app sheet gives us in this expression so we're going to be using this in many cases and i'm actually going to show us a shortcut after this that will help us better or more easily reference the current user's information rather than always having to do this any select again the reason that i'm wrapping this any around it is because the select always returns a list of values but if i'm going to compare a value with a value and use the equals operator i need this part on the right side of my equals operator to also be a value so i'm going to extract that one value from the list by using this any function all right so that will conditionally show the hourly rate only if the user is a super admin so i'm going to go ahead and save that change all right and we're just going to do a quick look here to verify that that actually happened that it is making its modification according to what we were hoping so i am now cj who is an admin not a super admin and i'm going to try to take a look at landon's hourly rate and as you can see it is not there um so and that is not a consequence of the view because we're not uh doing any sort of column constraints in the view itself it is because uh we had a column constraint in the actual data component of our application all right so like i mentioned earlier we're going to um i'm going to show you a shortcut that i'll often use to help us get user information the current user's information and in order to do this i'm going to create a slice so a slice is a subset of data from an existing table and the table that i'm interested in retrieving information from is going to be the user table because i want the current user's information i'm just going to call this current user the row filter condition on this is actually very simple app sheet is looking for a true false expression again so we're in that conditional expression uh mentality we we've got you know there's different ways to compare values together that will result in a true or a false so in our case um we are interested in validating the email column right so and we are what we're interested in is determining whether or not whatever record we're looking at is the current user so again all users are authenticating an app sheet with an email that is unique and so we're actually going to start by returning the current user's email so email user email this function returns the current user's email and we're going to set that equal to the email column which will have again emails in every single row so what this is doing is creating a slice from the user table that will only show whatever row is related to the current user where the email column equals the current user's email all right and i've called that current user so now i can reference that in other places within the application okay so the next thing that we're going to cover is how do we employ security filters based off of our user table so in order to do this the way there are a couple different places you can find security filters you can either go directly to the security section go to security filters and find the table you want to apply a security filter to you can also go to the data tab and under tables you can just find your table here and go to the security drop-down so the table that we're going to plot apply a security filter to is the time card table all right so um this will be a rather complex security filter um and we're just going to approach it in stages here so oftentimes if you just have a roll column and maybe you're just interested in in two different user types you've got an admin and you've got a non-admin then your expression here can be a little bit more simple it could just be an if expression and so we could have if and then what we can do is determine if the current user is an admin so what i'm going to do is reference the slice that i just created any current user i'm going to return the rule column and again this is going to extract the role from the list of roles that would result from this table query so any current user role equals admin oh that's not how you spell admin all right and if this is true if the current user is an admin then you can either just put true i want to show all records or um you can have uh more customization to it but in this case it may just be true and if the current user is not an admin then you may want them to only see time cards where the user equals user email and the user column in the time card table is a it is an email which again is is kind of it's just a reference to our user table so that's why that expression is working the way that it is all right okay so that is if you you only have just two different user roles um and this is just a more a an easier example of how to filter data in our case we are going for a little bit more complexity and because i would imagine some of you do have more extenuating use cases and we're going to try to make an accommodation for that okay so because we don't just have two options we're going to use an ifs rather than an if so this will allow us to have multiple value and result pairs so what we're going to be doing here is doing kind of a continual lookup on the current user's role and setting it equal to a certain value so we're validating okay is the user a super user if so then this is what they should see if they're an admin then this is what they should see and if they're a non-admin then this is what they should see so an ifs expression is going to allow us to have that kind of complicated branch logic if you will all right so the first thing i'm interested in is i'm just going to start with the top tier of user so any current user role equals super and now i will tell appsheet what the result should be if the user is a super user in this case i'm just going to put true because i want them to see all data the second user role is admin and the admin is going to be a little bit more complicated here so if the user is an admin we want them to see either their data which means that the user column should be set equal to their email but we should also allow them to see their team's data and this one is a little bit more challenging so in order to identify whether or not the user is on their team um we are going to have kind of two conditions here we are going to have the first one be that the uh let's see here let me try to format this better okay so the first one is if the user's region equals the current user's region all right so we are we are grabbing the region that is being dereferenced from the user column in the time card table and we're setting that equal to the current user's region secondly we are uh we don't want them to be able to see other admins um well really we only want them to see non-admins on their team right so um how we're going to validate that is if the user dot role equals non-admin okay and app sheet would require that both of these are fulfilled so they're either going to see their data or they're going to see their t their non-admin team members data all right so that is our admin user type finally we'll have um i'm just going to say true here but you could you could actually look up the non-admin type and essentially what the if function is going to do is it's going to evaluate this first and if this isn't true then it's going to evaluate this and then if that's not true which we know the only other user type is non-admin um then it's going to evaluate this so um it's important to know the priority of these events um because actually if it fulfills a certain condition meaning uh you know the current user is a super user then it's not even going to get to the rest of the expression here it's not going to waste time going through that computational power so um we know that if it has gotten to this point point excuse me that the user is a non-admin and so i'm just going to put true here and now the result if again they are a non-admin should be only that they can see their data so user equals user email okay so that is our most complicated expression of the day we worked through these different these three different user types and the admin was kind of a pest um because there's there's a bit of branch logic in there we're trying to help them see their data but also their teams all right and again the result of these functions the ands and the ors they're always resulting in a true or false um which is what app sheet is looking for in these security filters so it is going down every single row in the time card table and it's applying this expression to each row so each each time that the user column is referenced it's referring to the current road that it is evaluating through whenever it's going through the security filter all right so that is a security filter again let's save our changes and quickly check if uh it did what we wanted it to do all right so in our time cards view um we should let's see here let me actually make sure that uh these time cards are working here all right just trying to grab all the rows and i forgot my shortcut to grab all the rows so i'm just dragging this down all right okay so i'm going to copy this and we're going to throw this in the other application the time card section all right give it a second all right awesome okay great so i think we've got all of our data there and now we can resync okay great and what we're again what we're looking here at here is because cj is an admin we want him again to be able to see his data and his teams which would be landon who is a non-admin all right so i'm just going to scroll down until i get to january again all right and landon should be there and there should not be any other users so yep that finishes it december 31st okay so um our security filter did work and we could go through the the other user types to further validate that but um i think we're running short on time here so we're gonna keep checking on all right um we have already covered creating slices based off of a user table we created a slicer a slice actually on the user table um based off of the user's email so we could in the same way that we developed a security filter we could apply the same type of expression to a slice for other tables again the the reason that we would do that is sometimes you want to have a specific view that may apply to a user type and but also give them full access to data in other parts of the app so in that case you would create a slice rather than a security filter all right okay so now how do we conditionally show views based off of a user table all right so in our demonstration application we conditionally showed the user and the schedule table so that's what we're going to do here we're going to modify the user table and we want this to only show if the user is a let's let's say admin or super admin um because an admin should also be able to see their team's data um so what we're going to do here is because we have two values that we're kind of trying to accept um we could make that a list and we can compare the current users role with that list and we know that if we're comparing a value with a list we're going to use the in function so i'm going to start with that so in and then i'm going to put my list that i'm validating against so either the user is a super user or they are an admin all right um okay so that is again the way that in function works you you have your smaller piece first and then you have your larger piece second and so my list is actually going to go second here so i'm going to put a comma and before the comma i want to put the current user's role which is going to be the singular value all right so i'm going to reference my slice again so any current user role that is the column that i'm interested in okay so if the current user's role is either a super or it's an admin then they should see this view and again the way that i got there i clicked on this view i clicked the view hyperlink below the app and it took me to the view editor and so i scroll down and under the display drop down you will find the show if section all right okay so let's go ahead and save that and see if it worked and in order to test this you'll first see the admin so cj grease dot solutions and now we're going to try land because landon is a non-admin and we're going to see if he can see the user view all right and it has disappeared and so he would not be able to access that view and uh so that is showing or conditionally showing views and then finally we have conditionally showing actions on or based off of the user table so in our previous example we had um we had the ability to to trigger payment and i think just due to the nature of that view i think what we we may rather do is try to modify the add button on the user table so if i go back to cj here and the idea behind this is that i would i would imagine super users should be able to add users modify data things like that um but we would maybe withhold those privileges from admins and non-admins um so in this case i'm going to go to uh our user uh table or our user view just that you can see these actions so we've got the add action here and then if i click into a record we'll see the edit action for that record so again i want to restrict those to be only viewed or accessed by super users okay so in order to do that i'm going to navigate to our behavior section and actions are categorized by table name in alphabetical order so i'm going to find my user table and i've got my add and edit buttons here so the first one i'm going to modify is the add and i'm going to scroll down to the behavior drop-down and once i click that i will see where it says only if this condition is true so this allows me to input some conditional logic and so that app she can verify whether or not it should use or allow the the current user to see this button and use that button so again the uh the only user type that should see this is a super user so i'm going to start with that i'm going to start with the value that i want to validate against and then i'm going to set it equal to the current user's role so any current user and i'm interested in retrieving the rule column for the current user all right and i'm going to copy this because we're going to apply oop sorry we're going to apply the same expression to our edit button all right okay so let's save these changes and we'll give it a whirl okay so again cj is a he's just an admin so he should not be able to see the add or edit button so there is no add button here and we'll scroll uh into a record and we'll see that there is no edit button here either so there you have it we have conditionally shown our actions and just to verify we're going to look at app at greece.solutions who is a super admin um and see whether or not he can see those actions so we do have the add button here and if we select a record we do have the edit button all right so we have kind of gone through all the things that i wanted to demonstrate and and i will let uh clark kind of reel us in and and we can maybe address any questions that people have or things like that yeah absolutely guys uh landon thank you so much for the awesome demonstration i know that uh uh everyone learned a lot i know i did so we really appreciate uh appreciate you being able to do this for us um i'll give you a couple maybe a minute or two to realize more questions uh but in the meantime um if you don't already know uh we're actually training.com powered by crew tech we do these webinars every week or we try to do them every week sometimes things happen and we are delayed a week or two but we try to put out as much content as we can for you guys all free charge because we just we want to help you but in order to keep these things free we need uh if you wouldn't mind just hit that subscribe button click the bell um let us know that you support us every single one counts every single one matters and it's why our boss lets us do this for you um if you have any other questions elena if you wouldn't mind typing my email clark crewtech.com in that preview app is they have somewhere to see it yep um that's my email if you have any questions or if you have any future webinar talk what topics that you want us to to go over that's where you want to shoot those as well as in the comments on youtube uh either myself or cameron will be checking this so we'd love to help you guys out with whatever you guys need but we will need feedback in order to do so but it looks like i'm not getting any questions which really just means that landon was on top of it and nailed that one out of the park so big round of applause for him uh i don't know if you mentioned atlanta during the webinar but if you look at land on the screen right now you will see that i make the most uh hourly rate out of all the crew employees um i just felt like i needed to point that out that's true i did not change that that's just yeah that's just the reality of it so anyway without further ado i hope you guys have a wonderful rest of your thursday or friday depending on where you are in the world um and we'll see you again either next week or the week after uh i'll send out an email to what you guys know all right y'all have a great rest of your day see you guys

Channel: AppSheet Training

Views: 17,159

Keywords: AppSheet, Google Sheets, Custom UX, UX, PDF, Automation, Google Cloud, Email Automation, Document Generation, AppSheetTraining

Id: 2Z2qUTtxNFA

Length: 45min 18sec (2718 seconds)

Published: Thu Mar 25 2021