Funções personalizadas no Planilhas Google

Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

O Planilhas Google oferece centenas de funções integradas, como AVERAGE, SUM e VLOOKUP. Quando eles não forem suficientes para suas necessidades, você poderá usar o Google Apps Script para escrever funções personalizadas, por exemplo, para converter medidores em milhas ou buscar conteúdo ao vivo da Internet. Em seguida, use-os no Planilhas Google como uma função integrada.

Primeiros passos

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. Observação: este curso não foi desenvolvido e não está associado ao Google.

Esta é uma função personalizada simples chamada DOUBLE, que multiplica um valor de entrada por 2:

function DOUBLE(input) {
  return input * 2;
}

Se você não sabe escrever JavaScript e não tem tempo de aprender, verifique 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 escrever uma função personalizada:

  1. Crie ou abra um arquivo no Planilhas Google.
  2. Selecione o item de menu Extensões > Apps Script.
  3. Excluir qualquer código no editor de script. Para a função DOUBLE acima, basta copiar e colar o código no editor de script.
  4. Na parte superior, clique em Salvar .

Agora é possível 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 o Planilhas Google. Para usar ou explorar esses complementos, faça o seguinte:

  1. Crie ou abra um arquivo no Planilhas Google.
  2. Na parte de cima, clique em Complementos > Instalar complementos.
  3. Quando a Google Workspace Marketplace abrir, clique na caixa de pesquisa no canto superior direito.
  4. Digite "função personalizada" e pressione Enter.
  5. Se você encontrar um complemento de função personalizada de seu interesse, clique em Instalar para instalá-lo.
  6. Uma caixa de diálogo pode informar que o complemento requer autorização. Nesse caso, leia o aviso com atenção e clique em Permitir.
  7. O complemento fica disponível na planilha. Para usar o complemento em uma planilha diferente, abra a outra planilha e, na parte superior, clique em Complementos > Gerenciar complementos. Localize o complemento que você quer usar e clique em Opções > Usar neste documento.

Como usar uma função personalizada

Depois que você escrever uma função personalizada ou instalar uma da Google Workspace Marketplace, será tão fácil usá-la como função integrada:

  1. Clique na célula em que você quer usar a função.
  2. Digite um sinal de igual (=) seguido pelo nome da função e qualquer valor de entrada, por exemplo, =DOUBLE(A1), e pressione Enter.
  3. A célula exibirá Loading... no momento 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, lembre-se 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 privada no Apps Script.
  • O nome de uma função personalizada precisa ser declarado com a sintaxe function myFunction(), não var myFunction = new Function().
  • Não há diferença entre letras maiúsculas e minúsculas, embora os nomes das funções da planilha sejam tradicionalmente maiúsculas.

Argumentos

Assim como uma função integrada, uma função personalizada pode usar argumentos como valores de entrada:

  • Se você chamar a 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 um argumento (como =DOUBLE(A1:B10)), o argumento será uma matriz bidimensional dos valores de células. Por exemplo, na captura de tela abaixo, os argumentos em =DOUBLE(A1:B2) são interpretados pelo Apps Script como double([[1,3],[2,4]]). O exemplo de código de DOUBLE acima precisaria ser modificado para aceitar uma matriz como entrada.


  • Os argumentos da função personalizada precisam ser determinísticos. Ou seja, as funções integradas da planilha que retornam um resultado diferente sempre que forem calculadas (como NOW() ou RAND()) não são permitidas como argumentos para uma função personalizada. Se uma função personalizada tentar retornar um valor com base em uma dessas funções voláteis integradas, ela exibirá Loading... indefinidamente.

Valores de retorno

Todas as funções personalizadas precisam retornar um valor a ser exibido, de modo que:

  • Se uma função personalizada retornar um valor, ele será exibido na célula em que a função foi chamada.
  • Se uma função personalizada retorna uma matriz bidimensional de valores, os valores overflow em células adjacentes, desde que essas células estejam vazias. Se isso fizer com que a matriz substitua o conteúdo da célula existente, a função personalizada gerará um erro. Veja um exemplo na seção sobre como otimizar funções personalizadas.
  • Uma função personalizada não afeta células diferentes das que ela retorna um valor. Em outras palavras, uma função personalizada não pode editar células arbitrárias, apenas as células das quais 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 30 segundos. Se isso não acontecer, a célula exibirá um erro: Internal error executing the custom function.

