Use o Google Apps Script para estender as Planilhas. Adicione menus, caixas de diálogo e barras laterais personalizadas às Planilhas. Escreva funções personalizadas para as Planilhas e integre-as a outros Serviços do Google, como o Google Agenda, o Google Drive e o Gmail.
A maioria dos scripts criados para as Planilhas manipula matrizes para interagir com as células, linhas e colunas de uma planilha. Se você não estiver familiarizado com arrays em JavaScript, o Codecademy oferece um ótimo módulo de treinamento para arrays. Este curso não foi desenvolvido nem está associado ao Google.
Para uma introdução rápida ao uso do Apps Script com o Google Sheets, consulte o guia de início rápido de 5 minutos para Macros, menus e funções personalizadas.
Primeiros passos
O Apps Script inclui APIs especiais para criar, ler e editar planilhas de maneira programática. O Apps Script interage com as Planilhas de duas maneiras: qualquer script pode criar ou modificar uma planilha se o usuário tiver as permissões adequadas, e um script também pode ser vinculado a uma planilha. Os scripts vinculados têm capacidades especiais para alterar a interface do usuário ou responder quando a planilha é aberta. Para criar um script vinculado, selecione Extensões > Apps Script nas Planilhas Google.
O serviço de planilhas trata o app Planilhas como uma grade, operando com matrizes bidimensionais. Para recuperar dados da planilha, acesse a planilha em que os dados estão armazenados, extraia o intervalo que contém os dados e, em seguida, extraia os valores das células. O Apps Script facilita o acesso aos dados lendo dados estruturados na planilha e criando objetos JavaScript para eles.
Ler dados
Imagine que você tenha uma lista de nomes e números de produtos armazenados em uma planilha, conforme mostrado na imagem a seguir.
O exemplo a seguir mostra como recuperar e registrar os nomes e números dos produtos.
function logProductInfo() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}
Ver registros
Para ver os dados registrados, clique em Registro de execução na parte de cima do editor de script.
Gravar dados
Para armazenar dados, como um novo nome e número de produto na planilha, adicione o código a seguir ao final do script.
function addProduct() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
O código anterior adiciona uma nova linha na parte de baixo da planilha com os valores especificados. Se você executar essa função, uma nova linha será adicionada à planilha.
Menus e interfaces do usuário personalizados
Personalize as Planilhas adicionando menus, caixas de diálogo e barras laterais personalizadas. Para aprender o básico sobre como criar menus, consulte o guia de menus. Para saber como personalizar o conteúdo de uma caixa de diálogo, consulte o guia do serviço HTML.
Anexe uma função de script a uma imagem ou desenho em uma planilha. A função será executada quando um usuário clicar na imagem ou no desenho. Para saber mais, consulte Imagens e desenhos no Google Planilhas.
Se você planeja publicar sua interface personalizada como parte de um complemento, siga o guia de estilo para manter a consistência com o estilo e o layout do editor de planilhas.
Conectar-se ao Google Formulários
Conecte o Google Formulários às Planilhas pelos serviços Formulários e Planilhas. Esse recurso cria automaticamente um formulário do Google com base nos dados de uma planilha.
O Apps Script também permite usar acionadores, como
onFormSubmit, para realizar uma ação específica depois que um usuário responde ao
formulário. Para saber mais sobre como conectar as Planilhas aos Formulários, confira o guia de início rápido de 5 minutos Gerenciar respostas dos Formulários.
formatar dados
A classe Range tem métodos como
setBackground
para acessar e modificar o formato de uma célula ou um intervalo delas. O exemplo a seguir define o estilo da fonte de um intervalo:
function formatMySpreadsheet() {
// Set the font style of the cells in the range of B2:C2 to be italic.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const cell = sheet.getRange('B2:C2');
cell.setFontStyle('italic');
}
Validação de dados
Acesse as regras de validação de dados nas Planilhas Google ou crie novas. Por exemplo, a amostra a seguir mostra como definir uma regra de validação de dados que permite apenas números entre 1 e 100 em uma célula.
function validateMySpreadsheet() {
// Set a rule for the cell B4 to be a number between 1 and 100.
const cell = SpreadsheetApp.getActive().getRange('B4');
const rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(1, 100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);
}
Para mais detalhes sobre como trabalhar com regras de validação de dados, consulte
SpreadsheetApp.newDataValidation,
DataValidationBuilder,
e Range.setDataValidation
Gráficos
Incorpore gráficos em uma planilha que representam os dados em um intervalo específico. O
exemplo a seguir gera um gráfico de barras incorporado, supondo que você tenha dados
em células A1:B15:
function newChart() {
// Generate a chart representing the data in the range of A1:B15.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheet.getRange('A1:B15'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(chart);
}
Para saber mais sobre como incorporar um gráfico à sua planilha, consulte
EmbeddedChart e criadores de gráficos
específicos, como
EmbeddedPieChartBuilder.
Funções personalizadas no Google Planilhas
Uma função personalizada é semelhante a uma função integrada de
planilha, como =SUM(A1:A5), mas você define o comportamento da função com o
Apps Script. Por exemplo, é possível criar uma função personalizada, in2mm(), que converte um valor de polegadas para milímetros e usar a fórmula na sua planilha digitando =in2mm(A1) ou =in2mm(10) em uma célula.
Para saber mais sobre funções personalizadas, confira o Menus e funções personalizadas guia de início rápido de 5 minutos ou leia o guia mais detalhado sobre funções personalizadas.
Macros
As macros são outra maneira de executar o código do Apps Script na interface do Planilhas. Ao contrário das funções personalizadas, você as ativa com um atalho de teclado ou pelo menu das Planilhas. Para mais informações, consulte Macros do Google Sheets.
Complementos para o Google Planilhas
Os complementos são projetos do Apps Script especialmente empacotados que são executados no Planilhas e podem ser instalados na loja de complementos do Planilhas. Se você desenvolveu um script para o Planilhas e quer compartilhar, o Apps Script permite publicar seu script como um complemento para que outros usuários possam instalá-lo.
Performance e escalonamento
À medida que seus conjuntos de dados crescem, você pode ter problemas de performance. Para otimizar sua planilha e seus scripts:
- Siga as práticas recomendadas: leia o guia de práticas recomendadas para dicas sobre como minimizar chamadas de serviço e usar operações em lote.
- Otimize as fórmulas: se a planilha estiver lenta devido a fórmulas complexas (como
VLOOKUP,ARRAYFORMULAouIMPORTRANGE), use o Apps Script para realizar esses cálculos na memória e gravar os resultados em lotes. - Considere alternativas de banco de dados: para conjuntos de dados muito grandes (próximos a 10 milhões de células) ou entrada de dados de alta frequência (por exemplo, muitos formulários conectados), considere usar o Google Cloud SQL com JDBC ou o BigQuery.
Gatilhos
Os scripts vinculados a um arquivo do Planilhas
podem usar acionadores simples, como as funções
onOpen() e onEdit(), para responder automaticamente quando um usuário com acesso de edição
à planilha abre ou edita o arquivo.
Assim como os acionadores simples, os acionadores instaláveis permitem que
o app Planilhas execute uma função automaticamente quando um determinado evento ocorre.
No entanto, os acionadores instaláveis oferecem mais flexibilidade do que os simples e são compatíveis com os seguintes eventos: abrir, editar, mudar, enviar formulário e acionados por tempo (relógio).