i have a problem regarding grouping a Array by the values of a specific column which i get from a Google Sheet with .getRange().getValues().
Example Array:
var Arr = [
[Mat. Group 1, xyz, name xyz, -31.0, -610],
[Mat. Group 2, abc, name abc, -12.0, -434],
[Mat. Group 1, gew, name gew, -44.0, -234],
[Mat. Group 1, zrw, name zrw, -22.0, -214],
[Mat. Group 2, dbh, name dbh, -91.0, -193],
[Mat. Group 3, dse, name dse, -282.0, -176],
[Mat. Group 4, dwq, name dwq, -235.0, -151],
[Mat. Group 3, dge, name dge, -45.0, -150]
]
I want to group the values in column 4 (Net Price) by the Material Group in column 0. (col1 = Reference-Number, col 2 = Name of Reference, col 3 = Quantity)
So the result should look like this:
[
[Mat. Group 1, -1058],
[Mat. Group 2, -627],
[Mat. Group 3, -326],
[Mat. Group 4, -151],
]
There are a few solutions how to do this with an array of objects (Like these post: https://stackoverflow.com/a/57477448/15361059 ) by using array formulars.
I am new in array formulars. I tried to modify the solutions, but always crashed. So maybe you could support me with this issue.
Thanks in advance.
A reduce would work well here
var Arr = [
["Mat. Group 1", "xyz", "name xyz", -31.0, -610],
["Mat. Group 2", "abc", "name abc", -12.0, -434],
["Mat. Group 1", "gew", "name gew", -44.0, -234],
["Mat. Group 1", "zrw", "name zrw", -22.0, -214],
["Mat. Group 2", "dbh", "name dbh", -91.0, -193],
["Mat. Group 3", "dse", "name dse", -282.0, -176],
["Mat. Group 4", "dwq", "name dwq", -235.0, -151],
["Mat. Group 3", "dge", "name dge", -45.0, -150]
]
const newArr = Arr.reduce((acc, cur) => {
const idx = acc.findIndex(arr => arr[0] === cur[0]);
if (idx != -1) acc[idx][1] += cur[4];
else acc.push([cur[0], cur[4]]);
return acc
}, []);
console.log(newArr)You can get the expected result with grouping by key approach
const data = [["Mat. Group 1", "xyz", "name xyz", -31.0, -610],["Mat. Group 2", "abc", "name abc", -12.0, -434],["Mat. Group 1", "gew", "name gew", -44.0, -234],["Mat. Group 1", "zrw", "name zrw", -22.0, -214],["Mat. Group 2", "dbh", "name dbh", -91.0, -193],["Mat. Group 3", "dse", "name dse", -282.0, -176],["Mat. Group 4", "dwq", "name dwq", -235.0, -151],["Mat. Group 3", "dge", "name dge", -45.0, -150]]
const result = Object.values(data.reduce((acc, [group,,,, value]) =>
({ ...acc, [group]: [group, (acc[group] ?? [0, 0])[1] + value] }), {}));
console.log(result).as-console-wrapper{min-height: 100%!important; top: 0}