[SOLVED] Google Sheets Script new Date() with timezone is providing wrong time

Google Sheets Script new Date() with timezone is providing wrong time

When I run this code at 6pm in PST timezone (GMT-8), it gives me the correct date but 6am instead of 6pm.

  var nowvalue = Utilities.formatDate(new Date(), "GMT-8", "MM/dd/yyyy hh:mm");
  spreadsheet.getActiveRange().setValue(nowvalue);
  spreadsheet.getActiveRangeList().setNumberFormat('ddd" "m"/"d" "hh:mm');

If I set it to GMT+4, it correctly gives me 6am the next day (e.g., 12 hours ahead of me)

If I set it to just GMT, it correctly gives me GMT time right now.

So confused!!

Solution

Get PST timezone

This issues could be possibly caused by Daylight Saving Time(DST) and parameters like "GMT-8" does not automatically adjust to adapt DST. You may use time zone identifier instead.

Sample Code

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheet = ss.getActiveSheet();
  var nowvalue = Utilities.formatDate(new Date(), "America/Los_Angeles", "MM/dd/yyyy HH:mm");
  spreadsheet.getActiveRange().setValue(nowvalue);
  spreadsheet.getActiveRangeList().setNumberFormat('ddd" "m"/"d" "HH:mm');

}

Sample Output

References: Using timezone identifiers