00:00 A year ago I created Google Sheets for Coders. This is a quick guide, 15 minutes to coding. If you already know how to code, if you're a programmer somewhere else, I share with you a few things to know about programming inside of Google Sheets.
00:13 What you're watching now is more advanced tips and tricks. Uh I also uh put out the code here. If you're looking for way more advanced stuff, I have a course called Master Spreadsheet Automation.
00:30 It's over 20 hours of really deep, deep, deep coding in Google Sheets and goes through so many examples of using APIs, using AI, overcoming errors.
00:40 All sorts of things. I even share with you two live consulting sessions at the end of that course. You can also find app script at bettersheets.co slash snippets.
00:52 Speaking of BetterSheets, if you are watching this on BetterSheets now, great. If you are not a member of BetterSheets yet and watching this somewhere else, great.
01:00 Else, BetterSheets has now I think 548, so probably 549 tutorials there for you. 34 courses. You can learn to code, automate sheets, add AI, design better dashboards, and even learn how to publish add-ons, which I will talk about later in this video.
01:18 So, couple things I want to go over is the customization. So, I'm menu, creating native-like functions, which is like my favorite thing to do in App Script, getting the URL and sheet IDs via App Script, which is very, very important if you're automating sheets and then giving those sheets to other people,
01:21 you may need to access the URL and you don't have access to that URL uhm in various ways. So, get access to that vari- that URL. I'm gonna just show you a little deeper thing, and then I'm gonna talk to you a few- about a few things about developing Google Sheet add-ons once we get through all of those
01:48 and why it's important to develop add-ons. If you're a coder and you already know how to code, coding in Google Sheets provides you a user base of 2 billion users, oh my god.
02:00 It's so many people use Google Sheets. You can help so many people, I just created my seventh Google Sheet add-on, add-ons, get add-ons, you can see it, it's called Better Formulas, you can get it if you want, and it is just two custom formulas I made, and I published.
02:17 It's called Better Formulas. Let's see if it shows up here. There it is. It's better. Better Formulas. I think 17 people got it.
02:22 It's brand new. Umm I can install it right away and show you how it works. So what I did is actually I improved on two native functions, one called uh is email.
02:33 Uh it didn't include a lot of the domain TLDs that exist so I added more. And then also- There seemed to be an error in proper.
02:44 The actual native function in proper. So I re-did it called title case. And so now you can see it's here.
02:50 Better Formulas. Simple to install. If you create uh scripts or coding online or for users and you're looking for a much bigger u- user base, 2 billion users is pretty big.
03:02 So come on over to Google Sheets and start programming. Google Sheets is really awesome. But again in this video I'm gonna show you a few advanced tricks that I think are really cool and will make your coding life in Google Sheets so much better.
03:13 Especially one if you use code for yourself. Uh and two if you create code and others need to use it these are super important.
03:20 Good morning. Okay so first off let's go to extensions add uh extensions app script actually and we're gonna create a custom menu.
03:28 The custom menu is gonna be able to be used almost any function we create and it's gonna show up right here next to help.
03:35 So I never remember the code but I have it at better sheets of code slash snippet. bit. It's one of the top ones right here custom menu to function.
03:45 We can copy it from there or we can copy it to clipboard from here as well. Let's just copy all of that.
03:50 So we have the function on open. One thing to know is this is a simple trigger. It's called function on open.
03:57 It has to be spelled exactly like this with the same capital as. The other ones are function on edit as well.
04:04 And there's some usually some event where e there. And we have the function run like that. Uhm. You can only have one of these.
04:11 So if you have multiple files and you find that your on edits are not working appropriately make sure you only have one function on edit.
04:17 And in our case we have on open here. Which is. it. Is going to create two items in a menu.
04:24 And add that to our UI. So these menu items won't do anything but we can actually go function. Let's let's create them.
04:32 Let's let's do something fun like a toast menu toast message. So. So, So we're going to do spreadsheet app, get active, whatever active sheet we're on and toast and we're going to say hi.
04:47 And the second thing, because the first thing we're going to say hi. And the second item we're going to say bye.
04:57 So we're going to do function. Menu. Item. Same as before. And now we are going to say bye instead of hi.
05:06 There. So we have to say this and anything with OnOpen will be triggered literally when it's open. So we're going to close this and refresh the sheet, advanced coding and Google Sheets for programmers.
05:19 And right here next to the help. It'll pop up a custom menu and if we want to say hi. We do have to authorize this probably the first time it runs.
05:27 But now that we authorized it let's go say hi again and over in the bottom we need to authorize again.
05:33 Okay let's try that again custom menu. Say hi. Over in the bottom right corner it says hi. It says finish script as well.
05:42 Let's say bye. And there it says bye. So we have this custom menu. This custom menu will make it hella easier if you are creating app scripts and need someone to run it from inside this sheet.
05:55 They do not have to go to extensions, app script, and actually run any functions. You can make it. Clickable right here.
06:02 So what are native like functions? Well, the native functions are all like this, filter, and they have some information here.
06:11 So let's do some math. Uh, let's do math. And we have let's say two items, item one, and item. Two, and we're going to combine those in interesting ways.
06:25 Let's say we want a return of item one plus item two. So we just want to add these two things together.
06:33 Well, if we create this in the App Script, this let's do math, and we try to run it, it will run, right?
06:39 We will go- what equals let's do math, and we will do 55 comma 55. Let's see, what happens? We get an answer, but did you see this?
06:51 We get this unknown function, let's do math. We don't want that, especially when we're creating functions for people to use that they might not know No.
07:00 That that's an error they can avoid, or rather ignore, uh we want to avoid that function. So it's really simple, we're gonna add a common here with some uh asterisks, and all we're gonna do is add custom function.
07:14 That's all we need to do, save that, and now if I go to equals let a let's do. Now let's try that again.
07:23 Equals, let's go to equals, let's do map. It is right there, just along with land, let, left, everything else, it is alongside those.
07:33 If we want to make it really apparent that it is, or make it seem like a custom function we can do, let's do.
07:40 We can in all caps, if we want. Let's save that. Let's go delete that. Equals, let's do math. There it is.
07:48 We can do the tab. Item 1, item 2 will show up here. It will give us the options to add more information if we want.
07:56 But I love this custom function. It allows us to create. Simple functions that maybe do some rating, ranking, some arithmetic, some kind of custom proprietary math that you know or that you're programming for a boss or a client.
08:13 I did this all the time to do CPM. I even made that a Google Sheet add-on to add the CPM function into Google .
08:20 . So people can download and use it right away instead of having to copy the App Script. And that's why I think it's super cool to create and make it seem like it's a native function.
08:43 So let's create one that we might actually want to do, which is equals get uh URL. So we want to get the URL of the sheet what they were on. How do we do that? Well first off we're gonna use this custom function again. We are going to write a function. Walk us get sheet URL. We don't need any thing there.
09:03 It is just gonna give us a sheet URL. We need to write function correctly function. We are going to return URL. What is the URL variable? URL will be equals spreadsheetapp.getapp. active spreadsheet get URL.
09:25 That is all we need to do. And let's do this get sheet URL. Get sheet URL and parentheses and see what happens.
09:36 There it is. That's URL of the spreadsheet that we're on right now. Pretty simple, right? But it doesn't exist. Maybe we want just the ID.
09:44 Let's copy this whole function and say get sheet ID. The ID is the GID up here of each sheet or rather tab.
09:55 We'll have a different GID. This one is 11941120. So everything gets a different one. So let's do get active sheet get sheet ID.
10:15 So we can actually change this to ID instead of URL to make that more clear. That variable. Let's save it.
10:22 And now do equals get sheet ID. Again just end parentheses. Hit enter. And there we go. We got the matching GID.
10:30 So this is really helpful. Especially if you're trying to programmatically get these pieces of information to someone who's using your app script or using a sheet.
10:39 Maybe you built a template. And have some app scripts built in there. This is really cool. So few things to know about developing Google sheet add-ons is that is pretty simple.
10:50 You do need to change this on open. Uh on open will. This one in particular is going to be UI get UI create menu and then.
11:00 That will create literally this menu that's to the right. But when you're developing a Google sheet add-on. One thing to know is you need to create an add-on menu which is at extensions.
11:09 And it's these right here. So let's change this project name to new add-on test. And instead of create menu. What going to do is create add-on menu.
11:24 We're going to save this. And again, this will only happen, this function will only happen when we're open the sheet.
11:31 So let's re-refuse-refuse-refresh the sheet. And you will not see the custom menu over here on the right. But if we go to extend- It's not there.
11:45 Let's look. Ah, this is something that's very interesting. We got an error that said the string doesn't match the method signature.
11:52 I think I did this wrong. Uh, what we need to do is create an add-on menu and we do not need a string here because the add-on add-on menu should be this here, the name of this project.
12:04 Let's rename it and let's try that again. We can go here, refresh. And now that it's refreshed we go to extensions and here it is new add-on test and exactly the same things that we had before.
12:19 The menu. Let's say say hi. They need authorization again. So once authorized, let's see if this works now. New add-on test, say hi.
12:31 And there we go. It works exactly the same except it isn't add-on. So really cool for advanced coding. If you're coding and you're trying to get your code one in front of other.
12:40 People, it's super simple to get it into an add-on and then publish that add-on. I have an entire course on that at better sheets, better sheets at co-slash courses.
12:50 It is publish a google sheet add-on. And it goes through literally all of the steps to publish your google sheet add-on.
12:57 If you're interested in doing that, if you don't know yet how to- code then go take master's spreadsheet automation. If you're already a better sheets member then go take spreadsheet automation 101 first and you can also then dive into AI or APIs at your own uh risk or at your own will.
13:13 But I thought these were a few things that coders who are coming to google sheets- google sheets and trying to code may not know and- may not know exist is this custom menu creating native like functions with that custom function.
13:25 Getting the sheet URL is just a test and then creating that add-on. That add-on menu is probably one of the most uh needed things when you're creating an add-on.
13:36 It's so weird that you have to change your menu to this but you do. Ah, if you want to publish a go sheet add-on.
13:42 So if you have access to the sheet as a better sheets member, go grab it and it's all in this app script here.
13:49 If you're not a better sheets member yet, consider becoming one. Bettersheets.co become a better sheets member today.