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.
Add Autocomplete with Custom Function
About this Tutorial
0:09 If you ask me what is my favorite thing to do in Apps Script, it is this. We're going to turn our unknown function into a custom function that acts just like a native formula in Google.
0:20 Google Sheets. It's pretty darn cool and it's so easy. It is unbelievably easy. Okay. In, just before your function, just hit enter once.
0:33 Right here, we're gonna add a comment, but not in the way that we usually do, like, two slashes. Just add slash.
0:40 So, so some slash and then we're gonna add two asterisks. It will auto complete that there is this asterisk and then a slash.
0:49 And inside of this, we can hit enter. Now we have these comments in this sort of comment section. And this is typically, if you wrote, you can write some comments here and it will not affect the this formula at all.
1:04 It's great for writing long form comments if you're trying to give some message to someone, especially if you're sharing sheets and stuff.
1:10 I do this fairly regularly. But here's the thing we're gonna do today. And right now we're gonna do an at sign and then we're gonna write custom.
1:19 No space we're gonna not no space. Function at custom function. And if we just hit save, let's save project here.
1:27 And now we go and use. Remember we had the CPM inside of our sheet here. Right there. We want that CPM.
1:36 We wrote it and it was, it had a red line. Now we do equals CPM and it is. Auto completing right here and we can tab to accept it.
1:45 Isn't that fantastic? Isn't that amazing? So there are some extra information that you can do. The minimum and the bare minimum to use this is the at custom function.
1:56 But Google workspace has a little bit of other functions you can add. You can add a description. We will do right now.
2:02 We will just add a little description. Calculate calculate cost per me like. And I'm going to add CPM in there.
2:11 And now if we go back to our CPM function equals CPM there, Calculate cost per me like CPM. You might want to as well have the.
2:22 So that's something we might want to do. The parameters here and the return. So you might see that in another video.
2:30 Actually, I should rename this units. I don't know. I don't really like this units name. I just realized we can see a whole lot here.
2:38 The user is going to see a whole lot in your custom function. So if you just start typing, it'll be cost and units.
2:44 And I think this is too general. It also says if you see here this object object, which we want to change.
2:49 So I will edit that now to views. And then we need to change this as well to views. We need to add some parameters here too.
2:59 If you see here, we can add extra parameters and the description of them here. So we can add views and cost.
3:10 Save that. And we can see here, we want to give a better user experience if someone hasn't ever used this before.
3:17 Here now our example includes CPM cost views. And it has a description down here. All we did here was add a description cost per mille here.
3:26 We added at per am in double brackets cost then cost and then a hyphen space hyphen space. And we have input the cost, input views, and you can add more description here as well if you want.
3:38 And that'll be down here in this section. As we enter the cost, so if we just enter B2 and then hit the comma, notice that the Google sheets will automatically highlight the argument that you're in and it will also highlight down here the description of it.
3:58 So this says input views, this says input the cost. As you can see, these just correlate here. If we do not have this hyphen, let's see if we just have a space.
4:08 What happens? We will see some difference. Hopefully we'll see. There. Okay, we don't need the space there. We just need cost, cost.
4:20 If we have costs there, let's see. That changes anything. And you see cost here as an example. So whatever we put in the parameter it has in the example, and we have this cost here and cost input the cost.
4:39 Alright, so hopefully this helps you create a custom function really, really well. I think, again, custom function I think it's just one of the most fantastic things in Apps Script because it makes it so much user friendly for people who are not necessarily going into your Apps Script and seeing what
4:56 was that name of that function again. Do I have to remember? No, you just have to start typing and now all of this information is now for.
5:04 Seeing for the user or shown for the user. It's so fantastic. There's much more about it in custom functions in Google Sheets Help, which I might go over a couple in a couple of these sections in more videos.
Introduction to Google Sheets™ Add-on Course
What is an add on? What problems can it solve?
MAKE IT WORK
Write Apps Script to Create a New Google Sheets Formula
Add Autocomplete with Custom Function
How to Name Your Add-on
MAKE IT USABLE
Add-on menu on open
OnInstall to onOpen
Apps Script Manifest
Connect to a Google Cloud Project
OAuth Consent Screen
MAKE IT DISCOVERABLE
Add-on Logo Design
Add-on Banner Design
Add-on Web Site
MAKE IT MARKETPLACE-ABLE
Check Add On Works
OAuth Consent Screen Video
Complete the OAuth Consent Screen
OAuth - Action Needed
OAuth Approval Request Granted
Marketplace SDK Install and App Configuration
Marketplace Store Listing
Submit Store Listing for Approval