00:00 I wanted to share with you some tips and tricks on navigating thousands of lines of code in Apps Script. This is a blank Google Sheet.
00:06 We're just going to go over the extension to Apps Script. I'm going to share with you some of the interesting things about Apps Script and what are the like built in things that are happening, color coding, things that may be happening to you while you're working with Apps Script, but I'm going to explain
00:21 them and say why they're important. So the very first thing I'm going to do actually is show you that if you have a function and my function, like nothing.
00:30 is really going to work. So I'm going to just go over to bettersheets.co slash snippets where you can find actual code that I've written.
00:39 It's very useful. Like this is OpenAI using the Omni model and you can call OpenAI with the API key. I'm just going to copy it because that's what it's there for.
00:50 It's for you to copy and paste directly into your sheets. And so when we're creating functions in Apps Script, one of the basic things you should know is that it says function, then the next name of the function, and then parentheses.
01:02 And inside the parentheses you can put some kind of variables. And ah these variables are then going to be used within your function.
01:10 But let's say we did not use this func, this ah variable. And so here we might write text. Immediately that word prompt here is now grayed out.
01:23 And this graying allows you to know when and variables are not used. It will be a darker black. If the variable is used.
01:32 And it'll be a gray if they're not used. Also, as we're typing you may see things like this, which are like squiggly red lines.
01:44 Maybe I can zoom in a little bit more. Squiggly red line. This shows you that there's an error that's going to happen.
01:51 So if I want to save this, actually watch what happens. It says error syntax error unexpected token var line 8.
01:59 And the error is, occurring on line 8. But one of my rules of thumb is that just look just before it for the actual error and where you can fix it.
02:09 So in this case we need to finish this API key variable with some variable. We can put quotes here, single quotes or double quotes.
02:21 Does not matter. Actually matters for what depends on what's inside the quotes. Like if you want to go what's up with a single quote.
02:30 You want to wrap that in double quotes. So that's a good rule of thumb too. I like using double quotes a lot.
02:35 Some people like to use single quotes a lot. Also you can use backticks if you want to include quotes in like double quotes and single quotes you can use backticks here.
02:47 That's a good rule of thumb. But just know that if you ever see this red squiggly line all it means is that you're going to get an error here.
02:55 And that error usually is like a syntax error. You may be, ending your lines with a semicolon. And this is from Javascript.
03:04 At the end of every line should be a semicolon and that's from Javascript. But in Apps Script you don't necessarily have to do that.
03:09 But if you see a red squiggly line under your semicolon you know again right before it there's something wrong with this variable API key equals something.
03:21 We need something here. So we need something here. And that goes away and we can also save our function. Another thing with color coding is if you are using built-in functions from Apps Script which are your spreadsheet app, your document app, your drive app.
03:39 These start with a capital letter. Spreadsheet app. And they are pink. There is another thing that happens when there's pink.
03:49 We have pink. And it's essentially just any time we start with a capital letter. So if we call we want to call our function help Help me, Rhonda.
04:01 And so, question, and then we have some response here, variable, uh return response. Now, this function, if we use it inside of Apps Script, is going to be this, uh, lowercase letters.
04:18 In fact, we can add something cool here in front of it, which I love to do, which is slash asterisk asterisk at custom function.
04:28 And actually, that's it. Variable, or yeah, variable response equals, we'll just give back the question. Ronda's not very helpful. So, if we save this, and we go over here, we go equals, help me Ronda, it's here, but it's lowercase.
04:50 We want this uppercase. And watch, if I do help me Ronda, in all uppercase, it's pink. that's not a problem in this particular case.
05:01 Being pink here, no problem at all. So, if your text is pink, it just really means that you have a capital letter here.
05:07 And what that allows me to do, when I'm looking through Apps Script, is see these built-in functions. So, I use these a lot, these built-in functions.
05:17 But you can also create your own built-in functions, right? Help me Ronda now, there's a function we can call here, and we can go, you know, variable ronda's answer.
05:34 Equals, help me Ronda, question, right? Or put in the prompt from this, right? We can create these functions that we can call, and we're gonna also, and if they're capitalized, they're gonna be pink as well.
05:47 So, no problem with pink. Another thing that happens that I absolutely love is the autocomplete, or this might be called like an intelligence system or IntelliSense, but if we're, we're using, especially if we're using something like SpreadsheetApp, so instead of a function, help me Ronda, we just want
06:10 to create SpreadsheetApp dot, the moment I put dot, this autocomplete shows me all of the possibilities that I can do.
06:19 So, in my case, I just want to write some text, let's say, somewhere, or ah so I need to get an active spreadsheet.
06:27 This is one One failure of Apps Script that I'm going to explain right now is that this, getActiveSpreadsheet must have a parenthesis, but it does not automatically put it for you.
06:37 So, again, we can add functions to this getActiveSpreadsheet, add a dot, and we have all of the options. So let's get a sheet, and as we type, these will go down in number, so they'll just show you what are the search results, essentially.
06:55 This is insanely awesome. I I absolutely live by this, I will just hit. Tab, and look, it auto-completed that very first one.
07:04 So if I want sheet, a different one, sheet id, I just hit the down arrow, and now I can navigate this, and I can say, okay, I want to get sheet values, let's say.
07:16 Whatever it is, I can navigate that super easy, and it allows me to know, like, without going to the documentation, what is available, and what am I, like, trying to do.
07:26 So let's say getActive, getSheetByName. We want to get. Question sheet, hit dot, and now we have some other options here.
07:38 We can do things to this sheet, we can get some information, we can even delete columns, delete rows, copy to, we can do all of these cool things here.
07:47 What I'm going to do is getRange, and I just type in getRan, and already getRange is at the top, so I'm going to hit tab, but again, one of the failures of this is that you must add parentheses, and if you don't know that, it's going to give you some Errors get range getValues, let's say, and I'm going
08:06 to try to save this, might even save it, yeah, it's going to save it, uh let me make sure, let's say we have a question here, we don't want it to error because we don't have the sheet, we want it to error because we have this getRange, so let's see, help me Rhonda, let's execute it, we do have to review
08:26 permissions, we have to always authorize the very first time we ever run a function and we need authorization. It'll ask for authorization, let's see if we get an error, yes, we got an error, so type error and it's telling us exactly what's wrong, but it might be hard to know, so SpreadsheetApp.getActiveSpreadsheet
08:46 , getSheetByName, getRange, getValues is not a function, so it's telling us this entire thing is not a function, I know from experience and what I told you is that all of these need a parentheses, So, I wish, you know, this is not a function would be more specific, like, focus on the getRange part, um
09:06 , that will come with time and also as you're watching this video, you'll now know that everything needs a parentheses, so first look for that, if, um, let's say we have a parentheses, but we did getRan instead of getRange, so let me run that again, I'll save it and run it again, let's see what happens
09:27 , we get the exact same error function, Even though the error is different, we are not going now not actually naming it correctly, we do have the parentheses but we don't have the correct name, it still says is not a function, one good thing about this error message is it did change, it says getRan, 
09:46 so it doesn't say getRange.getValues, so it doesn't, it does really say like right there at the very end, we're not correct, like something is wrong right at the end.
09:58 So that is one interesting thing about the change in errors there. I'm going to actually use this variable question equals and we have this questionIsGrayedOut, as I said before, because we're not using it anywhere, let's just return log actually, logger.log, logger is a great built-in function that 
10:27 we allow us to not have to go to the sheet to see what's going on. But let's run this and see and see if there's any other issues.
10:34 We have got the getRange, we have the parentheses, we have whole getRange. Let's save it. Save it and run it.
10:41 If you know already what the problem is, yell it out. Uhm, we're going to get an exception, the parameters don't match the method signature for spreadsheet app sheet getRange.
10:53 Okay, I know what the issue is, but let me give you a hint. And it does have to do with getRange.
10:59 But it is It's very hard to figure out. Error code, right? This is just saying exception, the parameters. But it is giving you a clue, right in the parentheses.
11:10 The parameters parentheses don't match the method signature. What we're, what the answer is, is that we need a range. Uh, we need a range of, let's say A1, and let's go here and say this is the question.
11:27 And so we want that text there, into this log. So let's run it again. With an A1, and everything's there.
11:37 We get an actual response. So, if you are working with Apps Script and you're new to it, and you're getting these errors, these errors should be giving you clues in some way, shape, or form as to what the actual error is.
11:51 If it's a syntax error, it will uh actually not allow you to, mostly, most of the time not allow you to save so that might be frustrating if it is like some undefined, like maybe we uh added questions here and you'll see right away question here is gray, so that should give you a clue, but we'll save
12:11 it. The whole syntax here is correct, but we are going to get, I think, some error. Yeah, reference error questions is not defined.
12:19 So, this is telling us this particular variable questions is undefined, meaning there's nothing there. We don't know what it is.
12:27 It doesn't refer to anything, but you're looking at this and you're Like, well, variable question is here. It is, the problem was the S.
12:34 So, if we add, let's say, a capital S or even capital Q question, this is also going to give us an error, and if you see, it's not dark pink.
12:47 It is a little bit of a light pink. Let's actually save it. And you'll see there is a prompt. Let's say it's actually questions.
13:00 Let's save it and run it again and see what this error, yes, we get an error. Even though it is questions and questions, our capitalization is wrong.
13:08 And in Apps Script, capitalization is very important. Uh, we need it to be exactly capitalized the same way. Uh, we can, if it's capital Q, we can change either one.
13:20 I think this will still work. Let's run it and see. Yeah. So, they just need to be the same. But that graying out or even the light pink is going to allow you to get that clue before you save.
13:36 Even as you save and it saves correctly because there's no syntax errors. Uh, that will be helpful to you as you code.
13:44 So, I want to also point out that anywhere that my cursor is, it's going to highlight a gray. And it's, it's highlighting the word, but it's also highlighting every other place where that word is, uh, occurs.
14:01 Not if we, actually, including, even if we just highlight the letter A, it, it is highlighting in blue. But this doesn't mean that it's active.
14:13 So, if we go down and find something like response, this helps us see where is response used. So, again, I, I mentioned, if we don't use a variable, it's going to be grayed.
14:29 But if we do use a variable and we're like, We want to know where this variable is used. We want to, maybe it's, you know, a hundred rows later or a thousand rows later, we can see here.
14:39 If you look also on the right side of the bar, it gives you another clue where the other occurrences are.
14:48 So that, uh, black line is where we're viewing it. And so we now are out of the view of the first one and we see here, we now see, I guess it's little grayer or purplish color where that other question is right there.
15:05 So let's say we used variable questions as well here. We just wanted to log that there. Notice I highlight questions.
15:18 It doesn't, it doesn't highlight it in the right side, but it does highlight it in the, uh, text here. One cool thing about Apps Script that I want to mention is a keyboard shortcut.
15:33 Either Command D if you're using Mac or Control D. What this allows us to do is select everything that's highlighted here.
15:40 So if I select questions and I want to actually type, I want to rename this instead of questions, I want to say question.
15:47 I can do Command D and now I have both of these selections selected. I can hit Command D again and I have this one selected.
15:57 See, so I can select as many as I want, as many times as I hit Command D. And now, I can make move the cursor and it's moving them both places, or all three places, or all four places, however many you selected.
16:09 And I can hit the Delete. Now it's question. Save. So I'm saving a ton of keystrokes by doing this. And maybe I have to rename this one as well.
16:22 See, it's the same exact lettering ah letters. So it's going to be selected. So that's one thing you have to watch out for is it will select all of them.
16:30 So if we like get range, let's say. See if there's other ranges, there's no other ranges. Right, so this allows us to see what other things that are the same name.
16:46 And allows us to select it if we use Command D or Control D on a non-Mac. Let me show you an exact reason why this is really cool.
16:54 So let's create more question, variable questions. And it's like question one, question two, question three. We want A1, A2, A3, and A3.
17:05 We're getting all those values off the same sheet. And we want to though, maybe we screwed up and it's actually just one value in here, not get values.
17:18 And if you notice already, I just have my cursor on get values and it's already highlighting the other occurrences. If I double click on it, I'm hitting Command D twice once twice now if I hit delete it's deleting the entire thing Command Z to undo that.
17:34 But I can hit the left arrow, now I'm at the front, go over to the back. I am accessing all three of these at the same time.
17:42 Hit delete, we have now fixed that issue very very easily instead of having to delete the S off of each and every one of those.
17:52 It's really really cool. It will also start auto completing if we start writing variables. So this question here is the wrong variable.
18:02 So, I want to add. Start writing question and you can see all three variables here. So this is IntelliSense inside of our app script, inside of our own thing that we've created these variables and now it's helping us.
18:13 Did you mean one of these? Yes, I'm gonna hit enter and I'm actually going to combine these. So I'm gonna say questions that plus a space plus, plus, plus, plus, plus, question.
18:34 Maybe a little bit simpler. If we just want text it's okay, but maybe we want an array of these. Question one, comma, question two.
18:43 I'm just hitting down and selecting it. So it's very easy to type these. Uhm, let's actually get some, another question.
18:53 Yes. We can run this and see they're all there. Awesome. So I hope this was super helpful to navigate through Apps Script.
19:06 If you have any questions, feel free to ask anywhere on BetterSheets.co if you're watching on BetterSheets.co or anywhere else. I'm happy to answer more questions about navigating Apps Script.
19:17 I think these tips and tricks are going to improve the speed at which you code and the ability for you to code very, very easily.
19:25 Uh, you may have to copy paste lots of code. You may have to You change lots of variables, and this is how you do it very, very, very quickly.
19:33 I'm so excited that you got through it all. Bye. Thank you.