Reading And Writing Time Values From/to A Spreadsheet Using Gas

I really thought I was starting to understand how this works until I tried to distil a test case for this question and am totally confused again. With a google spreadsheet you can

Solution 1:

There does seem to be something strange going on with the behavior of your =dateToSeconds() versus your test() function. It looks like there is a bug that's causing the time discrepancy between the two. Please raise a bug for that in the issue tracker.

I was however able to get this working by using number formatting on the cells. Have a look at my modified copy of your spreadsheet. In cell A2 I entered "29/04/2012 11:00:00" and then set Format > Number > 15:59:00 Time. You can also do this programmatically by using Range.setNumberFormat("HH:mm:ss"); This results in cell A2 showing 11:00:00.

Then, I modified your test function to have this code:

function test() {
  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.getRange("E2").setValue(dateToSeconds(sheet.getRange("A2").getValue()));
  Logger.log(sheet.getRange("A2").getValue());
  Logger.log(sheet.getRange("A2").getNumberFormat());

  var values = sheet.getRange("A4:C4").getValues();
  Logger.log(values[0][0] + " " + values[0][1] + " " + values[0][2]);
  sheet.getRange("E4").setValue(newTime2(values[0][0], values[0][1], values[0][2])).setNumberFormat("HH:mm:ss");
}

and instead of a newTime function, I created a newTime2 with this code:

functionnewTime2(hours, minutes, seconds) {
  var date = new Date();
  date.setHours(hours);
  date.setMinutes(minutes);
  date.setSeconds(seconds);
  Logger.log(date);
  returndate;
}

I hope that helps.

Solution 2:

See this Post on how to get the correct value, there is a correction factor because Spreadsheet time and Java Script time are not the same they have different starting points:

GAS: How to read the correct time values form Google Spreadsheet