00:01 Hello, welcome to this video. I'm gonna talk about two things that I think you should know when you're starting to learn Google Sheets.
00:07 And this is the answer to basically a question that I just got, which was like, what are the two main things you would teach someone who's just learning to Google Sheets?
00:14 Now, I've been using Google Sheets for well over 10 years and teach Google Sheets at BetterSheets.com. Co, but there's two things that I think are incredibly unintuitive that you cannot learn, sort of yourself, or if you do learn them yourself, you are incredibly special.
00:31 And so I wanted to share these two things that are very unintuitive. But before that, I want to share with you what sort of might be the common answer.
00:40 One might say, oh, you should learn some formulas, or you should learn Apps Script in general, like how to code in Google Sheets.
00:47 But like beginners and people who are learning might also be taught to learn all of the menu functions or the interface, learn how to do things that you can sort of discover on your own.
01:01 Like a couple of very intuitive things that are relatively simple in spreadsheets and you will learn within moments or days of ever opening a Google Sheet for the first time, is you can type in a Google Sheet, a cell.
01:18 And you can type hello and then, You can type in some text. This is pretty normal and I don't think beginners necessarily need to learn this, especially if you've used other spreadsheet software or used any software online and you discovered somehow, you know, well, Sheet.new is actually a really
01:36 cool thing that you might not know, but just do Sheet.new and it'll start up a Google Sheet in your, in your Google Drive, whatever account you have logged in at the moment.
01:48 That's pretty cool and unintuitive. You might not know that. I thought that would be a little bonus here for you.
01:52 But there are really two main things that I think are incredibly unintuitive. You cannot learn them by just seeing the menu items and I think other people might answer.
02:01 Like, hey, you should probably learn the interface, but you're probably going to figure that out pretty soon. And you're probably going to figure out formulas that you can just hit the equal sign here and start typing some formula.
02:13 You can do like some. You can do there's so many like replace substitute so many subs. Institute average, you'll probably run into min if you have a some list of items like this.
02:32 And you're like, which is the minimum item min? This is relatively simple and intuitive to find out. And you'll see this all over the place.
02:42 What you might not see is that you can combine formulas. So if you go to betasheets.co and you just do slash formulas, you will get a list of every single formula that exists.
02:55 500, over 500 formulas that exist. And you can find out about each and every formula, but even included in this text, even where I have a database of each and every formula, you might not know that you can combine formulas.
03:11 You have to see someone do this and it looks like magic. And I think this is the first thing that I would teach someone who is just beginning Google Heats.
03:17 And this is something that's very unintuitive, let me say. So you can do, use something like the if formula. And you can say if d4 here, you're just going to click in d4 and see, okay now d4 is there.
03:30 And that's going to be a true or false statement. And you can say if d4 is over zero and you're going to add a comma and then whatever the value is, this is all like what I'm showing you right now.
03:40 Probably you know already or is really. Simple to show you and see. And now if that value is true, I want to say yes in quotes.
03:50 And if the value is false, I want to say nope. And there you go. You have now used the very first formula you've ever used.
03:57 If you've ever never used Google sheets before. Great. But what's the non-intuitive thing? What is the thing that I would actually.
04:03 Teach you that is not intuitive and it's combining formulas. Okay. So if I say here, let me show you is blank.
04:10 This is just taking some value of a D4 and it's going to return false because this is not blank. If I have a three, if I have three here, this is now true.
04:23 It's just going to. Be true or false. I can combine this instead of saying D4 is over zero. I can put is blank inside here.
04:35 D4 and now is blank is going to return true or false into the if formula that it needs an illogical.
04:48 So I have a true or false thing which is the is blank formula. So now the this formula is blank is inside of the if formula.
04:56 And I can say yes, this is is this blank if it's true? It's going to say yes. If it's false, it's going to say nope.
05:06 But actually I want to do sort of the opposite. I want to say if it's true, meaning it is blank, I want to write blank.
05:13 And if it's not blank, which means is blank will be false. I want to say something there. And so now it's blank.
05:22 And now I have a number some. Something there, right? So instead of having to do multiple formulas in multiple cells, I can combine formulas.
05:31 So. If you're looking for more resources on this kind of thing, go to better sheets dot co slash formulas, but even better.
05:39 You can find every single formula here listed. You'll have videos. Here's the sum. If you're looking for a formula, you'll have some videos, some tutorials.
05:47 You'll have descriptions of how to use it, what not to do, what misconceptions happen. But in addition to this, go to topics.
05:54 And there is a topic here called formula combinations. Where is it? There it is. Formula combinations. Go here. And now these videos.
06:04 All of these 22 videos include and feature not just a single formula, but formulas that are combined. Index match are two different formulas when combined.
06:17 Create absolute magic. You saw one if is blank. You saw now you have a video here called and or if so I combine.
06:25 Some lot of this and an or inside here. You can make quick pipelines for sales. You can turn titles into lowercase hyphen hyphen hyphen or hyphenated slugs.
06:37 By combining some formulas. You do calculations combining formulas I think is one of the most un- intuitive things you would ever know.
06:47 And now let's go to something else. Cause that sort of is the one of two main things that I would teach you if you were just learning Google Sheets Now that I think is not very intuitive.
06:58 The second thing, it's a little more advanced, but I think is one of the absolute most amazing- theseing parts of Google Sheets and absolutely blows every other sort of spreadsheet app out of the water.
07:13 Which is go up to extensions and go click on App Script. When you do that, I'm not teaching you App Script in this video, I will give you one thing to know and it is unintuitive.
07:24 Now one- One thing is that you can copy and paste code that exists online even from Google Docs Help, which I'll show you one, and paste it right in here.
07:35 That's the unintuitive thing. You don't need to know, you don't necessarily need to know how to code. You just need to know how to copy and paste in order to use this code.
07:46 That is the first step, and not many people know this. Many people will think of App Script, think, oh my god, that's a programming language, and I don't really want to learn a new programming language right now to use this.
07:56 But you don't need to know this. You don't need to know how to code in order to use the code.
08:00 So that is the unintuitive thing. That is the second main thing that I would teach you. But let me show you something cool.
08:06 There's an on-open menu, and if we just Google for on-open menu, the very first option here is custom menu in Google Workspace, and it is a help.
08:15 You can read this at your own leisure, but it creates a custom menu that you can run scripts off of.
08:21 And the cool thing is that right here in this gray box right here, the Google, Google even gives you a copy code sample button.
08:30 You don't even have to, you don't even have to use the mouse to select the whole thing. Literally, Google is giving you a copy code sample.
08:41 I'm clicking the copy code sample button. I'm going back here and not, Not even doing anything with this. I can't even delete this if I wanted to or keep it here as my function and I'm just gonna paste.
08:53 I'll do that again. Just right click paste. Okay, I do wanna allow that. There we go. So now I have this on open.
09:02 It is exactly the same code as Google is giving us. And you can, You can read what this does here on your own leisure, but I will explain it a little bit.
09:11 Basically this function on open is a built-in function in Apps Script that will just execute the moment you open this sheet.
09:18 Not open the script, but open the sheet. And what it's gonna create is a menu next to the help menu.
09:25 Right here. It's gonna be called custom menu and it's gonna have some options here that you can click on. When you click on them, it'll run this other function here, menu item one, menu item two.
09:37 And in this particular case, you're gonna get an alert, which is gonna be a little modal and a little text that says you click the first item.
09:42 So before we move on, I have to teach you this one last, thing is that you must save this project.
09:50 Click on save project or command S if you're on a Mac or control S, I think, on our Windows. Make sure you save this first.
09:58 So we copied from the custom menu on Google Docs help. We pasted it in here into our App Script. And now what we're gonna do, It's gonna be a little bit surprising.
10:09 We're gonna go back to our sheet. We're gonna refresh or we can close it. When we close this, the App Script this App Script tab will close.
10:19 So you can actually close this now, but make sure you save it before you close it. It will not save automatically.
10:26 That's one crazy, thing about App Script. But now I'm gonna hit refresh. This sheet is going to re-open, basically open again.
10:36 And in a few moments, we will see now here, right above sorry, right next to the help, is custom menu.
10:43 And now we have this custom menu that we've now copied and pasted right here. And if we click, click on first item, it'll run the script.
10:50 You click the first menu item. Okay! Great, finished script. Now we want to do custom menu, submenu, second item. You click the second item.
11:00 Great! Okay, you have now copied and pasted App Script into your sheet, and we did it from Google. So now, as you read through, through sort of things that you can add to your sheet.
11:12 Like, if you find, If somebody ever says something like, oh, you should just write a little App Script to do that.
11:17 Just start typing in in Google search for the thing you want to do, an App Script. There'll be two sort of options.
11:24 One, you will find App Script that exists on Google Help. So this, is developers.google.com slash App Script, some guides and menus.
11:33 And then you will find things off of off of Google's websites. You'll find them on better seats. You'll find it on spreadsheet dev.
11:41 You'll find it on Ben Collins. You'll find blogs and digital inspiration lab now. You'll find all types of script examples.
11:49 Compels places in different places. And now you can copy and paste that script into any Google sheet because now we know extensions.
11:59 App Script is where you go and you execute app scripts. So you can do really cool things. You can integrate with you can integrate with Gmail.
12:08 You can integrate with. Outside APIs, I myself have written the API access for open AI. So you can literally go to bettersheets.co, look for GPT or chat GPT clone or open AI or AI.
12:23 And I will give to members the exact app script they can use and all you have to do is copy and paste it.
12:29 Into your app script and you can start using it in moments. I have even some videos where I take old code, copy and paste it into my script and into my sheet and start using open AI really, really quickly within moments.
12:43 It's really exciting and I think going over those two things will help you. Understand the power of Google Sheets way more.
12:52 I think those are the most two most unintuitive things that you should learn. One, which was combining formulas that you can combine formulas.
13:00 No one is gonna sort of tell you that you'll just sort of see it happening. You're like, wait, what is this magic?
13:07 And then the second thing is knowing that you can copy and paste. Apps Script, you don't necessarily need to know Apps Script.
13:13 Now, if you want to learn Apps Script, BetterSheets members, you're watching this on BetterSheets.go. You can go and you can see I have spreadsheet automation 101.
13:20 This is a full course, three hour course, that's all it is. It takes you all the way from knowing nothing about Apps Script to knowing how to code in Apps Script, how to send emails automatically, how to- to use API how to capture APIs in Apps Script and in Google Sheets.
13:37 And that I think is like the best way to get started with Apps Script, but even then, I don't think I have a module where I talk about just go and copy Apps Script into your Apps Script.
13:49 This is just some- a little unintuitive thing you might have to see someone do and be like, can I do that?
13:55 And then the answer is yes. Just go to Apps Script and you can copy and paste Apps Scripts into your sheet.
14:02 And so I think- I hope, you know, you're watching this and I hope that you as a beginner could become immediately an intermediate user.
14:11 By one, combining formulas and also using Apps Script using it without having to write Apps Script. But check out spreadsheet automation 101.
14:18 It's course. I offer on bettersheets.co and also on Udemy. If you're not a member and you don't want to purchase a membership, but you do want to learn Apps Script, go check it out on Udemy, spreadsheet automation 101.
14:28 By bettersheets. And I think- you'll be way better. I think just going through that will make you a much better reader of Apps Script.
14:35 You Apps Script and you might not even have to learn to code. You can just copy and paste cool scripts, especially from bettersheets.
14:41 So thank you for watching and I hope this helps you become immediately an intermediate user of Google Sheets. Bye.