Get last row of specific column function - best so

问题:

With "lastRow" one can get the last row in a sheet. Sometimes you need the last row of a specific column. Google Apps script das not provide this function. There are around 5 questions and solutions already here on the site, but all have some specific code wrapped around it.

I took the solution what i think is the fastest in most cases - you start looking at the end of the sheet and look backwards in the column you specified - and made a clean function out of it.

So this is a contribution for beginners to copy paste this - the argument "RowNumber" is the column you want to look at as a number: 0 is A, 1 is B, 2 is C etc. The function returns the last row as a number ("4")

Because i am on day 3 as a apps script user, my question is: if this is not the fastest and cleanest way to do it, or the script does not work properly on some circumstances, please tell me. for my usecase it works perfect.

function getLastColumnRow(RowNumber, sheet) {
  var data = sheet.getDataRange().getValues();
  var numRows = data.length;

 // loop from bottom to top for last row in given row "RowNumber"
  while( data[numRows - 1][RowNumber] == "" && numRows > 0 ) {
    numRows--;
  }
  return numRows
}

回答1:

There's only one minor mistake on your code, you should use the === operator and not the ==. Because you're considering "0" values as empty. If you don't know the difference between them, try a quick search to learn more.

It's weird though that you named the variable that holds the column number as "RowNumber", why not "columnNumber"? It makes more sense to me.

Another thing that I usually try to do on all my scripts is to reduce API calls. And it's very common that after searching I'll need the data elsewhere. So, it would be good if this function accepted the data directly instead of the sheet object. Or maybe either one.

At last, it probably does not really make a big difference, but you could do one less subtraction per loop, by starting one less.

Putting my comments to practice, here's the resulting code:

function getLastRowOfColumn(columnNumber, optData, optSheet) {
  var data = optData !== null ? optData : optSheet.getDataRange().getValues();
  var row = data.length-1;
  for( ; row > 0 && data[row][columnNumber] === ''; --row ) ;
  return row+1;
}

A caveat on both our codes, we're considering blank just by checking a cell value. It might have a formula, which by definition means that the cell is not blank, that is returning an empty string. e.g.

=IF(A1="Some comparison that returns false"; "really?"; "")

回答2:

There are faster ways to do it and consuming less api data: 1) you can use binary search instead of looping potentially all rows. Make sure there are no holes in your column for that to work. 2) you can get just the column instead of the entire range by doing getRange ("a1:a") however you wi need to construct that range string based on the column you want

You can combine 1 & 2 Cheers