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
}
}
}
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.
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");
}