5 Funções de script do Google Sheets que você precisa saber

Google Sheets é uma poderosa ferramenta de planilha baseada em nuvem que permite fazer quase tudo o que você pode fazer no Microsoft Excel. Mas o verdadeiro poder do Google Sheets é o recurso de script do Google que vem com ele.

O Google Apps Script é uma ferramenta de script de fundo que funciona não apenas no Google Sheets, mas também no Google Docs, Gmail, Google Analytics e quase todos os outros serviços do Google Cloud. Permite automatizar esses aplicativos individuais e integrar cada um desses aplicativos entre si.

Isso abre a janela do editor de scripts e padrão para uma função chamada MyFunction (). É aqui que você pode criar e testar seu script do Google.

Para dar uma chance, tente criar uma função de script de folhas do Google que leia dados de uma célula, execute um cálculo e produza os dados para outra célula.

A função de obter dados de uma célula é o getRange () e Obter valor() funções. Você pode identificar a célula por linha e coluna. Portanto, se você tiver um valor na linha 2 e na coluna 1 (a coluna A), a primeira parte do seu script ficará assim:

function myfunction () var sheet = spreadsheetapp.getActiveSheet (); var linha = 2; var col = 1; var dados = folha.GetRange (Row, Col).Obter valor(); 

Isso armazena o valor dessa célula no dados variável. Você pode realizar um cálculo nos dados e depois escrever esses dados para outra célula. Portanto, a última parte desta função será:

 var resulta = dados * 100; folha.getRange (linha, col+1).setValue (resultados); 

Quando terminar de escrever sua função, selecione o ícone do disco para salvar. 

Na primeira vez que você executa uma nova função de script do Google Sheets como esta (selecionando o ícone Run), você precisará fornecer autorização para o script executar em sua conta do Google.

Permitir que as permissões continuem. Depois que seu script for executado, você verá que o script escreveu os resultados do cálculo para a célula de destino.

Agora que você sabe como escrever uma função básica de script do Google Apps, vamos dar uma olhada em algumas funções mais avançadas.

Use getValues ​​para carregar matrizes

Você pode levar o conceito de fazer cálculos sobre dados em sua planilha com script para um novo nível usando matrizes. Se você carregar uma variável no script do Google Apps usando getValues, a variável será uma matriz que pode carregar vários valores da folha.

function myfunction () var sheet = spreadsheetapp.getActiveSheet (); var dados = folha.getDatarange ().getValues ​​();

A variável de dados é uma matriz multidimensional que mantém todos os dados da folha. Para realizar um cálculo dos dados, você usa um para laço. O balcão do loop for funcionará em cada linha e a coluna permanece constante, com base na coluna onde você deseja puxar os dados.

Em nossa planilha de exemplo, você pode executar cálculos nas três linhas de dados da seguinte forma.

para (var i = 1; i < data.length; i++)  var result = data[i][0] * 100; sheet.getRange(i+1, 2).setValue(result);   

Salve e execute este script exatamente como você fez acima. Você verá que todos os resultados estão preenchidos na coluna 2 em sua planilha.

Você notará que referenciar uma célula e uma linha em uma variável de matriz é diferente de uma função GetRange. 

dados [i] [0] refere -se às dimensões da matriz em que a primeira dimensão é a linha e a segunda é a coluna. Ambos começam em zero.

getRange (i+1, 2) refere -se à segunda linha quando i = 1 (já que a linha 1 é o cabeçalho) e 2 é a segunda coluna onde os resultados são armazenados.

Use AppendRow para escrever resultados

E se você tiver uma planilha onde deseja escrever dados em uma nova linha em vez de uma nova coluna?

Isso é fácil de fazer com o APENDROW função. Esta função não incomodará nenhum dados existente na folha. Isso apenas anexará uma nova linha à folha existente.

Como exemplo, faça uma função que contará de 1 a 10 e mostrará um contador com múltiplos de 2 em um Contador coluna.

Esta função seria assim:

