1

I'd like to have a timestamp which triggers its input depending on a cell containing a formula like =IF(ISBLANK(cell),"", time ), and not upon manual input.

My script is the following and is working just fine in case of manual input:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName()=='my sheet'){
  var r = s.getActiveCell();
  if( r.getColumn() == 3 && r != '') { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT+02:00", "MM/dd/yyyy hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
  }
  else if( r.getColumn() == 4 && r != '') { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT+02:00", "MM/dd/yyyy hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("F" + row.toString()).setValue(time);
  }
  }
}

Is there a way to make it depend on a formula?

  • I don't understand when exactly you want the timestamp to be added. If a particular cell is not blank now, it will probably remain not blank next minute. What is the event that should trigger adding a timestamp. – user79865 May 31 '15 at 16:04
  • @HomegrownTomato the mentioned trigger is a conditional formula which will fill the cell with its information - IF('cell 1'='cell 2',"condition validated","") – AnDou Jun 1 '15 at 9:40
1

See if this works:

function onEdit(e) {
    var cols = [3, 4];
    if (e.source.getActiveSheet().getName() !== 'my sheet' || cols.indexOf(e.range.columnStart) === -1) return;
    var t = Utilities.formatDate(new Date(), "GMT+02:00", "MM/dd/yyyy hh:mm:ss");
    e.range.offset(0, 2).setValue(isFormula(e.source.getActiveSheet(), e.range.getA1Notation()) ? t : null);
}

function isFormula(sheet, range) {
    return sheet.getRange(range).getFormula() ? true : false;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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