Get Value of Last Populated Cell in a in range for a Column for Google Spreadsheet

Question

Given a range of cells in a column, find the last populated entry and return that value.

I've got a ledger with a column where I fill in a value most days in a new row, starting from the top, going town. On days I miss, the cell is left empty. I'd like to use the last value entered but am not sure how to search for it. Logically I want to search from the bottom up looking for the first non-empty cell in the range. The range grows each day but the function should always return the bottom entry. Some days no entry is made leaving that cell empty, so it cannot just stop at the first empty cell and go back 1. It needs to find the last populated cell in the entire range.

Don't know if find() or search() can locate the first "Non-WhiteSpaceOrEmptyString", or I'd use that to look from the bottom up.

Answer 1

Created a solution myself. In Google Sheets you can create custom function. On the Tools Menu, click Script Editor. In the editor, add the following function:

function LASTPOPULATED(range2d) {
  if (!Array.isArray(range2d)) { return null; }
  for (i=range2d.length-1; i>-1; i--) {
    e = range2d[i];
    if (!Array.isArray(e) || e.length != 1) { return null; }
    v = e[0];
    if (typeof(v) === 'number' || (typeof(v) === 'string' && v.length > 0)) { return v; }
  }
  return null;
}

To use, call LASTPOPULATED(range) where range is in the cell1:cell2 format. Google Sheets ranges are always 2-dimensional, so make sure it represents a column range, such as B1:B100 or the function will return null if more than 1 column is detected before it finds a populated element.