all right so in this video i'm going to talk about user-defined functions in google sheets
and i did a video before on user-defined functions in google sheets i'm going to
link to it i'm just going to basically add a couple of things to it mostly about arguments
and returns and i'm also gonna talk about arrays but i'm not actually gonna cover race for that i'm
gonna link to another playlist where i've covered everything you need to know about a race so if we
try to create like we have all these functions in spreadsheets right some sumif etc now if you
wanted to create your own function that does its own thing and it's not one of these functions
then you can create those functions by going under tools and script editor and if i just rename this
now in our editor now we can create this function right here so we're going to give it a new name
let's say my function is going to be called hello now this function doesn't have to accept
arguments but it could accept arguments now for now i'm not going to do any arguments in this
parentheses i'm just going to move on now the main thing about functions when you do user-defined
functions to use in your spreadsheet is that they have to return something so in this case
i can just return anything i want so for example i could return a number i could just say six so if i
just go ahead and save this now i should have a new function hello that returns six so if i
simply just go here and type equals hello now it's not going to auto-complete as you can see
by default but you can make it do the autocomplete and again i'm gonna link to a video below where
i go over this in this video we're just gonna concentrate on function arguments and returns
so if i just do this see this function doesn't accept any arguments so i'm just gonna do hello
open and close just like this without putting anything in parentheses and if i hit enter
well apparently it's stuck so i'm gonna have to just make sure i save this and refresh this
whole thing there it is so now we have a function we type equals hello it's going to return a 6.
now we could also return text instead of a number now i could just literally just return some text
and save this now it's going to return that text so see that function now returns the text hello
now the return could be either a number or text it can also be boolean value like true
or false i can do something like this if i save this i go back see that returns true
and the return finally can also be an array now when you think about arrays the structure of the
array needs to be the same way you structure arrays in a spreadsheet so it needs to be like
an array of arrays so that means it's going to be an array and you're going to have different
rows inside of this array like this as a raise and each one is going to be some comma separated thing
so for example i can do one comma hello and then in the second we'll do two comma
high now similar to any arrays you would use in spreadsheets it needs to be an array of arrays
and your column sizes must match for all of these so if you did two columns in the first
row you want to make sure you have two columns in the second row as well so at this point if
i just hit save and go back to see what this function produces see it returns this entire
array like this with a single function so if i take this out of here and go type hello in here
that returns that entire array in the spreadsheet similar to like functions in spreadsheets that
return a raise now that's as far as returns go so you can either return a raise or you can return
a single value now the next thing is arguments now your functions could optionally accept
arguments right now this function doesn't but if we wanted to we can create an argument right here
in parentheses so for example let's say this is going to be some text so just name for a variable
that's what it is right here and then we could return something that is going to use that text
so for example i could do something like hello with a space and concatenate that text to it and
save right so something you would do and then you would return now you don't have to return right
here in this line you could also just create a variable first and basically do some operation
and then you could return that result back so if i save this now this hello function should
accept this argument txt see right now it gets a little undefined because i did not pass this
argument here but now because we could have an argument i'm gonna go here and do jack
and now we could go here and in this parenthesis we can link to that cell as an argument and at
this point that jack text is going to become that argument in that function so see if i hit enter
it's going to say hello jack because this jack goes here as this argument this value right here
txt and then we can use that in here do some operation and return some sort of results back
if you wanted to have multiple arguments you can just comma separate them
something like this and then you can basically use both of those
and whatever operation you're going to do here in your function is up to you now like i said
i'm gonna create more useful examples of creating functions and i'm gonna upload a new video this
week but i also already have some existing videos like import json functions for example if you
watch that video that would be more useful example of a function but right now if i go back and take
a look see we have to now define the second argument here because it's undefined so now i
can go here and type this and then in my function because it's going to accept multiple arguments we
can simply just link to those now i have the first and second argument and that's gonna basically do
this so the first argument second argument now we can pass from the spreadsheet like you would with
regular functions in google sheet now the final thing i want to talk about is that this argument
can also be a range instead of a single cell now when you pass a single cell what happens is that
each one of these values basically becomes equal to the variable you define so if i have c pointing
to this that says jack then at that point this txt is that string jack or if you have a number that's
going to become that number like 5 or whatever now if you go inside of your function however
and select something like this a range instead of a single cell then what happens in this case
it's going to take this entire range and that's going to be our first argument in this function
so what that means is that now we can go back in here and accept that so i can just say names
for those and now this names will be an array of arrays so basically it's gonna be
this jack and olivia as you can see i get an error and this doesn't work so now what we can do
with this at this point you can imagine like that names equals to this array of arrays
and in that array of arrays the first one is going to be jack and the second one is going to be
olivia like this so that's really what that names is going to be i don't have to write this line
that's what automatically is going to happen by simply just accepting this argument names
as a range inside of this function so now we can work with that as a regular array so we can just
take instead of this txd i can just do names 0 which would get us this first array and then
if i wanted to get that first thing inside of that that would be 0 again as the column so that should
refer to jack at this point and then if i do names 1 0 that should be the second row inside of this
first array and then the zero would be the first column which is olivia so at this point if i hit
save this should basically produce the same result we used to get let's make sure this
updates see i'm gonna go back to the short one like this similarly if you basically just have
more columns like this and you refer to that range of multiple columns like this
now that again is going to be this array so the only difference right now
is that our array here is going to have multiple columns so it's going to return something like
this so it's going to be like two columns in the first row two columns in the second row and again
we can link to the first row first column that should be jack and second row first column that
should be olivia now if you did 0 1 that would refer to this 1 in this so that's pretty much
what it is it's just an array so at that point you just have to know how to deal with a raise and as
a result you can return back a single value or again you can get a whole array back it's really
up to you so for example if we wanted to create something that is similar to like index function
only i'm gonna do with zero index i'm not gonna convert it to a regular spreadsheet index just to
give you an example here i'm going to delete this go back and now remember this is an array names
so what i'm gonna do i'm actually gonna accept two more arguments here i'm gonna do r for row and
c for column so now we have three arguments names is the array r is going to be the row number
c is going to be the column number so at that point i'm going to return from that names r and c
meaning by r we want in that row so if we pass 0 we'd like to get something in the first row and 0
for the column would be again in the first column now again this is just comment i'm just doing this
so you can understand that this names is going to be this array but i'm just going to take it off
and i'm going to rename this function i'm just going to call this data index or something so
now i have this function data index which should accept array row number column number
so i'm going to go back here and do equals data index and then we refer to the range comma
then the row number let's say would be zero and column number zero and again these are javascript
indexes so zero zero should refer to first column first row from this array which should be jack
there it is now if i change this to 1 0 that would be the second column
well not second column second row here so that's olivia but still the first column
and if i do 1 1 that should be the second row second column so that should be this two
and then again if i do zero one that should refer to the first row and second column so that should
be this so the main thing this is an array these are individual arguments in here we could
also have those arguments from the spreadsheet instead of just typing zero one so we could go
here and do zero and one and then i could just simply just link to those like this is the zero
this is the one and that returns this one now if i do zero zero that should be jack
if i do one zero should be olivia see right there it updates but that's pretty much it
so that's all you really need to know about functions their returns and their arguments
and after that you just need to learn how you deal with these different arguments
and how do you produce either a single value as a return or a whole different array and that should
do it for this video thanks for watching please subscribe and i'll see the next one