Este artigo ensinará a você como usar o Google Apps Script no Planilhas Google. Compartilharemos exemplos de scripts e como eles afetam as planilhas. Também explicaremos alguns conceitos-chave, como funções personalizadas e tipos de dados.
Vamos direto ao assunto!
Principais conclusões:
- No Google Sheets, o Apps Script pode ser usado para automatizar a entrada de dados, gerar relatórios e limpar e formatar dados, entre outras coisas.
- Para usar o Google Apps Script no Planilhas Google, vá para Extensão > Script de aplicativos e digite ou cole seu código.
- Os principais conceitos do Apps Script incluem funções personalizadas, acionadores, propriedades e métodos, serviços do Apps Script, tratamento de erros e bibliotecas.
O que é o Google Apps Script?
O Google Apps Script é uma plataforma baseada na nuvem que permite criar seus próprios programas para trabalhar com seus aplicativos do Google (como Gmail, Docs e Sheets). Esses programas executam tarefas automaticamente. Eles podem enviar e-mails ou organizar dados. Eles também podem adicionar novos recursos aos seus aplicativos, como menus ou botões personalizados.
O Google Apps Script usa uma linguagem de programação simples chamada JavaScript. No entanto, você não precisa ser um programador experiente para escrevê-lo.
Em termos simples, é assim que os scripts do Google Apps funcionam:
- Você escreve instruções simples (código) no editor Apps Script.
- Os servidores do Google leem suas instruções e executam as tarefas que você deseja.
- Os resultados aparecem em seus aplicativos do Google. Esses resultados podem ser novos menus, tarefas automatizadas ou aplicativos personalizados.
Agora, como usar o Apps Script no Planilhas Google?
Como usar o Google Apps Script no Planilhas Google?
- Abra sua planilha do Google.
- Clique em “Extensões“ na barra de menus e selecione “Script de aplicativos”. Isso abrirá uma nova guia com o editor do Apps Script.
3. Você verá o arquivo Code.gs. Esse é o arquivo principal no qual você escreverá seu código JavaScript. Ele está localizado no menu “Editor”.
4. Digite ou cole seu código. Talvez você precise aprender Javascript para saber como escrever código, ou pode gerar código com o Gemini ou o ChatGPT. Mas aqui está o script que usaremos para este tutorial:
Qual é o cenário por trás desse script? Vamos supor que temos uma lista de contatos em uma planilha do Google e queremos usá-la para uma campanha básica de e-mail. O script buscará todos os dados necessários, como e-mail e nome, para compor e enviar e-mails.
5. Clique no botão “Salvar” para salvar o script.
6. No canto superior esquerdo, renomeie seu projeto e clique em “Executar.” O script enviará e-mails para todos os endereços em sua planilha.
7. Os scripts do Apps Script exigem autorização para acessar os dados do Planilhas Google. Você será solicitado a conceder essas permissões ao executar um script.
8. Você também pode configurar um acionador para executar o script automaticamente em uma programação (por exemplo, diária ou semanal). Para fazer isso, passe o mouse sobre os ícones à esquerda e clique em “Gatilhos.”
9. Clique no botão “+ Adicionar acionador”.
10. Vá para a origem do evento e selecione “Orientado pelo tempo.”
11. Selecione um tipo baseado em tempo.
- Data e hora específicas: Para um acionamento único.
- Cronômetro de minutos: Para ser executado em um intervalo especificado em minutos (por exemplo, a cada 15 minutos).
- Temporizador de horas: Para ser executado em um intervalo especificado em horas (por exemplo, a cada 3 horas).
- Cronômetro diurno: Para ser executado diariamente em um horário específico.
- Cronômetro da semana: Executar em dias específicos da semana em um horário específico.
- Cronômetro mensal: Para ser executado em dias específicos do mês em um horário específico.
12. Clique em “Salvar.” O acionador agora está ativo.
Aqui está o e-mail que recebi depois de executar o script.
Pronto! Agora você já sabe como usar o Google Apps Script no Planilhas Google. Mas vamos discutir mais casos de uso e ver como o Apps Script aprimora as planilhas.
Exemplos práticos de Apps Scripts em ação
1. Automatizar a entrada de dados
Você pode usar os scripts do Google Apps no Google Sheets para automatizar a entrada de dados.
A entrada manual de dados está sujeita a erros de digitação. Com automação, Com o Apps Script, você reduz as chances de erros humanos. Os Apps Script podem extrair dados de outras fontes (como sites, formulários ou e-mails) e conectá-los automaticamente à sua planilha.
Digamos que você receba por e-mail recibos de despesas comerciais. O Apps Script pode digitalizar esses e-mails, extrair os números relevantes (data, valor, categoria) e organizá-los ordenadamente em sua planilha de controle de despesas.
Vamos escrever e explicar um script:
O que significa esse script?
Presumimos que você tenha um rótulo do Gmail chamado “Receipts” (Recibos) onde armazena e-mails de despesas. O script obtém os primeiros 5 tópicos (conversas) não lidos desse rótulo. Ele percorre cada thread e suas mensagens individuais (e-mails).
Em seguida, ele extrai os detalhes das despesas. Lembre-se de que simplificamos bastante essa parte. O script pressupõe um formato específico para as despesas no corpo do e-mail (por exemplo, “$15.99” e “07/25/2024”).
Em seguida, se o script encontrar o valor e a data, ele obtém a planilha ativa e adiciona uma nova linha com os dados extraídos (e o assunto do e-mail para referência).
Por fim, após o processamento, o script marca o thread de e-mail como lido para evitar duplicatas.
Observação: Nosso script é simplificado. A precisão dos dados depende inteiramente do formato do e-mail e da capacidade do script de analisá-lo corretamente. Além disso, você pode ter colunas adicionais em seu rastreador de despesas real (por exemplo, categoria, método de pagamento).
2. Gerar relatórios
Você também pode usar o Google Apps Script no Google Sheets para gerar relatórios.
Pense naqueles relatórios tediosos que você precisa fazer - talvez resumos de vendas, atualizações de projetos ou notas de alunos. O Apps Script pode eliminar a dificuldade desse processo extraindo esses dados de uma planilha do Google ou de outros lugares.
Digamos que você acompanhe suas vendas semanais em uma planilha do Google. O Apps Script pode:
- Obtenha os números de vendas mais recentes de sua planilha.
- Calcule o total de vendas, as vendas por produto e talvez até o crescimento em relação à semana anterior.
- Faça um gráfico de pizza ou gráfico de linhas relatório e um resumo.
- Envie o relatório por e-mail ao seu gerente toda segunda-feira de manhã.
Você o configura uma vez e o Google Apps Script faz o resto - semana após semana!
Aqui está um exemplo de script, que explicaremos a seguir.
função generateSalesReport() {
// Obter os dados de vendas
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“WeeklySales”);
var data = sheet.getDataRange().getValues();
// Calcular o total de vendas e as vendas por produto
var totalSales = 0;
var salesByProduct = {};
for (var i = 1; i < data.length; i++) { // Pular a linha do cabeçalho
var product = data[i][1];
var sales = data[i][2];
totalSales += sales;
salesByProduct[product] = (salesByProduct[product] || 0) + sales;
}
// Criar o relatório
var report = “<h2>Relatório semanal de vendas</h2>\n\n”;
report += “<p>Total de vendas: $” + totalSales.toFixed(2) + “</p>\n\n”;
report += “<h3>Vendas por produto:</h3>\n\n”;
for (var product in salesByProduct) {
report += “<p>” + product + “: $” + salesByProduct[product].toFixed(2) + “</p>\n\n”;
}
// Enviar o e-mail
var emailSubject = “Weekly Sales Report”;
var emailBody = report;
MailApp.sendEmail(“YOUR_EMAIL_ADDRESS”, emailSubject, “”, {htmlBody: emailBody}); // Inclua seu endereço de e-mail
}
Leitura recomendada: Os 5 principais modelos de vendas do Google Sheets
O que significa esse script?
O script obtém todos os valores da planilha “WeeklySales”. Ele percorre os dados e calcula o total de vendas e as vendas por produto. Em seguida, cria um relatório HTML com os dados calculados. Por fim, o script usa MailApp.sendEmail para enviar o relatório como um e-mail.
MailApp.sendEmail
Aqui está uma planilha do Google que agrupa todas as vendas:
Aqui está a aparência do relatório de vendas semanais totais no e-mail:
Observação: Este é um script simplificado. Altere “WeeklySales” se sua planilha tiver um nome diferente. Substitua “YOUR_EMAIL_ADDRESS” pelo endereço de e-mail real do destinatário.
3. Limpar e formatar dados
A limpeza e formatação de dados é outra maneira de usar os Scripts de aplicativos no Planilhas Google.
Imagine que você tenha uma planilha bagunçada cheia de informações de clientes. Talvez alguns nomes tenham espaços extras, as datas estejam em formatos diferentes ou algumas células tenham erros de digitação. Limpar isso manualmente seria uma verdadeira dor de cabeça. É aqui que os Apps Scripts entram em cena para salvar o dia.
Você pode usar os scripts do Google Apps para examinar seus dados e localizar e corrigir problemas específicos, como:
- Espaços extras: Remova automaticamente os espaços extras antes e depois dos nomes.
- Formatos incorretos: Altere as datas de “29/07/2024” para “29 de julho de 2024” para que tudo coincida.
- Erros de digitação: Se o nome de um produto estiver escrito incorretamente, o script poderá corrigi-lo.
Você também pode usar os scripts do Google Apps para organizar informações e combinar ou dividir dados.
Vamos criar um Javascript para este exemplo.
Imagine que você tenha uma planilha do Google com pedidos de clientes. Cada linha tem informações como ID do pedido, nome do cliente, produto, quantidade e data do pedido. Você deseja organizar os pedidos por data, com os pedidos mais recentes na parte superior.
Nosso script poderia ser mais ou menos assim:
função sortSheetByDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange(“A2:E26”); // Ajuste o intervalo para cobrir seus dados
// Obter os dados
var data = range.getValues();
// Verificar se as datas estão no formato do objeto Date e convertê-las em strings
data.forEach(function(row) {
Se (Object.prototype.toString.call(row[4]) === ‘[object Date]’) {
row[4] = Utilities.formatDate(row[4], SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), ‘dd/MM/yyyy’);
}
});
// Analise as datas corretamente e classifique-as
data.sort(function(a, b) {
var dateA = new Date(a[4].split(‘/’).reverse().join(‘-‘));
var dateB = new Date(b[4].split(‘/’).reverse().join(‘-‘));
return dateB - dateA; // Ordenar do mais novo para o mais antigo
});
// Definir os dados classificados de volta para a planilha
range.setValues(data);
}
O que significa esse script?
O script encontra a ordem das informações que você deseja classificar. Em seguida, ele pega cuidadosamente todas as informações de cada formulário de pedido - nome do cliente, data do pedido, etc. - e as coloca em células separadas. - e as coloca em células separadas. É aqui que a mágica acontece! O script analisa cada data de pedido e descobre quais pedidos são mais recentes e quais são mais antigos.
Dados com datas que não estão em ordem cronológica:
Dados com datas em ordem cronológica após o uso do script:
Conceitos essenciais do Apps Script para conhecer e usar no Planilhas Google
1. Funções personalizadas
As funções personalizadas permitem que você crie suas próprias funções que podem ser usadas diretamente no Planilhas Google, assim como as funções incorporadas (SUM, MÉDIA, etc.). Esses funções são escritos em JavaScript no Apps Script e podem executar várias tarefas, desde cálculos simples até manipulações complexas de dados.
Aqui está um exemplo de uma função personalizada:
function DOUBLE(input) {
return input * 2;
}
Essa função personalizada duplica os números em uma célula. Depois de adicionar a função personalizada ao Script Editor, volte à planilha.
Digamos que a célula A1 contenha o número 580.
- Digite =DOUBLE(A1) na célula B1.
=DOUBLE(A1)
- O Planilhas Google executa sua função, fornecendo a ela o valor 1160 na célula B1.
- A função calcula 580 * 2 = 1160.
- A célula B1 exibe o resultado: 1160.
Diferença entre uma função personalizada e um código geral que você escreve no Script do Google Apps
A principal diferença entre uma função personalizada e qualquer outro código está na forma como você as utiliza e as chama no Planilhas Google.
Um código de função personalizado deve retornar um valor, como:
return input * 2;.
Uma função não pode modificar nenhuma parte da planilha nem acionar outras ações. Ela só pode computar e retornar um resultado com base em suas entradas.
Além disso, para usar uma função personalizada, você deve inserir o código =functionName() em uma célula na planilha, como:
=DOUBLE(A1)
2. Acionadores
Você também precisa aprender sobre acionadores se quiser usar o Google Apps Script no Planilhas Google.
Imagine que você tem uma tarefa no Planilhas Google que deve acontecer automaticamente, sem que você precise clicar em nenhum botão. É aí que entram os acionadores! Um acionador é como um pequeno despertador que você configura no código do Apps Script. Ele diz ao seu script para despertar e executar uma parte específica do seu código quando um determinado evento ocorrer na planilha.
Você pode personalizar os acionadores para executar o script quando uma planilha for aberta, uma célula for editada, um formulário for enviado ou até mesmo em uma data e hora específicas.
Você também pode usar acionadores para manter seus dados atualizados. Por exemplo, um acionador pode obter automaticamente os preços mais recentes das ações ou informações meteorológicas.
3. Propriedades e métodos
As propriedades e os métodos permitem que você altere o funcionamento do Planilhas Google. Você pode escrever scripts que formatam automaticamente as células ou movem os dados. Por exemplo, em vez de alterar manualmente a cor de centenas de células, seu script pode fazer isso instantaneamente.
Pense nas propriedades como as características ou os atributos de um objeto. No Planilhas Google, isso poderia ser:
- A cor de fundo de uma célula:
cell.getBackground()
- O valor dentro de uma célula:
cell.getValue()
- O tamanho da fonte do texto:
cell.getFontSize()
Você pode descobrir o valor atual de uma propriedade ou defini-la para alterar sua aparência ou comportamento.
Os métodos, entretanto, são como os verbos ou ações que um objeto pode executar. Por exemplo, uma célula no Planilhas Google pode:
- Alterar seu conteúdo:
cell.setValue(“Hello!”)
- Limpar-se completamente:
cell.clearContent()
- Mesclar com células vizinhas:
cell.mergeAcross()
Você “chama” um método para que isso aconteça. É como dar uma instrução.
Aqui está um exemplo de script simples:
função formatImportantCells() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(“A1:B10”); // Obter um grupo de células
var cells = range.getCells(); // Obter células individuais
for (var i = 0; i < cells.length; i++) {
if (cells[i].getValue() > 100) { // Verificar o valor
cells[i].setBackground(“yellow”); // Definir uma propriedade (cor)
}
}
}
Esse script verifica os valores nas células A1 a B10. Se um valor for maior que 100, o script tornará o plano de fundo da célula amarelo.
Em resumo:
- As propriedades são descritas: Eles informam sobre o estado atual das coisas.
- Os métodos funcionam: Eles fazem as coisas acontecerem.
- Notação de ponto: Você usa um ponto para acessar propriedades e métodos (por exemplo, cell.getValue()).
cell.getValue()
4. Tipos de dados
Os tipos de dados são outro conceito que você precisa aprender para usar o Google Apps Script no Google Sheets.
Os tipos de dados são rótulos que informam ao Apps Script com que tipo de informação ele está lidando. Assim como você distingue entre maçãs e laranjas, o Apps Script precisa saber se está trabalhando com números, texto, datas ou outros tipos.
Mas por que os tipos de dados são importantes no Planilhas Google?
Há pelo menos três motivos:
- Cálculos corretos: Imagine que você está somando despesas. Se o Apps Script achar que seus números são realmente texto, você obterá resultados estranhos. Os tipos de dados garantem que os cálculos sejam feitos com precisão.
- Formatação adequada: Você quer que suas datas pareçam datas e que seus números de telefone se comportem como números de telefone. Os tipos de dados ajudam a controlar como as coisas são exibidas e usadas na planilha.
- Prevenção de erros: Se você tentar usar uma data como se fosse um número, o Apps Script poderá sinalizar um erro. Conhecer os tipos de dados corretos ajuda a evitar esses problemas e a escrever códigos que funcionam sem problemas.
Digamos que você esteja acompanhando as vendas e tenha uma coluna no Google Sheets com o número de itens vendidos. Quando você usa o Apps Script para calcular o total de vendas, ele precisa entender que essa coluna contém números e não apenas caracteres aleatórios.
Por exemplo, o script abaixo menciona o tipo de dados:
Se (valueType === “number”) {
Aqui está o detalhamento:
- valueType: Essa variável armazena o resultado do valor typeof. O operador typeof é um recurso do JavaScript que informa diretamente o tipo de dados de uma variável.
- === “number” (número): Essa comparação verifica se o valueType é estritamente igual à string “number”. Se for, o script sabe que está lidando com um valor numérico e continua a adicioná-lo ao totalSales.
Acabamos de abordar alguns conceitos ou termos do Apps Scripts. No entanto, existem outros, como Apps Script Services, Tratamento de erros e Bibliotecas.
Dicas e práticas recomendadas para o Apps Script
1. Aprenda Javascript
Aprender JavaScript lhe dá uma base sólida para entender como o Google Apps Script funciona. Fica muito mais fácil entender o código que você encontra on-line, solucionar problemas em seus próprios projetos e escrever scripts mais avançados.
Depois de dominar os conceitos básicos do JavaScript, você manipulará os dados de maneiras realmente inteligentes e interagirá com outros serviços na Web.
Além disso, o JavaScript é uma linguagem amplamente utilizada, portanto, suas novas habilidades não serão desperdiçadas. Você pode aplicá-las a outros projetos além do Google Apps Script. Por exemplo, você pode criar sites simples ou até mesmo trabalhar em projetos complexos de desenvolvimento da Web.
2. Escrever código eficiente e de fácil manutenção
Seu código é como uma receita, e uma boa receita é:
- Eficiente: Ele não desperdiça ingredientes nem tempo. O código eficiente é executado mais rapidamente e usa menos de sua cota de Apps Script (o limite de quanto seus scripts podem fazer).
- Manutenível: É fácil de entender e atualizar posteriormente. O código sustentável evita dores de cabeça quando você precisa fazer alterações no futuro.
Aqui estão algumas dicas adicionais para o Google Apps Script:
- Não nomeie suas variáveis como “x” ou “temp”. Escolha nomes que descrevam o que elas armazenam. Por exemplo, em vez de “data”, use “customerNames” ou “orderDates”.”
- Divida seu código em funções menores e reutilizáveis. O ideal é que cada função execute uma tarefa específica. Isso tornará seu código mais organizado e mais fácil de testar.
- As coisas nem sempre saem como planejado. Use os blocos “try...catch” para capturar erros, o que evita que o script seja interrompido inesperadamente.
- Se você se pegar escrevendo o mesmo código várias vezes, crie uma função para ele. Esse princípio é chamado de “Don't Repeat Yourself” (DRY).
3. Depurar de forma inteligente
No Google Apps Script, os bugs são erros ou comportamentos inesperados em seu código. A depuração é o processo de encontrar e corrigir esses problemas.
Quando o Apps Script encontra um problema, ele geralmente exibe uma mensagem de erro. Essa mensagem é sua primeira pista. Ela geralmente informa o que deu errado. Você saberá se usou uma variável que não existe, se dividiu por zero ou qualquer outra coisa. Você também saberá onde ocorreu o erro, como a linha de código que está causando o problema.
O Apps Script tem um depurador integrado. É como uma versão mais poderosa de “console.log()”. Você pode pausar seu código, percorrê-lo linha por linha e inspecionar variáveis em tempo real.
Digamos que você tenha este código:
function calculateArea(length, width) {
var area = comprimento * largura;
console.log(“A área é: ” + área);
}
calculateArea(10,); // Observe que está faltando o argumento para a largura
Você o executa e ele trava com um erro: “TypeError: Cannot read property ‘*’ of undefined”. O que está acontecendo?
- Leia o erro: Ele diz que você está tentando usar o operador ‘*’ (multiplicação) em algo indefinido.
- Verifique o código: Você percebe que esqueceu de passar um valor para o argumento “width”.
Corrigir o erro: Altere a última linha para:
calculateArea(10, 5);
Use os scripts do Google Apps para aprimorar seu Planilhas Google
Parabéns! Você acabou de desbloquear o poder do Apps Script no Planilhas Google. Neste artigo, vimos alguns casos práticos de uso e compartilhamos exemplos de scripts. Agora você está totalmente preparado para usar o Google Apps Script no Planilhas Google.
Ao automatizar tarefas, personalizar funções e até mesmo criar aplicativos interativos na Web, você está no caminho certo para se tornar um ninja das planilhas. Continue aprendendo, continue criando scripts e continue ampliando os limites do que é possível fazer no Planilhas Google.
PERGUNTAS FREQUENTES
Como adicionar o Script Editor ao Planilhas Google?
O Script Editor está integrado ao Planilhas Google. Não é necessário adicioná-lo separadamente.
Como abrir o Script Editor no Planilhas Google?
Vá para Extensões > Script de aplicativos. Isso abrirá o Script Editor em uma nova guia.
Onde está o Script Editor no Planilhas Google?
O Script Editor não está fisicamente localizado em sua Planilha Google. Ele é aberto em uma guia ou janela separada quando acessado pelo menu Extensões. Vá para Extensões > Script de aplicativos.
English 
Español 
Português do Brasil 
Français