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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Old thread but I have found a simple way that seems to work ws getRange  quot A2 quot   getNextDataCell SpreadsheetApp Direction DOWN  getLastRow

User · Answer

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  gt  0   i--       if  data i  0     null  amp  amp  data i  0                return i 1

User · Answer

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

User · Answer

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

User · Answer

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  gt  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  lt  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

User · Answer

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

User · Answer

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         amp  amp  iLastRow  gt  0  iLastRow--       return iLastRow

User · Answer

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  quot A1 A quot  lrow  getValues    const Alast    lrow - Avals reverse   findIndex c  gt c 0

User · Answer

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    amp    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   lt Text No text gt     on adjacent rows   You said there are no gaps so the pairing  lt No Text Text gt  is not tested as it s irrelevant     If the logic finds  lt No Text No Text gt  then it looks back up the sheet  if it finds  lt Text Text gt  it looks further    down the sheet   I think you ll find this is quite fast  especially on datasets  gt  100 000 rows

User · Answer

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 lt  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

User · Answer

An update of Mogsdad s solution      var Avals   ss getRange  quot A1 A quot   getValues        var Alast   Avals filter function r  return r 0  length gt 0

User · Answer

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

User · Answer

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  gt  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  gt  0        if  sheet getRange 1  1  sheet getLastRow    sheet getLastColumn    getCell row index  c  getValue                 return c        break

User · Answer

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

User · Answer

var Direction SpreadsheetApp Direction  var aLast  ss getRange  A   ss getLastRow   1   getNextDataCell Direction UP  getRow      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

User · Answer

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

User · Answer

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

[google-apps-script] Determining the last row in a single column

Examples related to google-apps-script

Examples related to google-sheets