javascriptdategoogle-apps-scriptgoogle-sheetsformatdatetime

Google sheets - script editor Data validation builder - format filtered date list


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


Solution

  • Try

    var plSentDateValidationList = plSentDateList.map(x => x[1]).sort().map(x => Utilities.formatDate(x, 'BST', 'dd/MM/yyyy'));