As Planilhas Google oferecem centenas de
funções integradas, como
AVERAGE
, SUM
e
VLOOKUP
. Quando isso não for suficiente para suas necessidades, use o Google Apps Script para criar funções personalizadas, por exemplo, para converter metros em milhas ou buscar conteúdo ativo da Internet. Em seguida, use-as nas Planilhas Google como uma função integrada.
Como começar
As funções personalizadas são criadas usando JavaScript padrão. Se você não tem experiência com JavaScript, o Codecademy oferece um ótimo curso para iniciantes (em inglês). Observação: este curso não foi desenvolvido nem está associado ao Google.
Veja uma função personalizada simples, chamada DOUBLE
, que multiplica um
valor de entrada por 2:
/**
* Multiplies an input value by 2.
* @param {number} input The number to double.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
return input * 2;
}
Se você não sabe como escrever JavaScript e não tem tempo para isso, consulte a loja de complementos para ver se outra pessoa já criou a função personalizada de que você precisa.
Como criar uma função personalizada
Para criar uma função personalizada:
- Crie ou abra uma planilha no Planilhas Google.
- Selecione o item de menu Extensões > Apps Script.
- Exclua qualquer código no editor de script. Para a função
DOUBLE
acima, basta copiar e colar o código no editor de script. - Na parte superior, clique em Salvar .
Agora você pode usar a função personalizada.
Como receber uma função personalizada do Google Workspace Marketplace
O Google Workspace Marketplace oferece várias funções personalizadas como complementos para as Planilhas Google (em inglês). Para usar ou conhecer esses complementos:
- Crie ou abra uma planilha no Planilhas Google.
- Na parte superior, clique em Complementos > Instalar complementos.
- Quando Google Workspace Marketplace for aberto, clique na caixa de pesquisa no canto superior direito.
- Digite "função personalizada" e pressione Enter.
- Se você encontrar um complemento de funções personalizadas do seu interesse, clique em Instalar para fazer isso.
- Uma caixa de diálogo pode informar que o complemento requer autorização. Se esse for o caso, leia o aviso com atenção e clique em Permitir.
- O complemento fica disponível na planilha. Para usar o complemento em outra planilha, abra a outra planilha e, na parte superior, clique em Complementos > Gerenciar complementos. Encontre o complemento que você quer usar e clique em Opções > Usar neste documento.
Como usar uma função personalizada
Depois de criar uma função personalizada ou instalar uma usando o Google Workspace Marketplace, é tão fácil de usar quanto uma função integrada:
- Clique na célula em que você quer usar a função.
- Digite um sinal de igual (
=
) seguido do nome da função e qualquer valor de entrada, por exemplo,=DOUBLE(A1)
, e pressione Enter. - A célula exibirá
Loading...
temporariamente e retornará o resultado.
Diretrizes para funções personalizadas
Antes de escrever sua própria função personalizada, há algumas diretrizes que você precisa conhecer.
Nomeação
Além das convenções padrão para nomear funções JavaScript, esteja ciente do seguinte:
- O nome de uma função personalizada precisa ser diferente dos nomes das
funções integradas, como
SUM()
. - O nome de uma função personalizada não pode terminar com um sublinhado (
_
), que indica uma função particular no Apps Script. - O nome de uma função personalizada precisa ser declarado com a sintaxe
function myFunction()
, nãovar myFunction = new Function()
. - O uso de letras maiúsculas não importa, embora os nomes das funções da planilha sejam tradicionalmente em letra maiúscula.
Argumentos
Assim como uma função integrada, uma função personalizada pode usar argumentos como valores de entrada:
- Se você chamar sua função com uma referência a uma única célula como argumento
(como
=DOUBLE(A1)
), o argumento será o valor da célula. Se você chamar a função com uma referência a um intervalo de células como argumento (como
=DOUBLE(A1:B10)
), o argumento será uma matriz bidimensional dos valores das células. Por exemplo, na captura de tela abaixo, os argumentos em=DOUBLE(A1:B2)
são interpretados pelo Apps Script comodouble([[1,3],[2,4]])
. O exemplo de código paraDOUBLE
acima precisaria ser modificado para aceitar uma matriz como entrada.Os argumentos da função personalizada precisam ser determinísticos. Ou seja, funções de planilha integradas que retornam um resultado diferente a cada cálculo, como
NOW()
ouRAND()
, não podem ser usadas como argumentos para uma função personalizada. Se uma função personalizada tentar retornar um valor com base em uma dessas funções integradas voláteis, ela exibiráLoading...
indefinidamente.
Valores de retorno
Toda função personalizada precisa retornar um valor a ser exibido, de modo que:
- Se uma função personalizada retornar um valor, esse valor será mostrado na célula em que a função foi chamada.
- Se uma função personalizada retornar uma matriz bidimensional de valores, os valores vão passar para as células adjacentes, desde que essas células estejam vazias. Se isso fizer com que a matriz substitua o conteúdo das células existentes, a função personalizada gerará um erro. Por exemplo, consulte a seção sobre como otimizar funções personalizadas.
- Uma função personalizada não pode afetar outras células além daquelas para as quais retorna um valor. Em outras palavras, uma função personalizada não pode editar células arbitrárias, apenas as células de onde ela é chamada e as células adjacentes. Para editar células arbitrárias, use um menu personalizado para executar uma função.
- Uma chamada de função personalizada precisa retornar em até 30 segundos. Caso contrário, a
célula exibirá um erro:
Internal error executing the custom function.
Tipos de dados
O Planilhas Google armazena dados em formatos diferentes, dependendo da natureza dos dados. Quando esses valores são usados em funções personalizadas, o Apps Script os trata como o tipo de dado apropriado em JavaScript. Estas são as áreas mais comuns de confusão:
- As horas e as datas nas Planilhas se tornam objetos de data no Apps Script. Se a planilha e o script usarem fusos horários diferentes (um problema raro), a função personalizada precisará compensar.
- Os valores de duração nas Planilhas também se tornam objetos
Date
, mas trabalhar com eles pode ser complicado. - Os valores percentuais nas Planilhas se tornam números decimais no Apps Script. Por exemplo, uma célula com um valor
10%
se torna0.1
no Apps Script.
Preenchimento automático
As Planilhas Google são compatíveis com o preenchimento automático para funções personalizadas, assim como para funções integradas. Ao digitar o nome de uma função em uma célula, você verá uma lista de funções integradas e personalizadas que correspondem ao que foi inserido.
As funções personalizadas aparecerão nesta lista se o script incluir uma tag
JsDoc
@customfunction
, como no exemplo DOUBLE()
abaixo.
/**
* Multiplies the input value by 2.
*
* @param {number} input The value to multiply.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
return input * 2;
}
Avançada
Como usar os serviços do Google Apps Script
As funções personalizadas podem chamar determinados serviços do Google Apps Script para executar tarefas mais complexas. Por exemplo, uma função personalizada pode chamar o serviço Language para traduzir uma frase em inglês para o espanhol.
Ao contrário da maioria dos outros tipos de Apps Scripts, as funções personalizadas nunca pedem que os usuários autorizem o acesso a dados pessoais. Consequentemente, eles só podem chamar serviços que não tenham acesso a dados pessoais, especificamente:
Serviços compatíveis | Observações |
---|---|
Cache | Funciona, mas não é útil em funções personalizadas |
HTML | Pode gerar HTML, mas não pode exibi-lo (pouco útil) |
JDBC | |
Idioma | |
Bloquear | Funciona, mas não é útil em funções personalizadas |
Maps | Pode calcular rotas, mas não exibir mapas |
Propriedades | getUserProperties() só recebe as propriedades do proprietário da planilha. Os editores de planilhas não podem definir propriedades do usuário em uma função personalizada. |
Planilha | Somente leitura (é possível usar a maioria dos métodos get*() , mas não set*() ).Não é possível abrir outras planilhas ( SpreadsheetApp.openById()
ou SpreadsheetApp.openByUrl() ). |
Busca de URL | |
Utilitários | |
XML |
Se a função personalizada gerar a mensagem de erro You do not have permission to
call X service.
, o serviço exigirá a autorização do usuário e, portanto, não poderá ser usado em uma função personalizada.
Para usar um serviço diferente dos listados acima, crie um menu personalizado que execute uma função do Apps Script em vez de criar uma função personalizada. Uma função acionada a partir de um menu solicitará a autorização do usuário, se necessário, e poderá usar todos os serviços do Apps Script.
Compartilhamento
As funções personalizadas começam vinculadas à planilha em que foram criadas. Isso significa que uma função personalizada escrita em uma planilha não pode ser usada em outras planilhas, a menos que você use um dos seguintes métodos:
- Clique em Extensões > Apps Script para abrir o editor de script. Em seguida, copie o texto da planilha original e cole-o no editor de script de outra planilha.
- Faça uma cópia da planilha que contém a função personalizada clicando em Arquivo > Fazer uma cópia. Quando uma planilha é copiada, os scripts anexados a ela também são. Qualquer pessoa com acesso à planilha pode copiar o script. Os colaboradores com acesso apenas de leitura não podem abrir o editor de script na planilha original. No entanto, ao fazer uma cópia, ele se torna o proprietário da cópia e pode ver o script.
- Publique o script como um complemento do editor do Planilhas Google.
Otimização
Cada vez que uma função personalizada é usada em uma planilha, o Planilhas Google faz uma chamada separada para o servidor do Apps Script. Se a planilha tiver dezenas, centenas ou milhares de chamadas de funções personalizadas, esse processo poderá ser bastante lento.
Consequentemente, se você planeja usar uma função personalizada várias vezes em um grande intervalo de dados, modifique a função para que ela aceite um intervalo como entrada na forma de uma matriz bidimensional e retorne uma matriz bidimensional que pode invadir as células apropriadas.
Por exemplo, a função DOUBLE()
mostrada acima pode ser reescrita para aceitar uma
única célula ou um intervalo de células da seguinte maneira:
/**
* Multiplies the input value by 2.
*
* @param {number|Array<Array<number>>} input The value or range of cells
* to multiply.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
return Array.isArray(input) ?
input.map(row => row.map(cell => cell * 2)) :
input * 2;
}
A abordagem acima usa o método map do objeto Array
do JavaScript para chamar DOUBLE
de maneira recursiva em cada valor na matriz bidimensional de células. Ela retorna uma matriz bidimensional que contém os resultados. Dessa forma, você pode chamar DOUBLE
apenas uma vez, mas fazer com que ele seja calculado para um grande número de células de uma só vez, conforme mostrado na
captura de tela abaixo. Você pode fazer o mesmo com instruções if
aninhadas em vez da chamada map
.
Da mesma forma, a função personalizada abaixo busca com eficiência conteúdo ativo da Internet e usa uma matriz bidimensional para exibir duas colunas de resultados com apenas uma chamada de função. Se cada célula exigisse a própria chamada de função, a operação levaria muito mais tempo, já que o servidor do Apps Script precisaria fazer o download e analisar o feed XML todas as vezes.
/**
* Show the title and date for the first page of posts on the
* Developer blog.
*
* @return Two columns of data representing posts on the
* Developer blog.
* @customfunction
*/
function getBlogPosts() {
var array = [];
var url = 'https://gsuite-developers.googleblog.com/atom.xml';
var xml = UrlFetchApp.fetch(url).getContentText();
var document = XmlService.parse(xml);
var root = document.getRootElement();
var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
var entries = document.getRootElement().getChildren('entry', atom);
for (var i = 0; i < entries.length; i++) {
var title = entries[i].getChild('title', atom).getText();
var date = entries[i].getChild('published', atom).getValue();
array.push([title, date]);
}
return array;
}
Essas técnicas podem ser aplicadas a quase qualquer função personalizada usada repetidamente em uma planilha, embora os detalhes de implementação variem dependendo do comportamento da função.