Breaking Through Errors In Apps Script

<div>00:00 I want to share with you in this video some kind of errors that you're going to get when you're doing Apps Script . 00:05 When you're automating your sheets and you're writing Apps Script , it can get very frustrating if you encounter errors over and over again. 00:11 I'm going to encounter these errors because I think in uh in fact you should not run from errors but you should actually run into errors because errors when you know how to read them are going to tell you exactly what's wrong and sometimes you have to decipher it, meaning you have to know how to fix  00:29 the error .

And I'm going to do that here. So I'm going to introduce some errors and I'm going to show you where you can find errors and where you can fix those once you run into them. 00:38 So a few places where you're going to run into see those errors are when you save the Apps Script , when you run the Apps Script , in your executions, when you're running it from a custom menu , you'll see it at the top of your sheet, and you'll also find it in an executions list, which I will show you  00:55 that now. So we're just going to go code some Apps Script , we're going to code some code.

Something pretty simple, we're going to make a little bit of uhm some math that's automatic. 01:05 So the first thing I'm going to do actually is going to go get a custom menu from bettersheets.co slash app uh snippets, uh I'm going to just copy this code here, and what it does is it It creates a menu, a custom menu at the top that we can click. 01:20 and run these kinds of functions that we want to run.

The function that we want to run is do some math, and we're just going to take two variables that are in A1 and A2, uhm in the active sheet , we're going to take like 34 and 55, and in B1 we're going to put that in there. 01:38 So, if we have this function , do some math, and we want to take variable 1 equals SpreadsheetApp .getActiveSpreadsheet, getSheetByName, we're going to use uh data , we're going to rename it data once we get done with this, getRange , not range but range, uh getActiveSpreadsheet, a1, getValue, we are definitely 02:05 need to change that, and now we will do exactly the same thing but with a2, and call this 2, and then we're going to do some math with it, and we're going to say uh SpreadsheetGetActiveSpreadsheet. 02:20 GetActiveSp itchy, getRangeSetValue.

We're going to do that, and be 1, and the value we're going to set is result, actually. 02:33 Uhm. We'll call it Maths. Variable Maths equals 1 plus 2. So this is simple. I've saved it, saving project, you'll see the orange button up here, is not saved yet, and everything is looking good. 02:48 If we run it, if we select our function here, do some math, and run it, execution log here, what? Ask for permissions first. 03:00 Let's do it. Those umm very first time you run it, it's gonna ask you for those permissions. Let's make sure we have those permissions. 03:10 Run it.

There you go, and let's see, we have 89 here. Perfect. So it worked. But if, let's say we have not completed our, App script here. 03:21 We have written a syntax error , is what it's called. And it will give us here down in the bottom syntax error , syntax error , unexpected token line 10. 03:31 So we can look at line 10 and say, it underlines this curly bracket, right? And it tells you this is where the error is, which it is- is correct. 03:40 That is where the error is, but the error is caused because we have not added the parenthesis here.

So once we add the parenthesis, we can command S and save again. 03:52 This kind of syntax error will occur only when you save. So if you're writing a script- writing a long script. 04:00 And let's say we do not have this end curly bracket here. And we try to save. Even though like maybe you're trying to save a draft and you know this is not finished. 04:10 Or you don't know that it's not finished. And it has this red underline here and you try to do command S or up here save project. 04:19 It will give you a syntax error . And it says unexpected end of input line sixteen.

So it shows you hey, there's something missing here. 04:26 Uh, this is where the error is. Not necessarily what the fix is. What is the solution to that problem? The solution to this particular problem is that we need to get the correct syntax . 04:37 If you ever see syntax error . The solution to this solution is always fix the syntax . Which in many cases will be different. 04:44 In this case we must have function , the word function . We have the name of the function . A parenthesis in a parenthesis out. 04:52 And then uh curly brackets start and then curly brackets end.

There may be some syntax error . In here for instance if you miss a parenthesis here and instead do a umm semicolon let's try to save that without the parenthesis. 05:09 We also have a syntax error and it says unexpected token. So it tells us where the error is but it doesn't tell us the solution is to add this parenthesis. 05:18 But it does tell us where it is so line. And it shows us let's see it again because it does go away pretty quickly. 05:25 It says unexpected token and it gives us that semicolon right here. So it says ooh something's wrong here.

