Creating onEdit trigger for automatic calculations in Google Sheets

[liamj]

I want to set up an automatic calculation system using the onEdit function in Google Sheets. Here’s what I’m trying to achieve:

• Column A: Input values with + or - signs that I manually enter
• Column B: Contains existing values that should be updated based on Column A input

The workflow should work like this: when I type a value in Column A (like -50 or +75), it should automatically add or subtract from the corresponding Column B cell, then clear the Column A input.

Example scenario:

• B1 contains 300
• I enter -50 in A1
• Result: B1 becomes 250, A1 gets cleared
• Later I enter +25 in A2 where B2 has 100
• Result: B2 becomes 125, A2 gets cleared

I started with this code but can’t get it working properly:

function onEdit(event){
  var inputCol = 1;
  var resultCol = 2;

  if(event.range.getColumn() == inputCol && event.value){
    event.range.setValue(event.value + event.range.offset(0, resultCol - inputCol).getValue());
    }
}

This code doesn’t handle the clearing of input values and the calculation logic isn’t working as expected. Any help would be great!

[henryg]

your offset logic’s backwards. try event.range.offset(0,1).setValue(event.range.offset(0,1).getValue() + parseFloat(event.value)) then event.range.clearContent(). you’re setting the wrong cell and not clearing it after.

[emmad]

The main problem is you’re modifying the input cell instead of the target cell. I ran into the same thing building my expense tracker. Your calculation flow is backwards. Try this: javascript function onEdit(e) { if (e.range.getColumn() === 1 && e.value !== undefined) { var inputVal = Number(e.value); var targetRange = e.range.offset(0, 1); var existingVal = targetRange.getValue() || 0; if (!isNaN(inputVal)) { targetRange.setValue(existingVal + inputVal); e.range.setValue(''); } } } Key differences: I use Number() instead of parseFloat() - handles +/- signs better in my experience. Also setValue('') instead of clearContent() to avoid formatting issues I’ve hit before. Make sure Column B is formatted as numbers or you’ll get string concatenation instead of addition.

[Nova56]

Had the exact same issue! Your code’s trying to set the input cell value instead of the target cell. Here’s what fixed it for me:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  
  if (range.getColumn() == 1 && e.value) {
    var inputValue = parseFloat(e.value);
    var targetCell = sheet.getRange(range.getRow(), 2);
    var currentValue = targetCell.getValue() || 0;
    
    if (!isNaN(inputValue)) {
      targetCell.setValue(currentValue + inputValue);
      range.clearContent();
    }
  }
}

Three key fixes: use parseFloat() to handle +/- signs correctly, grab the target cell separately, and call clearContent() on the input range after doing the math. Don’t forget to save the script and refresh your sheet.