DEV Community

Vinicius Basilio
Vinicius Basilio

Posted on

Como somar horas no Google Sheets usando critérios para filtrar linhas?

Olá, pessoal! Hoje quero compartilhar um script super útil que criei para resolver um problema comum no dia a dia.

Se você já tentou somar "duração" no Google Sheets, deve ter percebido que as fórmulas SUMIF e SUMIFS não funcionam para somar durações de eventos ou produtos com base em critérios específicos. Isso pode ser um obstáculo dependendo do tipo de cálculo que você precisa fazer. Mas não se preocupe! O Google Sheets permite criar scripts em JavaScript e usá-los como fórmulas personalizadas.

No meu script, criei duas variações: a primeira aceita um critério e a segunda até dois. Estou planejando melhorar isso no futuro para tornar a função ainda mais flexível.

Vale lembrar que as fórmulas personalizadas não retornam valores diretamente computáveis pelo programa. Para contornar isso, você pode envolver o resultado com a função =VALUE(). Depois, basta aplicar a formatação correspondente ao tipo de dado — no nosso caso, "duração". Pronto para conferir o script?

Criando dados genéricos para teste

Antes de mais nada, vamos gerar dados para testar a fórmula. Usei nosso amigo GPT para isso.

Título Duração Categoria Status
Perdido em Marte 01:00:00 Filme Já assisti
Interestelar 02:49:00 Filme Já assisti
John Wick 01:30:00 Filme Já assisti
Vingadores: Ultimato 03:00:00 Filme Quero assistir
Stranger Things 00:45:00 Série Assistindo
The Witcher 01:00:01 Série Assistindo
O Mandaloriano 00:40:00 Série Assistindo
Breaking Bad 00:50:00 Série Já assisti
A Casa de Papel 00:55:00 Série Quero assistir
Game of Thrones 01:10:00 Série Quero assistir

Script

Tentei documentar tudo o melhor possível. Resolvi separar em funções menores e usar algo mais declarativo para aumentar a clareza do código.

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

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

function converterHMSParaSegundos(hms) {
  // Quebra a string do formato HH:MM:SS em partes
  const partes = String(hms).split(":");

  // Converte as partes em números inteiros
  const [horas, minutos, segundos] = partes;

  // Converte horas e minutos para segundos e soma com os segundos
  const totalSegundos =
    Number(horas) * 3600 + Number(minutos) * 60 + Number(segundos);

  return Number(totalSegundos);
}

function converterSegundosParaHMS(segundos) {
  // Calcula o número de horas, minutos e segundos
  const horas = Math.floor(segundos / 3600);
  const minutos = Math.floor((segundos % 3600) / 60);
  const segundosRestantes = segundos % 60;

  // Adiciona zero à esquerda para garantir que tenha sempre dois dígitos
  const formatoHoras = String(horas).padStart(2, "0");
  const formatoMinutos = String(minutos).padStart(2, "0");
  const formatoSegundos = String(segundosRestantes).padStart(2, "0");

  // Retorna o formato HH:MM:SS
  return `${formatoHoras}:${formatoMinutos}:${formatoSegundos}`;
}

/**
 * Soma as horas baseado um critério.
 *
 * @param {string[]} intervalo_soma - Conjunto de intervalos de tempo em formato HH:MM:SS.
 * @param {number[]} intervalo_de_criterios - Conjunto de critérios correspondentes aos intervalos de tempo.
 * @param {number} criterio - O critério para o qual as horas devem ser somadas.
 * @returns {string} Soma das durações passadas, ou uma mensagem de erro.
 */
function somarHorasSe(intervalo_soma, intervalo_de_criterios, criterio) {
  if (!todosSaoArrays(intervalo_soma, intervalo_de_criterios))
    return "Passe os intervalos para o calculo!";

  if (!todosArraysTemOMesmoTamanho(intervalo_soma, intervalo_de_criterios))
    return "Os intervalos devem ter o mesmo tamanho";

  // Filtra os intervalos de tempo para o critério específico
  const horasParaSomar = intervalo_soma.filter(
    (linha, index) =>
      String(intervalo_de_criterios[index]).trim() == String(criterio).trim()
  );

  // Converte as horas filtradas para segundos
  const horasEmSegundos = horasParaSomar.map((n) =>
    converterHMSParaSegundos(n)
  );

  // Soma todos os segundos
  const somaDosSegundos = horasEmSegundos.reduce((acumulador, valorAtual) => {
    return acumulador + valorAtual;
  }, 0);

  // Converte o total de segundos de volta para o formato HH:MM:SS
  return converterSegundosParaHMS(somaDosSegundos);
}

/**
 * Soma as horas baseado em critérios.
 *
 * @param {string[]} intervalo_soma - Conjunto de intervalos de tempo em formato HH:MM:SS.
 * @param {number[]} intervalo_de_criterios1 -  Primeiro conjunto de critérios correspondentes aos intervalos de tempo.
 * @param {number} criterio1 - O primeiro critério para o qual as horas devem ser somadas.
 * @param {string[]} intervalo_de_criterios2 -  Segundo conjunto de critérios correspondentes aos intervalos de tempo.
 * @param {string} semestre - O segundo critério para o qual as horas devem ser somadas.
 * @returns {string} Soma das durações passadas, ou uma mensagem de erro.
 */
function somarHorasSe2(
  intervalo_soma,
  intervalo_de_criterios1,
  criterio1,
  intervalo_de_criterios2,
  criterio2
) {
  if (
    !todosSaoArrays(
      intervalo_soma,
      intervalo_de_criterios1,
      intervalo_de_criterios2
    )
  )
    return "Passe os intervalos para o calculo!";

  if (
    !todosArraysTemOMesmoTamanho(
      intervalo_soma,
      intervalo_de_criterios1,
      intervalo_de_criterios2
    )
  )
    return "Os intervalos devem ter o mesmo tamanho";

  // Filtra os intervalos de tempo para os critérios passados
  const horasParaSomar = intervalo_soma.filter(
    (linha, index) =>
      String(intervalo_de_criterios1[index]) == String(criterio1).trim() &&
      String(intervalo_de_criterios2[index]).trim() === String(criterio2).trim()
  );

  // Converte as horas filtradas para segundos
  const horasEmSegundos = horasParaSomar.map((n) =>
    converterHMSParaSegundos(n)
  );

  // Soma todos os segundos
  const somaDosSegundos = horasEmSegundos.reduce((acumulador, valorAtual) => {
    return acumulador + valorAtual;
  }, 0);

  // Converte o total de segundos de volta para o formato HH:MM:SS
  return converterSegundosParaHMS(somaDosSegundos);
}

Enter fullscreen mode Exit fullscreen mode

Como usar?

Os critérios podem ser texto, ou números, mas as horas devem ser obrigatoriamente formatadas como tipo “Texto simples”.

Ilustração de onde fica o comando de formatação.

Vá até o app script:

Ilustração de onde fica  comando para abrir o app script

Cole o script e de "CTRL + S". Pronto. Para usar é o mesmo processo de uma fórmula nativa.

Uma vez aplicada a fórmula, podemos voltar a tratar como um tipo que o programa entenda usando a VALUE, seu código deve ficar assim:

=VALUE(somarHorasSe2($C$2:$C$11;$D$2:$D$11;C$14;$E$2:$E$11;$B15))
Enter fullscreen mode Exit fullscreen mode

Caso tudo tenha dado certo, esse deve ser seu resultado:

Ilustração do resultadi final

Foi só uma dica, espero que tenham gostado, e se tiverem sugestões deixem no comentário. Abraços.

Top comments (0)