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!

Cooper · Accepted Answer · 2022-05-04 21:27:23Z

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

answered May 4, 2022 at 21:27

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

slash company Over a year ago

Wow it works a thousand thanks :D Now I will try to understand how all your code works. Thanks again!

Cooper Over a year ago

This is a technique I use a lot. It's similar to the kind of thing that programmers do when building pivot tables.

Collectives™ on Stack Overflow

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

1 Answer 1

Try this:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Try this:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related