example of cellsI'm currently trying to add one specific column together as each cell in the column has multiple values (1, 1, 4, 6), These values need to be added together and replace the previous cells that has separated values. using the formula LEN(Trim) works for one cell when pasting it directly into the google sheet but when adding it to a function in google script it returns the entire sheet in error as #Value even though the scope is C2:C. Any thoughts on what is happening?

    function sumAll() {
      //getValues of entire range and have it be  only column C 
    // create a foreach loop that will setFormula and iterate through each cell in the column ranging from C2 to the end of C 
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var columns = sheet.getRange("C2:C");
  var lastRow = sheet.getLastRow();
  var values = rows.getValues();
 
const len =  rows.setFormula('=LEN(TRIM(C2:C))-LEN(SUBSTITUTE(TRIM(C2:C),",","")) 1');
  for(var i= 0; i<columns.length; i  ) {
        rows.setValues(len);


  }
}

CodePudding user response：

Try this:

function sumAll() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();
  let o = vs.map((r,i) => [r[2].toString().split(",").reduce((a,c,i) => (a =Number(c),a),0)]);
  sh.getRange(2,3,o.length,o[0].length).setValues(o);
}

Sheet0 (before):

Sheet0 (after):