Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Mar 2022]

Google Sheets get last row with hidden formulas and checkboxes with GAS

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.

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 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.

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.

Hire a Google Workspace Developer for your Business Needs

The Code

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:

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.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

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” row 2 is blank, but the last blank column is Row 13.

The Code

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.

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:

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:

Google Apps Script get last row in Google Sheets benchmark test

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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


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

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Further Reading

Changelog

  • 23 Feb 2022 – Add further reading section and table of contents. 
  • 04 Mar 2022 – Added an extra solution (Solution 1) that would improve performance and also added a chapter containing benchmark results on performance between Solution 1 and 2. Thanks to Rubens M Cury for the suggestion. 

39 thoughts on “Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Mar 2022]”

  1. Simply marvelous and thank you. I’ll may have to modify it a little.
    I am using it to add rows to last blank row in the scanned column or to append a row if there are no “spares”.

    I need the functions behavior to be slightly different.

    Let me ask you:
    What is expected behavior when there are no blank cells in the range? Might it be better returning the number of the last row anyway, instead of 0?

    Reply

  2. I edited the return line to accommodate my need. Now returns the last row number with data even if there are no blank cells in supplied range: return (rowNum == 0)?range.length:rowNum;

    Reply

    1. @Matt: What if all rows are blank – your tweak would still report the total number of rows – which is not expected, right? You could check on value of ‘blank’ to do what you wanted to achieve.

      Created modified function using Yagi’s solution – which considers range containing multiple rows and/or columns:
      function getLastRowSpecial(range){
      // Extract last row number of a given range having non-missing value, 0 if all rows are missing
      var rowNum = 0;
      for(var col=0; col < range[0].length; col++){
      var blank = false;
      for(var row = rowNum; row < range.length; row++){

      };
      // if no blank cases are found then extracting the number of rows — as all rows are filled with some value
      if (!blank)
      rowNum = range.length
      return rowNum;
      };

      function getLastColSpecial(range){
      // Extract last column number of a given range having non-missing value, 0 if all columns are missing
      var colNum = 0;
      for(var row = 0; row < range.length; row++){
      var blank = false;
      for(var col = colNum; col < range[0].length; col++){

      };
      // if no blank cases are found then extracting the number of columns — as all columns are filled with some value
      if (!blank)
      colNum = range[0].length
      return colNum;
      };

      Reply

  3. You write column but probably mean row, correct?

    Reply

    1. Hi Saaibo,

      I’m not too sure which part of the tutorial you are referring to. Could you provide me a snippet, please?

      Cheers,

      Yagi

      Reply

  4. How can I go to second last row in a google sheet.

    Reply

    1. Hi Farheen.

      Add -1 to the returned row number.

      Reply

  5. I need to count the number of cells starting from row 28 to the next blank row is it possible to do this using the above? I need to copy this range to another sheet, however, I’m struggling to figure out how to specify the number of rows as these are variable depending on the department data I am using.

    Thanks for your help.

    Reply

    1. Hi Fazila,

      Yes, you can. Take a look at the example towards the end of the tutorial. In the variable dataRange you can see that we use the getRange() method. The first argument for hte getRange() method is the row start number.

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

      You could change this directly by adding 28 (not all that advisable):
      var dataRange = sheet.getRange(28,1, lastRow-28, sheet.getLastColumn());

      You could set the row to a variable that you could put at the top of your function for easy reference:
      var startRow = 28;
      var dataRange = sheet.getRange(startRow,1, lastRow-startRown, sheet.getLastColumn());

      This will get your range for the data you wish to paste in.

      You could also use a very similar function to the getLastRowSpecial to find a start row location based on the data in a certain column. Say, if I have a list of courses in col A, then I could iterate through the courses until I find that course name.
      Cheers,

      Yagi

      Reply

  6. Thanks Yagi you’re right it would be better to find the start row location based on data entered, however the data entered is a question and in a merged cell would that make a difference?

    I’m setting up a form in sheets and trying to set up some code that will copy the responses to a master sheet when the team leader clicks on submit. However, different departments have different staff members so the table will be bigger for some departments than others which is why the data range will always change.

    Thanks

    Reply

    1. Hi Fazila,

      Once you got the last value you could loop down the rows again from your lastRow location for say, 10 rows and check each row with isPartOfMerge() method. This method will be TRUE if the cell is part of the merge. If isPartOfMerge() === false, then get the count number, subtract 1 and add it to your lastRow value.

      Reply

  7. Thanks Yagi I managed to put the script together to up the range starting from value in a cell. Thanks for all your help.

    Reply

  8. Hi Yagi, This helped and worked well , and solved my issue. Thanks Kind Regards, Abid

    Reply

  9. Hi!
    First thanks for this valuable tutorial. Second I’m facing the problem TypeError: Cannot read property ‘length’ of undefined (line 35, file “new function test”).
    What to do now?

    Reply

    1. Hi Ishmaria,

      It is difficult for me to determine your error. You could paste your code along with the error and line and I or someone from the community may be able to assist you.

      Cheers,
      Yagi

      Reply

  11. Hi Yagi,

    Thank you for your amazing code and tutorial! I’ve been trying to utilize this getLastRowSpecial function and change it to fit in with the copyRow function, but couldn’t find a way to get it to work!

    I keep getting this error ” TypeError: pasteDestination.getLastRowSpecial is not a function
    at onEdit(Code:66:52)”

    Basically, what i’m trying to do is to copy a row from sheet “leads” to sheet “confirmed” after I enter data into column 5 of sheet “leads”. I have checkboxes in sheet “confirmed” so I know I can’t use the typical getLastRow() function. I believe your getLastRowSpecial() is the answer but not sure how to incorporate with what I already have.
    Please review and point me to the right direction. Thank you very much.

    Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1XnLAoN7_gaUpKAlq0E19w6E1WHGggamwNe-0puYNyp0/edit?usp=sharing

    Here’s what I have so far for the script

    /*
    **** Move a row onEdit determined by a specific Cell value***
    */

    // Names of sheets
    var sourceSheet = “LEADS”
    var destinationSheet = “CONFIRMED”

    /* col: the column to watch,
    * changeVal: what value you want to change,
    * del: do you want to delete after the change?
    */
    var check = {
    “col”:6,
    “changeVal”: “Yes”,
    “del”: false
    };

    /* What you want to paste into the other sheet.
    * start: start column
    * cols: how many columns you want to copy
    */
    var pasteRange = {
    “start”: 5,
    “cols”: 6
    };

    function onEdit() {

    function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row++){

    };
    return rowNum;
    };

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet()

    if(sheet.getName() === sourceSheet){
    //Get active cell
    var cell = sheet.getActiveCell();
    var cellCol = cell.getColumn();
    var cellRow = cell.getRow();

    };
    };

    Thank you very much for your time and guidance.

    Reply

    1. Hi Yagi,

      Please disregard my question. I have figured it out. I just need to remove “pasteDestination.” from “pasteDestination.getLastRowSpecial(colmnToCheck) + 1”.

      Thanks for providing all of these incredible knowledge.

      Reply

  12. Hi Yagi. Thank you for your tutorial. I was just curious, what if I want to get the entire row for the empty cells. Is this possible with the code above?

    Thanks.

    Reply

    1. Hi Ramin,

      I’m not 100% clear on what you mean. Are you looking for the entire number of rows that include all trailing empty rows?

      Cheers,
      Yagi

      Reply

  13. Your tutorial showed up on my search “How to get the value of last non-blank cell using a Google Sheets api”.

    I’m not a scrip programmer, but have been successful in using the Google Sheets api to append data. My sheet has only 3 columns: A & B with appended data, C with in-cell formula just to SUM the last 7 inputs of data in B. I want to retrieve the last result in C at a given moment. Can I use the standard Google Sheet “GET” such as
    HTTP request GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}
    but I’m unsure what to put in {range}

    Thanks for any guidance you might offer.

    Reply

  14. Hi Yagi,

    When i use your function to get the lastRow, on a self refreshing sheet (pulling data from an external source), i still get rowNum = 0. It does not seem to work if the data has usedRange though the row cells may appear blank. JFYI, the range of this sheet may reduce or increase depending upon external source data.

    Reply

  16. Hey Yagi,

    I came across this great tutorial while trying to figure out how to copy/paste data from one sheet into another, while ignoring column A in the destination sheet. I have a static list of ID numbers there and all the other solutions I tried would only paste the data into the last row that had a value (including all the values in column A). So the paste would happen way down the sheet. This tutorial seems like it could help me, and I have used the functions here to find the last row in column B that has a value in it, so far that’s great. I just can’t figure out how to now paste my copied data into that cell. I’m hoping you can help. Here’s the code I have for copying the data, followed by the code from your tutorial to find the last row in B that has a value. Thanks in advance for any help!

    // in this example “data” = the data copied from the first sheet. I need it to be pasted into the cell defined by “lastRow”

    function myFunction() {
    var sss = SpreadsheetApp.openById(‘1dp5s8G9vFF5LzF5mAaY-JoySLGekh5UKHwt6jFcGOnA’); //origin sheet
    var ss = sss.getSheetByName(‘Form’);
    var range = ss.getRange(‘A8:H’);
    var data = range.getValues();

    //Logger.log(lastRow);

    };

    function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row++){

    };
    return rowNum;
    };

    Reply

  17. Hi Yagi,

    I am trying to use the code from this great tutorial to copy/paste data from one sheet to another, starting in the first empty row in column B (I have a static column of ID numbers in column A). So I have used your tutorial to get the last row containing a value in column B, no problem. I am now just struggling to figure out how to use that to paste the data that I have copied into that cell. Can you offer any advice? I am pasting the code I have so far below. Basically I need to setValue(data) at the cell found by your tutorial (“lastRow”) Thanks!

    function myFunction() {
    var sss = SpreadsheetApp.openById(‘1dp5s8G9vFF5LzF5mAaY-JoySLGekh5UKHwt6jFcGOnA’); //Origin Sheet
    var ss = sss.getSheetByName(‘Form’);
    var range = ss.getRange(‘A8:H’);
    var data = range.getValues();

    //Logger.log(lastRow);
    };

    function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row++){

    };
    return rowNum;
    };

    Reply

    1. Hey drewid79,

      You’ll need to create a new range, say, const lastRowVal = ss.getRange("B"+ lastRow).getValue() . This will grab the data in that cell. Then you will need to get the cell range in your destination range where you want to paste your data with ts.getRange( whatever location you want to put in ) then use setValue(lastRowVal) .

      You can find a greater explanation of these in the doc. See if you can figure it out from the hints above and lmk if you get stuck.

      Reply

  19. Honestly I believe it’s much better practice, faster and easier using the property DOWN accessed via the interfaces SpreadsheetApp.Direction – which is made exactly for this purpose.

    Reply

    1. Hi Rubens,
      This is definitely not a solution that I considered. I love it!

      The only issue that may arise when using this approach is if your dataset contains empty rows before that last cell containing data ( Which I have come across quite often in my work ).

      I’ve added an extra solution to the tutorial and cited you for your suggestion in the changelog.

      Thanks,

      Yagi

      Reply

      1. I’ve been using a different version of this but going up from the “last row”.

        Assuming you’re looking at Col A:
        sheet.getRange(sheet.getLastRow(),1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

        That takes care of empty cells in the middle of the column.

        I wonder if there’s much of a difference in speed.

        One caveat is it doesn’t work well with some arrayformulas.
        e.g. you need to change something like this:
        =arrayformula(if(len(A:A),”something”,””))
        …
        to something like this:
        =arrayformula(if(len(A:A),”something”, )) <– note the lack of the double-quotes

        Reply

        1. One thing to watch here is the condition where there is no data in column A (or our target column) but data in the rest of the row.

          Reply

  20. Hello Yagi,
    I stumbled recently on your tread about google app script and hope finding the solution for the script code resolving. Would like also to mention that REALLY novice in all script thing. Here we go:
    Database sheet have several cells in the row with data validation ( Col A = Place, Col B = Date, Col J = Initials and columns K,L,M,N and O have checkboxes.
    Following the comments above would like to mention that sheet doesn’t have any empty row and as been used on 24/7 basis is there any concerns about the l sheet length to have?
    When code is executed the range shown became the last row ( first one without any formatting) and when formatting is excluded script work’s like a charm.
    the code is as follows:

    function onOpen(e) {
    var spreadsheet = e.source;
    var sheet = spreadsheet.getActiveSheet();
    var lastRow = spreadsheet.getLastRow();
    if (sheet.getMaxRows() == lastRow) {
    sheet.appendRow([“”]);
    }
    lastRow = lastRow + 1;
    var range = sheet.getRange(“D” + lastRow + “:D” + lastRow);
    sheet.setActiveRange(range);
    }

    Can you please provide some inputs in order to solve the issues.

    P.S As mentioned above being novice the code shown was found on YT and not written by myself.

    Best regards
    Pedja

    Reply

    1. Hi Pedja,

      You can find the limits of Spreadsheet ranges here.

      I’m not 100% sure that I understand your problem, sorry.

      Looking at your script you are setting the active range to the bottom of the sheet navigating you to that new row. If the sheet rows are full, it will create a new empty row and then navigate you to that.

      Are you finding the formatting and data validation missing when a new row is created?

      ~Yagi

      Reply

      1. Hello Yagi and thank you for the quick reply.
        Let me.try to rephrase:
        Les imagine that I have row 1 with titles and rows 2,3,4 and 5 with inputs. ( all rows have formating in columns A,B,H and G). Theny first row without any input is 6 but formatting goes till row 1000. When the script execute. My first empty row is 1001 and would like it to be 6( first formated row but without inputs.
        Hope it’s clear this time

        Reply

        1. Okay got it. You can still use the solutions in this tutorial. Just choose a column like C, D, E, F or G that does not have formatting or hidden formulas as the range that you want to get the last row in.

          Then once you have the last row, you can use, getRange(lastRow, column start, 1, column width).setValues(your row values as a 2d array) to set the values. Let’s say your columns A, B and G all have formulas in them, then you would only setValues to the range from C to G because your formulas will update the rest. IF they just have formula then setValues will not remove the formatting.

          Reply

  21. Thanks Yagi, This code have saved me lot of time and hassale. I like your coding style, it is simple and to the point, as comapre to other sources.

    Reply

    1. Hi Preeti, Thank you for your kind words. I’m glad you found my coding style easier to understand.

      Happy coding!

      ~Yagi

      Reply

Leave a Reply