Selecting the last value of a column

Question

I have a spreadsheet with some values in column G  Some cells are empty in between  and I need to get the last value from that column into another cell   Something like    LAST G2 G9999    except that LAST isn t a function

User · Answer

It looks like Google Apps Script now supports ranges as function parameters. This solution accepts a range:

// Returns row number with the last non-blank value in a column, or the first row
//   number if all are blank.
// Example: =rowWithLastValue(a2:a, 2)
// Arguments
//   range: Spreadsheet range.
//   firstRow: Row number of first row. It would be nice to pull this out of
//     the range parameter, but the information is not available.
function rowWithLastValue(range, firstRow) {
  // range is passed as an array of values from the indicated spreadsheet cells.
  for (var i = range.length - 1;  i >= 0;  -- i) {
    if (range[i] != "")  return i + firstRow;
  }
  return firstRow;
}

Also see discussion in Google Apps Script help forum: How do I force formulas to recalculate?

User · Answer

This gets the last value and handles empty values    INDEX   FILTER  H H   NOT ISBLANK H H      ROWS  FILTER  H H   NOT ISBLANK H H

User · Answer

Summary    INDEX  FILTER  G2 G   NOT ISBLANK G2 G      COUNTA G2 G      Details   I ve looked through and tried several answers  and here s what I ve found  The simplest solution  see Dohmoose  answer  works if there are no blanks    INDEX G2 G  COUNT G2 G     If you have blanks  it fails   You can handle one blank by just changing from COUNT to COUNTA   See user3280071 s answer     INDEX G2 G  COUNTA G2 G     However  this will fail for some combinations of blanks   1 blank 1 blank 1 fails for me    The following code works  See Nader s answer and jason s comment     INDEX  FILTER  G2 G   NOT ISBLANK G2 G      ROWS  FILTER  G2 G   NOT ISBLANK G2 G           but it requires thinking about whether you want to use COLUMNS or ROWS for a given range   However  if COLUMNS is replaced with COUNT I seem to get a reliable  blank-proof implementation of LAST    INDEX  FILTER  G2 G   NOT ISBLANK G2 G      COUNT  FILTER  G2 G   NOT ISBLANK G2 G            And since COUNTA has the filter built in  we can simplify further using   INDEX  FILTER  G2 G   NOT ISBLANK G2 G      COUNTA G2 G      This is somewhat simple  and correct  And you don t have to worry about whether to count rows or columns  And unlike script solutions  it automatically updates with changes to the spreadsheet   And if you want to get the last value in a row  just change the data range    INDEX  FILTER  A2 2   NOT ISBLANK A2 2      COUNTA A2 2

User · Answer

I was playing with the code given by  tinfini  and thought people might benefit from what I think is a slightly more elegant solution  note I don t think scripts worked quite the same way when he created the original answer        Note that this function assumes a single column of values  it will    not  function properly if given a multi-dimensional array  if the    cells that are captured are not in a single row    function LastInRange values       for  index   values length - 1  values index         amp  amp  index  gt  0  index--       return String values index        In usage it would look like this    LastInRange D2 D

User · Answer

Similar answer to caligari s answer  but we can tidy it up by just specifying the full column range    INDEX G2 G  COUNT G2 G

User · Answer

This one works for me    INDEX I I MAX  I I lt  gt      ROW I I

User · Answer

I looked at the previous answers and they seem like they re working too hard   Maybe scripting support has simply improved   I think the function is expressed like this   function lastValue myRange        lastRow   myRange length      for    myRange lastRow - 1         amp  amp  lastRow  gt  0  lastRow--          nothing to do         return myRange lastRow - 1       In my spreadsheet I then use     lastValue E17 E999    In the function  I get an array of values with one per referenced cell and this just iterates from the end of the array backwards until it finds a non-empty value or runs out of elements   Sheet references should be interpreted before the data is passed to the function   Not fancy enough to handle multi-dimensions  either   The question did ask for the last cell in a single column  so it seems to fit  It will probably die on if you run out of data  too   Your mileage may vary  but this works for me

User · Answer

Is it acceptable to answer the original question with a strictly off topic answer    You can write a formula in the spreadsheet to do this  Ugly perhaps  but effective in the normal operating of a spreadsheet    indirect  R  amp ArrayFormula max  G G lt  gt     row G G    amp  C  amp 7     G G lt  gt     gives an array of true false values representing non-empty empty cells  G G lt  gt     row G G  gives an array of row numbers with zeros where cell is empty max  G G lt  gt     row G G   is the last non-empty cell in G   This is offered as a thought for a range of questions in the script area that could be delivered reliably with array formulas which have the advantage of often working in similar fashion in excel and openoffice

User · Answer

The answer     INDEX G2 G  COUNT G2 G     doesn t work correctly in LibreOffice  However  with a small change  it works perfectly      INDEX G2 G100000  COUNT G2 G100000     It always works only if the true range is smaller than  G2 G10000

User · Answer

In order to return the last value from a column of text values you need to use COUNTA  so you would need this formula    INDEX G2 G  COUNTA G2 G

User · Answer

LAST   function is not implemented at the moment in order to select the last cell within a range  However  following your example    LAST G2 G9999    we are able to obtain last cell using the couple of functions INDEX   and COUNT   in this way    INDEX G2 G  COUNT G2 G     There is a live example at the spreedsheet where I have found  and solved  the same problem  sheet Orzamentos  cell I5   Note that it works perfectly even refering to other sheets within the document

