00:00 All right in this video, this is gonna be fun because we get to create a calculator and we're going to abstract it, or OBS, vacate it behind a Google apps script, which is going to be really cool.
00:11 Uh, one of the concepts that I tried to share with you in better sheets, if you're trying to sell a sheet is, uh, 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.
00:29 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 then have that information.
00:39 But also if you ever want to sell your calculators or calculations, uh, I think custom functions are a really fun way to do it.
00:47 Uh, I think it was a really neat thing to do because it makes essentially just like you have formulas, like some, and like average, you can create your own formula.
00:58 That's customed 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 do need to figure out their own CPM.
01:12 Uh, if they are running ads, if they're going to try to create a CA uh, if they're going to try to create a rate for themselves, they want to know what their own CPM is.
01:22 So CPM is cost per milli. That's going to have a cost like $500, and it's going to have some kind of like opens or page views.
01:35 And we're going to have, like, let's say 45,000. So our CPM is going to be, how are we going to figure this out?
01:42 We're going to do equals 500 divided by this cop, the thousand, this divided by 1000. Now we have $11. So if we take this $11 and we go equals 11 times 45,045, pretty close, right?
02:04 This is probably means a couple. There we go. $11 and 11 cents pretty darn close, but we don't need this number.
02:15 Right? We have this number is cost, but we just want to double check that, right? That this calculation is correct.
02:20 We take the cost divided by the page. We use divided by a thousand. So this is pretty typical, right? You create a form sort of view.
02:31 You have three columns, not three columns, three rows or columns. Sometimes you'll do something like this cost page views and your CPM.
02:46 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 one.
03:00 There we go. So maybe it looks something like this, right? Where you have different columns. This takes up a lot of space.
03:07 When in reality, if we knew, okay, we know this calculation, we could do this in Google app script. Let's do that.
03:17 Right? And we can put this down to exactly. Sort of just one word, right? We can say equals get C PM, and we can enter a cost, whatever that might be, which would be like B two comma, and then the, uh, page views C two.
03:35 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.
03:48 We're opening our IDE our custom function is going to be named, get CPM. We can actually just say CPM. Nope.
03:56 Okay. CPM. Yeah, we can, can do CPM all caps. Now we need a cost comma views. Let's say we're just going to do page views.
04:08 All right. Now we got a return. The cost divided by views divided by 1000. So now remember we put in the text, we just tried to do this quickly and we did get CPM, but in our function, it's called CPM.
04:31 So let's go and edit this CPM. If you got to load, there we go. $11 and 11 cents. So we got the answer without having to show the calculation, right?
04:44 We only have the calculation here in our, uh, code. Now, a few caveats, right? What might happen is you might forget this calculation.
04:53 You might forget how does this work? We sometimes want to keep a formula within a cell so we can easily reference that and see if it's correct.
05:03 Right? But CPM is a fairly common calculation. Uh, if you don't, if you get it right the first time you're going to get it right each time.
05:12 So this is really good to have suffocate, to abstract away the math, uh, and be able to check it. If you do know how to get to extensions apps, there's one other thing that we want to clean up.
05:24 And I think Google sheets and Google script makes a really cool thing here possible. So let's say we have Another, uh, yeah, that was a dollars for 80,000 page views.
05:41 Then we want, we know there's a function here, but we forget. Let's say we forgot the name, CPM the three words.
05:46 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.
06:00 But Google script makes a really cool option for us to do over here and custom functions in the help screen.
06:08 At first, it shows us just how to create these custom functions. But it also, if we scroll down a bit of their help section, it shows us that him copy this text and it will allow us to auto complete.
06:22 So let's copy this just above this function. Now we just need to edit this text because let's look at exactly what I just put in here.
06:31 Let's save it and see what happens. We go equals C P. Now it shows up, but it gives us the wrong information.
06:39 It says, multiplies, the input value by two that's incorrect. What it does is actually right here gets costs per VLA.
06:53 And now we want to know like, how do we use this, right? How do we, what, what inputs do we need?
06:59 Because maybe we are not the people to use this. Right? We want to know cost and views. So we can do something like cost view.
07:12 We have return CPM, perimeter, cost views, put the costs and views. And then we save this. Now let's go back, go equal CP.
07:37 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.
07:48 Okay. Cost views. Oh, this is perfect. Right? Cost is going to be B3, karma C3 for page views. And now I don't have to reference this script.
08:02 It is now told to be here and told to any other users. But what I really love is the potential of this, right?
08:10 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, right.
08:22 I use Google sheets a lot, and I don't use all of them, nearly all of the meal. I don't even use a majority of them, but what this allows us to do when we write custom functions is allows us to get a little more granular and very specific to our own needs.
08:39 And also our company's needs, our employee's needs, our employer's needs. Right. We can create custom functions here for our boss.
08:46 Uh, let's say they love to, you know, write some extra texts or add something. This doesn't just have to be, uh, numbers.
08:55 Right? We can frame messages, function. Um, let's say get message. And we're going to just say, or get email let's let's actually make the message and email, get email, email accounting.
09:14 Um, we can return text and we can just say accounting, AC, maybe it's difficult. actt@google.com right now. What happens when we write that function right now, this moment, we need to remember this text, right?
09:33 We go, here we go. What's that email, if we can go get okay. I forgot what it is. What is it?
09:40 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.
09:58 We'd have nothing return. We might even just put that there just it's fine. Let's make sure it saves. And now we go equals get email accounting, get email for accounting.
10:20 Bam. We got an email. This is cool. We can actually save functions here as just text or text as functions here.
10:29 We don't necessarily have to only do math. This is really cool. I F I find this fascinating and fun, and I hope you do too.