CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
Gavis
Gavis

Reputation: 233

Insert or delete a timestamp based on cell value

With this script I can create automatically a timestamp in col G based on "SI" in col E.
Now I would that:
1 - When I delete an existing value "SI" in col E, the timestamp must be deleted
2 - And so, if I put a different value from "SI" in col E, the timestamp must be deleted
How could I proceed?

function onEdit(e){
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "STAMPA2" )
  if(e.range.columnStart == 5 && e.value === 'SI'){
     e.range.offset(0,2).setValue(new Date());
  }
}

UPDATE: Based on the answer of Cooper and Tanaike, I have edited the script, with this one, but the problem is that when a select "URGENTE" in col G the value in col E become "URGENTE", but if I change the value in col G with "SI" the date format is only dd/mm/yyyy and not dd/mm/yyyy - HH:MM (that I need).
How to solve?

function onEdit(e) {
  var s = e.range.getSheet();
  if (s.getName() == "STAMPA2" && e.range.columnStart == 5) {
    if (e.value == 'SI') {
      e.range.offset(0, 3).setValue(new Date());
    } else {
      e.range.offset(0, 3).setValue('');
    }
    if (e.value == 'URGENTE') {
      e.range.offset(0, 3).setValue('URGENTE');
    }
  }
}

Upvotes: 0

Views: 398

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I believe your goal is as follows.

  • When the value of SI is put to the column "E", you want to put the current date to the column "G".
  • When the value of SI is not put to the column "E", you want to delete the value of the column "G".

In this case, how about the following modification?

Modified script:

function onEdit(e) {
  var { range, value } = e;
  var s = range.getSheet();
  if (s.getName() == "STAMPA2" && range.columnStart == 5) {
    range.offset(0, 2).setValue(value === 'SI' ? new Date() : "");
  }
}

or

function onEdit(e) {
  var { range } = e;
  var s = range.getSheet();
  if (s.getName() == "STAMPA2" && range.columnStart == 5) {
    range.offset(0, 2).setValue(range.getValue() === 'SI' ? new Date() : "");
  }
}

Added:

For your updated question, how about the following sample script?

function onEdit(e) {
  var { range, value } = e;
  var s = range.getSheet();
  if (s.getName() == "STAMPA2" && range.columnStart == 5) {
    var r = range.offset(0, 2);
    if (value == 'SI') {
      r.setValue(new Date()).setNumberFormat('dd"/"MM"/"yyyy" "HH":"mm');
    } else if (value == 'URGENTE') {
      r.setValue('URGENTE');
    } else {
      r.clearContent();
    }
  }
}
  • In this sample, the date object is put as the date format of dd"/"MM"/"yyyy" "HH":"mm.
  • If you want to put the date as the string, you can also use r.setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm")) instead of r.setValue(new Date()).setNumberFormat('dd"/"MM"/"yyyy" "HH":"mm').

Upvotes: 1

Related Questions