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?

Does this answer your question? [How to initialize a JavaScript Date to a particular time zone](https://stackoverflow.com/questions/15141762/how-to-initialize-a-javascript-date-to-a-particular-time-zone) — , May 14 '22 at 14:55

score 2 · Answer 1 · answered May 14 '22 at 16:03

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'));

score 1 · Answer 2 · answered May 14 '22 at 15:58

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'))
}

This just seems to format a date, it doesn't seem to answer the OP: "*show the date for any specific time zone*". — RobG, May 14 '22 at 21:25
@RobG , sure, but the OP asked also a string `Expected Output 2022-05-14T00:00:00.000.` that's why I gave this solution. — Mike Steelson, May 16 '22 at 14:33
Yes, the format is what the OP wants but it won't return the expected values for date and time. BTW, *instanceof* is not considered a good way to test an object's "class", see [*How to check whether an object is a date?*](https://stackoverflow.com/questions/643782/how-to-check-whether-an-object-is-a-date). — RobG, May 17 '22 at 00:45

Create a new Date for any specific timezone

2 Answers2