Google sheet script - Filtering unique cell values - Pushing 2D array values into a single cell

Question

sorry to disturb again but I'm getting my head off.. I just want to filter unique strings that are in each cell of a range (strings are separated by semicolons) I can't push all the array values, only one is getting out of results. Thanks a lot !

Here is an example : https://docs.google.com/spreadsheets/d/12T5K9LovcFvmeBg82IS89p4ygtZ530mzpY9fIsZQa8w/edit?usp=sharing PS : I'm using script because they are too many strings in each cell for spliting cell by formula

Here's my coding :

function FilterReferences(){

  var classeur = SpreadsheetApp.getActive();
  var feuille = classeur.getSheetByName('Feuille 1');
  var plage = feuille.getRange('A1:A');
  var plageValues = plage.getValues();
  var NbLignesTotal = plage.getLastRow();

  for (var i = 1; i < NbLignesTotal; i++){
    try {
       var valeurCellule = plageValues[i-1][0];
        if (valeurCellule != ""){
          var source = valeurCellule.split(";");
          var dest = [];
          dest.push(source[0]);

          for (var n = 0 ; n < source.length ; n++){
          if (dest.indexOf(source[n]) == -1){  dest.push(source[n])};   }


        var plageDest = feuille.getRange('B1:B');
        plageDest.getCell(i,1).setValue([dest]);

     }
    } catch (e) {   }
 }
}

Answer 1

• You want to achieve the following situation. Each value is put in a cell.
  • AAA;BBB;AAA;BBB;CCC to AAA;BBB;CCC
  • AAA;CCC;AAA;DDD;ZZZ;CCC to AAA;CCC;DDD;ZZZ

Pattern 1:

In this pattern, your script is modified.

Modified script:

Please modify as follows.

From:

plageDest.getCell(i,1).setValue([dest]);

To:

plageDest.getCell(i + 1, 1).setValue(dest.join(";"));

• In your script, dest is an array. So in order to convert it to a string, join is used.
  • When plageDest.getCell(i,1).setValue([dest]); is run, the 1st element is put by setValue. I think that this is the reason of your issue.
• In order to put the value from the row 2, i + 1 is used instead of i.

Pattern 2:

In this pattern, your script is modified by reducing the process cost.

Modified script:

function FilterReferences() {
  var classeur = SpreadsheetApp.getActive();
  var feuille = classeur.getSheetByName('Feuille 1');
  var plage = feuille.getRange('A2:A' + feuille.getLastRow());  // Modified
  var plageValues = plage.getValues();

  // I added below script.
  const res = plageValues.map(([e]) => 
    [Object.keys(e.split(";").reduce((o, e) =>
      Object.assign(o, {[e]: null})
    , {})).join(";")]
  );
  feuille.getRange(2, 2, res.length, 1).setValues(res);
}

• In this script, the values are created in the loop. And then, the created values are put to the cells using setValues.

References:

• join()
• setValue(value)
• setValues(values)