Tipos de dados

O Planilhas Google armazena dados em diferentes formatos, dependendo da natureza dos dados. Quando esses valores são usados em funções personalizadas, o Apps Script os trata como o tipo de dados apropriado em JavaScript. Estas são as áreas mais comuns de confusão:

  • Os horários e datas no 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á ser compensada.
  • Os valores de duração no Planilhas também se tornam objetos Date, mas trabalhar com eles pode ser complicado.
  • Os valores percentuais no Planilhas se tornam números decimais no Apps Script. Por exemplo, uma célula com um valor de 10% se torna 0.1 no Apps Script.

Preenchimento automático

O Planilhas Google é compatível com o preenchimento automático e funções personalizadas, assim como as funções integradas. Ao digitar um nome de função em uma célula, você verá uma lista de funções integradas e personalizadas que correspondem ao que você digita.

As funções personalizadas aparecerão nessa lista se o script incluir uma tag JsDoc @customfunction, como no exemplo de 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çado

Usar os serviços do Apps Script

As funções personalizadas podem chamar determinados serviços do Apps Script para realizar 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 Script, as funções personalizadas nunca solicitam 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 o seguinte:

Serviços compatíveis Observações
Cache Funciona, mas não é muito útil em funções personalizadas
HTML Pode gerar HTML, mas não pode ser exibido (raramente útil)
JDBC
Idioma
Bloquear Funciona, mas não é muito útil em funções personalizadas
Maps Pode calcular rotas, mas não exibir mapas
Propriedades getUserProperties() recebe apenas 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 (pode 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á autorização do usuário e, portanto, não pode 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 escrever uma função personalizada. Uma função acionada em um menu solicitará a autorização do usuário, se necessário, e, consequentemente, poderá usar todos os serviços do Apps Script.

Compartilhamento

As funções personalizadas começam limitadas à 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ê utilize um dos seguintes métodos:

  • Clique em Extensões > Apps Script para abrir o editor de script. Depois, copie o texto do script da planilha original e cole-o no editor de outra planilha.
  • Faça uma cópia da planilha que contém a função personalizada clicando em Arquivo e g; Faça uma cópia. Quando uma planilha é copiada, todos os scripts anexados a ela também são copiados. Qualquer pessoa com acesso à planilha pode copiar o script. Os colaboradores que só têm acesso para visualização não podem abrir o editor de script na planilha original. No entanto, quando faz uma cópia, ela passa a ser o proprietário da cópia e pode ver o script.
  • Publique o script como um complemento do Planilhas Google.

Otimização

Sempre 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 (ou centenas, ou milhares) de chamadas de função personalizadas, esse processo poderá ser muito lento.

Consequentemente, se você planeja usar uma função personalizada várias vezes em um grande intervalo de dados, considere modificar a função para que ela aceite um intervalo como entrada na forma de uma matriz bidimensional, depois retorne uma matriz bidimensional que pode transbordar para 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 o método DOUBLE de maneira recursiva em todos os valores da matriz bidimensional de células. Ele retorna uma matriz bidimensional que contém os resultados. Dessa forma, é possível chamar DOUBLE apenas uma vez, mas fazer o cálculo para um grande número de células de uma só vez, conforme mostrado na captura de tela abaixo. É possível realizar o mesmo com as instruções if aninhadas em vez da chamada map.

Da mesma forma, a função personalizada abaixo busca eficientemente conteúdo ao vivo da Internet e usa uma matriz bidimensional para exibir duas colunas de resultados com apenas uma chamada de função. Se cada célula exigir a própria chamada de função, a operação precisará de mais tempo, já que o servidor do Apps Script terá que 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 todas as funções personalizadas usadas repetidamente em uma planilha, embora os detalhes de implementação variem de acordo com o comportamento da função.