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

I am using one single line code to get last row have data in column B (Index 2) as follow:

var tmp3 = pointDataSheet.getRange(pointDataSheet.getLastRow() + 1,2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();

Answer 2

var Direction=SpreadsheetApp.Direction;
var aLast =ss.getRange("A"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

As mentioned by lopezvit, According to the documentation, getNextDataCell is similar to go to the given range and pressing ctrl (command) + the arrow key. So this works because it goes to the las possible row, adds one (arrow down) and then ctrl + UP, so it will definitely get the last row with some content. The only thing that could be improved is to check if last cell + 1 is greater than max cell, and make an specific logic for that case.

Answer 3

Although I don't know whether this is a good method, how about this method? This method doesn't use the loops. Please check this as one of samples.

1. Retrieve the column data that you want to know the number of last row.
2. Import the column data to a new spreadsheet as a temporary sheet. (In this case, you can also add a new sheet to the spreadsheet you currently use and it can be used as a temporary.)
3. Retrieve the number of last row using getLastRow().
4. Remove the temporary spreadsheet.

Sample Script :

var col = ##; // Here, please input a column number.
var ss = SpreadsheetApp.getActiveSheet();
var coldata = ss.getRange(1, col, ss.getLastRow(), 1).getValues();
var tempss = SpreadsheetApp.create("temporary_sheet");
tempss.getActiveSheet().getRange(1,1,coldata.length, coldata[0].length).setValues(coldata);
var last_row = tempss.getLastRow(); // The number of last row
Drive.Files.remove(tempss.getId()); // In this case, the file is removed using Drive API.

Note :

In above case, the number of last row can be retrieved, even if the column has null cells. If the column has no null cells, you can retrieve the number of last row for a specific column by following script. This doesn't create a temporary sheet.

var last_row = ss.getRange(1, col, ss.getLastRow(), 1).getValues().filter(String).length;

Updated at May 19, 2021:

In this case, I would like to approach with the following 2 patterns.

1. Retrieving 1st empty cell of specific column by searching from TOP of sheet

2. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet

In order to achieve above, I think that the following 2 patterns, can be used.

1. Retrieve the values of the column and search the result using the loop.

2. Retrieve directly the result using the built-in methods of Google Apps Script.

I measured the process cost of them. As the result, it was found that the following 2 scripts are lowest of all methods.

1. Retrieving 1st empty cell of specific column by searching from TOP of sheet

Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
  const range = this.getRange(offsetRow, columnNumber, 2);
  const values = range.getDisplayValues();
  if (values[0][0] && values[1][0]) {
    return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  } else if (values[0][0] && !values[1][0]) {
    return offsetRow + 1;
  }
  return offsetRow;
};

// Please run this function.
function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const res = sheet.get1stEmptyRowFromTop(3);
  console.log(res); // Retrieve the 1st empty row of column "C" by searching from TOP of sheet.
}

• Please give the sheet object and column number.
• In this script, when 2nd argument is used, you can set the offset row. For example, when the 1st and 2nd rows are the header rows, you can use this script as const res = sheet.get1stEmptyRowFromTop(3, 2);.

2. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet

In this question, I thought that this pattern might be suitable.

Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
  const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
  return search ? search.getRow() : offsetRow;
};

// Please run this function.
function main() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const res = sheet.get1stNonEmptyRowFromBottom(3);
  console.log(res); // Retrieve the 1st non empty row of column "C" by searching from BOTTOM of sheet.
}

• Please give the sheet object and column number.
• In this script, when 2nd argument is used, you can set the offset row. For example, when the 1st and 2nd rows are the header rows, you can use this script as const res = sheet.get1stNonEmptyRowFromBottom(3, 2);.

Result:

When above script is used, the following result is obtained.

• When the script of "Retrieving 1st empty cell of specific column by searching from TOP of sheet" is used for the column "C", the row 6 is obtained.

• When the script of "Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet" is used for the column "C", the row 9 is obtained.

Reference:

• Benchmark: Process Costs for Retrieving 1st Empty Cell and 1st Non Empty Cell of Specific Column in Google Spreadsheet using Google Apps Script
  • In this report, you can see the detail data of the benchmark of each method.

Answer 4

My method takes a flat array of the column values, reverses it, searches for the index of the first defined cell.

Subtract that index from the total length of the values array to get the non reversed index, and then add any rows that may precede the column values to return the actual last row of that column on the spreadsheet.

const colValues = WORKSHEET.getRange(STARTROW, STARTCOLUMN, WORKSHEET.getLastRow(), 1)
    .getValues()
    .flat();

