Set date when a cell has specific text

Question

I have a script that will write the date and time to column 14 the moment a cell in column 4 has the text "Assign". The problem I'm facing now is that column 14 won't change when I don't directly put "Assign" in column 4.

For example: I tell spreadsheets column 4 cell 4 =I4 and I have the text "Assign" in I4, column 14 won't display the current date time.

The moment I manually make column 4 cell 4 "Assign" Column 14 will display the date time.

The script works onEdit, may this be the problem? I am new to Google Apps Script.

function onEdit(e) {
  var sh =  e.source.getActiveSheet();
  var col = e.range.getColumn();
  var val = e.range.getValue();

  //check if sheet is 'Blad1', column edited is 'C' & value is 'assign'
  if (sh.getSheetName() === 'Blad1' && col === 4 && val === 'Assign') {
    var row = e.range.getRow();
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy     hh:mm:ss');

    //set date in column 14 for same row
    sh.getRange(row, 14).setValue(date);

  }
}

The script only works when I directly put the text in column 4. I want the script to work whenever a cell in column 4 changes to "Assign".

P.S. Spreadsheet will get new data from a program called app sheet. When app sheet changes column 4 to "Assign" I need to show current time.

I suggest you read the documentation which is excerpted here: webapps.stackexchange.com/questions/119684/… You might be particularly interested in this bullet: "Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run." — Marc
uhmm oke. in your link i only read what installable triggers cant do. But is there an other way to do it? — AndroidzZ

ross ross · Accepted Answer · 2019-06-04T13:57:35

This script will only perform setValue() if it is column 4 being updated, that's one of the things the if statement checks specifically:

if (sh.getSheetName() === 'Blad1' && col === 4 && val === 'Assign') {

If you want this to run every time the sheet is edited and val === 'Assign', all you have to do is remove the column check from the if statement and define val using getRange() rather than the event object e.range.

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var val = sh.getRange(row, 4).getValue();

  //check if sheet is 'Blad1' & value is 'Assign'
  if (sh.getSheetName() === 'Blad1' && val === 'Assign') {
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy     hh:mm:ss');

    //set date in column 14 for same row
    sh.getRange(row, 14).setValue(date);
  }
}

Set date when a cell has specific text

1 Answers