How to get time value from google sheet

Question

I have a google spreadsheet filled by forms. Some columns contain only time (e.g. 2:00:00) and it puts there by form element date. When I read data from this spreadsheet using getRange, and log out the value of such cell (with time), logger reports me that:

Sat Dec 30 1899 02:18:48 GMT+0400 (GST)

I see that data was read not in right format. How I can get strict time data like it stored in sheet? Anybody encounter with such problem?

P.S. I found one bypass, but it not automatic. I have to change cell format to simple text and then I get a valid output. But I wonder if I can get plane text from cell or range by code?
It looks like form set date format for cell and put in it only time value, but you can't get the time back.

Are you saying that 2:00 returns 18:48 GMT+0400.? Also: have you checked the tz of your form, spreadsheet and script ? — Serge insas
read my answer in this post about time in SS from Forms : stackoverflow.com/questions/25095734/… — Serge insas

ynthrepic ynthrepic · Accepted Answer · 2015-12-17T14:05:57

I've figured out a rudimentary solution after much experimentation. I expect if Google update how times are added from Forms it'll break though. What I've done is taken the millisecond value of a time of 00:00:00 and taken the difference with a given time, then use some simple math to get the hour and minute out of that.

function extractTime(date) {

  var milliseconds =  date.getTime()+2209203000000;
  var time = {};
  time.hours = Math.floor(milliseconds/(1000*60*60));
  time.minutes = Math.round((milliseconds/(1000*60*60)-time.hours)*60);

  return time;

}

Have tested and it seems to work.

I have also posted this answer in my own question regarding this.

How to get time value from google sheet

1 Answers