Create a new Date for any specific timezone

Question

I am using Google Apps Script to check and re-format date data from some Google Sheets. But the problem is the result shows the times for the user who run the code. I want to show the date for any specific time zone. How is it possible?

Suppose, my input is checkDate('14/5/2022'); and it returns the date object for that time zone instead of my time zone.

Here is my code:

/**
 * This will return a JS Date object with a valid date input.
 * Unless this will return a false status
 */
function checkDate(input) {
  // const timeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  if (input instanceof Date && !isNaN(input)) {
    // will execute if a valid date
    return input;
    
  } else {
    // If not a valid date
    const splitter = input.indexOf('/') === -1 ? '-' : '/';
    const dateArr = input.split(splitter);

    if(dateArr.length === 3) {
      const year = dateArr[2].length === 2 ? '20' + dateArr[2] : dateArr[2];
      const NewTime = new Date(Date.UTC(year, dateArr[1]-1, dateArr[0], 0, 0, 0));

      return NewTime;
    } else {
      return false;
    }
  }
}

console.log(checkDate(new Date()));
console.log(checkDate('14/5/2022'));

Expected input checkDate('14/5/2022') and timeZone = 'GMT+1;

Expected Output 2022-05-14T00:00:00.000 french time. Not the UTC time.

Is it possible?

Answer 1

Apps Script is JavaScript, and JavaScript Date objects are always in UTC.

When you return a Date object from a custom function, or directly write a Date object to a spreadsheet cell from another type of function, it is automatically converted to the timezone of the spreadsheet. To set the spreadsheet timezone, choose File > Settings > Time zone.

To write a date so that it appears to use another timezone, convert the Date to a text string for display with Utilities.formatDate(), like this:

  const date = new Date();
  const timezone = {
    spreadsheet: SpreadsheetApp.getActive().getSpreadsheetTimeZone(),
    paris: 'Europe/Paris',
  };
  console.log(Utilities.formatDate(date, timezone.spreadsheet, 'yyyy-MM-dd HH:mm, zzzz'));
  console.log(Utilities.formatDate(date, timezone.paris, 'yyyy-MM-dd HH:mm, zzzz'));

Answer 2

2022-05-14T00:00:00.000 is string, so add this function

function myFormat(date) {
  return date.getFullYear()
    + '-'
    + ((date.getMonth() + 1) < 10 ? '0' + (date.getMonth() + 1) : (date.getMonth() + 1))
    + '-'
    + (date.getDate() < 10 ? '0' + date.getDate() : date.getDate())
    + 'T00:00:00.000'
}

complete script

function checkDate(input) {
  // const timeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  if (input instanceof Date && !isNaN(input)) {
    // will execute if a valid date
    return myFormat(input);

  } else {
    // If not a valid date
    const splitter = input.indexOf('/') === -1 ? '-' : '/';
    const dateArr = input.split(splitter);

    if (dateArr.length === 3) {
      const year = dateArr[2].length === 2 ? '20' + dateArr[2] : dateArr[2];
      const NewTime = new Date(Date.UTC(year, dateArr[1] - 1, dateArr[0], 0, 0, 0));

      return myFormat(NewTime);
    } else {
      return false;
    }
  }
}
function myFormat(date) {
  return date.getFullYear()
    + '-'
    + ((date.getMonth() + 1) < 10 ? '0' + (date.getMonth() + 1) : (date.getMonth() + 1))
    + '-'
    + (date.getDate() < 10 ? '0' + date.getDate() : date.getDate())
    + 'T00:00:00.000'
}
function test() {
  console.log(checkDate('14/05/2022'))
}