Funções personalizadas no Planilhas Google

O Planilhas Google oferece centenas de funções integradas como AVERAGE, SUM VLOOKUP. Quando elas não são o suficiente para suas necessidades, você pode usar o Google Apps Script para criar funções personalizadas — por exemplo, para converter metros em milhas ou buscar conteúdo ao vivo da Internet, depois use o conteúdo no Planilhas Google como uma função integrada.

Primeiros passos

As funções personalizadas são criadas usando JavaScript padrão. Se você é novo JavaScript, o Codecademy oferece ótimo curso para iniciantes. Observação: este curso não foi desenvolvido nem está associado ao Google.

Veja uma função personalizada simples, chamada DOUBLE, que multiplica uma 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 programar JavaScript e não tem tempo para isso, verificar 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:

  1. Criar ou abra uma planilha no Planilhas Google.
  2. Selecione o item de menu Extensões > Apps Script.
  3. Exclua qualquer código no editor de script. Para a função DOUBLE acima, basta copie e cole o código no editor de script.
  4. 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 opções funciona como complementos para o Planilhas Google. Para usar ou conhecer esses complementos:

  1. Criar ou abra uma planilha no Planilhas Google.
  2. Na parte superior, clique em Complementos > Instalar complementos.
  3. Depois que o Google Workspace Marketplace 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 do 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 um planilha diferente, 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 nesta documento.

Como usar uma função personalizada

Depois de escrever uma função personalizada ou instalar uma usando a Google Workspace Marketplace, é tão fácil de usar quanto função integrada:

  1. Clique na célula em que você quer usar a função.
  2. Digite um sinal de igual (=) seguido do nome da função e qualquer valor de entrada — por exemplo, =DOUBLE(A1), e pressione Enter.
  3. 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 de nomenclatura de 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 (_), o que indica uma função particular no Apps Script.
  • O nome de uma função personalizada precisa ser declarado com a sintaxe function myFunction(), e não var myFunction = new Function().
  • O uso de letras maiúsculas não importa, embora os nomes das funções da planilha são tradicionalmente maiúsculas.

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 sua função com uma referência a um intervalo de células como uma (como =DOUBLE(A1:B10)), ele será um argumento bidimensional matriz das células valores. Por exemplo, na captura de tela abaixo, argumentos em =DOUBLE(A1:B2) são interpretados pelo Apps Script como double([[1,3],[2,4]]). O exemplo de código para DOUBLE mencionado acima teria que ser modificado para aceitar uma matriz como entrada.


  • Os argumentos da função personalizada precisam ser determinísticos. Isso são funções de planilha integradas que retornam um resultado diferente a cada vez calculados, como NOW() ou RAND(), não são permitidos como argumentos. a uma função personalizada. Se uma função personalizada tentar retornar um valor com base no uma dessas funções integradas voláteis, ele vai mostrar 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, o valor será exibido na célula a partir da qual a função foi chamada.
  • Se uma função personalizada retornar uma matriz bidimensional de valores, os valores transbordam 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 um erro será gerado. Para ver um exemplo, consulte a seção 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 a as células das quais ele é chamado 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, o a célula exibe #ERROR! e a nota da célula é Exceeded maximum execution time (line 0).

Tipos de dados

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

  • As horas e as datas no Planilhas ficam Date no Apps Script. Se a planilha e o usam fusos horários diferentes (um problema raro), a função personalizada precisam de compensação.
  • Os valores de duração nas Planilhas também se tornam objetos Date, mas trabalhar com elas pode ser complicado.
  • Os valores percentuais nas Planilhas se tornam números decimais no Apps Script. Para Por exemplo, uma célula com o valor 10% se torna 0.1 no Apps Script.

Preenchimento automático

O Planilhas Google oferece suporte ao preenchimento automático para funções personalizadas, assim como para funções integradas. Conforme você digitar um nome de função em uma célula, você verá uma lista de funções que correspondam ao que foi inserido.

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

Como usar os serviços do Google Apps Script

As funções personalizadas podem chamar certos Serviços do Google Apps Script para realizar tarefas mais complexas tarefas. Por exemplo, uma função personalizada pode chamar a função Serviço de idioma para traduzir um inglês em espanhol.

Ao contrário da maioria dos outros tipos de Apps Scripts, as funções personalizadas nunca pedem que os usuários autorizar 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 O getUserProperties() só recebe as propriedades do proprietário da planilha. Os editores da planilha não podem definir propriedades do usuário em um 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 precisará da autorização do usuário e, portanto, não poderá ser usada em uma função personalizada.

Para usar um serviço diferente dos listados acima, crie um menu personalizado que executa uma função do Apps Script em vez de escrever uma função personalizada. Função acionada a partir de um menu. solicitará a autorização do usuário, se necessário, e, consequentemente, poderá usar todas as Serviços do Apps Script.

Compartilhamento

As funções personalizadas começam vinculadas à planilha em que foram criados. Isso significa que uma função personalizada escrita uma planilha não pode ser usada em outras planilhas, a menos que você use uma das seguintes métodos:

  • Clique em Extensões > Apps Script para abra o editor de script e copie texto do script da planilha original e colá-lo 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 eles também são copiados. Qualquer pessoa que tenha acesso à planilha pode copiar script. Os colaboradores com acesso somente leitura não podem abrir o editor de script. na planilha original. No entanto, quando fazem uma cópia, elas se tornam proprietário da cópia e possa 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 cria uma chamada separada para o servidor do Apps Script. Se sua planilha tiver dezenas (ou centenas ou milhares!) de chamadas de funções personalizadas, esse processo pode ser bem lentamente.

Consequentemente, se você planeja usar uma função personalizada várias vezes em um grande de dados, considere modificar a função para que ela aceite um intervalo como entrada na forma de uma matriz bidimensional, então retorna uma entrada matriz que pode ultrapassar as células apropriadas.

Por exemplo, a função DOUBLE() mostrada acima pode ser reescrita para aceitar uma 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 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 seja calculada para um grande número de células de uma vez, como mostrado em na captura de tela abaixo. (Você pode fazer o mesmo com if aninhado em vez da chamada map.

Da mesma forma, a função personalizada abaixo busca 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 exigisse sua própria chamada de função, a levaria muito mais tempo, já que o servidor do Apps Script 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 praticamente qualquer função personalizada usada repetidamente ao longo de uma planilha, embora os detalhes da implementação variam de acordo com o comportamento da função.