How can I trigger script when a cell's value is updated to certain text?

Question

I have three columns in a spreadsheet - Code(A), Status(B), Timestamp(C). (not actual structure, I just gave it as an example to illustrate the problem)

The cells in the Status column have a formula in them that changes the displayed text based on values from the Code column (ex. =IF(A1="01", "Paid", "Not paid")).

My aim is to have the script automatically insert a Timestamp in C when the status of a cell changes to "Paid".

The first code I tried was this:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var eRow = range.getRowIndex();
  var eColumn = range.getColumnIndex();
  var value = range.getValue();
  let mCell = sheet.getRange(eRow,eColumn+1);
  const date = new Date();

  if(eRow>1 && eColumn == 2 && sheet.getName() == "Sheet4" && value == "Plătit" ){
    mCell.setValue(date);
  }
}

However, I realized that this worked only if the text "Platit" was manually inserted in a cell, and not if the value displayed in that cell changed as an effect of a formula in it.

I've found no solutions to what I am attempting - how can I make the timestamp be added whenever the displayed value changes to a certain string?

Thank you for your help,

Catalin

score 0 · Answer 1 · answered Oct 12 '22 at 12:54

You need to watch column A instead of column B, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  insertTimestamp_(e);
}


/**
* Inserts a timestamp in column C when column A is manually
* edited and the new value in column B in that row is 'Plătit'.
*
* @param {Object} e The onEdit() event object.
*/
function insertTimestamp_(e) {
  if (!e.value
    || e.range.columnStart !== 1
    || e.range.rowStart <= 1
    || !e.range.getSheet().getName().match(/^(Sheet4)$/i)) {
    return;
  }
  const status = e.range.offset(0, 1).getValue();
  if (status !== 'Plătit') {
    return;
  }
  const timestampCell = e.range.offset(0, 2);
  timestampCell.setValue(new Date()).setNumberFormat('yyyy-MM-dd H:mm');
}

How can I trigger script when a cell's value is updated to certain text?

1 Answers1