Turn Your Apps Scripts into Native Formulas | Apps Script Custom Function Tutorial EASY

Today’s tutorial is going to be fun because we get to create a calculator and we're going to abstract or obfuscate it behind a Google Apps Script, which is going to 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.

I think actually this is useful for both cases. One to clean up your sheets from messy 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’s just like you have formulas, like sum and average, and you can create your own formula. It's a custom that’s exactly what you need.

Let's do this with our CPM.

CPM or Cost Per Mille 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 going to try to create a rate for themselves, they want to know what their own CPM is.

As an example, our CPM is going to have a cost of $500 and it's going to have 45,000 page views. Our CPM is going to be:

=B2/(B3/100)

Gif 01

So this is pretty typical. You create a form sort of view: three rows or two columns.

Sometimes you'll do something like this: cost, page views, and your CPM. We’ll move the data for this, but the reference messes up. So we enter this formula:

=B2/(C2/1000)

Gif 02

This takes up a lot of space:

If we know this calculation, we could do this in Google Apps Script. Let's do that.

In CPM, we can put this down to exactly one word:

=getCPM(B2,C2)

We don’t have the function yet, so let’s go to Extensions  Apps Script.

Our custom function is going to be named CPM.

Now we need cost views.

We've got to return the cost divided by views divided by 1000.

So now remember we put in the text in our sheet, what we just tried to do. We did =getCPM but in our function it's called CPM.

Let's go and edit that CPM.

There we go. We got the answer without having to show the calculation. We only have the calculation here in our code.

Gif 03 – 2:25 – 3:13

A few caveats:

What might happen is you might forget this calculation. We sometimes want to keep a formula within a cell so we can easily reference it and see if it's correct.

CPM is a fairly common calculation. This is really good to abstract away the math and be able to check it. If you do know to how to get to Extensions --> Apps Script, there's one other thing that we want to clean up.

I think Google Sheets and Google Script makes a really cool thing here possible. Let’s say we have another set of data:

• Cost – 1,000
• Pageviews – 80,000

Now we know there's a function here, but, let's say we forgot the name (“CPM”). maybe we didn't create that. If we enter =C in the cell (D3), you notice that normal functions, normal formulas show up here, but our CPM does not.

Gif 04 – 3:56 – 4:27

Google Script makes a really cool option for us to do in custom function.

Custom Functions in Google Sheets | Apps Script | Google Developers

Google Developers

At first, it shows us just how to create these custom functions. If we scroll down to their help section, it shows that we can copy this text and it will allow us to auto complete. Let's copy this just above this function.

Now we just need to edit this text. Let's save it and see what happens. It shows up, but it gives us the wrong information: Multiplies the input value by 2.

Let’s change that line of code to “get Cost Per Mille” and now we want to know how to use this and what inputs we need. We want to know cost and views, so we can do something like this:

With that edited code, we have exactly what we need without going to Apps Scripts.

Gif 05 – 5:04 – 5:36

This is perfect. Cost is going to be B3 and then C3 for page views. Now I don't have to reference the script. It is now told to be here and told to any other users.

Gif 06 - 5:48 – 5:55

What I really love is the potential of this. There are over 400 formulas. I use Google Sheets a lot and I don't use all of them. I don't even use a majority of them.

What this allows us to do when we write custom functions is to get a little more granular and very specific to our own needs. 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. We can make the message 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.

I find this fascinating fun, and I hope you do too. If you liked that app script video, I think you're going to enjoy this app script video about how I made pretty much $75,000 from one line of Google script.

I hope you enjoyed this tutorial. I hope you learned something new.

Watch the video for this tutorial:

Learn more about Apps Script:

Get more from Better Sheets

I hope you enjoyed this tutorial! If you want to do more with your Google Sheets, I have other tutorials, like how to create a timer with Apps Script and learning to code with Google Sheets. Beginner? Intermediate? There’s a lot of tutorials for everybody! Check them out at Bettersheets.co.

Join other members. Pay $19 a month and get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get access to 200+ videos, mini—courses, and Twitter templates. Find out more here.

Don’t make any sheets. Make Better Sheets.