I'm writing a google apps script to multiply the cells in each rows of columns 22 * 26, plus co-CodePudding

I have the following script which multiplies cells in 4 different columns (22,26,28 and 29) and should enter the totals in column 36 to each of the corresponding rows however I do not know how to set the values to the rows in column 36. The las line dsh.getRange(2, 36, data.length, data[0].length).setValues(netTotal); I believe to be incorrect. Your assistance is greatly appreciated. Thanks

  const dss = SpreadsheetApp.openById('1YfkGTaKsZN_QlQpR901Gxfr7sc_Hxrs3wlL0u08RUm8');
  const dsh = dss.getSheetByName("CONCRETE");
  const lr = dsh.getIndex();
  const lQ1 = dsh.getRange(2, 22, dsh.getLastRow() - 1, 17);
  
  var data = lQ1.getValues();
  var netTotal = []
  for (i in data){
    var row = data[i];
    var multiply = row[0] * row[4]   row[6] * row[7]
    netTotal.push([multiply])
    Logger.log(netTotal)
  }
   
 dsh.getRange(2, 36, data.length, data[0].length).setValues(netTotal);
}

CodePudding user response：

Sum of two products for each row

function myfunk() {
  const ss = SpreadsheetApp.openById('1YfkGTaKsZN_QlQpR901Gxfr7sc_Hxrs3wlL0u08RUm8');
  const sh = ss.getSheetByName("CONCRETE");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  vs.forEach((r,i) => {
    sh.getRange(i   2,36).setValue((r[21] * r[25])   (r[27] * r[28]));
  });
}

function myfunk() {
  const ss = SpreadsheetApp.openById('1YfkGTaKsZN_QlQpR901Gxfr7sc_Hxrs3wlL0u08RUm8');
  const sh = ss.getSheetByName("CONCRETE");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let vo = vs.map(r => [(r[21] * r[25])   (r[27] * r[28]}]);
  sh.getRange(2,36,vo.length,vo[0].length).setValues(vo);
}