How to compare and concatenate several data in a 2D array on google app script

Question

I have a google sheet "test" with the following data

Each user mail has a name associated to it. I would like to merge on one line the emails that are repeated and concatenate the first names associated with them to obtain the following result on column C and D like that :

I tried this code below because I think that the solution must be solved with a 2D array because the google sheet can receive a large number of emails and different first names.

function myFunction() {
var test = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");
const [,...value] = test.getDataRange().getValues(); // Get the values of the arrays

const arrayValue = value.flatMap(([a1,v1]) =>
value.map(([a2,v2]) => {
  const verif = a1==a2 && v1!=v2 ? a2.concat(','+v1+','+v2):'no';
  const name = verif == a2.concat(','+v1+','+v2) ?  v1.concat (','+v2):''; 
  const mail = name != '' ? a2:''; 
    return [mail,name];
  })
);
 test.getRange(1, 3, arrayValue.length,2).setValues(arrayValue);
}

Today with my code I only obtain this result 🥲

It's been several weeks that I'm looking for a solution to my problem and I haven't found anything to solve it. Do you have an idea?

Thanks!

Answer 1

Try this:

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  const vs = sh.getDataRange().getValues();
  let obj = { pA: [] };
  vs.forEach((r, i) => {
    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.forEach(p => {
    oA.push([p,obj[p].join(',')])
  })
  osh.getRange(1,1,oA.length,oA[0].length).setValues(oA);
}

Data:

A	B
[email protected]	name1
[email protected]	name2
[email protected]	name3
[email protected]	name4
[email protected]	name5
[email protected]	name5

output:

A	B
[email protected]	name1,name2,name3
[email protected]	name4,name5
[email protected]	name5