javascript - Another cell's data into current cell w/ additional data

Question

I want to input numbers, and let sheets calculate the change percentage in the same cell. For example:

Oct 20	Nov 20	Dec 20
90	100 (+10%)	95 (-5%)

Answer 1

Explanation:

Clearly you need an onEdit trigger that will run some code whenever you change the value of particular cells in a specific sheet.

The following script:

• Will be activated when you edit a cell in any column except for A (which contains the starting value).

• It will calculate the percentage difference based on this formula:

  

• It will return the desired expression back to the edited cell.

Updated Solution:

function onEdit(e) {
  const as = e.source.getActiveSheet(); // get active sheet
  const rng = e.range;
  const row = rng.getRow(); // get edited row
  const col = rng.getColumn(); // get edited column
  const new_val = rng.getValue(); // get new value
  if (as.getName() == "Sheet1" && col > 1 && row > 1){
    if(new_val!=''){
      let old_val = as.getRange(row,col-1).getValue();
      if(col>2){
        old_val = parseInt(old_val.substr(0, old_val.indexOf(' (')));
      }  
      let c = Math.round(100*(new_val-old_val)/old_val);
      rng.setValue(`${new_val} (${c}%)`);  
    }
    else{rng.clearContent();}
  }
}

How to use it:

Copy and Paste the code to the script editor. Click on save and then the script will be activated upon edits on "Sheet1" ( remember to change the name in the code to the name of your sheet), and on columns B onwards. You must not execute manually this function, this is a trigger function and it is executed by itself.

Illustration: