CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
Jason Watts
Jason Watts

Reputation: 25

Sheets Script to Clear cell only when both target cells are cleared

I got a script thanks to help of another user Marios but I required a tweak and was asked to create a new post.

The original script

function onEdit(e){

  var row = e.range.getRow();
  var col = e.range.getColumn();
  var dateTime = 4;
  var targetColumn = 9;
  var startRow = 19;
  var ws = "Tracking Sheet";
  var as = e.source.getActiveSheet();
  
  if(col === targetColumn && row >= startRow && as.getName() === ws ) 
     {
       let range = as.getRange(row,dateTime); 
       e.range.getValue() == '' ?  range.setValue('') : range.setValue(new Date());
     }
}

I tried to modify by adding an else statement but was unsucessful.

This works great for in all aspects but only for one targetColumn and I would like to have 2 targetColumns and then the script would only clear the dateTime cell if both of the targetColumns are seen to be empty or cleared.

  var row = e.range.getRow();
  var col = e.range.getColumn();
  var dateTime = 4;
  var targetColumn = 9;
  var targetColumn = 11;
  var startRow = 19;

Any help or a modification to the current script would be most welcomed,

Upvotes: 0

Views: 24

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

  • You assigned two different values to the same variable:

    var targetColumn = 9;
var targetColumn = 11;

    but that simply means targetColumn will only have the last value assigned to it, which is 11.

  • My approach would be to put the target columns into an array and then check whether both of them are empty before you delete the date value from the desired cell.

Solution:

function onEdit(e){

  var row = e.range.getRow();
  var col = e.range.getColumn();
  var dateTime = 4;
  var targetColumns = [9,11];
  var startRow = 19;
  var ws = "Tracking Sheet";
  var as = e.source.getActiveSheet();
  
  if(targetColumns.includes(col) && row >= startRow && as.getName() === ws ) 
     {
       let range = as.getRange(row,dateTime); 
       let t1 = as.getRange(row,targetColumns[0]).getValue();
       let t2 = as.getRange(row,targetColumns[1]).getValue();
       t1 == '' && t2 == '' ?
       range.setValue('') : 
       range.setValue(new Date());
     }
}

Upvotes: 1

Related Questions