Removing Last Updated value when No data was selected from the column to check

Question

I need help with my Google Sheet script. It is working fine when showing the timestamp of "Last Updated" at col B every time a new Call Status was set at col A; but when I delete the status at col A, the timestamp remains at col B. It should also be cleared when no data is selected at col A.

Link to my Google Sheet

My Script

    var timezone = "UTC+00:00";
    var timestamp_format = "hh:mm:ss dd/MM/yyyy"; // Timestamp Format.
    var updateColName = "Call Status";
    var timeStampColName = "Last Updated";

    var sheet = event.source.getSheetByName('StatusDate'); //Name of the sheet where you want to run this script.
    var actRng = event.source.getActiveRange();
    var editColumn = actRng.getColumn();
    var index = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf(timeStampColName);
    var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
    
    if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
        var cell = sheet.getRange(index, dateCol + 1);
        var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
        cell.setValue(date);
    }

Answer 1

Explanation:

• You can simply check whether the new value in column A is blank.

• If it is blank, then set the value in column B also to blank, otherwise, set date.

• You can use a Conditional (ternary) operator to achieve your goal in one line of code.

Replace the last line:

cell.setValue(date);

with:

e.value == '' ? cell.setValue('') : cell.setValue(date);

Solution:

var timezone = "UTC+00:00";
var timestamp_format = "hh:mm:ss dd/MM/yyyy";  // Timestamp Format
var updateColName = "Call Status";
var timeStampColName = "Last Updated";

var sheet = event.source.getSheetByName('StatusDate');  //Name of the sheet where you want to run this script
var actRng = event.source.getActiveRange();
var editColumn = actRng.getColumn();
var index = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers[0].indexOf(timeStampColName);
var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;

if (dateCol > -1 && index > 1 && editColumn == updateCol) { 
  var cell = sheet.getRange(index, dateCol + 1);
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
  e.value == '' ? cell.setValue('') : cell.setValue(date); // new code
}