How to Write a Function - Learn to Code in Google Sheets Part 2

Beginner

About this Tutorial

Continuing to show you how to write a function in Apps Script.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 I am so glad you've made it to part two where we learn how to write a function . I went through it very quickly in the last video I showed you how to write a function , but we're gonna step through it very slowly in this video. 0:11 So again, we're gonna go to our extensions app script and find our function once we're there. Let's write an actual function we might want to use. 0:23 The last one we did was just math. So if we wanna add more functions , we can just go down and go fun. 0:29 Really write the num, the name function , what what we're gonna do.

Common function or math formula that we might want to create is something called cpm. 0:40 This is a bit of a little bit of math, very little bit. But what it is, let's see, we have a cost and we have some kind of a number of impressions or views. 0:54 We use this across any sort of social media. We use it a lot. I've used a lot in the past cpm. 0:59 So we want our CPM here. Now, if we have a cost of like $566 of cross, you know, a million impressions, all we have to do is we can do this in formula , right? 1:13 We can say equals c4, nope, c3, the cost divided by C4 divided by 1000.

So we take the number of impressions divided by a thousand and then take the cost and divide it by that, right? 1:28 And so our cost per is gonna be per thousand is gonna be 57 cents, but we don't want to have to show this math, but we also want to provide sort of a name we want to type in just really equals CPM and get that num and enter these two numbers and get that number . 1:47 So let's do that. So we're gonna go function cpm, we're gonna have cost comma views, let's say not just impressions. 1:56 We want to do curly brackets and then we're gonna do fun.

We're gonna do variable melay is cost per miele. 2:06 Actually we can write that out. Cost per millet. So our miele is gonna be equals count divided by 1000. We can end each line with a semicolon if we want that. 2:18 So something from JavaScript we can do. Variable CPM is equal to the cost divided by this variable up here, Miele, end it with a semi. 2:31 Now the number we wanna return, we always wanna return something, return cpm, hit command S four safe. Now we can use this function equals cost per me life. 2:44 C4, no c3, c4. And we get an error .

So it tells us in this error F reference error count is not defined and tells us where it is. 2:56 Line seven, line seven count. Ah, because we don't have a count, we have views. So we can just rename these views, command S, and we should get rid of that error . 3:08 We can probably just copy and paste this to get to try it again. And there we go. 57, we have completed it. 3:17 Great. So in this we just wrote, we just wrote variables. These are all variables.

Var then the name of the variable and we can name those variables, anything we want, but we just have to remain consistent to know to use them again and variables are editable. 3:36 So Google Script will show you, as I mentioned, the last video and this video, if we don't use Miele, if we used sort of mile, if we mistype that, see this Miele is a little bit sort of grayed out. 3:49 So it'll tell us, hey, we're not using this variable somewhere. So, oh, we got it right here and now it is using it. 3:55 Some errors you might run into.

So if we wrote the function named cost per melay up here and we start typing equals cost per mile, let's say, and we do the same thing, C3 comma c4, we're gonna get an error hashtag name and it says unknown function cost per mile. 4:18 So what you can do is you can go back to your functions and say where ? Well here it is. Oh, I spelled it wrong. 4:25 You mean even com Command C Copy and try to paste it to see if that fixes it.

And that does you already saw reference error , but I'm gonna redo it again. 4:36 So if say this said mile here and we go back, it says error and it says reference error . Melay is not defined Line eight. 4:49 So we go to our function , we go to line eight and we say, Miele, where ? Where is it? Oh, it's not defined here because mile is spell MI is spelled wrong as mile. 4:58 There we go. We've debugged that.

And then also see that you sort of get an error with just typing this name even though it does exist as a function . 5:11 It tells us unknown function until we hit enter costs per might have to redo that because we did fix that error . 5:24 Unknown function , hit enter and it still works. So this function does exist, but in our sheet it doesn't tell us that it exists or it tell, it tells us that it does not exist. 5:38 There is actually an interesting way to fix this. If you want to check it out, go Google for custom function. 5:44 It adds a little bit of text.

I go deeper into this in advanced courses, but this course we wanna remain simple. 5:52 So thank you for watching. Watch.</div>