CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Timogavk
Timogavk

Reputation: 869

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

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();

Upvotes: 2

Views: 134

Answers (2)

Nina Scholz
Nina Scholz

Reputation: 386600

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);

Upvotes: 3

xdeepakv
xdeepakv

Reputation: 8125

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

Upvotes: 1

Related Questions