Pressione enter para ver os resultados ou esc para cancelar.

Automatizando o envio de emails pelo Google Spreadsheet

Continuando com a série de posts sobre Google Apps Script vamos apresentar uma ferramenta (código) que pode ser útil e muito legal para quem utiliza Spreadsheet do Google e precisa automatizar o envio de seus emails, criando a function sendEmails.

Algumas das utilidades da planilha serão:

Controle de Assinaturas
Se você assina ou vende assinatura de um determinado serviço, poderá colocar um alerta para uma determinada data ou prazo de encerramento da assinatura;

Controle de Vencimentos
Possibilita o controle das datas de vencimento de suas vendas, enviando email para seus credores numa determinada data ou prazo pré-determinado;

Controle Interno
Enviará um email para sua equipe com um alerta, assim que for identificado que um projeto esteja próximo ao seu prazo de entrega ou atrasado.

Entre outras.

Antes de começar o código, vamos criar uma tabela simples com 6 colunas conforme exemplo abaixo:
Pasted image at 2017_02_22 06_11 PM

 

  • Coluna 1 ou A (email adress), na programação é comum chamar a coluna pelo numeral, coloque a lista de emails a qual deseja direcionar a mensagem;
  • Coluna 2 (subject) será o título do email;
  • Coluna 3 (message) coloque a mensagem que vai aparecer no corpo do email;
  • Coluna 4 (dias para expirar) ficará os dias que faltam para expirar ou vencer uma determinada assinatura;
  • Coluna 5 (dias de contrato) os dias de contrato firmado;
  • Coluna 6 (início da assinatura) a data de início da assinatura.

Na célula “H2” temos a fórmula “countif” que conta o número de linhas ou de clientes da tabela. A função countif recebe dois parâmetros. O intervalo a ser verificado e a expressão de contagem. No caso “<>” significa diferente de branco ou nulo. Ao final subtraímos 1 do resultado para descontar a primeira linha com os rótulos das colunas:

Pasted image at 2017_02_22 06_12 PM

Detalhe da coluna 3 (message) é que podemos automatizar o número de dias a expirar dentro dela, conforme exemplo abaixo:

Pasted image at 2017_02_22 06_12 PM (1)

 

Exemplo de texto/fórmula:

Pasted image at 2017_02_22 06_13 PM

 

Na coluna 4 (dias para expirar), colocamos a fórmula utilizando a função “today()”. Por exemplo, se você desejar enviar um alerta para um cliente avisando que a assinatura dele acaba em alguns dias, faremos da seguinte forma:

Pasted image at 2017_02_22 06_13 PM (1)

Exemplo para 180 dias de assinatura. O cliente será avisado quando estiver faltando 20 dias para o fim de sua assinatura.

Feito isso…

Vamos programar!!!

1. Abra o editor de scripts.

Pasted image at 2017_02_22 06_14 PM

 

2. Após aberto, salve um nome para seu projeto.

Pasted image at 2017_02_22 06_14 PM (1)

 

3. Na primeira parte do código, vamos criar a função “sendEmails ()” e ativar a planilha a qual vamos utilizar, definir de qual linha será a primeira a ser processada e o número de linhas que iremos utilizar. O número de linhas é definido pela variável “numRows” que é selecionada como a célula “H2”, que sua vez, contém a fórmula “countif” e é representada no código pelo “sheet.getRange(2, 8)”.

Pasted image at 2017_02_22 06_14 PM (2)

 

4. Usando a função “for” vamos buscar os valores dentro da planilha.

Pasted image at 2017_02_22 06_15 PM

 

5. A função MailApp.sendEmail(emailAdress, subject, message)” enviará os emails para os destinatários inclusos na coluna 1 (A) da planilha, o mesmo acontecerá com o título (subject) e a mensagem (message) no corpo do texto.

Pasted image at 2017_02_22 06_16 PM

Abaixo o código por completo:

Pasted image at 2017_02_22 06_19 PM

Podemos acrescentar um botão para acionar o código com mais agilidade, sem precisar ir ao editor de scripts. Você encontrará como fazer no primeiro post da série Google Apps Scripts.
E, por fim, é possível criar uma função condicional usando a variável “expire como por exemplo: definir que somente serão enviados os emails se o cliente estiver com a assinatura vencida:

Pasted image at 2017_02_22 06_20 PM

Ou quando faltar menos de 30 dias para vencer a assinatura:

Pasted image at 2017_02_22 06_20 PM (1)

E assim por diante.

Aqui na Taller nós amamos números e gostamos de automatizar ao máximo nosso trabalho e espero que com esse conteúdo possa ajudar você a ter mais tempo para fazer coisas mais importantes ainda.

Tem alguma dúvida? Quer aprender algo novo com Google Spreadsheet? É só escrever nos comentários!

Para mais informações sobre essa funcionalidade do GAS você pode acessar aqui.

 


***
Recado da Taller:
Criamos o Programa de Otimização da Gestão Ágil para quem quiser levar as práticas de eficiência de trabalho para dentro da sua empresa.

