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

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

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:

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.

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

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

Column 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:

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 your 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? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

 

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

21 thoughts on “Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes”

  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?

  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;

    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;
      };

    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

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

    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

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

    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.

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

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

    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

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

    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.

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

    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

Leave a Reply