Get last row when using tickboxes

The following Google Apps Script is a quick piece of code you can insert into your scripts when wanting to get the row number of the last piece of data in a Google Sheet column. The traditional method of 'getLastRow()' was unavailable to me at the time - solely because I was using tickboxes within another column of my Google Sheet.

The purpose of the tickboxes were to allow easy user-selection and applied to the whole column. As an un-ticked tickbox has a value of false this meant the traditional 'getLastRow()' method would get confused and return a very large number. So even though my data would stop on row 6 for instance, the tickboxes continue down the sheet.

The alternative was a Function that could be given a Column, from which it would iterate through the data within it looking for values/blanks and then return the last row number.

Get the last row number from a given column

First we need to get our data from the spreadsheet before we can pass it into the Function:

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
var columnToCheck = ss.getRange('A:A').getValues();

Next we begin the loop and there are a few things going on here:

1. First we check if the value from the cell is empty/blank AND that our blank variable is not true.

2. If the first condition is met then we set our rowNum variable equal to the number of the loop we are in (as each iteration acts as going through each row in the Google Sheet). We also set the blank variable to true because it is blank:

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

    var rowValue = columnToCheck[row][0];
    if ( (rowValue == "") && (!blank) ) {

      rowNum = row;
      blank = true;

    }

3. If the first condition is not met then the next check is if the value from the cell is just empty/blank. If it is we set the blank variable to false.

else if (rowValue != "") {

   blank = false;

}

 

File Download

Download the Get last row when using tickboxes sheet here. Please use 'File' > 'Make a copy' for your own version.