If you notice if I can zoom in a little I don't know if I can zoom in much. 05:34 Oh there. I can zoom in a lot. There I can zoom in a lot. Um let's add a few lines. 05:42 You can see the parentheses here is red which is different than the other parentheses. When we close it it is fine. 05:50 We can hit command S or click up here safe project. Everything's fine.

So again the error is just showing you where the error is and not the solution but we can sometimes figure out what is the solution by seeing what is in the context of that error . 06:06 Mainly it's going to be parentheses umm. If maybe we have this comma here. Let's see what happens. If does it give us a syntax error and in this case it does not give us a syntax error because this is par- pretty much correct syntax for something. 06:22 But the unfortunate problem is that our solution is going to be wrong.

Our function will be wrong and that it will produce the wrong thing. 06:29 Um actually we can see that here. Let's run it. Do some math. Let's see if we have an error here. 06:36 Nope. No error . But we do have the wrong. Long. Output here. Um and that is going to be, we'll do some debugging here in a minute. 06:46 But first I want to actually make this plus as well and then this maths. I'm going to actually call this one two and delete everything. 06:58 Okay save it. Should be. Okay syntactically correct.

But now I'm going to open this and we want to run this from the menu. 07:09 So we're going to call this do some math. We're going to refresh our sheet so we have a menu show up. 07:20 Let's see that menu show up here. Oh and it didn't actually save uh the correct name here. Let's do that. 07:28 Let's fix this. Let's. I think I just did it too quickly do. Some math. Save it. Make sure it saves. 07:38 That's one important weird thing about apps good. So we have to make sure it really makes sure it saves once if we see that orange dot there it has not saved. 07:45 Alright.

Error finding and fixing. Let's call it that. Okay let's close this and refresh it again so that it opens again. 07:57 And we will see our custom menu come up. We'll do some math and here we get an error .

So this is the other place where we can get an error is when we try to run the function from the sheet we get a reference error one two is not defined. 08:12 So this tells us that something here one two which we might not know what that is if we're running code from someone else we were copying and pacing code. 08:20 and we get an error one two is not defined we can click on details not much more details but let's take this one two what is it that is not defined let's copy it dismiss it let's go look at the let's go look at the uh app script again and what we can do is command. 08:40 F and we can search for this one two so here we go we get variable maths equals one two that is the only place that it exists okay so then I must have some error because this variable is being assigned the whatever the output of one two is oh I see here variable one and variable two maybe I'm trying  09:04 to add these together or multiply them together and I forgot the actual umm plus sign or we want to multiply them so we can fix that by just adding that here and changing this one two maybe it's the one two wrong name sometimes you will try to type one two now syntactically this is correct so it is saving 09:29 but let's try to run do some math here one is not the fine so it's capital one is not the fine and again we can come back here command F1 1. 09:41 and we find oh one is here that is our variable why doesn't it have the variable here oh because the O is capitalized this happens a lot if we are doing like first item and here we type in first item without the capitalization and we say second item let's put the second item with the capitalization you 10:05 can tell beforehand that this gray this is grayed out and this is not and you can see like this is not used that's what that means if it's grayed out if the variable name is grayed out it means it's not used later on but syntactically this is Let's go. 10:20 correct syntax fine it's saving and let's run it again and show that you have see the error error first item is not defined so again we can copy it go here first item now we have capitalized one of the uh letters but it's still fine meaning both of them so this could be confusing because you're like  10:44 well these are the same thing these are exactly the same very available first item first item especially with capitalized uh L's and I's and 0's and O's those things that can get mixed up right um and I'm showing you this is a problem because the I is capitalized and the I is capitalized not here so  11:02 we can fix that with just capitalizing that I let's save it and show you that their error does not exist now do some math there we go we got the answer it's giving us the correct answer so we've gone through where you're saving them you're getting some syntax errors Okay.

That's a good one. Right, right 11:20 .

