Home > Software engineering >  Static TimeStamp and Clearing Cell based on another Cell
Static TimeStamp and Clearing Cell based on another Cell

Time:03-06

If there is any data in column H then I want Column W in that row to have a timestamp. But if the cell in H is empty I want W to be empty. I have the dynamic timestamp working, but for some reason I can't get it to clear when it's empty.

function timeStamp(e) {
  var s = SpreadsheetApp.getActive();
  var range = e.range;
  var column = range.getColumn();
  if (column == 8){
    var timestamp = Utilities.formatDate(new Date, s.getSpreadsheetTimeZone(), "hh:mm");
    var row = range.getRow();
    var value = e.value;
    var sheet = e.source.getActiveSheet();
    var r = s.getActiveCell();
   if(r.getValue() === ""){
        sheet.getRange(row, 23).setValue('')}
     else if(value !== "" && sheet.getRange(row, 23).getValue() === ''){
      sheet.getRange(row, 23).setValue(timestamp);
    }
      }
      }

CodePudding user response:

When a cell is cleared (changing the content from having a value to be a blank) e.value is undefined, not an empty string,

You might quickly fix your code by replacing

var value = e.value;

by

var value = e.value || '';

CodePudding user response:

if H then timestamp in W if !H then W is blank

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Your sheet' && e.range.columnStart == 8 && e.value) {
    e.range.offset(0, 15).setValue(new Date())
  }
  if (sh.getName() == 'Your sheet' && e.range.columnStart == 8 && !e.value) {
    e.range.offset(0, 15).setValue('')
  }
}
  • Related