1

I'm trying to write an onEdit script where I modify values on rows in Column C, and have corresponding rows in Column D & E updated with the last non empty values from Columns A & B, see screenshot:

exmaple

I'm mainly having problems trying to figure the last non empty value, I feel like some sort of reverse FOR that counts backwards from the activeCell() value and checks for IFBLANK until a non blank value is found? See the script is prepared:

function onEdit() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var r = s.getActiveCell();
  var sh = s.getActiveSheet();

  if( r.getColumn() == 3 ) {

    var activerow = r.getRow();
    var lasttime = ???
    var lastitem = ???

    s.getRange('G' + activerow.toString()).setValue(lasttime);
    s.getRange('H' + activerow.toString()).setValue(lastitem);
  }
}
1
  • Why don't you share a doc with us, so we can have a look and see your expected outcome? Jun 21, 2017 at 12:26

2 Answers 2

1

The following piece of code does what you want.

Code

function onEdit(e) {
  if(e.range.columnStart === 3) {
    e.range.offset(0,1).setValue(getLastValue("A:A", e));
    e.range.offset(0,2).setValue(getLastValue("B:B", e));
  }
}

function getLastValue(range, e) {
  var val =  e.source.getActiveSheet().getRange(range).getValues().filter ( function (d) {
    return d != "";
  });
  return val[val.length - 2];
}

Explained

We need to be in column C for anything to happen. In column D is shown the previous last entry of column A. This is done by feeding a helper function the corresponding range (A:A) and the object of the onEdit trigger (e). It filters the values for not empty ( != "") and passed on the previous last index of the array (return val[val.length - 2]). The same is done for column E.

2
  • Thank you for the input! Unfortunately getlastvalue returns the last value of the column, i.e. it will always return 3 for column E from column B, whereas if I edited C7, I would like to return 2 for column E (since PREVIOUS not LAST) non empty cell is 2. I'm going to try another approach at organizing my data so to avoid onEdit events, but thanks again for the write up. It's very illuminating for other problems I'm dealing with.
    – diggy
    Apr 21, 2017 at 23:26
  • @diggy The script worked for finding the last. Changed the code to show the previous last. Apr 22, 2017 at 7:28
0

In D1, copied across to E1 and the pair copied down to suit:

=if($C1<>"",max(A$1:A1),"")

should give results as shown in OP, whatever is entered into ColumnC (but provided something is).

IF
MAX

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.