Get the last row of a specific column function is the best solution
With "lastRow" you can get the last row on the sheet. Sometimes you want the last row of a specific column. Google Apps script das does not provide this functionality. There are about 5 questions and solutions on the site, but they all have specific code wrapped around it.
I made the solution that I think is the fastest in most cases - you start looking at the end of the sheet and look back at the specified column - and made a pure function out of it.
So this is a beginner's contribution to copy this paste - the "RowNumber" argument is the column you want to view as a number: 0 is A, 1 is B, 2 is C, and so on. The function returns the last line as a number ("4")
Since I am on the 3rd day as a script application, my question is, if this is not the fastest and cleanest way to do this, or the script does not work as expected under some circumstances, please tell me, for my use it works fine.
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
}
source to share
There is only one minor error in the code, you must use the operator ===
, not ==
. Because you consider the values "0" to be empty. If you don't know the difference between the two, try a quick search to find out more.
Strangely, although you named the variable that contains the column number as "RowNumber", why not "columnNumber"? This makes more sense to me.
Another thing that I usually try to do in all my scripts is shortening API calls. And it's very common that after searching I will need data elsewhere. Thus, it would be nice if this function took data directly instead of a sheet object. Or maybe one.
Finally, it doesn't seem to matter much, but you can do one less subtraction per loop by running less.
Putting my comments in 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 for both of our codes, we see it as simple by checking the value of the cell. It can have a formula that by definition means the cell is not empty, which returns an empty string. eg.
=IF(A1="Some comparison that returns false"; "really?"; "")
source to share
There are faster ways to do this and consume less api data: 1) you can use binary search instead of potentially looping all lines. Make sure there are no work holes in your column. 2) you can only get the column instead of the whole range by doing getRange ("a1: a"), however you need to build that range row based on the column you want.
You can combine 1 and 2 Greetings
source to share