Create Custom Functions in Google Sheets

0:00 All right in this video, this is gonna be fun because we get to create a calculator and we're gonna abstract it. Or obsfucate it behind a Google app script , which is gonna be really cool. One of the concepts that I try to share with you in Better Sheets, if you're trying to sell a sheet is to try to create calculators that we use ourselves, but mainly you might be using calculators yourself only, not even thinking about selling a sheet or giving this away to others. But I think actually this is useful for both cases. One to clean up your sheets from messy sort of calculators, where you have a form and have that information. But also if you ever want to sell your calculators or calculations I think custom functions are a really fun way to do it. I think it's a really neat thing to do because it makes essentially just like

0:46 you have formulas, like sum and average, you can create your own formula that's custom to exactly what you need, but let's do this with our CPM, right? CPM is a very common thing we do in marketing, in a lot of places creators need to figure out their own CPM. If they are running ads, if they're gonna try to create a rate for themselves, they want to know what their own CPM is. CPM. Is cost per mille that's gonna have a cost like $500 and it's gonna have some kind of like opens or page views. And we're gonna have like, let's say 45,000. So our CPM is going to be equals 500 divided by this divided by 1000. We take the cost, you divided by the page views divided by a thousand.

1:32 So this is pretty typical, right? You create a form sort of view. You have three columns, not three columns, three rows or column. Sometimes you'll do something like this cost page views and your CPM. And we'll do this. We have to move this over here, but the reference messes up. So we do equals cost per divided by 45,000 divided by 1000. There we go. So maybe it looks something like this, right? Where you have different columns. This takes up a lot of space when in reality, if we knew, okay, we know this calculation , we could do this in Google app script . Let's do that. Right. And we can put this down to exactly.

2:19 Sort of just one word, right. We can say equals get C PM and we can enter cost, whatever that might be, which would be like B two. And then the page review C2. And wouldn't that be cool? Right. But we don't have a function yet. Let's make one, let's go to extensions app script . Our custom function is gonna be named get CPM. We can actually just say CPM, we can do CPM all caps. Now we need cost views. Let's say we're just gonna do page views. All right. Now we've gotta return the cost divided by views divided by 1000. So now remember we put in the text, we just tried to do. Quickly, and we did get CPM, but in our function it's called CPM.

3:07 So let's go and edit this CPM. It's gonna load. There we go. $11 and 11 cents. So we got the answer without having to show the calculation . . We only have the calculation here in our code. Now a few caveats, right? What might happen is you might forget this calculation . You might forget how does this work? We sometimes want to keep a formula within a cell so we can easily reference it and see if it's correct. Right. But CPM is a fairly common calculation . This is really good to obsfucate to abstract away the math and be able to check it. If you do know to how to get to extensions app script , there's one other thing that we wanna clean up. And I think Google sheets and Google script makes a really

3:54 cool thing here possible. So let's say we have another yeah. Thousand dollars for. 80,000 page views. Now we want, we know there's a function here, but we forget, let's say we forgot the name, CPM the three words, and maybe we didn't create that. But if we go equals C you notice that normal functions , normal formulas show up here, but our CPM does not. But Google script makes a really cool option for us to do over here in custom functions in the. help screen at first, it shows us just how to create these custom functions . But it also, if we scroll down a bit in their help section, it shows us that we can copy this text and it will allow us to auto complete.

4:45 So let's copy this just above this function . Now we just need to edit this text, cuz let's look at exactly what I just put in here. Let's save it, and see what happens. We go equals C P. Now it shows up, but it gives us the wrong information. It says, multiplies the input value by two that's incorrect. What it does is actually right here gets cost per mille. now we want to know, like, how do we use this? Right. how do we, what, what inputs do we need? Because maybe we are not the people to use this. Right. We wanna know cost and views so we can do something like cost views. We have return CPM, parameter cost views. Put the costs and views, and then we save this now let's go back, go equal CP.

5:34 Now we have exactly what we need now we know right here without going to our. Extension right. App script , without going here, we know what we need to do. Okay. Cost and views. Oh, this is perfect. Right? Cost is gonna be B3 C3 for page views. And now I don't have to reference this script. It is now told to be here and told to any other users. But what I really love is the potential of this, right? There are over 400 formulas. If you go equals all of these, these there's over 400 of these, most of them we don't use. . I use Google sheets a lot and I don't use all of them, nearly all of them near, I don't even use a majority of them, but what this allows us to

6:20 do when we write custom functions . Is allows us to get a little more granular and very specific to our own needs. And also our company's needs. Our employees needs, our employers needs. . We can create custom functions here for our boss. This doesn't just have to be. Numbers, right? We can write messages, function . Let's say get message. And we're gonna just say, or get email let's let's actually make the message an email get email, email accounting. we can return. text. We can just say accounting, a CT, maybe it's difficult. A CTT, google.com . What happens when we write that function ?

7:05 Right at this moment, we need to remember this text, right? We go, here we go. What's that email, if we go get okay. I forgot what it is. What is it? Get email accounting. It'll load a little bit. There's our email address. but put this custom function above it. Get email for accounting parameters . We'd have nothing return. We might even just put that there just it's fun. Let's make sure it saves. and now we go equals get email accounting, get email for accounting. Bam. We got an email. This is cool. We can actually save functions here as just text or text as functions here. We don't necessarily have to only do math. This is really cool. I, I find this fascinating fun, and I hope you do too.

7:52 If you liked that app script video, I think you're going to enjoy this app script video about how I'm a pretty much $75,000 from one line of Google script I hope you enjoy. I hope you learned something new.