CODEWITHSUNDEEP
google-apps-script
Sam White
Sam White

Reputation: 138

How can I clear a specific cell when another cell has been cleared?

I have a script for Google Docs spreadsheet where, when any cell in a specific column is populated it automatically places a timestamp in a different column on that row:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 9 ) { //checks the column
     var nextCell = r.offset(0, 3);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
     time = Utilities.formatDate(time, "Canada/Eastern", "M/dd/Y, h:mm:ss");
       nextCell.setValue(time);
   };
 };
}

The script works great. However, I'd like the script to delete that timestamp if said cell has been cleared.

Or perhaps I can put the question a bit differently: How can I clear a specific cell when another specific cell in that row has been cleared?

Upvotes: 0

Views: 1859

Answers (1)

Serge insas
Serge insas

Reputation: 46794

the code is almost the same, I simplified it a bit to make it easier to understand what it's doing.

Edit :

better code to prevent issues when just modifying a cell in col 9.

I used the parameter coming in the onEdit trigger (called e in this example), see docs for details on that.

function onEdit(e) {
  var s = SpreadsheetApp.getActiveSheet();
  Logger.log(JSON.stringify(e)+'  ------------------> value = '+e.value);
  var r = e.range;
  var nextCell = r.offset(0, 3);
  if( s.getName() == "Sheet1" && r.getColumn() == 9 ){ //checks that we're on the correct sheet & column
    if( nextCell.getValue() === '' && e.value !=null){ //is empty?
      var time = new Date();
      time = Utilities.formatDate(time, "Canada/Eastern", "M/dd/Y, h:mm:ss");
      nextCell.setValue(time);
    }else{
      if(e.value==null){ nextCell.setValue('')};  
    }
  }
}

enter image description here

Upvotes: 1

Related Questions