in this video let's do something similar to a vlookup using a script for this example
we got this data and for each sales person in this rates worksheet in this table we get some
sort of commission rate so what we want to do we want to go find the appropriate commission rate
for now just for the first person for isabella in this rates table so that should return this
particular percentage and then once we do the first one we'll also do a script to do it for
the whole thing for the whole column now you could obviously do this with vlookup function
but we're gonna do this specifically with a script so what i'll do i'll go under extensions and do
apps script and this is pretty much what we're gonna do what we need to do
so the first thing we're going to need is to store some references to our both worksheets one of them
is called sales data right here and the other one is called rates let's go ahead and start
with this so i'll start by creating a constant to hold the whole spreadsheet
i'll continue that by doing a worksheet for our first tab that's called sales data
and for that we'll get that worksheet by its name which is this
and similar to this we need our rates worksheet which i believe was called
rates let's just make sure that's actually accurate there it is yes
let's rename this function
it's up to you what you want to call that so now let's go get our data in that rates worksheet so
i'm going to basically get this range of data
and for that we'll take that worksheet rates and you'll get range start from the second row
first column so second row first column that would be the cell with isabella
and we need to do this entire range like this so that would be
eight rows and three columns going this way and for rows we'll get it dynamically by getting the
row number nine minus one to get that eight out of this so we'll do ws rates get last row
minus one and for columns we'll do three columns so at this point to get the actual data we'll do
get values to get that data to an array like this so at this point this rates data should
be an array with this data in it so now let's try to find one of these people in this table to see
if it works so for example let's try to search jose and that should return this commission rate
so for now i'll just say search value equals that person
later the search value will replace it at some point
but for now that's good enough so now in that rate data i'm going to use this method find
and this is going to accept the callback so the callback is going to accept each
row from that table so i'll just call it r and for each row in that table we have to
match some sort of data so from our row i want to search and find this jose this person in
this first column so the first column in our array would be column number zero therefore
i'll do r0 to find in the first column if you wanted to search and find that person's name in
the second column you would do one now i'm going to do 0 here and i'm going to check if that equals
this and for that i'll just pass this search value variable so what should happen with this it should
basically just find the first match for jose which is this one and when it does find it it's
going to return this entire row of data so let's try to save that in a variable for the time being
let's try the console log to see what that looks like before we get to the rest of this
so i'm going to save this console lock this variable match row and i'm going
to make sure i have the right function here which is this one and i'm going to run this
now if you never ran a script before in this particular file it's going to ask you for
permissions so you want to go ahead and make sure you give those permissions in my case i did run
scripts in this file before so it just runs right away so you can see when i did this i got this row
back which is that data for jose now let's try to see what happens if we search for somebody that's
not in that table let's just add something here and rerun this and see when i do that now we get
undefined so if we don't get a match then what we're going to get is undefined here and if we
do get the match it's going to give us the whole row i'm going to get this back to jose now at this
point if i run this that's going to be this right now we want this percentage right here which is
column number 3 in this array which would be 0 1 2 because arrays start from 0. so if i do match row
2 that should just give me the percentage without the rest of the data and there it is
that's the match let's go and make sure jose's percentage is actually this 1.5 something and
there it is it's just formatted differently but if you look up here you'll see it's the same number
so i'm going to go back close this now one thing i want to do if i don't find a match i want to right
now return a blank right now if we don't find a match like for example if i had a letter here and
rerun this see i'm gonna get this error because we're trying to get that data from the column
but there is no data there is nothing returned and for that reason i can do an if statement
i can say const match and basically i'll say if that match row exists then we want to do this
otherwise we're just going to return a blank which you can do double quotes you can also do null
that should be fine too so at this point if i
try to run this and return this match
see it returns null you could also do double quotes and then finally if i turn this to
something that actually exists and rerun this it will return that percentage and this now
will be our match so i was able to get this for a single value and if i wanted to place that in
here for here we would probably want to do isabella so let's just
quickly replace that with isabella right here and if i run this you'll see that it's going to change
the percentage to isabella's percentage but now if i wanted to actually place that value
here in the cell in this h2 cell in the sales data worksheet then i would do this ws sales data
get range and then use that cell which is this h2 i could do h2 or i can also do row column
right now i'll just do this and then i can do set value because we just have a single value
and just make it the match like this so at this point if i run this
we should be able to see that we place that value here now this is not formatted
as a percentage but the value ends up being here okay great now finally maybe we'll also modify
to grab what we're searching from this cell c2 instead of just hard coding it
so for that reason instead of doing this we can do that same sales data get range
and then grab from that cell which right now is c2 so i'll just go ahead and do that
and then in this case we're going to get that value instead of setting
and that should get that value from that cell and then we're going to search for it
we're going to find it in that other table and then return the match let's just comment this
line the console log and see there's nothing here so at this point if i rerun this code
it should go find the match for isabella and return the rate here coming from
this table okay so now that we got this now let's try to do this for this whole column so for that
instead of getting this one value and placing it in the cell we want to get this whole column
of values right here you could get this in different ways and i've done a lot of videos
to find the last row and all of that i just did it as a matter of fact in that rates table too but in
this one i'm gonna keep it simple i'm just gonna write the range from c2 up to whatever the last
row was which was like c2041 so let's go ahead and do that so what i'll do here i'll make a
little change i'll go here and do c2041 i hope i remember that correctly and right now because it's
a whole range instead of a single cell i'm going to switch this to get values instead of get value
now this will be an array so i'll change it to search values and now what's going to happen is
this is a whole array coming from here and we need to search every single thing in that array and the
way we can do that we can take the search values array and we can just map through that array
and for each row in that array we're going to do some calculations here now each row would refer to
one of these lines now even though it's just one column it's still gonna come back as a whole row
so we need to still say the first column within the row that we just got here so that's gonna be
zero so if we wanted to refer to that person's name that would be r0 which would be basically
this search value that we're trying to find so what i'm going to do i'm going to take
a couple of lines here these two and i'm going to move them within this loop in this map
and here instead of search value i just have to use this now the problem is that i already have
this r variable here so now i have a conflict so i want to make sure i rename this variable for this
loop something else so i'll just do search row instead of row and then we can refer to that as
search row 0 for the first column in that and that means now i can grab this and
place this here as the search value itself so now we'll search for that value within that row
in that rate table we'll find row information and then if there is a match we'll basically
return the match if there is no match we'll do no so all we have to do at this point
is simply just return those results so we can basically output that so when you do map it's
going to return a similar size array and that works excellent for us because if this column has
1000 rows then we need to return 1000 rates so we need the same size results so all we need to do is
just make sure we save this results in a variable so we'll just call it matching rates like this
and the last thing we have to do here let's actually comment this line out for a second and
run this and actually see what console log returns so i can explain you what
i'm changing here in this next step so i'm going to go ahead and save this rerun this
and as you can see what i get is a whole array of matches basically all the rates starting from
first row and then second third and so on now the problem is because i want to basically grab that
information and put it here in my spreadsheet the spreadsheet has to be in format of rows
and columns but right now this is just a single dimensional array so it's just comma separated
values here what we need to do we need to place each one of these in an array like a structure
so that it becomes row and column and for that when we do this return over here we'll just
return this as an array here and here and right now if i show you what happens if i just rerun
this code you'll see that it still returns the same results only now it's in the structure of we
have this array of rows and each one has this kind of column like structure in it so now we should be
able to put this back in a spreadsheet because it's in the format that our spreadsheet needs it
so all i have to do now is go to this sales data worksheet
grab the range where i need the results so that's going to be from h2 through age same row here
2041 and that's basically starting from here all the way down until the last row here
and now instead of set value we're gonna do set values because it's a whole range of data and
we'll just take this matching rates and basically place it here so i'm going to go ahead and comment
this line save it and we'll rerun this to see what it looks like let's actually just format
this column h really quickly so that looks pretty like that so now it's looking like percentages
let's go back now and rerun this thing and see what happens so i'm going to go ahead and run that
so done i'm going to go back and take a look and as you can see we got our values here so let's
just check a few of those to make sure this is actually accurate for olivia that's 8.62 let's
go check it out olivia 8.62 that's accurate let's check for jennifer as well 9.64 so jennifer that's
correct let's check one more that should be good enough let's do something like grace that's fine
319 so let's go check it out grace 319 as you can see it works as it should and we did our vlookup
with a code there is no function here and there you go that's how you do a vlookup with a script
thanks for watching please subscribe and i'll see you in the next video