O Planilhas Google^{(Google Sheets)} é uma poderosa ferramenta de planilha baseada em nuvem que permite fazer quase tudo o que você poderia fazer no Microsoft Excel . Mas o verdadeiro poder do Planilhas Google^{(Google Sheets)} é o recurso de script do Google^{(Google Scripting)} que o acompanha.

O script do Google Apps^{(Google Apps)} é uma ferramenta de script em segundo plano que funciona não apenas no Planilhas Google^{(in Google Sheets)} , mas também no Google Docs, Gmail, Google Analytics e em quase todos os outros serviços de nuvem do Google . Ele permite automatizar esses aplicativos individuais e integrar cada um desses aplicativos entre si.

Neste artigo, você aprenderá a começar a usar scripts do Google Apps , a criar um script básico no Planilhas Google^{(Google Sheets)} para ler e gravar dados de células e as funções de script avançadas mais eficazes do Planilhas Google .^{(Google Sheets)}

Como criar um script do Google Apps^{(How to Create a Google Apps Script)}

Você pode começar agora mesmo a criar seu primeiro script do Google Apps a partir do ^{(Google Apps)}Planilhas Google^{(Google Sheets)} . 

Para fazer isso, selecione Ferramentas^(Tools) no menu e, em seguida, Editor de scripts^{(Script Editor)} .

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

Para tentar, tente criar uma função de script do Planilhas Google^{(Google Sheets)} que lerá dados de uma célula, realizará um cálculo nela e enviará a quantidade de dados para outra célula.

A função para obter dados de uma célula são as funções getRange()^(getRange()) e getValue() . 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 row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

Isso armazena o valor dessa célula na variável de dados . ^(data)Você pode realizar um cálculo nos dados e, em seguida, gravar esses dados em outra célula. Então a última parte desta função será:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

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

Na primeira vez que você executar uma nova função de script do Planilhas Google^{(Google Sheets)} como esta (selecionando o ícone de execução), será necessário fornecer Autorização^{(Authorization)} para que o script seja executado em sua Conta do Google^{(Google Account)} .

Permita que as permissões continuem. Depois que seu script for executado, você verá que o script gravou os resultados do cálculo na célula de destino.

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

Use getValues ​​para carregar arrays^{(Use getValues To Load Arrays)}

Você pode levar o conceito de fazer cálculos em dados em sua planilha com scripts 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 planilha.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

A variável de dados é uma matriz multidimensional que contém todos os dados da planilha. Para realizar um cálculo nos dados, você usa um loop for . O contador do loop for funcionará em cada linha e a coluna permanecerá constante, com base na coluna em que você deseja extrair os dados.

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

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

Salve^(Save) e execute este script exatamente como você fez acima. Você verá que todos os resultados são preenchidos na coluna 2 da planilha.

Você notará que fazer referência a uma célula e linha em uma variável de matriz é diferente de uma função getRange. 

data[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 gravar resultados^{(Use appendRow To Write Results)}

E se você tiver uma planilha na qual deseja gravar dados em uma nova linha em vez de em uma nova coluna?

Isso é fácil de fazer com a função appendRow . Esta função não incomodará nenhum dado existente na planilha. Ele apenas anexará uma nova linha à planilha existente.

Como exemplo, faça uma função que conte de 1 a 10 e mostre um contador com múltiplos de 2 em uma coluna Contador .^(Counter)

Esta função ficaria assim:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (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.

Processe feeds RSS com URLFetchApp^{(Process RSS Feeds With URLFetchApp)}

Você pode combinar a função de script anterior do Planilhas Google e o ^{(Google Sheets)}URLFetchApp para extrair o feed RSS de qualquer site e escrever uma linha em uma planilha para cada artigo publicado recentemente nesse site.

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

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

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

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

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

A saída em sua planilha será algo como isto:

Em vez de incorporar o URL do feed RSS no script, você pode ter um campo em sua planilha com o URL e, em seguida, ter várias planilhas – uma para cada site que deseja monitorar.

Concatenar strings^{(Concatenate Strings)} e adicionar^(Add) um retorno de carro^{(Carriage Return)}

Você pode levar a planilha RSS um passo adiante adicionando algumas funções de manipulação de texto e, em seguida, usar as funções de e-mail para enviar a si mesmo um e-mail com um resumo de todas as novas postagens no feed ^(RSS)RSS do site .

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

Você vai querer construir a linha de assunto e o corpo do texto do e-mail analisando todas as informações da mesma matriz “itens” que você usou para gravar os dados RSS na planilha. 

Para fazer isso, inicialize o assunto e a mensagem colocando as seguintes linhas antes do loop For “itens”.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Então, no final do loop for “items” (logo após a função appendRow), adicione a seguinte linha.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

O símbolo “+” concatenará todos os quatro itens seguidos por “ ” para um retorno de carro após cada linha. No final de cada bloco de dados de título, você desejará dois retornos de carro para um corpo de e-mail bem formatado.

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

Como enviar e-mail no script do Google Apps^{(How To Send Email In Google Apps Script)}

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

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

O MailApp é uma classe muito conveniente dentro dos scripts do Google Apps que lhe dá acesso ao serviço de e-mail 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 e-mail^{(send any email)} apenas com o endereço de e-mail, linha de assunto e corpo do texto.

Esta é a aparência do e-mail resultante. 

Combinar a capacidade de extrair o feed RSS^(RSS) de um site , armazená-lo em uma planilha do Google^{(Google Sheet)} e enviá-lo para você mesmo com links de URL incluídos, torna muito conveniente acompanhar 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 de nuvem.

5 Google Sheets Script Functions You Need to Know

Google Sheets is a powerful cloud-based spreadsheet tool that lets you do nearly eνerything you could do in Micrоsoft Excel. But the real power of Google Ѕheets is the Google Scripting featurе that comes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

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

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Related posts

• 4 maneiras de converter Excel para Planilhas Google

• Planilhas Google vs Microsoft Excel – Quais são as diferenças?

• Como remover bordas de tabela no Google Docs

• Como o Google Docs Chat ajuda você a colaborar em documentos

• Como inserir uma marca d'água no Word e no Google Docs

• Como adicionar números de slides a uma apresentação do PowerPoint

• Como colocar objetos em camadas em uma apresentação do PowerPoint

• Como alterar o tamanho do slide no PowerPoint para apresentações melhores

• Google Docs vs Microsoft Word – Quais são as diferenças?

• Alterar para orientação de paisagem no Google Docs

• Como mostrar a contagem de palavras no Microsoft Word

• Como usar a função CONCATENAR no Planilhas Google

• Como alterar a fonte padrão nos aplicativos do Office

• Como criar e executar uma macro no Word

• 10 ferramentas para recuperar uma senha PST do Outlook perdida ou esquecida

• Como usar o Google Forms como um rastreador de despesas

• Como corrigir o Outlook preso ao carregar o perfil

• Como excluir linhas em branco no Excel

• Como encontrar e substituir palavras no MS Word e no Google Docs

• Como exportar seus e-mails do Microsoft Outlook para CSV ou PST