Bonjour à tous! Aujourd'hui, je souhaite partager un script très utile que j'ai créé pour résoudre un problème courant dans la vie quotidienne.

Si vous avez déjà essayé de additionner la « durée » dans Google Sheets, vous avez peut-être remarqué que les formules SUMIF et SUMIFS ne fonctionnent pas pour additionner les durées d'événements ou de produits en fonction de critères spécifiques. Cela peut être un obstacle selon le type de calcul que vous devez effectuer. Mais ne vous inquiétez pas ! Google Sheets vous permet de créer des scripts en JavaScript et de les utiliser comme formules personnalisées.

Dans mon script, j'ai créé deux variantes : la première accepte un critère et la seconde en accepte jusqu'à deux. Je prévois d'améliorer cela à l'avenir pour rendre la fonction encore plus flexible.

Il convient de mentionner que les formules personnalisées ne renvoient pas de valeurs directement calculables par le programme. Pour contourner ce problème, vous pouvez envelopper le résultat avec la fonction =VALUE(). Ensuite, appliquez simplement le formatage correspondant au type de données – dans notre cas, « durée ». Prêt à découvrir le script ?

Création de données génériques pour les tests

Tout d'abord, générons des données pour tester la formule. J'ai utilisé notre ami GPT pour cela.

Scénario

J'ai essayé de tout documenter du mieux possible. J'ai décidé de le diviser en fonctions plus petites et d'utiliser quelque chose de plus déclaratif pour augmenter la clarté du code.

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);
}

Comment utiliser ?

Les critères peuvent être du texte ou des chiffres, mais les heures doivent être formatées en « Texte brut ». Accédez au script de l'application, collez le script et appuyez sur "CTRL S". Fait. Pour l'utiliser, c'est la même démarche qu'une formule native.

Une fois la formule appliquée, nous pouvons la traiter à nouveau comme un type que le programme comprend en utilisant VALUE ; votre code devrait ressembler à ceci :

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

Si tout s'est bien passé, voici votre résultat :

Juste un conseil, j'espère que cela vous a plu, et si vous avez des suggestions, laissez-les dans les commentaires. Acclamations.

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