Here is the function I made. The var myArray is a list of names. I'm not able to get only unique values from this array and set it to another sheet. I just want the names once.

function array() {          
      var app = SpreadsheetApp;
      var ss = app.getActiveSpreadsheet().getSheetByName("test");
      var destinationSheet = app.getActiveSpreadsheet().getSheetByName("test2");

      var myArray = ss.getRange(2,1,30).getValues();

      destinationSheet.getRange(2, 1, 30).setValues(myArray);   
}

Can someone help me to filter this array?

Here is the function I made. The var myArray is a list of names. I'm not able to get only unique values from this array and set it to another sheet. I just want the names once.

function array() {          
      var app = SpreadsheetApp;
      var ss = app.getActiveSpreadsheet().getSheetByName("test");
      var destinationSheet = app.getActiveSpreadsheet().getSheetByName("test2");

      var myArray = ss.getRange(2,1,30).getValues();

      destinationSheet.getRange(2, 1, 30).setValues(myArray);   
}

Can someone help me to filter this array?

• javascript
• arrays
• google-apps-script
• google-sheets

4 Answers 4

Here's an alternative approach (for Google Script)

function myArray() {
var ss, s, a;
ss = SpreadsheetApp.getActive();
s = ss.getSheetByName("test").getRange(2, 1, 30).getValues();
a = [];
a.push(s[0]);
for (var n = 1; n < s.length; n++) {
    if (a.join().indexOf(s[n].join()) == -1) {
        a.push(s[n])
    };
}
ss.getSheetByName("test2").getRange(2, 1, a.length, a[0].length).setValues(a);
}

Javascript Arrays have the method from using which you can create an array form a set of values, be it String, Map or Set.

Add this after the definition of myArray in your code

myArray = Array.from(new Set(myArray));

You can read more about it here.

OR

You can use a simple forEach loop to filter unique values.

Assuming your array contains names only, and getValues returns 2-D arrays, so, your array looks something like - [['name1', 'name2', 'name3']].

var newArray = [];
myArray.forEach(function(x){
    if(newArray.indexOf(x[0]) === -1){
        newArray.push(x[0]);
    }                   
});

To add to @JPV's answer, which is nearly perfect, but does not account for the instance that "ab" follows "abc". In this case the code would not recognize "ab" as a new instance. To get that you'd need to change the code to the following:

function countDistinct() {

var sh = SpreadsheetApp.getActive();
var ss = sh.getSheetByName('Sheet1');
var length = ss.getLastRow()
var data = ss.getRange(1,1,length,1).getValues();
var a = [];
var b = 0;

for (var n = 0; n<length; n++) {

if (a.indexOf(data[n].join()) == -1) {

  a.push(data[n].join());
  b++;

  }
}

Logger.log(a);
Logger.log(b);

}

Variable b would give you the number of unique values, while a would just print the array.

Assuming your array contains names only, and getValues returns 2-D arrays, so, your array looks something like - [['name1', 'name2', 'name3']]. Assuming your array contains names only, and getValues returns 2-D arrays, so, your array looks something like - [['name1', 'name2', 'name3']].

var newArray = [];
myArray.forEach(function(x){
    if(newArray.indexOf(x[0]) === -1){
        newArray.push(x[0]);
    }                   
});

what should i do i have 2 values in array ?

what should i do if have 2 values in array ?