Where Can I Get My Garden Tools Sharpend

Google Apps Script: getRange, getLastRow, getDataRange, spreadsheetApp – requires a basic understanding of GAS. Updated 01 Dec 2021

Finding the last row of a data range in Google Sheets using Google Apps Script is a pretty common task. We often do this to find the next available free row to insert new data or to copy an entire data range to put into an array in Google Apps Script.

Generally, for fairly clean data, or a small range of data, we would rely on two approaches to get the data we need:

getLastRow(): this will get the last row in a Google Sheet that has data in it. It determines the last row based on the last available row value in all columns in the spreadsheet.
getDataRange(): this will get the range up to the last row and column with values in it.

Let's take a quick look at this clean data set:

…or jump to the code.

Clean Data Set Google Sheets

If we call the getLastRow() method we would expect it to be row 12 in this case. Alternatively, if we called and got the values of getDataRange() we would get all the data and cells from A1 through D12.

//****GLOBALS****

var SpreadsheetID = "1A7Ix7rzrhu7zBKcS7zPeU8w8c_nTae0mBHycvMUE68A" ;

var SheetName = "CleanData" ;

function myFunction ( ) {

var ss = SpreadsheetApp . openById ( SpreadsheetID )

var sheet = ss . getSheetByName ( SheetName ) ;

Logger . log ( sheet . getLastRow ( ) + " Is the last Row." ) ;

var range = sheet . getDataRange ( ) ;

Logger . log ( range . getLastRow ( ) + " Is the last Row." ) ;

//Range Values

var data = range . getValues ( ) ;

Logger . log ( data ) ;

}

The above code would return:

Note! Where it says '…last Column' read '…last Row'. Yeah, I messed it up. Sorry. 🤷‍♂️🐐

Google Apps Script getDataRange getLastRow

Perfect. A nice and efficient way to get data or last rows without getting any unnecessary data and bogging down our runtime.

The Problem

What happens, though, if we have a larger data range that includes a number of preset formula columns that are hidden? If they don't draw from any data these formulas would be set all the way down the page. Further, what if we had a preset of checklists that we have run down the page. A little something like this:

Google Sheets Hidden Formulas that will impact the getLastRow method in Google Apps Script

In the gif above, you can see that that there are a number of hidden formulas indicated by the orange columns and four checklist columns indicated in grey. When data is added to the rows in columns A:D the orange fields are automatically updated. This is useful for the user, but not for us.

The problem is that now when we run getLastRow() the result will be row 1,000 because we have dragged all those formulas and checkboxes to the bottom of our sheet which currently stops on row 1,000.

Google Apps Script problem with getLastRow — P.S. I couldn't compress this gif any further, so it may take a bit to load for you. **Note!** *Where it says '…last Column' read '…last Row'. Yeah, I messed it up. Sorry. 🤷‍♂️🐐*

As you can see, running the script again on the "Projects" sheet, all 1,000 rows are selected and the getDataRange() has also got all the data right down to row 1,000. This slows down our script. Imagine if you have a much larger data range with many columns that you wish to draw from. This would seriously slow things down unnecessarily.

So let's take a little look at a better way to get the last row when we have these hidden formulas and checkboxes.

The solution

My solution to this is to select a column from my sheet that we know does not contain formulas and iterate through that column to get the first empty cell. We can use column "A" in our example.

Select a column to determine the last row - Google Apps Script

We also need to take into consideration that some cells might also be blank between other values. For example, row 5 might be blank but there still data in rows 6 and seven. We only want to search for the last empty space after all the data is complete.

You can see in the example sheet above that if we search through column "A" that row 2 is blank, but the last blank column is Row 13.

The Code