const getLastRowOfCol = (colValues, startRow = 1) => {
  return (colValues.length - colValues.reverse().findIndex(cell => !!cell)) + startRow - 1;
};

For my use case, I needed to get the last row of column 5 after row 13.

const ss = SpreadsheetApp.openById(mySpreadsheetId);
const ws = ss.getSheetByName('myWorkSheetName');

const colValues = ws.getRange(14, 5, ws.getLastRow(), 1)
    .getValues()
    .flat()
const colLastRow = getLastRowOfCol(colValues, 13)

Answer 5

The best solution depends on how many rows your sheet has and the type of the data. I've done a comparison and benchamark of various proposed solutions. Most work only on plain data and fail if the data has gaps, formulae, array fourmulae, importranges, local references, filter or query functions and more. Which is pretty much always.

The always accurate solution that works reasonably fast on all data types is the Reversed for + getValues() one. Use this if you want no headaches:

    // replace 'yourSheetName' and column 1 with your values
var tab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yourSheetName')
var column = tab.getRange('A:A')

// get lastFilledRow
var value = ''
const max = tab.getMaxRows()
var values = column.getValues()
values = [].concat.apply([], values)
for (row = max - 1; row > 0; row--) {
  value = values[row]
  if (value != '') { break }
}
var lastFilledRow = row + 1

If you really want a one liner and you are certain your data has no local references, use the getNextDataCell() solution, it’s fast and simple

// replace 'yourSheetName' and column 'A' with your values
var tab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yourSheetName')
var column = tab.getRange('A' + tab.getMaxRows())

// get lastFilledRow
var lastFilledRow = column.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1)

If you want to see the full analysis and benchmark results, go here

Answer 6

An update of Mogsdad's solution:

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

Answer 7

Update 2021 - Considers also empty cells

The accepted answer as well as most of the answers (if not all of them) have one common limitation which might not be the case for the owner of the question (they have contiguous data) but for future readers.

• Namely, if the selected column contains empty cells in between, the accepted answer would give the wrong result.

For example, consider this very simple scenario:

the accepted solution would give 4 while the correct answer is 6.

Solution:

Find the index of first non-empty value starting from the end of the array by using the reverse method.

const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1')
const lrow = sh.getLastRow();
const Avals = sh.getRange("A1:A"+lrow).getValues();
const Alast  = lrow - Avals.reverse().findIndex(c=>c[0]!='');

Answer 8

Old thread but I have found a simple way that seems to work

ws.getRange("A2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow()

Answer 9

This is the best way for me, get the reference column and then get the last row

var ssm = SpreadsheetApp.openById(id).getSheetByName(name);
var lastRow = ssm.getRange('A2').getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1, 0).getRow();
ssm.getRange(lr, 1, 1, 8).setValues([data]);

Answer 10

This is what worked for me:

var ss = SpreadsheetApp.openById(YourSpreadsheetID);
var main_sheet = ss.getSheetByName(YourMainSheet);
main_sheet.getRange('K16').activate(); // substitute your cell from where you want to count
main_sheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
var last_row_submissions = main_sheet.getCurrentCell().getRowIndex();

Answer 11

This worked for me:

var ss = SpreadsheetApp.openById(YourSpreadsheetID);
var main_sheet = ss.getSheetByName(YourMainSheet);
main_sheet.getRange('K16').activate(); // substitute your cell from where you want to count
main_sheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
var last_row_submissions = main_sheet.getCurrentCell().getRowIndex();

Answer 12

After a while trying to build a function to get an integer with the last row in a single column, this worked fine:

    function lastRow() {
    var spreadsheet = SpreadsheetApp.getActiveSheet();
    spreadsheet.getRange('B1').activate();
    var columnB = spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
    var numRows = columnB.getLastRow();
    var nextRow = numRows + 1; 
    }

Answer 13

I realise this is quite an old thread but it's one of the first results when searching for this problem.

There's a simple solution to this which afaik has always been available... This is also the "recommended" way of doing the same task in VBA.

var lastCell = mySheet.getRange(mySheet.getLastRow(),1).getNextDataCell(
  SpreadsheetApp.Direction.UP
);

This will return the last full cell in the column you specify in getRange(row,column), remember to add 1 to this if you want to use the first empty row.

Answer 14

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 15

I tried to write up 3 following functions, you can test them for different cases of yours. This is the data I tested with:

Function getLastRow1 and getLastRow2 will return 0 for column B Function getLastRow3 will return 1 for column B

