I have this code snippet:
function getTimeFrames(){
var ss = SpreadsheetApp.getActive();
var cell = ss.getRange("C1").getValue();
Logger.log(cell);
var s = ss.getSheetByName(cell).getRange("M2:M");
var unique = new Set(s.getValues());
unique.forEach(x => Logger.log(x))
}
This code would be called from a Google Sheets spreadsheet which serves as an UI, where the C1 cell would be a name of a sheet used for storing data. In that sheet I have a column (M) that I need to get the unique values out of in order to store in a drop-down in the UI sheet.
The problem I have is that I can't get the unique values part working at all. Throughout my experimentation, I would either get a list of all the values of column M or no Logging output at all.
Any help is greatly appreciated!
Try
function getTimeFrames(){
var ss = SpreadsheetApp.getActive();
var cell = ss.getRange("C1").getValue();
Logger.log(cell);
var unique = ss.getSheetByName(cell).getRange("M2:M").getValues().flat().filter(onlyUnique)
console.log(unique)
}
function onlyUnique(value, index, self) {
return self.indexOf(value) === index;
}
This was really a tough job before ES6. But now, with a combination of filter() and indexOf() you can easily pull it off.
For Example:
var List = ['One','Two','Two','One','One','Two','One','Three']
const uniqueList = (value,index,self) =>
{return self.indexOf(value) ===index;}
var finalUniqueList =List.filter(unique);
Try spreading the unique values into an array:
function getTimeFrames() {
const sheet = SpreadsheetApp.getActive()
const cell = sheet.getRange(`C1`).getValue()
const values = sheet.getSheetByName(cell)
.getRange(`M2:M`)
.getValues()
.filter(String)
.flat()
const unique = [...new Set(values)]
Logger.log(unique)
}