I'm trying to format the date in Google App Script and then store it into the Google Sheet. Everything is working fine but the problem is source date has no specific format, dates may come in different formats like DD-MM-YYYY, MM-DD-YYYY, YYYY-MM-DD or date can also come with time and then I've to convert it into YYYY-MM-DD format and then save in google sheet. I've tried to convert to covert using the below codes:
var date = new Date("01-15-2022").toISOString().split('T')[0] // MM/dd/yyyy
Logger.log(date);
this code works only if the source date has in a format like MM/dd/yyyy and yyyy/MM/dd only if the date is in a format like dd/MM/yyyy then codes does not work.
I want a method that converts all the date formats in a single format like yyyy/MM/dd
I got a little bad news for you. There is no way to distinguish algorithmically between 01-02-2022 (Feb 1, 2022) and 01-02-2022 (Jan 2, 2022). So technically this source data is the infamous 'garbage in'.
Somehow I managed to find an alternative option to achieve this using Moment Js Library below is the solution:
function myFunction() {
let formats = ["DD/MM/YYYY", "MM/DD/YYYY", "YYYY/MM/DD", "DD/MM/YYYY hh:mm:ss", "DD/MM/YYYY hh:mm:ss A", "MM/DD/YYYY hh:mm:ss A", "YYYY/MM/DD hh:mm:ss A"]
Logger.log(convertDate("5/1/2022 12:00:00 PM", formats));
}
function convertDate(date, formats){
eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.9.0/moment.min.js').getContentText());
let dateMomentObject = moment(date, formats);
let dateObject = dateMomentObject.toDate();
let dateStr = moment(dateObject).format('YYYY-MM-DD');
return dateStr;
}