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

CodePudding user response：

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:

Sheet0 After:

CodePudding user response：

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:

• Array.prototype.indexOf()
• Array.prototype.find()
• Map