Depend on your case, you will tweak them for your needs.

function getLastRow1(sheet, column) {
  var data = sheet.getRange(1, column, sheet.getLastRow()).getValues();

  while(typeof data[data.length-1] !== 'undefined'
     && data[data.length-1][0].length === 0){ 
    data.pop();
  }
  return data.length;
}

function test() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet6');
  Logger.log('Cách 1');
  Logger.log("Dòng cuối cùng của cột A là: " + getLastRow1(sh, 1));
  Logger.log("Dòng cuối cùng của cột B là: " + getLastRow1(sh, 2));
  Logger.log("Dòng cuối cùng của cột C là: " + getLastRow1(sh, 3));
  Logger.log("Dòng cuối cùng của cột D là: " + getLastRow1(sh, 4));
  Logger.log("Dòng cuối cùng của cột E là: " + getLastRow1(sh, 5));
  Logger.log('Cách 2');  
  Logger.log("Dòng cuối cùng của cột A là: " + getLastRow2(sh, 1));
  Logger.log("Dòng cuối cùng của cột B là: " + getLastRow2(sh, 2));
  Logger.log("Dòng cuối cùng của cột C là: " + getLastRow2(sh, 3));
  Logger.log("Dòng cuối cùng của cột D là: " + getLastRow2(sh, 4));
  Logger.log("Dòng cuối cùng của cột E là: " + getLastRow2(sh, 5));
  Logger.log('Cách 3');
  Logger.log("Dòng cuối cùng của cột A là: " + getLastRow3(sh, 'A'));
  Logger.log("Dòng cuối cùng của cột B là: " + getLastRow3(sh, 'B'));
  Logger.log("Dòng cuối cùng của cột C là: " + getLastRow3(sh, 'C'));
  Logger.log("Dòng cuối cùng của cột D là: " + getLastRow3(sh, 'D'));
  Logger.log("Dòng cuối cùng của cột E là: " + getLastRow3(sh, 'E'));

}

function getLastRow2(sheet, column) {
  var lr = sheet.getLastRow();
  var data = sheet.getRange(1, column, lr).getValues();

  while(lr > 0 && sheet.getRange(lr , column).isBlank()) {
    lr--;
  }

  return lr;
}

function getLastRow3(sheet, column) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(column + lastRow);
  if (range.getValue() !== '') {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  } 
}

Answer 16

This seems to work:

function myFunction() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('B1').activate();
    spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
    var LastDataRow = spreadsheet.getCurrentCell().getRowIndex();
    Logger.log(LastDataRow);
};

Answer 17

Never too late to post an alternative answer I hope. Here's a snippet of my Find last Cell. I'm primarily interested in speed. On a DB I'm using with around 150,000 rows this function took (average) 0.087 seconds to find solution compared to @Mogsdad elegant JS solution above which takes (average) 0.53 sec on same data. Both arrays were pre-loaded before the function call. It makes use of recursion to do a binary search. For 100,000+ rows you should find it takes no more than 15 to 20 hops to return it's result.

I've left the Log calls in so you can test it in the console first and see its workings.

/* @OnlyCurrentDoc */

function myLastRow() {

var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var colArray = ss.getRange('A1:A').getDisplayValues();  // Change to relevant column label and put in Cache
var TestRow=ss.getLastRow();
var MaxRow=ss.getMaxRows(); 

Logger.log ('TestRow = %s',TestRow);
Logger.log ('MaxRow = %s',MaxRow);

var FoundRow=FindLastRow(TestRow,MaxRow);

Logger.log ('FoundRow = %s',FoundRow);    

function FindLastRow(v_TestRow,v_MaxRow) {

/* Some housekeeping/error trapping first
* 1) Check that LastRow doesn't = Max Rows. If so then suggest to add a few lines as this
* indicates the LastRow was the end of the sheet.
* 2) Check it's not a new sheet with no data ie, LastRow = 0 and/or cell A1 is empty.
* 3) A return result of 0 = an error otherwise any positive value is a valid result.
*/

 return   !(colArray[0][0]) ? 1                   // if first row is empty then presume it's a new empty sheet
        :!!(colArray[v_TestRow][0]) ? v_TestRow   // if the last row is not empty then column A was the longest
        : v_MaxRow==v_TestRow ? v_TestRow         // if Last=Max then consider adding a line here to extend row count, else
        : searchPair(0,v_TestRow);                // get on an find the last row
}

function searchPair(LowRow,HighRow){

var BinRow = ((LowRow+HighRow)/2)|0;   // force an INT to avoid row ambiguity

Logger.log ('LowRow/HighRow/BinRow = %s/%s/%s',LowRow, HighRow, BinRow);

/*   Check your log. You shoud find that the last row is always found in under 20 hops.
 *   This will be true whether your data rows are 100 or 100,000 long.
 *   The longest element of this script is loading the Cache (ColArray)
 */

 return   (!(colArray[BinRow-1][0]))^(!(colArray[BinRow][0])) ? BinRow
        : (!(colArray[BinRow-1][0]))&(!(colArray[BinRow][0])) ? searchPair(LowRow,BinRow-1)
        : (!!(colArray[BinRow-1][0]))|(!!(colArray[BinRow][0])) ? searchPair(BinRow+1,HighRow)
        : false;   // Error
 }
}

