javascript – How to read time values ​​correctly in a Google spreadsheet

Question:

Panorama

Cell A1 has the value 01:00 (one hour). By using a function like the following:

function prueba() {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hoja = libro.getSheetByName("Hoja 1");
  var datos = hoja.getDataRange().getValues();
  var valor = datos[0][0];
  Logger.log(valor.getHours());
  Logger.log(valor.getMinutes());
}

The values ​​are recorded

1.0  
36.0

What is expected is that the registered values ​​would be:

1.0  
0.0

How can I make my code read the expected values?

Comments

This is a "real question" for which I found the answer on stackoverflow.com . See the reference.

The question was not translated, but you could tell that it is based on the reference.

References

Answer:

Panorama

The spreadsheet and Javascript handle dates differently, so it is necessary to enter an additional lines of code. A main function and two complementary ones are presented to obtain seconds, minutes or hours from a date.

Code

Modifications to the function presented in the question:

function prueba() {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hoja = libro.getSheetByName("Hoja 1");
  /* Las siguientes líneas se puede retirar
    var datos = hoja.getDataRange().getValues();
    var valor = datos[1][1];
    Logger.log(valor.getHours());
    Logger.log(valor.getMinutes());
  */
  // Agregar las siguientes líneas
  var h = Math.floor(getValueAsHours(hoja.getRange(2,2)));
  var m = Math.floor(getValueAsMinutes(hoja.getRange(2,2))) - h*60;
  Logger.log(h);
  Logger.log(m);
}

From Eric Koleda's answer on 2013-07-22 14: 20: 54Z the following auxiliary functions were taken:

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

  // Tomar el valor de fecha en la zona horaria de la hoja de cálculo.
  var spreadsheetTimezone = range.getSheet().getParent()
      .getSpreadsheetTimeZone();
  var dateString = Utilities.formatDate(value, spreadsheetTimezone, 
      'EEE, d MMM yyyy HH:mm:ss');
  var date = new Date(dateString);

  // Inicializar la fecha del epoch.
  var epoch = new Date('Dec 30, 1899 00:00:00');

  // Calcular el número de milisegundos entre el epoch y el valor
  var diff = date.getTime() - epoch.getTime();

  // Convertir los milisegundos a segundos y devolver el resultado.
  return Math.round(diff / 1000);
}

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

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

Usage example to record the hours in cell A1 .

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

Explanation

From Sergei insas answer on 2013-07-18 15: 36: 28Z

The spreadsheets use 12/30/1899 as the reference 12/30/1899 while Javascript uses 01/01/1970 , this means that there are 25,568 days of difference between both references. This assuming the same time zone is used on both systems. When converting a date value in a spreadsheet to a Javascript date object the GAF engine automatically adds the difference to maintain consistency between both dates.

In this case, you do not want to know the real date of something, instead you want an absolute value of hours, for example, a duration, so it is necessary to remove the 25,568 day offset. This is done using the getTime() method which results in milliseconds counted from the Javascript reference date, the only thing you know is the value in milliseconds of the reference spreadsheet date and subtract this value from the current date object. Then a little math to get hours instead of milliseconds and we're done.

Comments

Issue 402: Get literal string from a spreadsheet range was mentioned in Eric's answer, but this was marked as fixed 4 days ago with the observation that the documentation is about to be published. It was solved by adding range.getDisplayValue and range.getDisplayValues, however, this could not be useful in the case that you do not want to modify the format in which a date type data is displayed.

Regarding the code, the comments included in it were translated, but the variable names were left as is. A line break was introduced on one of the lines that was too long to prevent the horizontal scroll bar from displaying.

Post Views: 0
Scroll to Top