Make your Custom Functions Like Native Functions

<div>00:00 Hey Better Sheets members. Uh, thanks for watching this video.

This is going to be a little bit of, um, for the advanced folks here who are writing scripts and potentially who are writing scripts and creating spreadsheets for sale. 00:15 Um, if you're selling to someone, the user, or even using this with clients, creating a custom function , happens a lot custom functions or anything where you use a script, um, and you might call it something. 00:28 You go to say function , um, triple, and you say in this triple, we're going to use a number , some kind of input. 00:41 Um, what are we going to do with this info?

Well, we're going to just return, um, the input. What are we get times three. 00:51 Um, and let's see what happens, right? Well, let's do triple here. Now I'm getting this from the docs with you. 00:59 The docs use double, um, as an example. So let's go here and we write in triple, oh, let's just do triple five, right? 01:09 I probably now have to say this and now this works and it works. Right. So it's going to load that data . 01:17 It's going to take that five and can triple it or multiply it by three eventually. Right?

The first time it happens, it just loads for a long time, because it's very new. 01:28 And so for tripling five or five times three, it's 15, right? And now we have triple and we put any number in and I'll calculate it. 01:40 Right. So what's interesting is we can write these custom functions and then tell users that these exist. Right. Um, and then they'll do something like this, right? 01:52 They'll start typing and nothing will happen. Well, we can actually add auto, complete this.

This could actually give users the feeling that they're using. 02:01 Some native functionary and every native function has this auto-complete sum. And then it also tells us some interesting information about that, um, or useful information about that formula or that, uh, function . 02:14 So let's see how, how to do this. And we're going to go right to the docs. It actually says it right here in the, in the Google app dots, I'm going to use their function double just to show you how this works. 02:27 And then we'll talk about some other things. I think you can use this for.

So we'll add that double. Um, we will save and we will write double and show you that now that works Double That number and we get these big numbers, right. 02:48 But we didn't get the auto complete. Well, how do we add the auto-complete?

We just use the comment function , and we're going to do add custom function and we do this right double. 03:01 And it shows you right here at custom function in this, in this, um, comment , uh, which usually I use these like slash slash write a bunch of comments , maybe two, you can also do something like this. 03:16 And this is a comment that you can add some information to a script in case someone needs to come and edit something. 03:21 So now let's look at writing double and it's right there.

Double what's really cool to two is right at this moment when someone's like, oh, I need to like, w what, how do I use this? 03:33 Right. We might have a very complicated function here. We might have something that involves two, three or four options. Um, we might have something where we definitely need to have quotes, right. 03:45 Or it needs to be some kind of number . Right. Um, we can add that here in the, um, Mo in the example, we have all this information filled out. 03:59 I will go through this piece by piece.

So now let's see what happens when you start typing double right away. 04:07 We get the word doubles. We know this works, right. If I was a user, I bought a sheet and it told me I have some custom scripts to work with some custom functions built only for this script, for this sheet. 04:18 And right away, it shows me like the, the thing that I'm trying to figure out, right. And how to use this. 04:26 Like, this is really good user experience, right? Multiplies the input value by two. Well, what is the input value? The input is the number I put here. 04:35 Oh, it's a number .

It could only be a number , right. If I do this, what does happen? See, it's like, oh, result was on number that's bad user experience. 04:45 I want a good user agreements. I need to put in a number . Right. So that's really cool that we can have all of this information. 04:52 Well, how do we get all of this information? This is, it says number here. It says multiplies the input value by to devalue, to multiply. 05:01 And it is all here in the, um, comments . So the very first line is multiplies the input value by two. 05:10 Okay. Well, where did that show up?

That showed up in this, um, what I was called the about section, and it's also this like preview, right? 05:19 Multiplies the input value by two. So right here, This is a preview, right. It's also called about, and let's see what happens if C in it cuts it off. 05:39 Right. So we don't have all that other information, but I just have them. That's where it goes. Now this a program, what that means is parameter . 05:48 And then in these curly brackets, we're going to write number well, what, where did we see that?

We saw that right here. 05:57 So input, and this is number and we can write anything we want. We don't have to write number . We can write any texts. 06:03 We can say a number only Save that, go back and See. Now it says a number only, literally any texts we put in here. 06:17 Let's see. Literally any text We put in here, let's see what happens to that. There you go. Literally, any text you put in here goes in here, let's see. 06:32 And obviously we want to have just Like one or two numbers.

And even if gates are really Long string so long, Let's see what happens there. 06:45 I also put an intro and now It, some that was a little too emergency in this about it has this. 06:54 This is a preview. Cool. So anything We put here? Oh, about, and then let me just take off that, uh, move new line pattern. 07:07 Enter in there, See there, because literally it needs to be put in here. And even if it's a really long string , so long right there. 07:13 So that is a program.

So the very first thing we put in here, it's going to be about, then we can do app program and in the curly brackets, we'll reference , whatever is in here. 07:28 So if maybe it's input yeah. And A number we can say right here and put number , number , we can also maybe put first number second number and turn. 07:51 I don't think that'll work. Maybe it is a times number there. Double. They will call us and multiply. Cool.

So now we go back here, let me start writing double. 08:11 And there it is double and multiply, multiply the input value by two, but right there, when we start typing, Whoops, double and That's. 08:26 Okay. Right, right here, input number first number second number . Great. So we do 34 and that, and let's see what happens when I have to try again. 08:39 There you go. That's the result. Then we also have app return, which tells us what does this return? The input multiplied by two, the input multiplied by The name number by number , and then multiply by two. 09:00 Okay.

