CODEWITHSUNDEEP
google-apps-script
Andy James
Andy James

Reputation: 39

scrips to subtract value in one cell from another cell

My current script adds a timestamp, i now want to add the function to subtract the value in cell 13 from that in cell 14 and put in into cell 12 at the same time it adds the date stamp

function onEdit(e) {
  
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var value = e.range.getValue();
  var sheet = e.source.getActiveSheet().getName();
  var destination = e.source.getActiveSheet().getRange(row,11);
  var destination2 = e.source.getActiveSheet().getRange(row,10);
  var num1 = e.source.getActiveSheet().getRange(row,13).getValue;
  var num2 = e.source.getActiveSheet().getRange(row,14).getValue;
  
if(col === 4 && row > 4 && sheet ==="Bayswater" && value === "Completed" && destination2.getValue() ==="" && destination.getValue() ===""){
    
  e.source.getActiveSheet().getRange(row,11).setValue(new Date(new Date().setHours(0,0,0,0))).setNumberFormat('dd-MMM-yy');
  e.source.getActiveSheet().getRange(row,10).setValue(new Date(new Date().setHours(0,0,0,0))).setNumberFormat('dd-MMM-yy');
  e.source.getActiveSheet().getRange(row,12).setValue('=num1-num2')
}

Upvotes: 1

Views: 762

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

Probably the last line should be like this:

e.source.getActiveSheet().getRange(row,12).setFormula('=(N' + row + '-M' + row + ')');

Or it was just a typo. You forgot to add () after getValue in the two lines:

var num1 = e.source.getActiveSheet().getRange(row,13).getValue(); // <-- here
var num2 = e.source.getActiveSheet().getRange(row,14).getValue(); // <-- here

If you add () you can use the last line this way:

e.source.getActiveSheet().getRange(row,12).setValue(num1-num2);

Upvotes: 1

Related Questions