1

I am trying to add a timestamp when a particular cell returns TRUE. With help from this community, I was able to modify the code below. It works perfectly if I type in TRUE. However, when a formula's result returns TRUE, it does not work. I must be doing something wrong.

Here is the code:

function onEdit() {
    var s = SpreadsheetApp.getActiveSheet();
    if( s.getName() == "Campaign" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();

    if( r.getColumn() == 17 ) { //checks the column
        var nextCell = r.offset(0, 10);
        if( r.getValue() === true)
            nextCell.setValue(new Date()).setNumberFormat('MM/dd/yyyy');
        }
    }
}
1
  • Welcome to Web Applications. Please show the formula. P.S. instead of getActiveCell() on an onEdit function usually is better to use range property from the edit event object. – Rubén May 28 at 2:22
1

An onEdit(e) only runs when the user hand edits the spreadsheet, and no events get triggered when a formula result changes.

Your description suggests that you have a formula cell in column Q in every row, and the value in that cell changes when other cells in the row are edited.

To insert a timestamp, you will need to make the onEdit(e) function read the value in the formula cell in column Q rather than the value in the cell that was edited. Try this:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Campaign'
    || sheet.getRange('Q' + e.range.rowStart).getValue() !== true) {
    return;
  }
  sheet.getRange('AA' + e.range.rowStart)
    .setValue(new Date())
    .setNumberFormat('MM/dd/yyyy');
}
2
  • Yes! this is exactly what I am trying to achieve! Thanks for the explanation! it worked perfectly. I don't have enough reputation to upvote your answer, but believe me, I clicked that button. I'll re-read this a few dozen times to wrap my head around it. But for now the sheet works! Thanks again! – Francis Wolfson May 28 at 12:01
  • thanks! I still can't upvote, but just marked as accepted. Thanks again! – Francis Wolfson May 30 at 0:48

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.