Determining the last row in a single column

Question

I have a sheet with data in cols A through H.

I need to determine the last row in column A that contains data (it's all contiguous - no gaps in the data/rows).

There is also data in the other columns that have more rows of data than column A, so I need to isolate only column A. (And/or just a range within col A).

I can do this on the spreadsheet level using

=COUNTA(A2:A100) 

However in all of my researching for a Google Apps Script solution, all I seem to find are requirements to perform multiple functions encompassing dozens of lines of code - including plenty of i++ stuff... Which I could do less complexly via offsetting directly from A1.

Is there possibly a column-specific way of modifying this method?

var aLast = ss.getDataRange().getNumRows(); 

If a convoluted process is what is required, then so be it. But I find it difficult to imagine (and even more difficult to find!) a simpler solution.

Does anyone care to enlighten me (or pop my bubble)?

Answer 1

How about using a JavaScript trick?

var Avals = ss.getRange("A1:A").getValues(); var Alast = Avals.filter(String).length; 

I borrowed this idea from this answer. The Array.filter() method is operating on the Avals array, which contains all the cells in column A. By filtering on a native function's constructor, we get back only non-null elements.

This works for a single column only; if the range contains multiple columns,then the outcome of filter() will include cells from all columns, and thus be outside the populated dimensions of the range.

Answer 2

This will get the last row in a sheet assuming based on column A.

function getLastDataRow(sheet) {   var lastRow = sheet.getLastRow();   var range = sheet.getRange("A" + lastRow);   if (range.getValue() !== "") {     return lastRow;   } else {     return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();   }               } 

This fixes @mrityunjay-pandey partially-correct answer.

To extend this answer to get the last row and column, we can use:

function columnToLetter(column) {   var temp, letter = '';   while (column > 0) {     temp = (column - 1) % 26;     letter = String.fromCharCode(temp + 65) + letter;     column = (column - temp - 1) / 26;   }   return letter; }  function letterToColumn(letter) {   var column = 0, length = letter.length;   for (var i = 0; i < length; i++) {     column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);   }   return column; }  function getLastDataColumn(sheet) {   var lastCol = sheet.getLastColumn();   var range = sheet.getRange(columnToLetter(lastCol) + "1");   if (range.getValue() !== "") {     return lastCol;   } else {     return range.getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();   }               }  function getLastDataRow(sheet) {   var lastRow = sheet.getLastRow();   var range = sheet.getRange("A" + lastRow);   if (range.getValue() !== "") {     return lastRow;   } else {     return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();   }               }  function run() {   var sheet = SpreadsheetApp.getActiveSheet();   var [startRow, lastRow] = [2, getLastDataRow(sheet)];   var [startCol, lastCol] = [1, getLastDataColumn(sheet)]; }