Determining the last row in a single column

问题:

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

回答1:

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.

回答2:

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

回答3:

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

回答4:

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

回答5:

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

};

回答6:

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

回答7:

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

回答8:

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

回答9:

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

回答10:

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

回答11:

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.
 */

回答12:

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

回答13:

Update from Mogsdad solution,

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

回答14:

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

回答15:

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

回答16:

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.