Automatic Calculation in Sheets: OnEdit Function Help

Other Questions Google Sheets
1

Hey everyone! I’m stuck with a Sheets problem. I want to make an onEdit function that does math automatically. Here’s what I need:

When I type a number in column A (like -100 or +100), it should:

  1. Add or subtract that number from the value in column B
  2. Update the result in column B
  3. Clear the cell in column A

For example:

  • If A1 is -100 and B1 is 200, B1 should become 100
  • If A2 is +100 and B2 is 50, B2 should become 150

I’ve tried this code:

function updateSheet(e) {
  var firstCol = 1;
  var secondCol = 2;

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

But it’s not quite right. Can anyone help me finish this? Thanks!

2

hey scarlettturner, i think i got a solution for ya. try this code:

function onEdit(e) {
  var r = e.range;
  if (r.getColumn() == 1 && r.getValue()) {
    var b = r.offset(0, 1);
    b.setValue(b.getValue() + Number(r.getValue()));
    r.clear();
  }
}

it should do exactly what u want. lmk if it works!

3

I’ve been working with Google Sheets for years, and I’ve found that onEdit functions can be tricky. Here’s a robust solution that should work for your needs:

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

This script handles potential errors like non-numeric inputs and empty cells in column B. It’s also more efficient as it only triggers for edits in column A. Remember to set up a trigger for this function in the script editor. Let me know if you run into any issues!

4

I’ve encountered a similar issue before and can share a solution that worked for me. Here’s a modified version of your script that should accomplish what you’re looking for:

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

This script will trigger on edit, check if the edit was in column A, perform the calculation, update column B, and clear column A. Make sure to save this as a bound script in your spreadsheet and it should work automatically. Let me know if you need any clarification on how it functions.