function myfunction () var sheet = spreadsheetapp.getActiveSheet (); para (var i = 1; i<11; i++)  var result = i * 2; sheet.appendRow([i,result]);  

Aqui estão os resultados quando você executa esta função.

Processo RSS Feeds com urlfetchapp

Você pode combinar a função de script de folhas do Google anterior e a Urlfetchapp Para puxar o feed RSS de qualquer site e escrever uma linha para uma planilha para cada artigo publicado recentemente no site.

Este é basicamente um método de bricolage para criar sua própria planilha de leitor de feeds RSS!

O script para fazer isso também não é muito complicado.

function myfunction () var sheet = spreadsheetapp.getActiveSheet (); var item, data, título, link, desc; var txt = urlfetchapp.buscar ("https: // www.TopSecretWriters.com/rss ").getContentText (); var doc = xml.parse (txt, falso); title = doc.getElement ().getElement ("canal").getElement ("título").getText (); var itens = doc.getElement ().getElement ("canal").getElements ("item"); // analisando itens únicos no feed RSS para (var i em itens) item = itens [i]; título = item.getElement ("título").getText (); link = item.getElement ("link").getText (); Data = item.GetElement ("Pubdate").getText (); desc = item.getElement ("Descrição").getText (); folha.appendRow ([título, link, data, desc]); 

Como você pode ver, Xml.analisar puxa cada item do feed RSS e separa cada linha no título, link, data e descrição. 

Usando o APENDROW função, você pode colocar esses itens em colunas apropriadas para cada item no feed RSS.

A saída em sua folha será algo assim:

Em vez de incorporar o URL de alimentação RSS no script, você pode ter um campo em sua folha com o URL e depois ter várias folhas - uma para cada site que deseja monitorar.

Concatenar strings e adicionar um retorno de carruagem

Você pode levar a planilha RSS um passo adiante adicionando algumas funções de manipulação de texto e depois usar funções de email para enviar um e -mail com um resumo de todas as novas postagens no feed RSS do site.

Para fazer isso, sob o script que você criou na seção anterior, você deseja adicionar alguns scripts que extrairão todas as informações na planilha. 

Você deseja construir a linha de assunto e o corpo de texto email analisando todas as informações da mesma matriz de "itens" que você usou para escrever os dados RSS na planilha. 

Para fazer isso, inicialize o assunto e a mensagem colocando as seguintes linhas antes dos "itens" para loop.

VAR Subject = 'Últimos 10 artigos publicados no Mysite.com '
var message = "

Então, no final dos "itens" para loop (logo após a função Apêndrow), adicione a seguinte linha.

mensagem = mensagem + título + '\ n' + link + '\ n' + date + '\ n' + desc + '\ n' + '\ n \ n';

O símbolo "+" concatenará todos os quatro itens juntos, seguidos de "\ n" para um retorno de carruagem após cada linha. No final de cada bloco de dados do título, você deseja que dois retornos de carruagem para um corpo de email bem formatado.

Depois que todas as linhas são processadas, a variável "corpo" contém toda a string de mensagem de e -mail. Agora você está pronto para enviar o e -mail!

Como enviar e -mail no script do Google Apps

A próxima seção do seu script do Google será enviar o "assunto" e o "corpo" por e -mail. Fazer isso no script do Google é muito fácil.

var e -mailAddress = [email protected];
MailApp.sendEmail (emailaddress, assunto, mensagem);

O MailApp é uma aula muito conveniente do Google Apps Scripts que oferece acesso ao serviço de email da sua conta do Google para enviar ou receber e -mails. Graças a isso, a linha única com a função SendEmail permite enviar qualquer email com apenas o endereço de e -mail, a linha de assunto e o texto do corpo.

É assim que o email resultante será. 

A combinação da capacidade de extrair o feed RSS de um site, armazená -lo em uma planilha do Google e enviá -lo com links de URL incluídos, torna muito conveniente seguir o conteúdo mais recente de qualquer site.

Este é apenas um exemplo do poder disponível nos scripts do Google Apps para automatizar ações e integrar vários serviços em nuvem.