How to find last row of a range?

Question

When I use sheet.getLastRow(); it returns the last row which has content.

But when I use range.getLastRow(); it returns the last row of the range and not considering content in it.

Can anyone suggest a way to find last occupied cell in the range.

Answer 1

You can create a loop in the list that comes as return of getRange().getValues() checking if current position in the list is empty.

function getLastRowWithTextInARange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1,1,5,1).getValues();

  for (var i=0; i<range.length; i++){
    if (range[i][0] == ""){
      Logger.log("Last row with content in the range.");
      break;
    } else {
      Logger.log(range[i][0]);
    }
  }  
}

Answer 2

I have given a piece of code as part of a previous post which returns the populated range (for one column). You could test it as below:

function readRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');

  var values = getPopulatedRange(sheet,"B").getValues(); // assuming Col B is your range
  var lastCell = "B" + values.length;
  var lastCellValue = values.reverse()[0];

  Logger.log(values); // gives all values upto the final populated in your range
  Logger.log(lastCell); // shows the last cell in notation like: B8
  Logger.log(lastCellValue); // shows the last populated cell value

};

function getPopulatedRange(ss,column) { //Returns the range in a single column from row 1 to the last populated cell in that column
  var lastRow = ss.getMaxRows();
  var values = ss.getRange(column + "1:" + column + lastRow).getValues();
  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return ss.getRange(column  + "1:" + column + lastRow);
}

Answer 3

I stumbled across this while trying to look for a solution for myself. Unfortunately, the answers provided did not provide a solution based on what I assumed the OP wanted. Since what I was looking for was similar I went ahead and put something together. It may not be the best solution, but it addresses the OP's concerns and my own as well.

My assumptions are the following:

• You want the last row of any given range that has content.
• The range provided can have multiple columns.

Proposed Solution:

function getLastRowInRange(ss, columnsArr) { //Use numbers for columns in array
  var arr = [];

  //Find last row in each column
  for(var i in columnsArr){
    let lastRN = ss.getRange(ss.getLastRow() + 1,columnsArr[i]).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
    arr.push([columnsArr,lastRN])
  }

  //Sort and return highest count
  arr.sort(function(a,b){return b[1]-a[1]})
  return arr[0][1];

}

Example of use:

  var sh = SpreadsheetApp.getActiveSheet();  
  var tss = SpreadsheetApp.getActive();
  var namedSht = tss.getSheetByName('Name of Sheet');
  var columnsArr = [1,2,4]; //Columns A, B and D

  getLastRowInRange(namedSht, columnsArr);

How it works:

The last row with content for each column is determined and then placed into an array that gets sorted. The first entry in the array should be the highest count value and is returned.