0
206
ViewsHow to compare and concatenate several data in a 2D array on google app script
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!
·
Juan Pablo Isaza
1 answers
Answer question0
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 |
|---|---|
| one@domain.com | name1 |
| one@domain.com | name2 |
| one@domain.com | name3 |
| two@domain.com | name4 |
| two@domain.com | name5 |
| three@domain.com | name5 |
output:
| A | B |
|---|---|
| one@domain.com | name1,name2,name3 |
| two@domain.com | name4,name5 |
| three@domain.com | name5 |
·
Juan Pablo Isaza
Report
Answer question