Controle de validade de produtos no Google Sheets e Apps Script

Manter o controle de validade de produtos é essencial para qualquer negócio que lide com estoque, como supermercados, farmácias ou lojas de conveniência. Produtos vencidos não só fazem um negócio perder dinheiro, como colocam em risco a reputação da empresa, a segurança dos consumidores e podem trazer inúmeros problemas jurídicos.

De forma simples e com uma planilha que você poderá fazer uma cópia/download ao final, vamos te mostrar como automatizar o controle de validade de produtos no Google Sheets usando o Google Apps Script para receber alertas automáticos via e-mail quando os produtos estiverem perto de vencer.

Apps Script: O que é e como funciona o VBA do Google Sheets

Com os alertas automatizados, você pode se antecipar e se livrar de um problema futuro – podendo até mesmo, dar descontos para vender mais rapidamente os produtos próximos do vencimento.

Como montar a planilha de controle de validade

Para começar, você terá que estruturar uma tabela no Google Planilhas. Nosso exemplo será com base em uma tabela simples de 3 colunas, como essa abaixo:

A partir dessa tabela, vamos configurar um script para enviar e-mails com alguma recorrência (diário, semanal, etc), quando produtos estiverem próximos do vencimento.

Se você tem uma base de dados do estoque em outra planilha, com muitas outras colunas, você pode criar uma nova planilha somente para atender essa automação. Use QUERY com IMPORTRANGE para selecionar somente esses 3 dados vitais para o funcionamento desse script.

Guia definitivo: usando IMPORTRANGE no Google Sheets

Automatizando o controle de validade com Google Apps Script

Agora que você já tem uma tabela no Google Sheets, o próximo passo é usar o Apps Script para criar uma automação que vai verificar a validade dos produtos linha por linha, e irá notificar por e-mail com aqueles próximos do vencimento.

Se você ainda não tem muita experiência com códigos no Apps Script, essa é uma ótima oportunidade para aprender. Não se preocupe, vamos disponibilizar abaixo o código completo para cuidar disso.

function enviarAlertaVencimentos() {
  const planilha = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const hoje = new Date();
  const diasEmMilissegundos = 30 * 24 * 60 * 60 * 1000; // 30 dias
  const dataLimite = new Date(hoje.getTime() + diasEmMilissegundos);

  const intervaloDados = planilha.getDataRange();
  const dados = intervaloDados.getValues();

  // Cabeçalhos da planilha
  const [cabecalho, ...linhas] = dados;

  const indiceEAN = cabecalho.indexOf('EAN (código de barras)');
  const indiceQuantidade = cabecalho.indexOf('Quantidade em Estoque');
  const indiceValidade = cabecalho.indexOf('Validade');

  // Array para armazenar os produtos que vão vencer em 30 dias
  let produtosVencendo = [];

  linhas.forEach(linha => {
    const dataValidade = new Date(linha[indiceValidade]);

    if (dataValidade <= dataLimite && dataValidade >= hoje) {
      const eanProduto = linha[indiceEAN];
      const quantidadeProduto = linha[indiceQuantidade];

      // Formatação da data usando locale pt-BR (DD/MM/YYYY)
      const validadeFormatada = dataValidade.toLocaleDateString('pt-BR');

      produtosVencendo.push(`EAN: ${eanProduto}, Qtd: ${quantidadeProduto}, Validade: ${validadeFormatada}`);
    }
  });

  if (produtosVencendo.length > 0) {
    const listaProdutos = produtosVencendo.join('\n');

    // Enviar e-mail
    const emailDestinatario = "[email protected]"; // Substitua pelo seu e-mail
    const assuntoEmail = "Produtos vencendo nos próximos 30 dias";
    const corpoEmail = `Os seguintes produtos estão com validade próxima:\n\n${listaProdutos}`;

    MailApp.sendEmail(emailDestinatario, assuntoEmail, corpoEmail);

    Logger.log("E-mail enviado com sucesso.");
  } else {
    Logger.log("Nenhum produto está vencendo nos próximos 30 dias.");
  }
}

Leia atentamente abaixo para entender como funciona e como adicionar o script na sua planilha.

Como funciona o código

O código pode parecer complexo, mas não é. Basicamente, o script vai percorrer linha por linha dos produtos da sua planilha e vai verificar se a data de validade está dentro dos próximos 30 dias.

Caso esteja, o código de barras do produto, a quantidade em estoque e a data de validade em si serão capturados, e adicionados a uma lista de dados temporária que será usada na etapa de enviar o e-mail.

MUITO IMPORTANTE: O e-mail, por padrão, será enviado para [email protected]. Você deve alterar isso na variável emailDestinatario, inclusive podendo adicionar novos endereços – separando por vírgula: email1, email2, email3

A variável assuntoEmail é o título do e-mail e corpoEmail nada mais que é a mensagem que será enviada. Você pode modificar como quiser e caso queira quebrar a linha, use barra invertida + n: \n

Como adicionar o script na sua planilha

1. Abra a sua planilha.
2. No menu superior, vá para Extensões > Apps Script.
3. Copie todo o código que disponibilizamos acima, cole e salve o projeto.
4. Clique em Executar para conceder as permissões necessárias ao script.
   • Permissões necessárias: leitura dos dados da planilha e Gmail para envio dos e-mails de alertas.
5. Feito isso, agora é só criar um gatilho do script para ele executar automaticamente com alguma recorrência. Clique no ícone do relógio “Recorrência” e configure a execução automática.

Tome como exemplo o acionador abaixo:

Nesse caso, a minha função enviarAlertaVencimentos() será executada todos os dias entre 8 a 9h da manhã.

A título de curiosidade, caso tenham produtos vencendo nos próximos 30 dias, o e-mail de alerta será como esse abaixo:

Planilha para cópia/download

❎

Clique no botão abaixo para acessar o Google Sheets.

Abrir Planilha

Faça uma cópia da Planilha​

E-mail

Ao fazer uma cópia, você continua aceitando a nossa Política de Privacidade. 🔒

FAZER UMA CÓPIA

Conclusão

Não há dúvidas de que automatizar uma tarefa é uma maravilha! Fazer isso com o controle de validade de produtos no Google Sheets + Apps Script não é diferente. Isso não só melhora a gestão do estoque, como também evita desperdícios e prejuízos ao negócio.

Aqui no Blog da Octadata não nos limitamos a explicar como as coisas funcionam. Nosso foco também é trazer soluções aplicáveis para o dia a dia com Google Sheets, Apps Script e outras ferramentas Google.

Aproveite e entre para a Comunidados, nosso grupo 100% gratuito no WhatsApp para compartilhar conhecimento e resolver dúvidas.

Perguntas e Respostas (Q&A)