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('')
}
}