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

personally I had a similar issue and went with something like this:

function getLastRowinColumn (ws, column) {
  var page_lastrow = ws.getDataRange().getNumRows();
  var last_row_col = 0
  for (i=1; i<=page_lastrow;i++) {
    if (!(spread.getRange(column.concat("",i)).isBlank())) {last_row_col = i};
  }
  return last_row_col
}

It looks for the number of rows in the ws and loops through each cell in your column. When it finds a non-empty cell it updates the position of that cell in the last_row_col variable. It has the advantage of allowing you to have non-contiguous columns and still know the last row (assuming you are going through the whole column).

Answer 2

I've used getDataRegion

sheet.getRange(1, 1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow()

Note that this relies on the data being contiguous (as per the OP's request).

Answer 3

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 4

For very large spreadsheets, this solution is very fast:

function GoLastRow() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A:AC').createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
  var rg = spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria).getRange();

  var row = rg.getNextDataCell (SpreadsheetApp.Direction.DOWN);  

  LastRow = row.getRow();

  spreadsheet.getActiveSheet().getFilter().remove();

  spreadsheet.getActiveSheet().getRange(LastRow+1, 1).activate();

};

Answer 5

Update from Mogsdad solution,

var Avals = ss.getRange("A1:A").getValues();
var Alast = Avals.filter(function(r){return r[0].length>0});

Answer 6

This may be another way to go around lastrow. You may need to play around with the code to suit your needs

    function fill() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('a1').activate();
      var lsr = spreadsheet.getLastRow();
      lsr=lsr+1;
      lsr="A1:A"+lsr;

      spreadsheet.getActiveRange().autoFill(spreadsheet.getRange(lsr), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};