Google Script - get last row of specific column

问题:

I'm looking for a way to find the last row (with data) of specific column. Let's say column A last data item in row 100 and column B has last data item in row 50.

.getLastRow(); will always return 100 to me.

I need a simple way to get 50 instead (which is the row number of last non empty row in column B).

I can't use loops. It's very important as there are thousands of rows, it will simply take too much time.

I've googled a lot, but all answers involve looping.

回答1:

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;

回答2:

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.

回答3:

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

回答4:

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