This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.
Hey there stranger!
Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.
Create a CPM Custom Function (Create Better Calculators!)
About this Tutorial
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. email@example.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.
Sheet Stories / Video Notes + ADDED: Email Notifications
ChatGPT Clone in Google Sheets Part 2
Why Different Cell References in AI Integration in Sheets?
Show Sheet Tabs Based on Edit
Add Title Case to Google Sheets
How to Power Testimonials with Google Forms and Sheets
Getting Started Coding in Apps Script
Seek Errors When Coding Apps Script
Think Like a Programmer: Develop The Mindset of an Apps Script Coder
ChatGPT Clone in Google Sheets Part 1
Embed a Number in a Website from a Google Sheet
Create Navigation Like A Book or Presentation
Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet
Hold a Giveaway Raffle in a Google Sheet
Embed a Headline in a Website from Google Sheets
Capture Emails from Website Form to a Google Sheet (Without Zapier)
Quickstart Tutorial OpenAI API in Google Sheets
Create a new Spreadsheet from just a Name in a Sheet.
Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?
Email Yourself a Cell from a Google Sheet, Every Day
OpenSea Data Inside Sheets
Create an Email Campaign Stats Calculator
Twitter App Clone in a Google Sheet
Dylan Asks: How to Automatically Delete Rows If Cell Contains Value
Highlight Row as You Move Your Cell Selection
Create a Timer with Apps Script
LinkTree in a Google Sheet
Password Protecting Data In a Google Sheet
Automatic Weekly Backup of Google Sheets
Create a CPM Custom Function (Create Better Calculators!)
Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script
What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users
Sync Two Tabs Without ImportRange()
Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.
Password Protecting Data In a Google Sheet Part 2 The Basics
Benoit Asks: How to Convert Case
Learn to Code in Google Sheets, For Programmers
Add a Checkbox to Turn on Dark Mode
Write Your First Script
Find Keywords in Any Column. Create quick search dropdown to find keywords
Basic CRM - Add a Powerful Script To Move Row Based on Status
How To Improve: 1,000 Business ideas: Business Idea Generator
Let's Make a Bookmarklet!
Troubleshooting Bitly in a Google Sheet Script
Unique Features - Design a Better Dashboard Part 2
How To Set Up Stripe Webhook to Google Sheets with Google Script
How to Edit a Macro
Sheet Stories / Video Notes + Clear 24 Hour Old Videos
Add A Timestamp to Task Lists (without Now Formula)
Make your Custom Functions Like Native Functions | Custom Function Autocomplete
Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop
New Syntax for WhatChanged Formula in Google Script
How to Record Macros
2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)
Deep Inside Dark Habits Google Script
How to Trigger Macros Daily
5 Ways to Create Coupon Codes | Create UUIDs
Create a Radio Button From Checkboxes Using Google Apps Script