Get last row Google Sheets script

Last Updated on 2022-03-04 by Yagi

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.

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. 🤷‍♂️🐐

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:

In the gif above, you can see 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.

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.

Solution 1

This first solution is the fastest by far but it does have one issue which I will cover at the end. Depending on your use case you can decide which solution to use.

You might know in Google Sheets that you can get to the bottom of a range by using the Ctrl + ↓ for PC or ⌘ + ↓ for Mac. You can do the same programmatically by using the getNextDataCell() method from a range.

The getNextDataCell() method allows you to get the last cell containing data of a contiguous range in a given direction. Where contiguous means that there are no empty cells in between. So if you have any empty rows in your range of data this is not the approach that you should choose. Otherwise, go for it it will save you some milliseconds in processing time.

The Code

/** * Gets the last row number based on a selected column range values * * @param {Object} range - start cell range of your select column you wish to get the last row of. * @param {number} range.row - row number * @param {number} range.col = column number * @returns {number} : the last row number with a value. */ function getLastRowSpecial(range) { const ss = SpreadsheetApp.getActiveSpreadsheet() const sheet = ss.getSheetByName("LastRow"); const lastRow = sheet.getRange(range.row, range.col).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow(); return lastRow };

This function takes an object as an argument. The object contains two properties:

1. range.row: The row you want to start on. This will normally be your header row.
2. range.col: The column you want to get the last row of.

In our example, we want to get the last row of column A. Our header would be row 3 and our column would be column 1. So we could run this function in our project like this:

function runsies(){ ... let range = { row: 3, col: 1 } getLastRowSpecial(range); ... }

Solution 2

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.

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” 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]]

Performance of Solutions

Without considering Solution 1’s limitations when working on rows with empty cells between data points, Solution 1 does outperform Solution 2.

I ran a benchmark test between the two solutions. In a sheet with a total depth of 1000 rows, I set a row containing data with a depth of 25, 50, 100 rows. I ran both solutions 100 times on each depth for each test and ran each test 4 times. The results are below:

Keeping in mind that performance with Google Apps Script can be quite variable, it still does appear that there is on average an 8.48% increase in performance for solution 1 over 25 rows of data, 13.54% over 50 rows of data and 13.48% increase in performance.

I expect that the difference in performance in time would add up considerably over a very deep sheet with thousands of data rows.

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? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *