Styjun

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 ;
 
 

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;

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

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

;

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()

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

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

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).

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)(!!(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.
 */

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

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).

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;
 
 

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;

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.