google app script date parse is get wrong

Question

var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataa = ss.getSheetByName("123").getRange("C1").getValue();
// C1 => 18:34:00.000
Logger.log(Utilities.formatDate(dataa, "GMT", "HH:mm"));
//Log =>[20-01-24 17:31:02:475 JST] 10:06

I bring the date of the spread sheet.

It was expressed as a google app script date.

However, the results are different.

Why is this like this?

The date is from 1 timezone and you're formatting it to another it seems. Check your spreadsheet & script timezones. — ross
– ross, Commented Jan 24, 2020 at 8:55
You might not need to format for comparison purposes. Also as ross said, the timezone of your spreadsheet is most likely different from GMT — Karan
– Karan, Commented Jan 24, 2020 at 9:00
@cloudbabby Please show us what the value in C1 looks like in the spreadsheet. That output looks like duration, not datetime. — Diego
– Diego, Commented Jan 24, 2020 at 9:01
If the format is always the same hh:mm:ss then you can split them as a string on : and compare the resulting array values: [hh,mm,ss]. Also convert them to numbers first using array.map — Karan
– Karan, Commented Jan 24, 2020 at 9:19

Diego · Accepted Answer · 2020-01-24 09:22:15Z

The problem you're facing is with .getValue(), which will parse the data in the cell into whatever Google thinks it is. To get the value exactly as you see it in the spreadsheet, use .getDisplayValue().

Assuming that the values in column C are always in the same format (HH:mm:ss), you can simply split the value using the colon as delimiter and then compare these values against your control. (Take a look at the various Date methods to compare against hours, minutes, etc.)

var range = ss.getSheetByName("Sheet1").getRange("C1"); // 18:34:00
var displayValue = range.getDisplayValue();
var displayValueParts = displayValue.split(":");
var hour = displayValueParts[0];
var minutes = displayValueParts[1];
var seconds = displayValueParts[2]

Note here that just using .getValue(), Google interprets the value in C1 as a date.

var range = ss.getSheetByName("Sheet1").getRange("C1"); // 18:34:00
var value = range.getValue();
Logger.log(value); // Sat Dec 30 11:51:56 GMT+00:00 1899

Collectives™ on Stack Overflow

google app script date parse is get wrong

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related