/************************************************************************

* Gets the last row number based on a selected column range values

* @param {array} range : takes a 2d array of a single column's values

* @returns {number} : the last row number with a value.

function getLastRowSpecial ( range ) {

var rowNum = 0 ;

var blank = false ;

for ( var row = 0 ; row < range . length ; row++) {

if ( range [ row ] [ 0 ] === "" && !blank){

rowNum = row;

blank = true ;

} else if ( range [ row ] [ 0 ] !== "" ) {

blank = false ;

} ;

return rowNum ;

} ;

Here, we have created a function called getLastRowSpecial() that takes a single column range of values in a 2D array.

Line 12 and 13 has 2 set variables:

rowNum: this will record the row number of an empty cell.
blank: a boolean true/false value that we will switch between when cells are not blank.

The for loop on line 14 loops through each row on our selected column.

Line 16 checks if the row item is blank and if the blank variable has not been set to false (or in English…um…true). If these two conditions are met, we register the row number in our rowNum variable and set blank to true.

Alternatively, if there is a value in the cell, we want to set blank to false.

This way if the consecutive cells are also blank rowNum will not change unless a cell with a value is found.

Once the for loop is done, it returns the final row number of the empty cell. Because our iteration starts a zero (being coding language) and our Sheet values start at one, the number returned will be the last row with data in the selected column in the sheet.

The Example

Heading back to our example lets add the getLastRowSpecial() function to some code that will simply log the last row and show the values of the data range base on this last row.

//****GLOBALS****

var SpreadsheetID = "1A7Ix7rzrhu7zBKcS7zPeU8w8c_nTae0mBHycvMUE68A" ;

var SheetName = "Projects" ;

function myFunction ( ) {

var ss = SpreadsheetApp . openById ( SpreadsheetID )

var sheet = ss . getSheetByName ( SheetName ) ;

//Select the column we will check for the first blank cell

var columnToCheck = sheet . getRange ( "A:A" ) . getValues ( ) ;

// Get the last row based on the data range of a single column.

var lastRow = getLastRowSpecial ( columnToCheck ) ;

//TEST

Logger . log ( lastRow ) ;

//EXAMPLE: Get the data range based on our selected columns range.

var dataRange = sheet . getRange ( 1 , 1 , lastRow , sheet . getLastColumn ( ) ) ;

var dataValues = dataRange . getValues ( ) ;

Logger . log ( dataValues ) ;

} ;

/************************************************************************

* Gets the last row number based on a selected column range values

* @param {array} range : takes a 2d array of a single column's values

* @returns {number} : the last row number with a value.

function getLastRowSpecial ( range ) {

var rowNum = 0 ;

var blank = false ;

for ( var row = 0 ; row < range . length ; row++) {

if ( range [ row ] [ 0 ] === "" && !blank){

rowNum = row;

blank = true ;

} else if ( range [ row ] [ 0 ] !== "" ) {

blank = false ;

} ;

return rowNum ;

} ;

When we run myFunction() we call the spreadsheet and relevant sheet tab in lines 6 and 7.

We want to use column "A" as our last row reference column so on line 10 we get the range of that column and its associated values in a 2D array with the name, columnToCheck.

Line 13 uses our new magic getLastRowSpecial() function using columnToCheck as the parameter and stores it in the lastRow variable.

We then log the last row number on line 16.

Finally, for shits and giggles, we get the data range of the sheet, adding in our lastRow variable, get the values and log it out for the whole world to see (well at least you and me).

The logged results would be as so:

[ 19-05-11 03 : 54 : 58 : 892 PDT ] 12.0

[ 19-05-11 03 : 54 : 59 : 228 PDT ] [ [ Tasks and Deadlines for Global Enrichment Group , , , , , , , , , , , ] , [ , , , , Time Remaining , , , , 2 days before deadline reminder Email , , Deadline Email , ] , [ Name , Task , Start Date , Due Date , Days , % , Completed , Reviewed and Approved , Date , Sent , Date , Sent ] , [ James Orbinski , Analyse antibiotic degradation in global communities . , Fri Mar 02 00 : 00 : 00 GMT+03 : 00 2018 , Fri Jul 12 00 : 00 : 00 GMT+03 : 00 2019 , 62.0 , 0.12474849094567404 , false , false , Wed Jul 10 00 : 00 : 00 GMT+03 : 00 2019 , false , Fri Jul 12 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Huang Yiping , Impact study on mobilizing military to plant millions of trees along China's northern desert edges . , Thu Jan 31 00 : 00 : 00 GMT+03 : 00 2019 , Wed Jun 05 00 : 00 : 00 GMT+03 : 00 2019 , 25.0 , 0.2 , false , false , Mon Jun 03 00 : 00 : 00 GMT+03 : 00 2019 , false , Wed Jun 05 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ C . Fred Bergsten , Present on the migration of SWIFT exchange transfers to crypto-currencies . , Sat Feb 02 00 : 00 : 00 GMT+03 : 00 2019 , Sun May 12 00 : 00 : 00 GMT+03 : 00 2019 , 1.0 , 0.010101010101010102 , false , false , Fri May 10 00 : 00 : 00 GMT+03 : 00 2019 , false , Sun May 12 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Thierry de Montbrial , Develop quick-response guidelines for rapid resources depletion related violence . , Tue Nov 13 00 : 00 : 00 GMT+03 : 00 2018 , Wed Sep 04 00 : 00 : 00 GMT+03 : 00 2019 , 116.0 , 0.39322033898305087 , false , false , Mon Sep 02 00 : 00 : 00 GMT+03 : 00 2019 , false , Wed Sep 04 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Loukas Tsoukalis , Monitor the impact of Brexit on the EU . , Sun Apr 22 00 : 00 : 00 GMT+03 : 00 2018 , Mon Jul 15 00 : 00 : 00 GMT+03 : 00 2019 , 65.0 , 0.1447661469933185 , false , false , Sat Jul 13 00 : 00 : 00 GMT+03 : 00 2019 , false , Mon Jul 15 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Victor Halberstadt , Proposal to provide free medicine to children rescued from child exploitation globally . , Tue Dec 11 00 : 00 : 00 GMT+03 : 00 2018 , Wed Oct 30 00 : 00 : 00 GMT+03 : 00 2019 , 172.0 , 0.5325077399380805 , false , false , Mon Oct 28 00 : 00 : 00 GMT+03 : 00 2019 , false , Wed Oct 30 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Robbert Dijkgraaf , Feasibility plan for globally distributed pop-up mathematics and science education hubs for remote maker communities . , Wed Feb 28 00 : 00 : 00 GMT+03 : 00 2018 , Sun Jul 14 00 : 00 : 00 GMT+03 : 00 2019 , 64.0 , 0.1277445109780439 , false , false , Fri Jul 12 00 : 00 : 00 GMT+03 : 00 2019 , false , Sun Jul 14 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Sergei Guriev , Paper on strategies for delpoying global social services and their transfer from privatisation . , Sun May 05 00 : 00 : 00 GMT+03 : 00 2019 , Thu Dec 19 00 : 00 : 00 GMT+03 : 00 2019 , 222.0 , 0.9736842105263158 , false , false , Tue Dec 17 00 : 00 : 00 GMT+03 : 00 2019 , false , Thu Dec 19 00 : 00 : 00 GMT+03 : 00 2019 , false ] , [ Canan Dağdeviren , Finalize rapid construction of piezoelectric biometric reader factories . , Tue Apr 09 00 : 00 : 00 GMT+03 : 00 2019 , Thu Jun 06 00 : 00 : 00 GMT+03 : 00 2019 , 26.0 , 0.4482758620689655 , false , false , Tue Jun 04 00 : 00 : 00 GMT+03 : 00 2019 , false , Thu Jun 06 00 : 00 : 00 GMT+03 : 00 2019 , false ] ]

Conclusion

Would I use this to get the data range on a small dataset? Probably not, but more often than not we are working with larger ranges of columns and rows and this is when I whip out my getLastRowSpecial() function.

I also use this function when I want to find the first empty space at the end of a data set to paste in values, but I have hidden formulas and tick boxes in the data.

What do you think you would use it for? Have you got a better alternative? Let me know in the comments below.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro.

Got a more specific problem you need help with, but don't have the time to develop the skills? I can connect you with a trusted freelancer . I have a team of hand-picked experts ready to help.

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

**Obviously the data in the Google Sheet is a work of fiction.