Hey there stranger!

Sign up to get access.

Write Apps Script to Create a New Google Sheets Formula

About this Tutorial

Create a brand new custom formula in Apps Script. We'll do this for our Add-on we'll be publishing to the Google Workspace Marketplace.

Video Transcript

0:00 Alright, before we write some Apps Script, that is our actual function in the add-on that we're gonna create. We're gonna name it, I'm gonna call it CPM custom function.
0:10 This is the thing we're gonna create, CPM, cost per mille. I don't need to do anything in the sheet itself.
0:16 All I have to do is go to extensions, Apps Script, and open up. Apps Script, and I can start. It already has, if you've never worked in Apps Script before, it has function, my function, my function with a couple parentheses, and a couple curly brackets.
0:31 We're gonna rename this function to CPM. And then we're gonna need let me explain CPM. CPM is cost per mille.
0:40 It's a term used in advertising or, I mean, yeah, advertising and marketing. To take a cost, the amount of cost it takes to buy an ad, and the amount of views it gets, we're gonna divide that by a thousand, and say, what is the cost per thousand views or units.
1:01 Mille is one thousand in French. So what we need is two inputs. We need cost, and then, comma, we're gonna add cost per of views.
1:11 We'll just save views, or units we can actually say here. It does not really matter, except to your user. Your user's gonna see, your very we want to say variable.
1:25 Actually, maybe that's const CPM equals, and it's usually, we'll take the units, divide by one thousand, and then we'll take the cost, and we'll divide it by that.
1:38 That is the entire. CPM function, and we need to return something in this return, in this add-on, turn CPM. That is the entire thing.
1:52 We can actually test it now that it is a function that is written. We have saved it. I hit save up here, or command S, we can call this CPM.
2:01 Pm. Custom. Function. If we want. We can use it. We can see. Okay, here's our cost. Here's our views. And we can say our cost is 500, and our views are 500,000.
2:17 So if we did the math here cost 500 divided by. The views divided by 1000 would be one. That is what we should get.
2:27 All of that math should be CPM. D4, comma. Yeah, D4, D5. Let's see if this comes out with. One. Perfect.
2:43 So our CPM function is absolutely working correctly. If we had some text, I think it will get some error. Yeah, in our actual function here that we did.
2:54 Oh, be very safe. D4. We had a value error and we have a number error result was not a number.
3:01 So we are still getting. Error even if we did the math inside of our cell. So that's it. That's our function.
3:09 CPM function we have created here. We'll be adding some more functionality to it later in the course. We'll be adding auto complete in the next video and we'll talk about naming a little bit.
3:19 That's sort of important at this particular moment. Because the name that you call either custom function or the name of the add on will now propagate over.
3:30 You'll see we'll use it many, many times. We'll have to put in the marketplace. We'll have to put it here.
3:35 I thought this was the one thing. We'll get to it. I thought this was the one thing that I think is important now.
3:41 Before we get too far. But if you want to do more coding, again, this vid, this course is not about the coding.
3:47 If you want to do more coding, I have a couple of videos in this about other additional concepts of coding.
3:53 And also spreadsheet automation 101 is a video is a course that literally teaches you how to code in Google sheets.
4:00 And then I also have a very quick course called learn. Under coding Google sheets, which is a crash course, 40 minutes and you're done.
4:07 But this one, now we have a custom function. But we want to be able to use this a little bit better.
4:13 So you'll see that in the next video, how the CPM right now is an unknown function. And we don't want that.
4:18 We want it to be a known function. We want it to work as an auto complete. As a native function, just like any others, just like cost sign and just like if all have all this auto complete, we'll do that in the next video.