Conheça a Programa →

***

  • Raphael Santos

    Boa tarde!
    Onde ficaria, considerando a estrutura completa, a condição do expire? Viria depois da expressão completa?
    Entendo que ela seria ativada de acordo com o valor da célula correspondente à expiração e só então o email daquela linha seria enviado, correto?

    • Anderson Cunha

      Olá Raphael! Obrigado pelo comentário…
      Para fazer com que o expire funcione basta trocar a linha 19 que vemos no exemplo completo pelo código que exibimos logo abaixo.
      Logo se você quiser enviar um email 5 dias antes da expiração bastaria trocar o código da linha 19 por:
      if (expire ‘ ao invés de ‘<'.
      Espero ter ajudado!

  • Gustavo Marques

    Olá. Muito bom. Olha… Gostaria de vincular duas planilhas principais com uma planilha que busque os resultados de um critério (2). Assim, se atendido o critério nas planilhas primeiras, certas células seriam copiadas para esta (2). É possível? Obrigado!

  • Gustavo Marques

    Ah! Tem como o subject do email buscar o nome da planilha que gerou o envio? Valeuu!

  • Ricardo Saldanha

    Muito obrigado por esta aula. Encontrei aqui a resposta exacta para o meu problema. No entanto, não consigo filtrar os email’s pelo vencimento. Após reescrever a linha 19 o script continua a enviar 3 email’s. Quando devia enviar apenas 2. O que estou fazendo mal? Estou a começar a dar os primeiro passos nisto… Mais uma vez obrigado!

    function sendEmails() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;
    var numRows = sheet.getRange(2, 8).getValue();
    var dataRange = sheet.getRange(startRow, 1, numRows, 3);
    var data = dataRange.getValues();
    for(i in data){
    var row=data[i];
    var emailAddress = row [0];
    var subject = row [1];
    var message = row [2];
    var expire = row [3];

    if (expire<10); {

    MailApp.sendEmail(emailAddress, subject, message);

    }
    }
    }

    • Anderson Cunha

      Boa tarde Ricardo, obrigado pela observação.
      Detectamos o problema, está na linha 7 do código:
      “var dataRange = sheet.getRange(startRow, 1, numRows, 3)”, temos que alterar o “numRows” de 3 para 4, pois a condição está na quarta coluna (dias a expirar).
      Abaixo o código completo:

      function sendEmails() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var startRow = 2; // primeira linha de dados a ser processado
      var numRows = sheet.getRange(2, 8).getValue(); // número de linhas a serem processadas da célula H2

      // buscar no intervalo as células A2:D3
      var dataRange = sheet.getRange(startRow, 1, numRows, 4);

      // buscar valores para a linha no intervalo
      var data = dataRange.getValues();
      for (i in data) {
      var row = data[i];
      var emailAddress = row[0]; // coluna 1 – email
      var subject = row[1]; // coluna 2 – assunto
      var message = row[2]; // coluna 3 – mensagem
      var expired = row[3]; // coluna 4 – expiração

      if (expired > 0) {
      MailApp.sendEmail(emailAddress, subject, message);
      }
      }
      }

      Abraço!!!

  • Johansson Martins de Meneses G

    https://uploads.disquscdn.com/images/dffe11bf56afc3810b45370148b389573eb39973dca092291d2d63428e3f571c.png

    Como faço para enviar dados de outros campos ?

    Nesse seu exemplo só enviar os dados do campo Subject e message.

    me ajuda

    • Anderson Cunha

      Fala Johansson, tudo bem?

      Você deve adicionar uma nova variável, como por exemplo:

      var emailAddress = row[0]; // coluna 1 – email
      var subject = row[1]; // coluna 2 – assunto
      var message = row[2]; // coluna 3 – mensagem
      var expired = row[3]; // coluna 4 – expiração
      “var NOVA = row [4]; // coluna 5 – adicionar nova mensagem ao email.”

      E não esqueça de alterar o campo abaixo, até a coluna que contenha a informação desejada, no caso coluna 5 = E.
      // buscar no intervalo as células A2:E3
      var dataRange = sheet.getRange(startRow, 1, numRows, 5);

      Espero ter ajudado, abraço!

  • Guilherme Lima

    Olá, teria alguma forma de programar o horário que esse envio seria feito? Por exemplo, a função sendEmails() ser rodada às 23:30? Abs

    • Anderson Cunha

      Oi Guilherme,
      Você pode ver mais sobre o assunto no link a seguir: https://developers.google.com/apps-script/guides/triggers/installable
      Ou você pode criar um botão (o primeiro post da série explica como) e acionar o código sempre que abrir a planilha, deste modo o acompanhamento dos “status” da planilha seria recorrente..

  • Rogério Roger

    LEgal esse post. Ta dando esse erro: As coordenadas ou as dimensões do intervalo são inválidas. (linha 5, arquivo “sendEmails”)
    O codigo ta identico ao seu. pode me ajudar?