User · Answer

I have gone through way too many of these implementations of last-row for a specific column  Many solutions work but are slow for large or multiple datasets  One of my use cases requires me to check the last row in specific columns across multiple spreadsheets  What I have found is that taking the whole column as a range and then iterating through it is too slow  and adding a few of these together makes the script sluggish  My  quot hack quot  has been this formula   ROW index sheet A2 A max row sheet A2 A   sheet A2 A lt  gt  quot  quot     -1   Example  Add this to Cell A1  to find the last row in column A  Can be added anywhere  just make sure to manage the  quot -1 quot  at the end depending on which row the formula is placed  You can also place this is another col  rather than the one you re trying to count  and you don t need to manage the -1  You could also count FROM a starting Row  like  quot C16 C quot  - will count values C16 onwards  This formula is reliably giving me the last row  including blanks in the middle of the dataset  To use this value in my GS code  I am simply reading the cell value from A1  I understand that Google is clear that spreadsheet functions like read write are heavy  time-consuming   but this is much faster than column count last-row methods in my experience  for large datasets   To make this efficient  I am getting the last row in a col once  then saving it as a global variable and incrementing in my code to track which rows I should be updating  Reading the cell every-time your loop needs to make an update will be too inefficient  Read once  iterate the value  and the A1 cell formula  above  is  quot storing quot  the updated value for the next time your function runs   Please let me know if this was helpful to you  If I encounter any issues I will comment on this answer

User · Answer

I m surprised no one had ever given this answer before  But this should be the shortest and it even works in excel           ARRAYFORMULA LOOKUP 2 1  G2 G lt  gt     G2 G     G2 G lt  gt    creates a array of 1 true 1  and 1 false 0    Since LOOKUP does a top down approach to find 2 and Since it ll never find 2 it comes up to the last non blank row and gives the position of that         The other way to do this  as others might ve mentioned  is     INDEX G2 G MAX  ISBLANK G2 G -1  -ROW G2 G  -1    Finding the MAXimum ROW of the non blank row and feeding it to INDEX       In a zero blank interruption array   Using INDIRECT RC notation with COUNTBLANK   is another option   If V4 V6 is occupied with entries   then    V18    INDIRECT  R -  amp COUNTBLANK V4 V17  1 amp   C  0    will give the position of V6

User · Answer

In a column with blanks  you can get the last value with     sort G G row G G   G G lt  gt

User · Answer

Regarding  Jon Schneider s comment  if the column has blank cells just use COUNTA     INDEX G2 G  COUNT  A   G2 G

User · Answer

So this solution takes a string as its parameter  It finds how many rows are in the sheet  It gets all the values in the column specified  It loops through the values from the end to the beginning until it finds a value that is not an empty string  Finally it retunrs the value   Script   function lastValue column      var lastRow   SpreadsheetApp getActiveSheet   getMaxRows      var values   SpreadsheetApp getActiveSheet   getRange column    1     column   lastRow  getValues       for    values lastRow - 1         amp  amp  lastRow  gt  0  lastRow--       return values lastRow - 1       Usage    lastValue  G     EDIT   In response to the comment asking for the function to update automatically   The best way I could find is to use this with the code above   function onEdit event      SpreadsheetApp getActiveSheet   getRange  A1   setValue lastValue  G         It would no longer be required to use the function in a cell like the Usage section states  Instead you are hard coding the cell you would like to update and the column you would like to track  It is possible that there is a more eloquent way to implement this  hopefully one that is not hard coded   but this is the best I could find for now   Note that if you use the function in cell like stated earlier  it will update upon reload  Maybe there is a way to hook into onEdit   and force in cell functions to update  I just can t find it in the documentation

User · Answer

to get the last value from a column you can also use MAX function with IF function        ARRAYFORMULA INDIRECT  G  amp MAX IF G G lt  gt     ROW G G       4

User · Answer

I found another way may be it will help you   INDEX  SORT  A5 D   1   FALSE    1   -will return last row  More info from anab here  https   groups google com forum  fromgroups   topic How-to-Documents if0 fGVINmI

User · Answer

function getDashboardSheet spreadsheet      var sheetName    Name     return spreadsheet getSheetByName sheetName           var spreadsheet   SpreadsheetApp openByUrl SPREADSHEET URL           var dashboardSheet   getDashboardSheet spreadsheet         Logger log  see   dashboardSheet getLastRow

User · Answer

function lastRow column     var sheet   SpreadsheetApp getActiveSpreadsheet      var lastRow   sheet getLastRow      var lastRowRange sheet getRange column startRow     return lastRowRange getValue        no hard coding

User · Answer

Actually I found a simpler solution here   http   www google com support forum p Google Docs thread tid 20f1741a2e663bca amp hl en  It looks like this    FILTER  A10 A100   ROW A10 A100   MAX  FILTER  ArrayFormula ROW A10 A100     NOT ISBLANK A10 A100

User · Answer

try this   INDIRECT  B  amp arrayformula max  B3 B lt  gt     row B3 B      Suppose the column in which you are looking for the last value is B   And yes  it works with blanks

User · Answer

Found a slight variation that worked to eliminate blanks from the bottom of the table   index G2 G COUNTIF G2 G   lt

[google-apps-script] Selecting the last value of a column

Examples related to google-apps-script

Examples related to google-sheets

Examples related to google-sheets-formula

Examples related to array-formulas

Examples related to custom-function