patrick patrick - 1 year ago 81
Javascript Question

How to read the correct time values from Google Spreadsheet

I'm trying to get from a time formatted Cell (hh:mm:ss) the hour value, the values can be bigger 24:00:00 for example 20000:00:00 should give 20000:


two Google Spread Sheet

if your read the Value of E1:

var total = sheet.getRange("E1").getValue();

The result is:

Sat Apr 12 07:09:21 GMT+00:09 1902

Now I've tried to convert it to a Date object and get the Unix time stamp of it:

var date = new Date(total);
var milsec = date.getTime();
var hours = milsec / 1000 / 60 / 60;



The question is how to get the correct value of 20000 ?

Answer Source

Expanding on what Serge did, I wrote some functions that should be a bit easier to read and take into account timezone differences between the spreadsheet and the script.

function getValueAsSeconds(range) {
  var value = range.getValue();

  // Get the date value in the spreadsheet's timezone.
  var spreadsheetTimezone = range.getSheet().getParent().getSpreadsheetTimeZone();
  var dateString = Utilities.formatDate(value, spreadsheetTimezone, 
      'EEE, d MMM yyyy HH:mm:ss');
  var date = new Date(dateString);

  // Initialize the date of the epoch.
  var epoch = new Date('Dec 30, 1899 00:00:00');

  // Calculate the number of milliseconds between the epoch and the value.
  var diff = date.getTime() - epoch.getTime();

  // Convert the milliseconds to seconds and return.
  return Math.round(diff / 1000);

function getValueAsMinutes(range) {
  return getValueAsSeconds(range) / 60;

function getValueAsHours(range) {
  return getValueAsMinutes(range) / 60;

You can use these functions like so:

var range = SpreadsheetApp.getActiveSheet().getRange('A1');

Needless to say, this is a lot of work to get the number of hours from a range. Please star Issue 402 which is a feature request to have the ability to get the literal string value from a cell.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download