Google Form on Submit get values and format the time

I am using Google Apps Script with a Google form. When the user submits the Google Form I get a value from a question. I then take that value and make it a date object, from what I saw on this post about daylight savings I use that to determine the timezone. I run the date object through Utilities.formatDate and want to get the correctly formatted date.

example: 9:00 AM

But instead I am getting a completely different time than expected.

My question is: Can someone help me understand why the below code is outputting a time that is 3 hours different?

function onSubmit(e) {

  var values = e.values;  
  Logger.log(values);

  try {    
    var start1 = new Date(values[3]);
    var startN = new Date(start1).toString().substr(25,6)+"00";
    var startT = Utilities.formatDate(start1, startN, "h:mm a");
    Logger.log(startT);

  } catch(error) {
    Logger.log(error);
  }
}

Answer

The assumption that Utilities formatDate does not support GMT… parameter is not true.

The post you mentioned in reference is used to get calendar events and is a useful way to get the right value when you get events from another daylight saving period (getting the TZ info from the calendar event itself), for example events for next month will be in “summer time” while we are still in “winter time”…

Your issue might come from different sources depending on time zone settings of your script vs timezone of the source. Could you describe the exact configuration in which you use this script ?

In the mean time, here is a small code that demonstrates how the code is working + the logger results :

function testOnSubmit() {
  var eventInfo = {};
  var values = {};  
  values['3'] = new Date();
  eventInfo['values'] = values;
  Logger.log('eventInfo = '+JSON.stringify(eventInfo)+'nn');
  onSubmit(eventInfo);
}

function onSubmit(e) {
  var values = e.values;  
  try {    
    var start1 = new Date(values[3]);
    Logger.log('onSubmit log results : n');
    Logger.log('start1 = '+start1)
    var startN = new Date(start1).toString().substr(25,6)+"00";
    Logger.log('startN = '+startN);
    var startT = Utilities.formatDate(start1, startN, "h:mm a");
    Logger.log('result in timeZone = '+startT);
  } catch(error) {
    Logger.log(error);
  }
}

EDIT : additionally, about the 30 and 45′ offset, this can easily be solved by changing the substring length like this :

var startN = new Date(start1).toString().substr(25,8);

the result is the same, I had to use the other version a couple of years ago because Google changed the Utilities.formatDate method at some moment (issue 2204) but this has been fixed.

EDIT 2 : on the same subject, both methods actually return the same result, the GMT string has only the advantage that you don’t have to know the exact timezone name… there is also the Session.getScriptTimeZone() method. Below is a demo script that shows the resulst for 2 dates in January and July along with the log results :

function testOnSubmit() {
  var eventInfo = {};
  var values = {};  
  values['3'] = new Date(2014,0,1,8,0,0,0);
  eventInfo['values'] = values;
  Logger.log('eventInfo = '+JSON.stringify(eventInfo)+'nn');
  onSubmit(eventInfo);
  values['3'] = new Date(2014,6,1,8,0,0,0);
  eventInfo['values'] = values;
  Logger.log('eventInfo = '+JSON.stringify(eventInfo)+'n');
  onSubmit(eventInfo);
}

function onSubmit(e) {
  var values = e.values;  
  var start1 = new Date(values[3]);
  Logger.log('onSubmit log results : ');
  Logger.log('start1 = '+start1)
  var startN = new Date(start1).toString().substr(25,8);
  Logger.log('startN = '+startN);
  Logger.log('result in timeZone using GMT string = '+Utilities.formatDate(start1, startN, "MMM,d  h:mm a"));
  Logger.log('result in timeZone using Joda.org string = '+Utilities.formatDate(start1, 'Europe/Brussels', "MMM,d  h:mm a"));
  Logger.log('result in timeZone using Session.getScriptTimeZone() = '+Utilities.formatDate(start1, Session.getScriptTimeZone(), "MMM,d  h:mm a")+'n');
}

Note also that the Logger has its own way to show the date object value ! it uses ISO 8601 time format which is UTC value.