Gathering all the unique values from one column and outputting them in another column..?
You can do that inside google-spreadsheets with the UNIQUE
function.
Here is the doc to all available functions.
(You find UNIQUE
in the Filter
category)
Most likely you want to insert into cell E1
:
=UNIQUE(D1:D)
This will populate column E
with the unique values from all of column D
while preserving the order. Furthermore this will dynamically update and reflect all changes made to column D
.
To do that from within google-apps-script:
SpreadsheetApp.getActiveSheet()
.getRange("E1").setFormula("=UNIQUE(D1:D)");
here is a way to do that... probably not the only one but probably not bad...
I added a few logs to see intermediate results in the logger.
function keepUnique(){
var col = 3 ; // choose the column you want to use as data source (0 indexed, it works at array level)
var sh = SpreadsheetApp.getActiveSheet();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data=ss.getDataRange().getValues();// get all data
Logger.log(data);
var newdata = new Array();
for(nn in data){
var duplicate = false;
for(j in newdata){
if(data[nn][col] == newdata[j][0]){
duplicate = true;
}
}
if(!duplicate){
newdata.push([data[nn][col]]);
}
}
Logger.log(newdata);
newdata.sort(function(x,y){
var xp = Number(x[0]);// ensure you get numbers
var yp = Number(y[0]);
return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on numeric ascending
});
Logger.log(newdata);
sh.getRange(1,5,newdata.length,newdata[0].length).setValues(newdata);// paste new values sorted in column of your choice (here column 5, indexed from 1, we are on a sheet))
}
EDIT :
Following Theodros answer, the spreadsheet formula is indeed an elegant solution, I never think about it but I should !!! ;-)
=SORT(UNIQUE(D1:D))
gives exactly the same result...