I am using "filter" method in script editor to filter a range and obtain list of all values in the second column of the filtered range. plSentDateValidationList
contains list of dates and is fed as an input into data validation builder with "requireValueInList" method.
// Get DC dates using FILTER
var plSentDateList = PSIData.filter(function(item){
return item[0] === miDC;
})
var plSentDateValidationList = plSentDateList.map(x => x[1]).sort();
//Logger.log(plSentDateValidationList);
var plSentDateValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(plSentDateValidationList).setAllowInvalid(false).build();
// Apply validation rule to adjacent cell using offset
activeCell.offset(0,1).setDataValidation(plSentDateValidationRule);
Everything works as expected, however, the dates from the filter output are long version of date. The logger output of variable plSentDateValidationList
is:
Info [Tue Mar 02 13:30:00 GMT-05:00 2021, Tue Mar 02 13:30:00 GMT-05:00 2021]
However, I want this to be formatted as "dd/mm/yyyy" so that the resulting dropdown from data validation list is concise.
I have tried Utilities.formatDate()
method without much luck. I think I am missing something simple here. How do I format the list plSentDateValidationList
output [Tue Mar 02 13:30:00 GMT-05:00 2021, Tue Mar 02 13:30:00 GMT-05:00 2021]
to dd/mm/yyyy
format and then use list of these formatted dates into the data validation builder?
Note: the spreadsheet settings "locale" is set to United Kingdom and Timezone is set to GMT+5:30
Try
var plSentDateValidationList = plSentDateList.map(x => x[1]).sort().map(x => Utilities.formatDate(x, 'BST', 'dd/MM/yyyy'));