CODEWITHSUNDEEP
javascriptdatetimegoogle-apps-scriptspreadsheet
CHEWWWWWWWWWW
CHEWWWWWWWWWW

Reputation: 169

Getting time on Google spreadsheet

So I'm writing a script which requires reading a time value from a Google spreadsheet and using it. I've tried to run a test function to ensure that the values being extracted are correct but it seems that the values are different from what is reflected on the spreadsheet.

Input

function testTime(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("TimeSheet");
  var time = sheet.getRange(2,1).getValue();
  //value on the sheet is 10:00:00 AM 
  Logger.log(time);
  Logger.log(time.toTimeString());
}

Output

Sat Dec 30 09:30:59 GMT+07:36 1899
09:54:17 GMT+0800 (HKT)

The time is supposed to be 10:00:00 but is reflected as 09:54:17 instead which is the first weird error.

Secondly is there anyway where I can extract the actual value "10:00:00 AM" or even better in a the simpler format "10:00"?

EDIT:

I've found a way to solve the second issue and get the time in the desired format. However, there is still some weird interaction with the time. I've added 2 lines and it's respective output

All the logger lines

  Logger.log(Utilities.formatDate(time, "GMT+07:37", "HH:mm"));
  Logger.log(Utilities.formatDate(time, "GMT+08:00", "HH:mm"));
  Logger.log(time);
  Logger.log(time.toTimeString());

Output

10:00
10:23
Sat Dec 30 10:00:00 GMT+07:36 1899
10:23:18 GMT+0800 (HKT)

Under spreadsheet settings,

Locale = United States

Time Zone = (GMT+08:00) Hong Kong

Upvotes: 0

Views: 391

Answers (2)

CHEWWWWWWWWWW
CHEWWWWWWWWWW

Reputation: 169

Upon experimenting with Utilities.formatDate and Time Zone settings on both the spreadsheet and script files, I've found a solution for both issues.

  1. Issue with Time

Even though I'm in Singapore, I've set the Time Zone to (GMT+8:00) Taipei and it gives me the correct time which is 10:00:00AM. Don't know why but I guess it works

  1. Issue with Format

I simply used

Utilities.formatDate(time, "GMT+08:00", "HH:mm"));

which gave me my desired format which is

10:00

Upvotes: 0

Fi Teach
Fi Teach

Reputation: 88

  1. If you want to have exactly the same value as shown in spreadsheet cell, use var time = sheet.getRange(2,1).getDisplayValue();

  2. Also, you can break apart the long date/time string by using split(" ") method and then combining elements from resulting array in whatever way you want.

Upvotes: 2

Related Questions