I am trying to set a new calculated column based on values of two other columns. The condition is: whenever I insert new data in columns C and D, the column E should be calculated as "=C/D".

This would be the equivalent of this in pandas:

df['new']= df['C']/df['D']

but I do not know if JS supports this no-iteration feature Pandas offers

I tried this but nothing happens:

function sample(){
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet2');
  var range=sheet.getRange('E2:E20000')
  for (i=0; i<range.length; i  ){
    if (i==''){
      i.setFormula(`=${C[i]/D[i]}`);
    }
    else {
      pass
    }
  }
}

CodePudding user response：

Try this:

function sample() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName('Sheet2');
  const sr = 2;
  const rg = sh.getRange(2,1,sh.getLastRow() - sr   1,5);
  const vs = rg.getValues();
  let cold =vs.map((r,i) => {
    r[4] = `${"=C"   Number(i   sr)   "/D"   Number(i   sr)}`;
    return [r[4]];
  });
  sh.getRange(sr,5,cold.length,cold[0].length).setFormulas(cold).setNumberFormat("#,##0.00");
}

setFormulas

setNumberFormat

CodePudding user response：

You can setFormula over the entire column like this:

var range=sheet.getRange('E2:E20000')
range.setFormula("C2/D2")

This is similar to setting conditional formatting. The top left of the range is all that matters. Everything else is relative. See my answer here.