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
– Alan Wells, Commented Dec 22, 2017 at 17:42
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
– Thomas Perrin, Commented Dec 22, 2017 at 21:29

Wicket · Accepted Answer · 2017-12-23 18:37:10Z

1

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

edited Dec 23, 2017 at 18:37

answered Dec 22, 2017 at 20:35

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thomas Perrin Over a year ago

Hi, I already tried with getDisplayValue() but I still got 23:01 in my variable instead of 00:01

Wicket Over a year ago

@ThomasPerrin Update your answer to show the code that you are trying and the value shown both on the spreadsheet cell and on the formula bar.

Thomas Perrin Over a year ago

thanks for your help @Rubén, I edited the main Question with all infos.

Wicket Over a year ago

@ThomasPerrin : What is the locale/timezone of your spreadsheet?

Thomas Perrin Over a year ago

It says (GMT+01:00)Paris

Collectives™ on Stack Overflow

Cannot Access Duration Value in Google Sheets with Google Apps Script

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related