GAS: Finding the last column in a given row

Question

I have the following functions. The first, lastRowF, finds the last row in a column that you give it. It works perfectly.

//Finds last row in a given column
function lastRowF(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(column + "1:" + column + lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return lastRow;
}

The second, lastColF, does the same thing but for columns in a given row. Or at least it should.

//Finds last column in a given row
function lastColF(row) {
  var lastCol = SpreadsheetApp.getActiveSheet().getMaxColumns();
//  var values = SpreadsheetApp.getActiveSheet().getRange("A" + row + ":" + colLetter(lastCol) + row).getValues();
  var values = SpreadsheetApp.getActiveSheet().getRange("A2:AB2").getValues();

  for (; values[lastCol - 1] == "" && lastCol > 0; lastCol--) {}
  return colLetter(lastCol);
}

As it is, the function simply returns lastCol as it's originally declared. The for loops is exited immediately.

I've determined that values is the issue, but I cannot figure out WHY, and it's driving me mad. The array populates correctly - msgBox(values) lists all of the values in the row - but attempting to call an individual value simply returns undefined. It doesn't make any sense to me because checking the whole array and individual values in lastRowF works perfectly fine.

Range#getValues returns a 2D array. Not a 1D array. Note that you can save yourself some time by using getLastColumn() and getLastRow() instead of getMax____(), since the Last equivalents will skip the (possibly many) rows/columns which are empty across all columns/rows. At worst (formula applied to all cells in the dimension), they will take no extra time. — tehhowch
@tehhowch well damn. I'd completely forgotten that getLastColumn() exists. Since getLastRow() looks at the entire sheet I couldn't use it for the rows because the column I was checking wasn't necessarily the longest column, and trying to run getRange().getLastRow() returns the last cell in the range, regardless of whether or not it's the last cell with data. — Tock
Sheet#getLastRow and Sheet#getLastColumn both have the same behavior - they return the last element in which there is defined data. If you have an array formula filling an entire column, then that's obviously gonna affect the output of getLastRow, just like a row formula would influence getLastColumn — tehhowch

Tedinoz Tedinoz · Accepted Answer · 2018-10-20T06:48:15

I'm not sure if this assists the questioner. In any event, it is my favourite bit of code from Stack Overflow (credit to Mogsdad who in his turn adapted it from another answer to a different question. This is the power of Stack Overflow in action.)

To determine the last row in a single column - (done in 2 lines of code):

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

You can do the same thing for Columns by changing the direction of the range.

GAS: Finding the last column in a given row

1 Answers