javascript - Calculating the total of values in JS function and displaying it on a row

Question

I have a custom script I'm using in google sheets to pull data from a sheet and to display on a different sheet. I want it to calculate the sum of hours (the last values in the arrays) and display it in the "week x total x" row pushed by the function. I cant seem to figure out how to do this.

Here is the function

var array = [
        ['123', 'x', 3, 3],
        ['123', 'x', 2, 3],
        ['123', 'x', 3, 3],
        ['123', 'x', 4, 3],
        ['123', 'x', 2, 3],
        ['123', 'x', 4, 3],
        ['123', 'x', 4, 3],
        ['123', 'x', 3, 3],
        ['123', 'x', 3, 3],
        ['123', 'x', 3, 3],
    ]

    function sortByWeek(array) {
        for (var i = 1; i < array.length; i++) {

            // sort tasks by week number
            for (var j = 0; j < i; j++) {
                if (array[i][2] < array[j][2]) {
                    var x = array[i];
                    array[i] = array[j];
                    array[j] = x;
                }
            }
        }

        // insert rows between different weeks;
        var arrayFinal = [];
        let same = array[0][2];
        let total = 0;
        for (var e = 0; e < array.length; e++) {



            // If week num is same as previous -> add item
            if (array[e][2] === same) {
                total = total + array[e][3];
                arrayFinal.push(array[e]);
            }

            // If week num is no the same as previous -> add rows -> add item
            else {

                // If not first -> add rows
                if (!(e === 0)) {
                    var rows = [
                        ["Week " + same + " total " + total]
                    ];
                    for (var s = 0; s < rows.length; s++) {
                        arrayFinal.push(rows[s]);
                    }
                    total = 0;
                }

                // add item
                arrayFinal.push(array[e]);
            }
            same = array[e][2];
        }
        for (var s = 0; s < rows.length; s++) {
            arrayFinal.push(rows[s]);
        }

        for (var z = 0; z < arrayFinal.length; z++) {
            console.log(arrayFinal[z].toString());
        }
    }

This is what it gives me

123,x,2,3
123,x,2,3
Week 2 total 6
123,x,3,3
123,x,3,3
123,x,3,3
123,x,3,3
123,x,3,3
Week 3 total 12
123,x,4,3
123,x,4,3
123,x,4,3
Week 3 total 12

But this is what i want it to give:

123,x,2,3
123,x,2,3
Week 2 total 6
123,x,3,3
123,x,3,3
123,x,3,3
123,x,3,3
123,x,3,3
Week 3 total 15
123,x,4,3
123,x,4,3
123,x,4,3
Week 3 total 9

I have been staring this for too long and there must be super simple answer to this but i cant see it.

question from:https://stackoverflow.com/questions/65848818/calculating-the-total-of-values-in-js-function-and-displaying-it-on-a-row

Answer 1

You can do this in a much shorter way if you use Array.sort() and Array.reduce()`:

var arr = [ 
        ['123', 'x', 3, 3],
        ['123', 'x', 2, 3],
        ['123', 'x', 3, 3],
        ['123', 'x', 4, 3],
        ['123', 'x', 2, 3],
        ['123', 'x', 4, 3],
        ['123', 'x', 4, 3],
        ['123', 'x', 3, 3],
        ['123', 'x', 3, 3],
        ['123', 'x', 3, 3] ];
        
 const res=arr
  .sort(([,,a],[,,b])=>a-b)
  .reduce((sum=>(a,c,i,ar)=>{
    a.push(c.join(",")); // to turn it into a string
    sum+=c[3]
    if (!ar[i+1]||ar[i+1][2]!=c[2]){
      a.push("week "+c[2]+" total "+sum);
      sum=0;
    }
    return a;
  })(0), []);
              
 console.log(res.join("
")) // to turn the res array into a string