How to write a custom formula/script in Google Sheets

Question

I'd like to write a function or script in Google Sheets that would allow me to enter a value in one column (A), and upon pressing enter said value would be subtracted from one column (B) and added to another column (C)? Both column B and C contain only values no formulas. I would also like the value (A) to reset after pressing enter.

ie. column B= 10 column C= 10 --> upon entering '5', 'Enter' in column A --> B should change to 5, C should change to 15, and A resets to empty field

I found a macro which can do this in excel but can't translate it to Google Sheets, and it only performed half the function (only subtracted from one column, didn't add to the third column).

Answer 1

You can do it using the onEdit function. This function automatically runs every time the sheet is edited. In code, it is checked if the edited value is column A, then perform the calculation for B & C and reset A.

Below function will perform the operation as per your requirement.

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var col = range.getColumn();
  var row = range.getRow();
  
  // Check if the edited cell is in column A and not in the header row
  if (col == 1 && row > 1) {
    var value = range.getValue();
    
    // Get the current values in columns B and C
    var bValue = sheet.getRange(row, 2).getValue();
    var cValue = sheet.getRange(row, 3).getValue();
    
    // Subtract the entered value from column B and add it to column C
    sheet.getRange(row, 2).setValue(bValue - value);
    sheet.getRange(row, 3).setValue(cValue + value);
    
    // Clear the input cell in column A
    range.clearContent();
  }
}