¡Hola a todos! Hoy quiero compartir un script súper útil que creé para resolver un problema común en la vida diaria.

Si alguna vez intentó sumar la "duración" en Google Sheets, es posible que haya notado que las fórmulas SUMAR.SI y SUMIFS no funcionan para sumar duraciones de eventos o productos según criterios específicos. Esto puede ser un obstáculo dependiendo del tipo de cálculo que necesites hacer. ¡Pero no te preocupes! Google Sheets te permite crear scripts en JavaScript y usarlos como fórmulas personalizadas.

En mi guión, creé dos variaciones: la primera acepta un criterio y la segunda acepta hasta dos. Estoy planeando mejorar esto en el futuro para que la función sea aún más flexible.

Vale la pena mencionar que las fórmulas personalizadas no devuelven valores directamente computables por el programa. Para solucionar este problema, puede ajustar el resultado con la función =VALUE(). Luego, simplemente aplique el formato correspondiente al tipo de datos (en nuestro caso, "duración"). ¿Listo para ver el guión?

Crear datos genéricos para pruebas.

En primer lugar, generemos datos para probar la fórmula. Utilicé nuestro amigo GPT para esto.

Guion

Traté de documentar todo lo mejor posible. Decidí dividirlo en funciones más pequeñas y usar algo más declarativo para aumentar la claridad del código.

function allAreArrays(...arrays) {
  return arrays.every(Array.isArray);
}

function allArraysHaveSameLength(...arrays) {
  const lengths = arrays.map((arr) => arr.length);
  return lengths.every((val) => val === lengths[0]);
}

function convertHMSToSeconds(hms) {
  // Breaks the string in HH:MM:SS format into parts
  const parts = String(hms).split(":");

  // Converts the parts into integers
  const [hours, minutes, seconds] = parts;

  // Converts hours and minutes into seconds and adds the seconds
  const totalSeconds =
    Number(hours) * 3600   Number(minutes) * 60   Number(seconds);

  return Number(totalSeconds);
}

function convertSecondsToHMS(seconds) {
  // Calculates the number of hours, minutes, and seconds
  const hours = Math.floor(seconds / 3600);
  const minutes = Math.floor((seconds % 3600) / 60);
  const remainingSeconds = seconds % 60;

  // Adds a leading zero to ensure it always has two digits
  const hourFormat = String(hours).padStart(2, "0");
  const minuteFormat = String(minutes).padStart(2, "0");
  const secondFormat = String(remainingSeconds).padStart(2, "0");

  // Returns the HH:MM:SS format
  return `${hourFormat}:${minuteFormat}:${secondFormat}`;
}

/**
 * Sums hours based on a criterion.
 *
 * @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
 * @param {number[]} criteria_range - Set of criteria corresponding to the time intervals.
 * @param {number} criterion - The criterion for which hours should be summed.
 * @returns {string} Sum of the passed durations, or an error message.
 */
function sumHoursIf(sum_range, criteria_range, criterion) {
  if (!allAreArrays(sum_range, criteria_range))
    return "Pass the intervals for the calculation!";

  if (!allArraysHaveSameLength(sum_range, criteria_range))
    return "Intervals must be the same size";

  // Filters the time intervals for the specific criterion
  const hoursToSum = sum_range.filter(
    (row, index) =>
      String(criteria_range[index]).trim() == String(criterion).trim()
  );

  // Converts the filtered hours to seconds
  const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));

  // Sums all the seconds
  const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
    return accumulator   currentValue;
  }, 0);

  // Converts the total seconds back to HH:MM:SS format
  return convertSecondsToHMS(sumOfSeconds);
}

/**
 * Sums hours based on criteria.
 *
 * @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
 * @param {number[]} criteria_range1 - First set of criteria corresponding to the time intervals.
 * @param {number} criterion1 - The first criterion for which hours should be summed.
 * @param {string[]} criteria_range2 - Second set of criteria corresponding to the time intervals.
 * @param {string} criterion2 - The second criterion for which hours should be summed.
 * @returns {string} Sum of the passed durations, or an error message.
 */
function sumHoursIf2(
  sum_range,
  criteria_range1,
  criterion1,
  criteria_range2,
  criterion2
) {
  if (
    !allAreArrays(
      sum_range,
      criteria_range1,
      criteria_range2
    )
  )
    return "Pass the intervals for the calculation!";

  if (
    !allArraysHaveSameLength(
      sum_range,
      criteria_range1,
      criteria_range2
    )
  )
    return "Intervals must be the same size";

  // Filters the time intervals for the passed criteria
  const hoursToSum = sum_range.filter(
    (row, index) =>
      String(criteria_range1[index]) == String(criterion1).trim() &&
      String(criteria_range2[index]).trim() === String(criterion2).trim()
  );

  // Converts the filtered hours to seconds
  const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));

  // Sums all the seconds
  const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
    return accumulator   currentValue;
  }, 0);

  // Converts the total seconds back to HH:MM:SS format
  return convertSecondsToHMS(sumOfSeconds);
}

¿Cómo utilizar?

Los criterios pueden ser texto o números, pero las horas deben tener el formato "Texto sin formato". Vaya al script de la aplicación, péguelo y presione "CTRL S". Hecho. Para usarlo, es el mismo proceso que una fórmula nativa.

Una vez aplicada la fórmula, podemos tratarla nuevamente como un tipo que el programa entiende usando VALOR; tu código debería verse así:

=VALUE(sumHoursIf2($C$2:$C$11;$D$2:$D$11;C$14;$E$2:$E$11;$B15))

Si todo salió bien, este debería ser tu resultado:

Solo un tip, espero que te haya gustado, y si tienes sugerencias déjalas en los comentarios. Salud.

Publicación original: https://dev.to/casewinter/como-somar-horas-no-google-sheets-usando-criterios-para-filtrar-linhas-364p