How to Get The Last Row - Google Sheets Apps Scripts - Array Methods Part 10

okay so let's see how we find less row of your data with Apps Script so let's start with the basic method so the basic method is this right so first let's get our spreadsheet to variable so we'll go get active sheet and we'll call it our spreadsheet it's good enough so that's basically our active sheet really not spreadsheet but I'm just gonna leave it SS doesn't matter so we're gonna take that SS and then it will look through our methods there's actually a method called cat last bro or get last columns so if we do get last row that should return an integer so I'm gonna give this variable a name LR and let's just log this out LR and semicolon so if I run this keep in mind if you are running this for the first time you may need to give permissions for the app script to run hopefully if you made it to this video you already know that stuff but now that I've ran this if I look through my logs see it says 5 so if I go back and look 5 is actually accurate so that usually works for something like this but let's try to expand this a little bit so what if we have some numbers here and then we made this formula we're gonna do if this is not well let's just do this if it is blank then let's leave it blank otherwise let's do a formula let's take this number and I don't know multiply it by this closed parenthesis hit enter I'm gonna zoom in a little bit just to make sure you can actually see what I'm doing here so that's my formula right so if this cell is blank we're gonna leave it blank otherwise we're gonna multiply those numbers together so that's 30 and I'm just gonna drag this down until rule 15 so basically now this formula is gonna leave it blank when it's blank and it's gonna feel the formula when it's not so now let's run our thing again and now you can see we get the actual last row where the formula is not the last row of the data so this method now it's gonna return 15 even though these are kind of blank but because we have a formula feeling that n it's still gonna get that as a result so you may get this when you get a query function getting results it will get some extra things so there are a lot of situations when this might be happening you may also have this problem so I'm just gonna delete this for now we may need to come back to this but actually I'm just gonna keep this like this I'm just gonna make a duplicate of this let me delete this again so another possible situation now if we run this it should be 5 hopefully let's first of all test that command enter ctrl enter should bring us this 5 so that's correct the fee for however most spreadsheets aren't very consistent with the way the data is laid out so if somebody has some comments or some other information on this other side of yours cheat now what's gonna happen we're gonna run this we're gonna get 12 because well this is the line so now because we have this other data on the right side this is not accurate for our data set anymore so one way to rectify this problem is to use a different method here so once I get my active sheet I'm gonna take that variable SS and then I'm going to get the range and by the range I'm gonna get me a starting cell let's say this is where my data starts a1 so I'm gonna get that range right here a 1 and then I'm gonna use this method that's called get data region and that should return this range now to make sure that's what we're getting let's just use this method get a1 notation as text and now this should be the actual a1 notation that's the range so let's run this and see what we get so see it says a1 through c5 that's our data range a1 through c5 that's correct so this method is actually pretty nice now if I'm adding more information here so let's do this like this this something like this if I run this that's gonna get me a 1 through c7 as you can see which is correct now if we add another cell here on the right see right there so if we run this let's take a look it says a1 through d7 so now we have this included that's perfect so this works most of the time with exception sometimes it's kind of weird so let's try something like this so I'm gonna run this and it says a1 through d7 now let's look at our data a 1 through d7 would be this this was not included in our data so it's kind of like if it's like on a side like this it's not including as a part of your data set although if you have probably like a cell that's filled in here now if I run this it will probably include it there it is a 1 through e8 which is now correct so with exception of some very weird cases like this this should be fine another case where it's not fine is where we have let's say a blank row so if we have a blank row or a blank column that will automatically just separate our data so now if I run this and look at a lock a 1 through d4 see that's getting from here through here that's our data set and the blank row it's kind of taking that second part as a separate data set now the last thing I want to test I'm gonna delete that blank row what if we actually have column labels for every column so I'm gonna go call one and go from here through here let's see if this works and if it includes this with no problem so I'm gonna go here run this log a 1 through 8 a 1 e 8 so that's nice so if we have column numbers on top and we don't have the blank row problem we should be fine with this method so let's just try to do something like this so let's say these are my columns and some columns are blank and we have this let's see what we get out of that a one through eight nice so where we're at right now is that if we have a label for each column on top with our data set then this method of getting our data is actually gonna work pretty nice we're gonna give a starting cell then I'm going to get the region but what we wanted was the last row not the a1 notation now to fix that problem we're just gonna change this to get last row and if I run this that's eight so now I have eight my last row so this is a much nicer method of now getting the last row compared to just the last row the other thing with this method is that if I go back here and remember this whole formula thing scrolling down so if I run this on this data set if I look seeds as row five is the last one so it doesn't include this formula stuff so that's that not a problem we're going to still have is what if we have blank rows in our data and what if that's the case so if we have a blank run our data it should be four as the last column so let's see and there it is so to go around this we're gonna have to write some extra code to get the last row so this is that now let's go back to this so what I'm gonna do I'm gonna first get this entire thing as far as necessary to an array and to do that well there are multiple ways to do that one way we can't just get to our sheet and get range and then I'm gonna choose a1 notation that would be one method to do this and I'll go from a1 through e a1 through e open and that means I guess all the way down and we're gonna do get values to put that whole thing in a race so let's do our range and save let's take a look what's in there for a second and if I run this if I look at the log see basically we get all those blanks all the way down in our spreadsheet so we got from here through here each column and then basically all the way down so what we're going to do now we're going to go from the bottom and we're gonna keep checking if all the columns in this range are blank and we're gonna keep going up up up up up until we find the first one when the columns in the range was selected are not blank and we're gonna basically say that's the last row so to do this we're gonna create a for loop and we have to make sure that in this loop we start from the bottom and we go up instead of going from the top and going down because we want to find the first number ank row in the data from the bottom not from the top because we could have this one blank too but we want to get this row so to do that we're gonna do set a variable here and we're gonna start from the bottom end and to get the bottom end that's gonna be the length of our array minus 1 because array index starts from 0 so if there are 1000 rows in an array if we start the count from zero that's 999 so that's where we start to do the semicolon we're gonna keep going as long as that I is greater than 0 or greater than equal to 0 so we're gonna go all the way to 0 right so equal to 0 oh not less than greater than we're gonna decrement that I by one so we're gonna decrease I by one every time so we're gonna start with 999 we're gonna keep going until we hit zero in our array so now we're gonna have a row as the variable so if I just do var row equals two that's gonna be from this range I want the row that we're currently in so range I would give me the current row so the first time it's gonna be the last one and what we need to do in this row we need to make sure that every single argument in that bro is blank and this row is gonna be an array just to make sure you understand this let's just log there bro if I save this in Brandes look at my log see it's each row as an array and it keeps going and hopefully at some point we're gonna get some data there it is now in this array we need to check if every single one of them is blank and if it's blank we're gonna move to the next one right so the way we're gonna do that we're gonna take that bro and because it's an array we're gonna have every method on that and with every method we're gonna have the function and again if you don't understand every method you should watch my video on this and we're gonna pass every cell as an argument here in this function and we're gonna return logical true or false so we're gonna check if that C is blank so this is gonna give me true if the whole thing is blank what I have to do I have to wrap this inside of an if function so let's just create a variable here something like this I'm gonna create an if function I'm gonna say if the whole thing is blank then the way we want to approach this we want to just set a variable on top so I'm gonna create a variable less row and I'm gonna say my last row equals two actually I'm gonna call it last row index because technically it's not the last row so I'm gonna say last row index equals to I right so we want to make sure that we set our last row to I and as I'm going through this I changed my mind so what I'm gonna do I'm just gonna set the last row to I right in the beginning and then I'm gonna do the rest of the logic so first I'm gonna set the last row to the row we're currently in so let's say we're in Breaux whatever the last one right now and we're gonna set it to 1002 and then we're gonna check is the whole thing blank and if it's blank we want to go to the next one and do this over again and we're gonna set that so on and so on and so on until we are here and then when we're in this 10 which is the last row it's already gonna be set to 10 and we're gonna check if every one of them is blank and if it isn't this is gonna give us false so I'm gonna take that false and add an exclamation sign in front of it and it's gonna make it true so now I've reversed my logic so basically at least one of them has something in it if that's the case we want to get out of this loop so we're gonna break out of the loop and what we're gonna have so break is gonna stop the loop at this point so we're gonna check now what is the index so if I run this it should be right below what we need so see it says nine so if you look here it should have been ten and the reason it says nine is because remember we have this is an array and a raise start from zero so the count goes zero one two three four five six seven eight nine but the way we count in Google sheets starts from one so we always have to add one to this index to get less rows so as an end result our last row should be this plus one and I don't think I had a variable for it so I guess I'm gonna set it that's gonna be this I'm gonna simplify this a little bit one thing I'm gonna do instead of having this as a separate line I'm just gonna copy this whole thing that gives me this is blank and I'm gonna replace this is blank with this get rid of that we set our last row to I and we also have this row which is the same as this so instead of doing all of that I can't just replace this row with this and get rid of this line so that should give us the same thing let's run this I forgot to log our results let's see what we have ten perfect so now we were able to get the ten now if we have something in here that should give us 14 what's right here that give us like 16 perfect so that gives us 16 so now I would probably want to move it to its own function because this is something that we might be doing a lot getting the last row so to move this to a function we're going to create a function I'm going to call it get last row and here we basically need most of this if not all of it there we go and in this function I'm gonna accept arguments one is gonna be the worksheet I'm gonna call it variable sheet and the second one is gonna be range string so that will be this now instead of doing this to get our active sheet I'm gonna get rid of that and I'm gonna use that sheet variable that the function is going to give us here range a 1 through e only instead of that we're gonna just pass the range that the user is going to provide us and then we're gonna get those values we're gonna set our last row index as a variable we're going to get the last row although we don't need to get it we'll simply just return this now we probably could build some error checking and stuff like that but I'm just gonna live it to you I guess if you want to improve on this but now let's just use this I'm gonna take this go back here now to use this function what we need to do we need to well I don't need any of this stuff I just have to get my spreadsheet to a variable and then I will create a variable LR and I'll make it equal - so the sheet is gonna be that SS and the range as the string I'm gonna pass to that is gonna be a 1 through E I yes that's what we had a one through E just like that and hopefully that will get us the last row and this should give us what we need so let's run this function do the log and we get 16 as the last row here we go and that's because I chose from this column to this column now if I do so let's actually do something like this zoom out just a little bit now if I run this it's still gonna give us 16 because we're getting through a column now if I include let's say G in there a 1 through G that's the area we're checking this in I run this luck and it says 18 as the last row right and that's 18 because well this is where it is now it doesn't even have to be here so if we do this see a little separate there that should give us 19 so let's run that and verify and there it is that's 19 now that doesn't have to be our active sheet right we can get our sheet by the name so I can say let me check in here what's the last row so if I wanted to go from a1 through F area so I'm gonna go a 1 through F and here as sheet I'm gonna go get sheet by name and then the name of this sheet is sheet 1 and then I'm gonna move to this tab so we know that we're running a different sheet and now we stored in this variable SS we pass it to our function and we give a range and now if I run look at the log it says five so and that's here the fifth row and remember the formula thing we had here the if function it doesn't care about that a few ways to get the last row thanks for watching please subscribe and I'll see you in the next video

Channel: Learn Google Spreadsheets

Views: 44,522

Keywords: Get, last row, The Last Row, Google Sheets, Apps Scripts, How to, tutorial

Id: zWcNzLbJpb0

Length: 24min 33sec (1473 seconds)

Published: Mon Jan 07 2019