How to use split with a column where each cell's integer is stored as an array-CodePudding

I am trying to get a script going in google sheets that takes data from a specific column (this column is quantity, and each integer is separated by a comma.

I need to use the split function to ignore the commas and get the sum of all numbers in a cell, but I also need them stored in some form of array in order to iterate through the entire column (in my instance its column C)

I have been looking for answers of how to achieve this but none seem to have both the split function while also getting the sum for each cell.

Any insight would be greatly appreciated. Thanks

CodePudding user response：

To get you started with Google App Script here is a simple example.

A few notes on my coding style. I rarely use global variables and I try to limit the scope of variables within my function by using let.

I always use a try{} catch(){} block to display any error messages, whether it is server side or client side dialog or web app.

The before total below coerces total to be a number.

Code.gs

function test() {
  try {
    // I just run this from the script editor but it could be assigned to a menu option
    // get the active spreadsheet
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    // get Sheet1
    let sheet = spread.getSheetByName("Sheet1");
    // get the values on the sheet as a 2D array 
    // [["Values","Sum"],["1,2,3",""],["4,5,6,7,8",""],["9,10",""]]
    let data = sheet.getDataRange().getValues();
    data.shift(); // remove the headers
    // now loop through each row of values and sum up the values
    // I'm using the arrow version of a javascript function
    let results = data.map( row => {
        let values = row[0].split(",");  //  split the comma delimited string
        let sum = values.reduce( (total,value) =>  total parseInt(value) );  // note the   before total
        return [sum];
      }
    );
    // now we put into the second column
    sheet.getRange(2,2,results.length,1).setValues(results);
  }
  catch(err) {
    console.log(err);
  }
}

References

CodePudding user response：

you can use reduce method, see below :

myfunction(severalRange){
  var result =0
  for(var cell in severalRange){
    var value = severalRange.getValue(); // sample value is  C1: "1,2,3,10" C2 : "1"
    var arrayValue = value.split(",");// [ '1', '2', '3', '10' ]
    arrayValue = arrayValue.map( item =>parseInt(item)) //[ 1, 2, 3, 10 ]

    // Use reduce method of array, see : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/Reduce
    // in other word : itterate over an array, starting at initial = 0 and do previous value   currentValue 
    const initialValue = 0;
    const sumWithInitial = arrayValue.reduce(
      (previousValue, currentValue) => previousValue   currentValue,
      initialValue
    );
  
    result = result   sumWithInitial
    console.log(sumWithInitial); // 16 then 17
    }
  return result
}

Feel free to ask additional question