Let's see where that shows up. So right here. Nope. It's not there. Uh, Sarah we'll do this. Nope. Did I say never? 09:31 Well, times number then. Okay. And let's go back and look double multiply that. Uh, oh, I think it's because it's in this second line. 09:54 All right.

So the return isn't showing up, but one last thing I wanna mention is that normal comment is going to look like, oh, sorry, normal multiline comment is going to look something like this, where you have a slash then An ethics than asterix and slash. 10:11 And You can write anything in here has no effect on the, um, has no effect on the Functions . You're writing hand. 10:20 This however is two stars and it's not three. So if we use three, let's see what happens. We have nothing, right? 10:31 So the three stars doesn't work.

It is only two star and only two stars, not one, not three, two stars or Two asterixis and then ends with an asterisk down here. 10:43 And if you want any more information in here is, uh, some J JS doc.

And this goes through, if you prefer reading this information, doc, this is a really fun, uh, thing to find out that I recently found out that you can add this auto complete really makes a sheet feel really good. 11:02 And again, the use case that I think is really useful for this is if you're adding a custom menu and a custom function to sheets that you're selling, these can really put it over the top, uh, to have someone feel like they really, this was made for them. 11:20 And the other, uh, I think really good use case is notes to yourself.

So if you find yourself writing a function and you're writing like some complicated C complicated function , right, and you have a few numbers here, number , number two, number also, number a range . 11:48 And then you have your pump Function and you find yourself, you know, commenting, This is a number and you're going to do number should be, and you can find yourself commenting things like this, right. 12:05 It might be a good opportunity to think, oh, I should add this functionality, right. Do star right here.

Start this jazz doc. 12:13 You like, oh, um, this complicated function , this results in a complicated number . Right. And now when I, right now, again, I'm just using this pump, you know, we have to add, we forgot to actually add it at Custom function , add that. 12:43 And now we do at complicated or At a really small error at a need, of course not custom Com. It is a custom object. 13:00 So now complicated. Oh no, not again. Okay. I forgot to hit command S save. And now we do equals complicated function and this results in a complicated number . 13:18 So actually it's not the result.

This gets complicated numbers And puts the simple number . Right. And we can just do result. 13:32 Turns one, No matter what we put in there, it's just going to return one. Oh, it's not even gonna let us do that. 13:49 Okay. We're just going to return one, not return to one and no matter what we put in here, A Let's see if it gives us no errors, it just gives us one. 14:01 Um, this might be a really good use case.

If you are writing some complicated things, you want some specific numbers and you don't necessarily want to do say data validation . 14:12 You're, you're like, you know what whoever's using, this is going to be me. And I'm going to remember this, right. 14:17 Cause I'm writing number here, but I'm going to remember it by adding some, Um, comments here, common here, and then Common here. 14:29 Uh, and you're gonna be reading this. This takes a lot of time. It takes a lot of time.

And then also makes these functions very hard to use later on when you're like six months down the road, you're like, what did, I mean here? 14:42 And you have to go into the script and you have to find it. This will bring that information Right here. 14:49 So we can do something like parameters number , number range . And we can remember that right here, right there. Number , number range . 15:02 We probably should have a spate of enter there. Let's see if That changes things. There you go. Number, number also range. 15:13 All right.

So found one more interesting wrinkle here is that you can name anything you want here. These like inputs that you have, you just have to use parameters . 15:27 Then the curly brackets and the name, the exact texts that you have here. So here's number one, here's number two. 15:34 And now you can add as many parameters you want. You can Write, um, Range here. And now we do at PARiM. 15:42 We do what our texts, we want it to display, uh, any range .

And then we just say range , and we say, the range you need. 15:55 Now this will show up in complicated function , that there's number , number , any range . And then down here, it says, number one, right here, number one, number two. 16:06 And it describes it in more detail here.

So this is really, really powerful again, because you can, uh, create very complicated functions instead of leaving comments or in addition to leaving comments and, and describing how this works. 16:25 You can let a user know when that user might be you in six months, um, or that might be a user you're selling a sheet to. 16:32 So this is a really useful thing.

If you're selling sheets or even selling, not selling sheets, selling your time as a consultant, or you're working in an office and you're like, you know what, there's this really complicated math problem that we all do. 16:45 Um, I want to make it a custom function , but I want others to know how it works. Right.

Um, and describe very clearly what they have to put in and then what they get out of it right here in this script and right here in the text of the script, so that it shows up to someone as if it were a native function , I think is really, really powerful. 17:07 And it could really be really cool if you end up using this and you can share me a screenshot of it, let me know. 17:15 I'd be really excited to see how you use it.

Um, if you're using it for yourself to keep yourself notes , or if you're using it to sell to clients, if you're sharing it in an office and you're like, oh my God, yes, people kept asking me about this. 17:29 One thing that I wrote, this one function that I wrote. Um, I think this makes sheets really, really a lot better. 17:36 Thanks so much for watching this whole video. Hopefully this was really helpful to you.

Again, you can find this in the custom function docs, but I had never known about this before recently. 17:50 And didn't know about this JS doc custom function tag that does this. So I think it was really, really cool and something that I can use a lot.</div>

Make your Custom Functions Like Native Functions | Custom Function Autocomplete