Panorama

The spreadsheet and Javascript handle dates differently so it is necessary to introduce a few 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 helper 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;
}

Example of use to register the hours of the cell A1.

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

Explanation

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

Spreadsheets use as a reference date 12/30/1899while Javascript uses 01/01/1970, this means that there is a difference of 25,568 days between both references. This is 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 the two dates.

In this case you don't want to know the actual date of something, instead you want an absolute value of hours, for example a duration, so you need to remove the 25.568 day offset. This is done using the getTime()Javascript method which returns milliseconds counted from the reference date, the only thing you know is the value in milliseconds of the reference spreadsheet date and subtract this value from the date object. current. Then a bit of 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 note that documentation is yet to be published. It was resolved by adding range.getDisplayValue and range.getDisplayValues, however, this might not be useful if you don't 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. Introduced a line break on one of the lines that was too long to prevent the horizontal scrollbar from showing.

Short answer

From a beginner's, or "quick and dirty" point of view, perhaps the simplest solution is that instead of using methods like getValue/ getValuesuse getDisplayValue/ getDisplayValuesto work with String objects instead of Date objects.

Basic concepts

Internally, Google and JavaScript spreadsheets use serial numbers to handle dates and times, that is, they use an "EPOCH", as a reference point or zero and from which time units are added to determine the date-time.

time unit

• In Google spreadsheets the unit of time is a day
• In JavaScript it is a millisecond.

EPOCH

• In the Google spreadsheet the EPOCH is December 30, 1899 00:00:00. Does not use time zone
• In JavaScript the EPOCH is January 1, 1970, 00:00:00 UTC

Conversion

When Google Apps Script reads a time value, it also considers the spreadsheet time zone and the Apps Script project time zone, which are not always the same, and this could add a few minutes and seconds in certain cases, for example

• If in the Google spreadsheet configured with the Central Mexico time zone there is 0:00:00, the Date object will showSat Dec 30 1899 00:36:36 GMT-0600 (CST)

Example

Modification of the question code

function prueba() {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hoja = libro.getSheetByName("Hoja 1");
  var datos = hoja.getDataRange().getValues();
  var valor = datos[0][0].match(/\d\d/g);
  Logger.log(valor[0]); // Hora
  Logger.log(valor[1]); // Minuto
}

Another example

The following function takes the data from the active cell and if it is later than 7:05 it writes to the right Sí, and otherwise No. Use getDisplayValue and the match method of the String object with the regular expression /\d\d/g to extract the hour and minute.

function estaRetrasado() {
  var celdaActiva = SpreadsheetApp.getActiveRange();
  var destino = celdaActiva.offset(0, 1); 
  var horaSalida = celdaActiva.getDisplayValue();
  var hms = horaSalida.match(/\d\d/g); 
  var hora = 7;
  var minuto = 5;
  var resultado = (
    (hms[0] == hora && hms[1] > minuto) ||
    (hms[0] > hora)
  )?'Sí':'No';
  destino.setValue(resultado);
}