How to read a time in googlespreadsheet with google apps script?

Question

After hours spent to identify a rational or a solution, my hope is now with this community !

I'm desesperatly trying to get ("read") a time entered by user in a google spreasheet and to use it correctly in a google apps script for example to create google calendar event.

The desired format is "HH:mm"

My starting point is the google apps script example provided on https://developers.google.com/apps-script/quickstart/forms

From this example I modified the parameters of the spreasheet (sorry for the french!) using the "Change locale and time zone" instructions : settings illustration

I also changed the display format of the columns 'C' and 'D' to not have the AM/PM put in the initial example:

Start Time  End Time
13:00:00    14:55:00
13:00:00    14:55:00
...

To enable debug in script editor, I removed "_" at the end of setUpConference (line 14).

I launched the script "setUpConference" in debug to check the values read from the datasheet. My surprise is to have for the first data line

Ethics for monsters 5/15/2013   13:00:00    14:55:00    Rm 323: Minotaur's Labyrinth

the corresponding data of the variable "session"

["Ethics for monsters", (new Date(1368568800000)), (new Date(-2209115361000)), (new Date(-2209108461000)), "Rm 323: Minotaur's Labyrinth"]

and sessions[2] is showned in the script editor as: Sat Dec 30 1899 13:50:39 GMT+0100 (CET)

I understand that having only "time" (HH:mm), the date is incomplete (so the 1899 day) but how to obtain the time "13:00:00" rather than this strange "13:50:39" ?

Ps: my calendar time zone is also GMT+0100 (CET)

---

some edits with more information:

• I share the google spreadsheet I used for test

• I simplified the code of my google app script to focus on the issue (initial code was the one provided by google on https://developers.google.com/apps-script/quickstart/forms

  /** * A special function that inserts a custom menu when the spreadsheet opens. */ function onOpen() { var menu = [{name: 'Set up conference', functionName: 'setUpConference'}]; SpreadsheetApp.getActive().addMenu('Conference', menu); }

  /**
   * A set-up function that uses the conference data in the spreadsheet to create
   * Google Calendar events, a Google Form, and a trigger that allows the script
   * to react to form responses.
   */
  function setUpConference() {
    /*  if (ScriptProperties.getProperty('calId')) {
      Browser.msgBox('Your conference is already set up. Look in Google Drive!');
    }*/
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName('Conference Setup');
    var range = sheet.getDataRange();
    var values = range.getValues();
    setUpCalendar(values, range);
  }
  
  /**
   * Creates a Google Calendar with events for each conference session in the
   * spreadsheet, then writes the event IDs to the spreadsheet for future use.
   *
   * @param {String[][]} values Cell values for the spreadsheet range.
   * @param {Range} range A spreadsheet range that contains conference data.
   */
  function setUpCalendar(values, range) {
    // comment cal for debug
    //var cal = CalendarApp.createCalendar('Test Conference Calendar');
    for (var i = 1; i < values.length; i++) {
      var session = values[i];
      var title = session[0];
      Logger.log("i= "+i+" - "+ "session[2]= " + session[2] + " | session[3] =" + session[3] );
  
      // This formats the date as Greenwich Mean Time in the format
      // year-month-dateThour-minute-second.
      var formattedHour = Utilities.formatDate(session[2], "GMT+1", "HH:mm");
      Logger.log("formattedHour = "+formattedHour);
  
      var start = joinDateAndTime(session[1], session[2]);
      var end = joinDateAndTime(session[1], session[3]);
      var options = {location: session[4], sendInvites: true};
      // comment cal and event creation
      /*var event = cal.createEvent(title, start, end, options)
          .setGuestsCanSeeGuests(false);
      session[5] = event.getId();*/
    }
    range.setValues(values);
  }
  
  /**
   * Creates a single Date object from separate date and time cells.
   *
   * @param {Date} date A Date object from which to extract the date.
   * @param {Date} time A Date object from which to extract the time.
   * @return {Date} A Date object representing the combined date and time.
   */
  function joinDateAndTime(date, time) {
    date = new Date(date);
    date.setHours(time.getHours());
    date.setMinutes(time.getMinutes());
    return date;
  }
  

Answer 1

As linked in some of the comments sheets and JS use different date epochs and they don't always play nice.

change the var values = range.getValues(); to var values = range.getDisplayValues();

this will force it to grab the cells values as string.

changing your date join function as follows will make it handle the strings(may need to ensure the dates in your spread sheet to have leading zeros):

function joinDateAndTime(date, time) {
  var t = new Date(date);
  t.setHours(parseInt(time.substring(0, 2)));
  t.setMinutes(parseInt(time.substring(3, 5)));
  return t;
}