okay so this video will be a follow-up to one of my previous videos when i show how to use google
sheets data with google data studio and control access to who can see which data based on their
email address now i had some questions how we can improve that to create multi-level access
so for example if we have different sales reps here isabella olivier jose etc what if we have
like a department head or a manager that is supposed to be able to log in and see the data
for a few of these people in the list but each person should still only see their own data
so i made this data a lot smaller so we can test it and see how it goes i'm going to pretty much do
what they have suggested in their documentation for a case like this and see how it goes and most
likely it should work just fine now what we're going to do here we're going to create another tab
i'm going to call this one access control it doesn't matter what you call this tab that's
what i called it the name doesn't matter i need a nice clean list of ids i don't really have ids
here i just have their names so that's what i'm gonna use if you have better unique identifiers
that's what you should use but in this case i'm gonna go with their names so i'm just
gonna go ahead and select this list and paste it over here and just get rid of
all duplicates right here so i'm gonna go under data and remove duplicates header row
there it is so we have our sales people i'm going to go ahead and enter their emails
in the next column so this is where you need to match the correct email to the right person
and that email should be that person's google account because it's going to basically control
with their google account email whether they should be able to see their data or not
so for edward i'm gonna enter my email so i can test and make sure this works
so so far this is pretty much the same thing i did in my previous video what i'm gonna do different
here is that i'm gonna also do another level of access so the way we're gonna do this let's say
we're gonna have a manager we should be able to see isabella's data jake's data and adwords data
right here so this one that i've entered my email i should be able to only see edward's data and for
this i'm gonna enter another email i can actually log in to check that i can see isabella jake and
edward and all i'm gonna do is just basically repeat the same email for all three of them
that's pretty much it unlike the previous video i'm not gonna do a vlookup and i'm not gonna add
an email column here i'm just gonna leave these separate the key thing here is that
for the key lines here that we should give access to a certain email we should just make sure that
particular in this case name appears in this table with an email next to it and you could obviously
repeat the same name multiple times like you can see i did edward twice and once i give
access to this email and then i list edward again and i give another email
again okay so now that we got this going let's go ahead and go to data studio
so i'm under datastudio.google.com right there i'm gonna go ahead and create a new blank
report we're going to connect to that spreadsheet from our google drive so if you have a long list
of worksheets you just need to search and find the one you actually need i'm going to
go ahead and click on the one i was just working with and as you can see we have two worksheets
you remember we have new data which is our data and we have access control which is that second
one i've made so i'm going to start with that access control worksheet the second
one and i'm gonna use the first column as headers that's great i'm gonna press add
add to report so for now this data is not filtered or anything it's just using that table to create
some report that really doesn't make sense so what we're going to do here we're going to go ahead
and go to this data right here and added the data we just connected to that access control table
and we're gonna filter this by email right here i'm gonna click filter by email
i'm gonna check this box i'm gonna choose that email column that i've created myself
and as you can see i need to now grant access to be able to view the report and this was that
email that i was using for one of those lines for edward good enough i'm going to click allow
and click done and you can see now i can only get edwards stuff after i did this but this
is just that access control table because by itself it doesn't really have anything
useful for us for reports but this is the table we're going to use to
filter our other tables now what we need to do we need to add another data source to this which
is going to be our first worksheet at this point so i'm gonna start by going here under add data
and i'm gonna connect again to google sheets and select that same spreadsheet
so there it is i'm gonna click on this choose that new data which is pretty much this right here
and use the first row as headers click add
add to report and that's now in there so once that data source is connected
then i'm going to go ahead and click on this blend data so now basically i'm going to blend that new
source that i did to my previous source right here i'm going to click blend data and see this is our
previous bond with emails for access control and i'm gonna click on this add another data source
and choose that connection that i did it's gonna be one of these two it's gonna be very hard to
tell which one it is but it wasn't the first one because this has these three columns so i'm gonna
go ahead and remove that one and click and check the second in the list right here so there it is
those are the columns good so i need to add those columns whether they're going to be dimension
or metric or whatever so i'm going to choose which columns i'm going to need for my reporting
so i'll go ahead and pretty much just use all of them i'll use date i'll do region
and i'm gonna add matrix so for metrics here i'm gonna use this sales amounts
and i guess record count for this should be good too i probably want to give this
a little different name so i'm gonna edit this and call this just count
and that's for making sure i don't confuse it with this other record count here so we
have this count here with this so this point the important part is going to be to choose the right
join key from both tables so join key is going to be that common key column in my case
i was using that sales reps name but remember that
if you have a better key use that instead so for me from this table see i have sales rep from this
table which is correct now i named my column name the same but if you didn't have the same column
name you would have to just drag it over see it automatically figured it out so sales rep from
this too so that makes the connection between these two i'm going to go ahead and hit save
so now we have our blended data set so first of all i'm going to remove this record count
because that really doesn't mean anything that's the record count from our access control table
that doesn't really mean much for our reporting i'm going to make this a little bigger here so
we can see what we got so i have sales reps named twice i'm going to remove one of those
so i'm just gonna get rid of all of these whatever's in here and
just start over from scratch to avoid any confusion i'm deleting all of this
dimensions in metrics i'm gonna put sales which is in here i'm gonna start with that now in my
dimensions i'm gonna add all the other columns i'm gonna need here so i'll do like date i'll do
sales reps name region category so that looks pretty good let's just
resize these columns in this video i'm not gonna go over design changes and any of that stuff i'm
just gonna leave everything standard just watch my previous video or better my data studio videos if
you want to do more fancy stuff here but here what i want to show you is that we currently just get
records for edward so i logged in with that email that i attached to
edward and that's what i got all i get is adwords results so if we go back to our table
we should see those are three records matching edward so here see we have these two and this
so so far filtering works for one user let's also just throw in a quick pivot table here too
and we'll just do sales
and count i guess actually these are columns we don't need those just sales under matrix
is good maybe we'll add count under metrics too and maybe for just something here on the left
we don't really need this record count as a dimension i'll add something like
sales rep that should just give us the name of edward and the total see it says edward did three
sales which we can see right here and the total for all of those was this much
that's good enough for summary again you can do more if you wanted to
but that creates my report i'm gonna go ahead and name this one report for all
and at this point i should be able to just share this report
with other users so if you remember i did create that second user that i was trying
to give access to three different reps so i'm gonna go to share and enter that email here
and just press ok so that person should be able to view this report
let's go and log in as that person to data studio and see what we can actually see
so here we are now logged in as that user so this may take a little bit to show up here as
you can see i logged in but i can see it here sometimes you may have to basically
click shared with me sometimes you may have to refresh or go under shared with me option and see
that showed up one report for all but it may take a little bit some time so i'm gonna go
ahead and open that now as this other user and as you can see as i'm logged in as that
user it's asking me to grant permission as that user for this i'm going to click allow
and let's just zoom in a tiny bit and if you look here as this user i'm able to see information for
jake isabella and edward for all three of them and then my pivot table shows totals for all
three of those sales reps and just to check that's correct if we go back to our spreadsheet this is
the other account let's go back to data studio and go to access control see here to this email
i have assigned isabella jake and edward and that's exactly what i get here jake isabella and
edward so now using the structure we can actually do this control and if we want to have one user
who can see different data sets we can actually do it by simply just adding those emails here to
this table just make sure if you go and modify your access table or your data you go back and
open your report and refresh data
and that should do it thanks for watching please subscribe and i'll see in the next video