Running in Apps Script so you will find that you can run it here you can select here and you will get the error down here so again let's say there is some error first and let's run it from here we will get the error right here same error that we would get if we were running it from our custom menu   11:42 here and at the top of the sheet and I want to show you one last place where you might see errors and this is really good if someone else is running the error is running in Apps Script and getting an error and you are not you can go over here on the left side it says executions and you can see all the 12:01 executions here now one note of reference here uhh something to note is that if someone if you created a trigger you can see the fact that other people have created a trigger but you can't see their trigger so you can't fix their trigger but you can fix the Apps Script umm and every execution here is 12:25 every execution in the sheet so if you have something like an on edit you'll see a ton of on edits so it can get pretty hard to find the actual failed thing especially if you're running lots of automations or on edits or on opens and a lot of people are using that sheet I will show you how to find that 12:41 so we have this failure here and they will share share with you the same failure that we saw in the sheet so we'll say a reference error this is really good place if you add logging so let's call this and let's do would say we do not know the fact that this is first item and this is very uh first and 13:03 we're trying to log it we can do logger.log and put in the item we can say first item let's copy paste that logger.log second item so these two variables what are they are are are variables wrong let's find out let's save that let's go here do some math we're going to get the error or like hey we're  13:26 getting an error someone else ran this code let's go look at those uh logs and there it is there's some logs uh we can add more information to this log instead of just the just the result we can say say in quotes first item give it a space and then a plus sign and then here second item give it a colon 13:54 and a space and a plus sign now let's run this again get the error again but look at it in the uh execution so we get the error let's look at our executions it is the top one right now it's the top one maybe there's other failures we may if it happens right away if we're running that section right away 14:15 we may have to take some time refresh this takes a little bit of time there we go No.

first item 34 second item 35 55 first is not defined ooh I have found the error right this is first item this is first not first item so we can go over to our sheet our app script and say oh here it is first is should 14:37 be first item and now it is fixed these logs will remain even if you fix the errors here's the logs that's fantastic go over to our executions says completed and we have still logs so that's really cool that we can still see the same exact logs even when it's not has an error another error you might  15:00 get is variable undefined and if we let's say have actually well do this function do some math this first item does not exist now and let's run do some math it has saved so it's not a syntax error but it's a reference error it's a reference error first item is not defined so we can go for first and see 15:23 oh here's first item first item oh yeah we didn't get it maybe it's here and you'll get this often by the way if we create let me say delete this and delete this to fix it so u u we'll get this undefined regularly if you create do more math first second so we have two items first and second and we will 15:54 return first plus second if we want to test this usually the correct way is to go over here let's create a new sheet undo equals do more math 44 comma 55 and we get 99 that is testing this function but you're like hmm I want to test it from here so I'm going to select it from this sheet do more math  16:24 and click run what's going to happen is it's running totally fine but it has no variables here variables need if we actually do something to the variables let's see variable result equals first, second, turn result let's see if we actually run some function if it gives us this error null right so this 16:58 is giving us null because there's nothing happening happening here umm let's try to do more not just math but times maybe it's because it's zero zero so I've given it something more complicated to do let's see if it will run nope it still runs so we actually f- found the error but it's not where we thought 17:22 so again we have all of these places where it could be it's actually in the result so in the result it's his first undefined because these are null so it's actually running the script as it is but because first and second are null here meaning there's nothing there it's doing this math and it's ending 17:39 up with a result of undefined but it's not actually telling us that let's do this let's take out result and see if it will tell us nope still doesn't tell us but what you need to do is if you have a umm if you have an app script where it takes in variable In its name you need to actually define those 18:04 variables somewhere so as a test what I like to do is even include these here but I still like to do variable first equals forty four variable second equals is equal to fifty six. 18:21 And then put this set this value as result.

Now run it. Now it will give us some actual information here and it will have a correct answer here so it's not undefined. 18:33 So that undefined actually didn't show up as an error when we executed the script but it did show us in the log in the result of it. 18:41 Uh hey it was undefined. It gave us the word undefined.

So that's very interesting and hopefully this video has helped you know where you're going to find those errors, how to fix them or rather how to approach fixing them. 18:53 If you have a syntax error it's literally just you've done something wrong with the JavaScript. Uh usually you can just take that put that into check. 19:00 At GBT or some or umm just look at how the function is written.

Not what the function involves but how it is written. 19:10 And then if you're finding you're getting errors, read the error , go to that line and literally look at just before it is going to be where you're going to probably find the error . 19:20 Or. more. 8 Searching for whatever variable is going to come up in that error will help you umm find and fix your errors in Apps Script .</div>