How to split sums of array with separator with Google App Script?

user_12472644 0
Viewed 106

I got an array from a single column from google sheet like [[23],[25],[26],[89],[""],[45],[78],[62],[""],[123],[45],[68]] How can I split it with empty values [] and get sums for each sub-array like (163/185/236) or more, cuz array length may be more then 1000 including 20-50 empty values?

I modified the script from the answer. But it doesn't work in my case. I don't understand why?

function getSums(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName('TemplateTest');
    var r = sh.getRange(1, 1, sh.getLastRow());
    var arr = r.getValues(); //log: [[23],[25],[26],[89],[""],[45],[78],[62],[""],[123],[45],[68]]
    var sums = [];
    var sum = 0;
    // iterate through the array
    for (var i = 0; i < arr.length; i ++) {
         if (arr[i].length == 0 || i == arr.length - 1) {
             // empty array, push sum to array and reset
             sums.push(sum);
             sum = 0;
         } else {
        // array has values, sum them up and add to sum
             sum += arr[i].reduce((a, b) => a + b);
         }
    }
    Logger.log(sums) //log: [16345786212345]
}

Where I'm wrong and how can I fix it?

DEMO:

function getSums(){
    //var ss = SpreadsheetApp.getActiveSpreadsheet();
    //var sh = ss.getSheetByName('TemplateTest');
    //var r = sh.getRange(1, 1, sh.getLastRow());
    var arr = [[23],[25],[26],[89],[""],[45],[78],[62],[""],[123],[45],[68]]
    //var arr = r.getValues();
    var sums = [];
    var sum = 0;
    // iterate through the array
    for (var i = 0; i < arr.length; i ++) {
         if (arr[i].length == 0 || i == arr.length - 1) {
             // empty array, push sum to array and reset
             sums.push(sum);
             sum = 0;
         } else {
        // array has values, sum them up and add to sum
             sum += arr[i].reduce((a, b) => a + b, 0);
         }
    }
    console.log(sums) //log: [16345786212345]
}
getSums();

google-apps-script google-sheets javascript
2 Answers

You could take a sinple loop and add the values until you reach undefined as value, then push the sum to the result set and reset sum.

At the end push the final sum to the result set, too.

var data = [[23.0], [25.0], [26.0], [89.0], [""], [45.0], [78.0], [62.0], [""], [123.0], [45.0], [68.0]],
    result = [],
    sum = 0,
    value,
    i;

for (i = 0; i < data.length; i++) {
    value = data[i][0];
    if (value === "") {
        result.push(sum);
        sum = 0;
        continue;
    }
    sum += value;
}
result.push(sum);

console.log(result);

user_14476750

Using array.reduce, It could be done very easily.

const data = [
  [23.0],
  [25.0],
  [26.0],
  [89.0],
  [],
  [45.0],
  [78.0],
  [62.0],
  [],
  [123.0],
  [45.0],
  [68.0]
];
let [result, sum] = data.reduce(
  ([arr, sum], curr) => {
    if (curr.length) sum += curr[0];
    else arr.push(sum);
    return [arr, sum];
  },
  [[], 0]
);
result.push(sum); //push last data
console.log(result);
console.log(result.join("."));

user_15943590
Related