Google Sheets – Multiply Cell Value on Edit

google sheetsgoogle-apps-script

I am trying to make a functionality that allows me to enter a value into a cell and have that value automatically be multiplied by another cell, overwriting the original entry.

For example, if Cell A2 has 5 inside it and I am entering 10 into cell C2, I would like it to automatically multiply A2 by the value I have just entered in C2 and overwrite the ‘10’ I have just written in Cell C2 by 50 which would be the answer to this calculation.

I'm still very new to script editor so below is the best I've been able to do.

function onEdit(e) {
  var Column1 = 1
  var Column3 = 3;
  if (e.range.getColumn() == Column3) {
e.range.setValue(Column1 * Column3)
} 
}

I've also tried the script below without any luck.

function onEdit(e) {
 var sheet = SpreadsheetApp.getActiveSheet();
 var dataRange = sheet.getRange("A:C");
 var data = dataRange.getValues();
   for (var i = 0; i < data.length; ++i) {
   var rowData = data[i];
   var Column1 = rowData[0];
   var Column3 = rowData[2];
   if (e.range.getColumn() == Column3) {
   e.range.setValue(Column1 * Column3)
   }
   }
   }

I appreciate your feedback.

Best Answer

  • When the value is put to the column "C", You want to put the value multiplying the value of the column "A" to the value of the column "C" to the column "C".
  • You want to achieve this using OnEdit event trigger.

If my understanding is correct, how about this modification? I modified your above script.

Modified script:

function onEdit(e) {
  var Column1 = 1
  var Column3 = 3;
  if (e.range.getColumn() == Column3) {
    e.range.setValue(e.value * e.range.offset(0, Column1 - Column3).getValue());
  } 
}
  • e.value is the put value to the column "C".
  • e.range.offset(0, Column1 - Column3) is the column "A" of the same row of the column "C" that the value is put.
  • e.range.offset(0, Column1 - Column3).getValue() is the value of column "A".

Note:

  • This is a simple modification. So please modify it for your situation.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

  • When you delete a value from the column "C", you don't want to run the script.

From your reply comment, I could understand like above. If my understanding is correct, how about this modification?

Modified script:

function onEdit(e) {
  var Column1 = 1
  var Column3 = 3;
  if (e.range.getColumn() == Column3 && e.value) {
    e.range.setValue(e.value * e.range.offset(0, Column1 - Column3).getValue());
  } 
}
Related Topic