Issue with getLastRow()

Question

I have an issue with getLastRow(). I thought this function is supposed to return the last row that has data. But since the worksheet that I am working on has a lot more vacant rows below the rows filled with data, the functions seems to go in and select the last vacant row on its execution instead of selecting the last row which has data, which may not be the same. Can some one tell me how to fix this anomaly. Is this a google script bug or am I not clearly understanding what to do? Please ask further questions if I didn't make myself clear.

Answer 1

Do you have any "whole-column" formulae in the sheet? Either array formulae, or formulae that are copied all the way down? Even if these formulae are designed to return blank cells down the bottom, getLastRow() will still catch them.

If this is the case, try:

function getLastPopulatedRow(sheet) {
  var data = sheet.getDataRange().getValues();
  for (var i = data.length-1; i > 0; i--) {
    for (var j = 0; j < data[0].length; j++) {
      if (data[i][j]) return i+1;
    }
  }
  return 0; // or 1 depending on your needs
}

Answer 2

A simpler way is

var lastRow = sheet.getDataRange().getValues().length ;

Answer 3

Here is another version of the currently selected answer. This swaps out the inner loop for an array join with no spaces between items. This joins all characters on the row. If there is a character in any position it will kick out similar to the current answer.

I did some speed tests, and while I thought the chosen answer might be faster, for some reason this seems to process faster based on my tests. I believe I found this concept a few years ago here on stack overflow, but not seeing that reference now.

function getLastPopulatedRow(sheetX) {
  var arrVals = sheetX.getDataRange().getValues();
  for (var i = arrVals.length-1; i > 0; i--) {
    if (arrVals[i].join('')){
      return i+1
    }
  }
  return 0; 
}