Cannot Access Duration Value in Google Sheets with Google Apps Script

Question

In my Google Sheet, cell A3, there is a duration value of 00:01:00

Then I access this value in Google Sheets as so...

var my_time = my_sheet.getRange("A3").getValue();
var hms = Utilities.formatDate(my_time ,"GMT", "HH:mm:ss");

When I compare the two variables, I can't get the same duration time in both variables. Instead I have...

my_time | Date (1473042018) | Sat Dec 30 1899 00:01:00 GMT+0100 (CET)

hms | String | "23:01:00"

EDIT: Here is the public_file for testing; Here the code (accessible through the file):

function myFunction() {
  var my_sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var result_cell = my_sheet.getRange("A4");
  result_cell.clear();

  var my_time = my_sheet.getRange("A3").getValue();
  var hms = Utilities.formatDate(my_time ,"GMT", "HH:mm:ss");
  result_cell.setValue(hms);}

What I am trying to do is get the duration value in cell A3, in a variable in Google Apps Script.

I don't understand why A3.getValue() returns "Sat Dec 30 1899 00:51:39 GMT+0100 (CET)" when the typed time is "00:01:00". I expected "Sat Dec 30 1899 00:01:00 GMT+0100 (CET)"

Below is a screenshot of my spreadsheet and code:

I'm assuming that you want both variables to have an identical value? Which one do you want to change? The first,the second? Both? If you want to change the second variable to match the first, then you'll need to change "HH:mm:ss" to something else. For date and time patterns that are available to use, see the documentation at the following link: Link - Format Date and time patterns — Alan Wells
Since the source (A3) = "00:01:00", my_time is correct (I ignore the date, the time is good), but hms is not good, I thought I'll get "00:01:00" but I found "23:01:00" instead. What I want is to get in a variable in Google Apps Script the duration typed in A3 by the user ("00:01:00"), and actually I can't success to get that time, I get 23:01:00 instead. — Thomas Perrin

Rubén Rubén · Accepted Answer · 2017-12-22T20:35:45

Google Sheets and Google Apps Script don't use the same base value to calculate date-time values, so it's simpler to get the the duration by using getDisplayValue() than using getValue() and using JavaScript / Google Apps Script services classes and methods.

Example:

A1 cell displayed value: 23:01:00
Formula bar displayed value: 23:01:00.000

Script:

function myFunction() {
  var duration = SpreadsheetApp.getActiveSheet().getRange("A1").getDisplayValue();
  Logger.log(duration);
}

Logs:

[17-12-23 12:33:02:470 CST] 23:01:00

Cannot Access Duration Value in Google Sheets with Google Apps Script

1 Answers