Remove sheet's duplicate rows (names), but store leftover data to add to the name row [Google AppScript]

user_19804335 0
Viewed 35

I'm working with a large sheet of duplicated names, but with unique data in the cells next to the name. I need to remove the duplicate names and append the additional unique data to the end of the first-named row. I'll include screenshots to explain what I mean:

I have an array similar to this: [John Smith, General], [John Smith, Tall], [John Smith, Rich], [John Smith, Blue], [John Smith, Children], [John Smith, Tall], [John Smith, Rich]

I need to turn it into this: [John Smith, (General, Tall, Rich, Blue, Children)]

I'm pretty new to javascript/app script and I'm having a hard time wrapping my head around this. This is my current thought process:

  var filteredData = []

  for (var i = 0; i < peopleData.length; i++){
    //If name doesn't exist on filtered list, add it
    if (filteredData.indexOf(peopleData[i][0]) == -1){
      filteredData.push([peopleData[i][0], peopleData[i][1]]);
    }
    else{
      //Name already exists, add attributes to existing entry
      filteredData[i-1] = filteredData[i-1] + ", " + peopleData[i][1];
    }
  }

Any pointers would be greatly appreciated. Thanks

Sheet representation of before,

Sheet representation of after

google-apps-script google-sheets javascript
2 Answers

Try this:

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 2).getDisplayValues();
  let obj = { pA: [] };
  vs.forEach(r => {
    if (!obj.hasOwnProperty(r[0])) {
      obj[r[0]] = [r[1]]
      obj.pA.push(r[0]);
    } else {
      obj[r[0]].push(r[1])
    }
  });
  let oA = obj.pA.map(p => {
    return [p, obj[p].join(",")];
  })
  sh.getRange(2, 1, sh.getLastRow() - 1, 2).clearContent();
  sh.getRange(2, 1, oA.length, oA[0].length).setValues(oA);
}

Sheet0 Before:

COL1 COL2
Jack 1
Jill 1
John 1
James 1
Jenny 1
Jack 2
Jill 2
John 2
James 2
Jenny 2
Jack 3
Jill 3
John 3
James 3
Jenny 3
Jack 4
Jill 4
John 4
James 4
Jenny 4

Sheet0 After:

COL1 COL2
Jack 1,2,3,4
Jill 1,2,3,4
John 1,2,3,4
James 1,2,3,4
Jenny 1,2,3,4
user_72150910

I believe your goal is as follows.

  • You want to achieve the following situation.

    • From

        var peopleData = [["John Smith", "General"], ["John Smith", "Tall"], ["John Smith", "Rich"], ["John Smith", "Blue"], ["John Smith", "Children"], ["John Smith", "Tall"], ["John Smith", "Rich"]]

    • To

        var filteredData = [["John Smith","General, Tall, Rich, Blue, Children"]]

In this case, how about the following modification?

Modification points:

  • In your script, filteredData is a 2-dimensional array. Unfortunately, Array.prototype.indexOf() cannot be used for 2 dimensional array. I thought that the main reason for your issue might be due to this.
  • In order to avoid the duplicated value of the 2nd element, I think that it is required to add one more condition.

When these points are reflected in your script, how about the following modification?

Modified script:

var peopleData = [["John Smith", "General"], ["John Smith", "Tall"], ["John Smith", "Rich"], ["John Smith", "Blue"], ["John Smith", "Children"], ["John Smith", "Tall"], ["John Smith", "Rich"]];

var filteredData = [];
for (var i = 0; i < peopleData.length; i++) {
  var t = filteredData.find(([a]) => a == peopleData[i][0]);
  if (!t) {
    filteredData.push([peopleData[i][0], peopleData[i][1]]);
  } else if (!t[1].includes(peopleData[i][1])) {
    t[1] += ", " + peopleData[i][1];
  }
}

console.log(filteredData) // [["John Smith","General, Tall, Rich, Blue, Children"]]

  • In this modification, in order to search the value from 2 dimensional array, Array.prototype.find() is used.

Note:

  • For example, as another approach, in your situation, when Map() object is used, the script can be written as follows.

var peopleData = [["John Smith", "General"], ["John Smith", "Tall"], ["John Smith", "Rich"], ["John Smith", "Blue"], ["John Smith", "Children"], ["John Smith", "Tall"], ["John Smith", "Rich"]];
var filteredData = [...peopleData.reduce((m, [a, b]) => m.set(a, m.has(a) ? m.get(a) + (!m.get(a).includes(b) ? `, ${b}` : "") : b), new Map())];
console.log(filteredData) // [["John Smith","General, Tall, Rich, Blue, Children"]]

References:

user_71086530
Related