Funções personalizadas no Planilhas Google

As Planilhas Google oferecem centenas de funções integradas, como AVERAGE, SUM e VLOOKUP. Quando essas opções não são suficientes para suas necessidades, use o Google Apps Script para escrever funções personalizadas, por exemplo, para converter metros em milhas ou buscar conteúdo dinâmico da Internet. Depois, use essas funções no Google Sheets como se fossem funções integradas.

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 nem está associado ao Google.

Esta é 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 programar em JavaScript e não tem tempo para aprender, confira a loja de complementos para saber se alguém já criou a função personalizada que você precisa.

Como criar uma função personalizada

Para escrever uma função personalizada:

1. Crie 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 copiar e colar 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 funções personalizadas comocomplementos para as Planilhas Google. Para usar ou conhecer esses complementos:

1. Crie ou abra uma planilha no Planilhas Google.
2. Na parte de cima, clique em Complementos > Instalar complementos.
3. Quando o 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 que lhe interessa, clique em Instalar.
6. Uma caixa de diálogo pode informar que o complemento requer autorização. Se for o caso, leia o aviso com atenção e clique em Permitir.
7. O complemento fica disponível na planilha. Para usar o complemento em outra planilha, abra o arquivo e, na parte de cima, 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 escrever ou instalar uma função personalizada na Google Workspace Marketplace, é fácil usar como uma 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 vai mostrar Loading... por um momento e depois retornar o resultado.

Diretrizes para funções personalizadas

Antes de escrever sua própria função personalizada, confira algumas diretrizes.

Nomeação

Além das convenções padrão para nomear funções JavaScript, fique atento ao 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 denota uma função particular no Apps Script.
• O nome de uma função personalizada precisa ser declarado com a sintaxe function myFunction(), não var myFunction = new Function().
• O uso de maiúsculas e minúsculas não importa, embora os nomes das funções de planilha sejam tradicionalmente em maiúsculas.

Argumentos

Assim como uma função integrada, uma função personalizada pode receber 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 das 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 código de exemplo para DOUBLE acima precisaria ser modificado para aceitar uma matriz como entrada.

  
  

• Os argumentos de função personalizada precisam ser determinísticos. Ou seja, as funções integradas de planilha que retornam um resultado diferente a cada cálculo, como NOW() ou RAND(), não são permitidas como argumentos de uma função personalizada. Se uma função personalizada tentar retornar um valor com base em uma dessas funções integradas voláteis, ela vai mostrar Loading... indefinidamente.

Valores de retorno

Cada função personalizada precisa retornar um valor para ser exibido, de modo que:

• Se uma função personalizada retornar um valor, ele vai aparecer na célula de onde a função foi chamada.
• Se uma função personalizada retornar uma matriz bidimensional de valores, eles vão transbordar para as células adjacentes, desde que estejam vazias. Se isso fizer com que a matriz sobrescreva o conteúdo das células, a função personalizada vai gerar um erro. Por exemplo, consulte a seção sobre otimização de funções personalizadas.
• Uma função personalizada não pode afetar células diferentes daquelas em que ela 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 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 vai mostrar #ERROR! e a observação da célula será Exceeded maximum execution time (line 0)..

Tipos de dados

As Planilhas Google armazenam dados em formatos diferentes, dependendo da natureza deles. 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 de confusão mais comuns:

• Horários e datas nas Planilhas se tornam objetos Date 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 de porcentagem no Planilhas Google se tornam números decimais no Apps Script. Por exemplo, uma célula com o valor 10% se torna 0.1 no Apps Script.

Preenchimento automático

As Planilhas Google oferecem suporte ao preenchimento automático de funções personalizadas, assim como as 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 você inseriu.

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

Usar serviços do Google Apps Script

As funções personalizadas podem chamar determinados serviços do Google 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 espanhol.

Ao contrário da maioria dos outros tipos de Apps Script, 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 têm acesso a dados pessoais, especificamente os seguintes:

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 mostrar (raramente útil)
JDBC
Idioma
Bloquear	Funciona, mas não é muito útil em funções personalizadas
Maps	Pode calcular rotas, mas não mostrar 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 (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 vai exigir 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 escrever uma função personalizada. Uma função acionada em um menu pede autorização ao usuário, se necessário, e pode 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, a menos que você use um dos métodos a seguir:

• Clique em Extensões > Apps Script para abrir o editor de script. Em seguida, copie o texto do script da planilha original e cole 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, todos os scripts anexados a ela também são copiados. Qualquer pessoa com acesso à planilha pode copiar o script. Os colaboradores que têm apenas acesso de leitura não podem abrir o editor de script na planilha original. No entanto, quando eles fazem uma cópia, se tornam proprietários dela e podem ver o script.)
• Publique o script como um complemento do editor do Planilhas Google.

Todos os scripts vinculados a contêineres têm as mesmas listas de acesso dos contêineres. Isso significa que qualquer pessoa com permissão para editar a planilha pode editar qualquer código do Apps Script anexado a ela.

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 (ou centenas, ou milhares!) de chamadas de função personalizadas, esse processo pode ser bem lento. Alguns projetos com muitas funções personalizadas ou funções complexas podem ter um atraso temporário nas execuções.

Portanto, 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 e retorne uma matriz bidimensional que possa 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 o método na matriz bidimensional de células para receber cada linha. Em seguida, para cada linha, ela usa map novamente para retornar o dobro do valor de cada célula. Ele retorna uma matriz bidimensional que contém os resultados. Assim, você pode chamar DOUBLE apenas uma vez, mas calcular um grande número de células de uma só vez, como mostrado na captura de tela abaixo. Você pode fazer a mesma coisa com instruções if aninhadas em vez da chamada map.

Da mesma forma, a função personalizada abaixo busca conteúdo dinâmico da Internet de maneira eficiente e usa uma matriz bidimensional para mostrar duas colunas de resultados com apenas uma chamada de função. Se cada célula exigisse uma chamada de função própria, a operação levaria muito mais tempo, já que o servidor do Apps Script teria que baixar e analisar o feed XML a cada vez.

/**
 * 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 da implementação variem dependendo do comportamento da função.