Google Sheets onEdit Calculate

Question

i'm trying to figure out how I could make an onEdit event for the following method.

Example: Column1 = Value I will enter to calculate Column2 (+/-) Column2 = Value based on Cell Value (+/-) Column2 based on cell value entered

I need for each row a1:b1, a2:b2 and so on this:

When I enter for example into A1(-100) and B1 have already a value of 200 it should give me the result 100 and delete the value in A1 entered. Same for A2(+100) and B1(50) should give me a result of 150 and delete value in A2.

So the final result should be B1 = 100 and B2 = 150 and so on

I tried it with this Code

function onEdit(e){
  var Column1 = 1;
  var Column2 = 2;

  if( e.range.getColumn() == Column1 && e.value){
    e.range.setValue(e.value * e.range.offset(0, Column2 - Column1).getValue());
    }
}

But this is just halfway what I need and I got stuck here now for hours. If anyone could help me to finish this would be amazing.

soMario soMario · Accepted Answer · 2020-12-23T13:39:17

Explanation:

According to your explanation, you want to add the numbers.

For example:

(-100) + 200 would give you 100.

Your current scripts lacks these two functionalities:

It does not add the values.
It does not clear the value in column A after the operation is finished.

Solution:

function onEdit(e) {
  const inputCol = 1;
  const as = e.source.getActiveSheet(); // get active sheet
  const ar = e.range;
  const row = ar.getRow(); // get edited row
  const col = ar.getColumn(); // get edited column
  if (as.getName() == "Sheet1" && col == inputCol){
      const rng = as.getRange(row,inputCol+1);
      rng.setValue(parseInt(e.value)+rng.getValue());  
      ar.clearContent();
  }
}

I assume that you want to apply this operation on a particular sheet and hence I added a condition to run the code only for Sheet1. If you don't want this behaviour, then remove it altogether. If you want to use a different sheet name then change Sheet1 to the name of your sheet.

Google Sheets onEdit Calculate

1 Answers

Explanation:

Solution: