Fundamentos do Apps Script com o Planilhas Google no 2: Planilhas, Planilhas e Intervalos

1. Introdução

Esta é a segunda parte da playlist de fundamentos do Apps Script com o Planilhas Google. O codelab anterior se concentrou nos conceitos do editor de script, nas macros e nas funções personalizadas. Este codelab aborda o serviço da planilha, que você pode usar para ler, gravar e manipular dados no Planilhas Google.

O que você vai aprender

  • Como planilhas, páginas e intervalos são representados no Apps Script.
  • Como acessar, criar e renomear a planilha ativa (aberta) com as classes SpreadsheetApp e Spreadsheet.
  • Como mudar o nome de uma página e a orientação de coluna/linha de um intervalo usando a classe Sheet.
  • Como especificar, ativar, mover e classificar um grupo de células ou um intervalo de dados usando a classe Range.

Antes de começar

Este é o segundo codelab da playlist "Fundamentos do Apps Script com o Planilhas Google". Antes de começar, conclua o primeiro codelab: Macros e funções personalizadas.

Pré-requisitos

  • Conhecimentos dos tópicos básicos do Apps Script explorados no codelab anterior desta playlist.
  • Ter noções básicas sobre o Editor do Apps Script
  • Ter noções básicas sobre o Planilhas Google
  • Capacidade de ler a notação A1 no Planilhas
  • Ter noções básicas sobre o JavaScript e a classe String dele

A próxima seção apresenta as principais classes do serviço de planilhas.

2. Introdução ao serviço de planilha

Quatro classes abrangem a base do serviço da planilha: SpreadsheetApp, Spreadsheet, Sheet e Range. Esta seção descreve essas classes e para que elas são usadas.

A classe SpreadsheetApp

Antes de analisar planilhas, páginas e intervalos, analise a classe mãe: SpreadsheetApp. Muitos scripts começam chamando métodos SpreadsheetApp, porque podem fornecer o ponto de acesso inicial aos arquivos do Planilhas Google. Considere SpreadsheetApp como a classe principal do serviço de planilha. A classe SpreadsheetApp não é detalhada aqui. No entanto, mais adiante neste codelab, você encontrará exemplos e exercícios para entender melhor essa aula.

Planilhas, planilhas e as turmas delas

Como um termo do Planilhas, uma planilha é um arquivo do Planilhas Google (armazenado no Google Drive) com dados organizados por linhas e colunas. Às vezes, uma planilha é chamada de "Planilhas Google" da mesma forma que um documento é chamado de "Documento do Google".

Você pode usar a classe Spreadsheet para acessar e modificar os dados dos arquivos do Planilhas Google. Também é possível usar essa classe para outras operações no nível do arquivo, como adicionar colaboradores.

f00cc1a9eb606f77.png

Uma página** representa a página individual de uma planilha, às vezes chamada de ""tab." Cada planilha pode conter uma ou mais páginas. Você pode usar a classe Sheet** para acessar e modificar dados e configurações no nível da página, como mover linhas ou colunas de dados.

39dbb10f83e3082.png.

Em resumo, a classe Spreadsheet opera na coleção de páginas e define um arquivo do Planilhas Google no Google Drive. A classe Sheet funciona em páginas específicas de uma planilha.

Classe Range

A maioria das operações de manipulação de dados (por exemplo, leitura, gravação ou formatação de dados de células) requer que você defina a quais células a operação se aplica. Você pode usar a classe Range para selecionar conjuntos específicos de células da página. As instâncias desta classe representam um intervalo, um grupo de uma ou mais células adjacentes em uma página. É possível especificar intervalos pelos números das linhas e colunas ou usando a notação A1.

O restante do codelab mostra exemplos de scripts que funcionam com essas classes e os respectivos métodos.

3. Configurar

Para continuar, você precisa de uma planilha com alguns dados. Disponibilizamos uma para você: clique neste link para copiar a planilha de dados e, em seguida, clique em Fazer uma cópia.

5376f721894b10d9.png

Uma cópia da planilha de exemplo que você pode usar está na sua pasta do Google Drive, com o nome "Copiada de planilha sem título". Use esta planilha para concluir os exercícios deste codelab.

Como lembrete, você pode abrir o editor de script no Planilhas Google clicando em Extensões> Apps Script.

Ao abrir um projeto do Apps Script no editor de script pela primeira vez, ele cria um projeto de script e um arquivo de script para você.

A próxima seção mostra como usar a classe Spreadsheet para melhorar essa planilha.

4. Acessar e modificar planilhas

Nesta seção, você aprenderá a usar as classes SpreadsheetApp e Spreadsheet para acessar e modificar planilhas. Especificamente, os exercícios ensinam você a renomear e duplicar páginas em uma planilha.

Essas operações são simples, mas geralmente fazem parte de um fluxo de trabalho maior e mais complexo. Depois de entender como automatizar essas tarefas com código de script, fica mais fácil aprender como automatizar operações mais elaboradas.

Renomear a planilha ativa

Suponha que você queira alterar o nome padrão, "Copiada de planilha sem título", para um título que reflita melhor a finalidade da sua planilha. Você pode fazer isso com as classes SpreadsheetApp e Spreadsheet.

  1. No editor de script, substitua o bloco de código padrão myFunction() pelo seguinte código:
function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. Para salvar o script, clique em Salvar save.
  2. Para renomear seu projeto do Apps Script, clique em Projeto sem título, insira "Avocado price" como o novo nome e clique em Renomear.
  3. Para executar seu script, selecione renameSpreadsheet na lista de funções e clique em Executar.
  4. Autorize a macro seguindo as instruções exibidas na tela. Se você receber a mensagem "Este app não foi verificado", clique em Avançado e em Ir para os preços do Avocado (não seguro). Na próxima tela, clique em Permitir.

Quando a função for executada, o nome de arquivo da sua planilha mudará:

226c7bc3c2fbf33e.png

Vamos analisar o código inserido. O método getActiveSpreadsheet() retorna um objeto que representa a planilha ativa, ou seja, a cópia da planilha de exercícios que você criou. O objeto da planilha é armazenado na variável mySS. Chamar rename(newName) em mySS altera o nome do arquivo de planilha no Google Drive para "Preços do Avocado 2017 em Portland, Seattle".

Como a variável mySS é uma referência à planilha, é possível tornar o código mais limpo e eficiente chamando os métodos Spreadsheet no mySS em vez de chamar getActiveSpreadsheet() várias vezes.

Duplicar a página ativa

Sua planilha atual tem apenas uma página. Você pode chamar o método Spreadsheet.duplicateActiveSheet() para fazer uma cópia da página:

  1. Adicione a seguinte função abaixo da função renameSpreadsheet() que já está no projeto do script:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. Salve seu projeto de script.
  2. Para executar seu script, selecione duplicateAndOrganizeActiveSheet na lista de funções e clique em Executar.

Retorne ao Planilhas Google para ver uma nova guia "Copiada da página" do Planilhas Google é adicionada à sua planilha.

d24f9f4ae20bf7d4.gif

Nessa nova função, o método duplicateActiveSheet() cria, ativa e retorna a página duplicada na planilha. A planilha resultante está armazenada em duplicateSheet, mas o código ainda não faz nada com a variável.

Na próxima seção, você usará a classe Sheet para renomear e formatar a página duplicada.

5. Formatar sua planilha com a classe Planilhas

A classe Sheet oferece métodos que permitem que os scripts leiam e atualizem as páginas. Nesta seção, você aprenderá a mudar o nome e a largura das colunas de uma página usando métodos da classe Sheet.

Mudar o nome da página

Para renomear planilhas, basta renomear em renameSpreadsheet(). Apenas uma chamada de método é necessária.

  1. No Planilhas Google, clique no arquivo Sheet_Original para ativá-lo.
  2. No Apps Script, modifique a função duplicateAndOrganizeActiveSheet() para corresponder ao seguinte:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}
  1. Salve e execute a função.

No Planilhas Google, uma página duplicada é criada e renomeada quando você executa a função:

91295f42354f62e7.gif

No código adicionado, o método setName(name) muda o nome da duplicateSheet, usando getSheetID() para conseguir o número de ID exclusivo da planilha. O operador + concatena o ID da planilha ao final da string "Sheet_".

Modificar linhas e colunas de uma página

Você também pode usar a classe Sheet para formatar a página. Por exemplo, podemos atualizar a função duplicateAndOrganizeActiveSheet() para redimensionar as colunas da página duplicada e adicionar linhas congeladas:

  1. No Planilhas Google, clique no arquivo Sheet_Original para ativá-lo.
  2. No Apps Script, modifique a função duplicateAndOrganizeActiveSheet() para corresponder ao seguinte:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}
  1. Salve e execute a função.

No Planilhas Google, uma página duplicada é criada, renomeada, ativada e formatada:

2e57c917ab157dad.gif

O código adicionado usa autoResizeColumns(startColumn, numColumns) para redimensionar as colunas da página e facilitar a leitura. O método setFrozenRows(rows) congela o número de linhas informado (neste caso, duas), que mantém as linhas de cabeçalho visíveis à medida que o leitor rola a planilha para baixo.

Na próxima seção, você aprenderá sobre intervalos e manipulação básica de dados.

6. Reorganizar dados com a classe Range

A classe Range e os métodos dela fornecem a maioria das opções de manipulação e formatação de dados no serviço de planilhas.

Nesta seção, apresentamos a manipulação básica de dados com intervalos. Esses exercícios focam a utilização de intervalos no Apps Script, enquanto outros codelabs nesta playlist vão além da manipulação e da formatação dos dados.

Mover intervalos

Você pode ativar e mover intervalos de dados com métodos de classe e notação A1, uma forma de identificar conjuntos específicos de células em planilhas. Se você precisar se familiarizar novamente, confira esta descrição da notação A1.

Vamos atualizar seu método duplicateAndOrganizeActiveSheet() para mover alguns dados também:

  1. No Planilhas Google, clique no arquivo Sheet_Original para ativá-lo.
  2. No Apps Script, modifique a função duplicateAndOrganizeActiveSheet() para corresponder ao seguinte:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}
  1. Salve e execute a função.

Quando você executa essa função, uma página duplicada é criada, ativada e formatada. E o conteúdo da coluna F é movido para a coluna C:

10ea483aec52457e.gifs

O novo código usa o método getRange(a1Notation) para identificar o intervalo de dados a ser movido. Ao inserir a notação A1 "F2:F" como o parâmetro do método, você especifica a coluna F (excluindo F1). Se o intervalo especificado existir, o método getRange(a1Notation) retornará a instância Range. O código armazena a instância na variável myRange para facilitar o uso.

Quando o intervalo é identificado, o método moveTo(target) move o conteúdo de myRange (os valores e a formatação). O destino (coluna C) é especificado usando a notação A1 "C2". Essa é uma célula única em vez de uma coluna. Ao mover dados, não é necessário fazer a correspondência entre os tamanhos e os intervalos de destino e de destino. O Apps Script simplesmente alinha a primeira célula de cada uma.

Classificar intervalos

A classe Range permite ler, atualizar e organizar grupos de células. Por exemplo, é possível classificar um intervalo de dados usando o método Range.sort(sortSpecObj):

  1. No Planilhas Google, clique no arquivo Sheet_Original para ativá-lo.
  2. No Apps Script, modifique a função duplicateAndOrganizeActiveSheet() para corresponder ao seguinte:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}
  1. Salve e execute a função.

Agora, a função, além da nossa formatação anterior, classifica todos os dados da tabela usando as informações de preço na coluna C:

a6cc9710245fae8d.png

O novo código usa getRange(a1Notation) para especificar um novo intervalo que abrange a A3:D55 (a tabela inteira, excluindo os cabeçalhos das colunas). Em seguida, o código chama o método sort(sortSpecObj) para classificar a tabela. Aqui, o parâmetro sortSpecObj é o número da coluna a ser classificada. O método classifica o intervalo para que os valores indicados da coluna vão da menor para a maior (valores ascendentes). O método sort(sortSpecObj) pode realizar requisitos de classificação mais complexos, mas você não precisa deles aqui. É possível ver todas as diferentes maneiras de chamar intervalos de classificação na documentação de referência de método.

Parabéns, você concluiu todos os exercícios no codelab. A próxima seção analisa os principais pontos deste codelab e os exibe na próxima.

7. Conclusão

Você chegou ao fim deste codelab. Agora você pode usar e definir as classes e os termos essenciais do serviço de planilhas no Apps Script.

Tudo pronto para o próximo codelab.

Você achou este codelab útil?

Sim Não

O que vimos

A seguir

O próximo codelab desta playlist aborda em mais detalhes como ler, gravar e modificar dados em uma planilha.

Encontre o próximo codelab em Como trabalhar com dados.