2

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);
    }
1
  • 1
    Welcome to Web Applications. The script is not complete and there are missing details like if this is a simple or a installable trigger, a description of what you tried and / or where did you get stuck. Oct 21, 2020 at 17:44

1 Answer 1

2

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
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.