/* The premise for the above logic is that the binary search is looking for a specific pairing, <Text/No text>
 * on adjacent rows.  You said there are no gaps so the pairing <No Text/Text> is not tested as it's irrelevant.
 * If the logic finds <No Text/No Text> then it looks back up the sheet, if it finds <Text/Text> it looks further
 * down the sheet.  I think you'll find this is quite fast, especially on datasets > 100,000 rows.
 */

Answer 18

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 19

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)];
}

Answer 20

I have changed Tanaike's answer a bit. This version creating a sheet instead of spreadsheet.

var col = 1; // Here, please input a column number, in this case it is the number of A column(1).
var ss = SpreadsheetApp.getActiveSheet();
var coldata = ss.getRange(1, col, ss.getLastRow(), 1).getValues();
var tempss = SpreadsheetApp.getActiveSpreadsheet().insertSheet("temporary_sheet");
tempss.getRange(1,1,coldata.length, coldata[0].length).setValues(coldata);
var last_row = tempss.getLastRow(); // The number of last row
SpreadsheetApp.getActiveSpreadsheet().deleteSheet(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("temporary_sheet"));

Answer 21

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 22

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);
};

Answer 23

To get the number of columns or last column's index:

var numColumns = sheet.getLastColumn()

To get the no of rows or last row's index:

var numRows = sheet.getLastRow()

where

var sheet = SpreadsheetApp.getActiveSheet()

Answer 24

I am using getDataRange() followed by getNumRows(). The first function

Returns a Range corresponding to the dimensions in which data is present

and the second function

Returns the number of rows in this range.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getActiveSheet();
var lastRow = ws.getDataRange().getNumRows();

P.S I hope this works for all cases.

Answer 25

Here's an alternative way of solving this. It uses a while loop but takes into consideration empty gaps between rows.

function getLastRow (column) {
  var iLastRow = ss.getActiveSheet().getMaxRows();
  var aValues = ss.getActiveSheet().getRange(column + ":" + column).getValues();
  var row = "";
  while(row == ""){
    row = aValues[iLastRow-1];
    iLastRow--;
  }
  return iLastRow;
}

Answer 26

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 27

You can do this by going in the reverse way. Starting from the last row in spreadsheet and going up till you get some value. This will work in all the cases even if you have some empty rows in between. Code looks like below:

var iLastRowWithData = lastValue('A');
function lastValue(column) {
  var iLastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var aValues = SpreadsheetApp.getActiveSheet().getRange(column + "2:" + column + lastRow).getValues();

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

Answer 28

I rewrote the getLastRow/getLastColumn functions to specify a row index or column index.

function get_used_rows(sheet, column_index){
      for (var r = sheet.getLastRow(); r--; r > 0) {
        if (sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getCell(r, column_index).getValue() != ""){
          return r;
          break;
        }
      }
    }

function get_used_cols(sheet, row_index){
  for (var c = sheet.getLastColumn(); c--; c > 0) {
    if (sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getCell(row_index, c).getValue() != ""){
      return c;
      break;
    }
  }
}

Answer 29

You can also use the following code:

function findTheLastRow(){
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var range = sheet.getRange("B1:B").getValues();
  var filtered_r = range.filter(String).length;

  ui.alert("Column B's last cell is number: " + filtered_r + " and its value is: " + range[filtered_r - 1][0]);
}

This script counts the amount of cells that have a value in a column, so the cells above the last cell needs to have a value in order to get the right result.

Answer 30

Although there is no straighforward formula, I can think of, it doesn't require dozens of lines of code to find out the last row in column A. Try this simple function. Use it in a cell the normal way you'd use some other function =CountColA()

function CountColA(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][0] != null && data[i][0] != ''){
      return i+1 ;
    }
  }
}