Skip to content

google script sheet, merging rows duplicate in array [closed]

lets say i have a 3D array data with something like this (dynamically duplicates, sometimes 2-5 records) after processing from a sheet get range and wanted to ouput a certain value format to a new sheet

[abc, 123, 456]
[abc, 123, 567]
[abc, 123, 644]
[xyz, 345, 434]
[xyz, 334, 345]
[aaa, 124, 433]
[aaa, 124, 435]

my goal now is to merge the result into something like this before writing to the sheet

[abc, 123, 456:567:644]
[xyz, 345, 434]
[xyz, 334, 345]
[aaa, 124, 433:435]

that is the result i wanted to have and write it to 3 column sheet, delimiter for : is actually , but i put : cause don’t want to make it confuse in array form. as long as the first and 2nd column values are the same, we should make it become one row but concatenating the 3rd column values of the duplicate records into one row instead.

is it better to make a duplicate array and compare them and push to a new array with concatenating the 3rd column values together?

Answer

I would do something like this.

let rows = [
    ['abc', 123, 456],
    ['abc', 123, 567],
    ['abc', 123, 644],
    ['xyz', 345, 434],
    ['xyz', 334, 345],
    ['aaa', 124, 433],
    ['aaa', 124, 435],
    ['abc', 123, 5672],
]


function mergeRow (rows) {
    let newRows = []
    let matched = []
    
    for (var i = 0; i < rows.length; i++) {
        
        if (!matched.includes(i)) {
            let a = rows[i]
            let nextIndex = i+1
            let matches = []
            
            for (var x = nextIndex; x < rows.length; x++) {
                if (a[0] === rows[x][0] && a[1] === rows[x][1]) {
                    matches.push(x);
                }
            }
            
            let newRow = a
            let lastItem = a[2]
            
            matches.forEach(index => {
                lastItem += ':' + rows[index][2]
                matched.push(index)
            })
            
            newRow[2] = lastItem
            newRows.push(newRow)
        }
    }
    console.log('newRows', newRows) 
}

mergeRow(rows);