Planilhas do Google Curso de Capacitação de Servidores
Início do conteúdo
Script do Google Apps com o Planilhas Google

Apresentando o serviço de planilha

Quatro classes englobam a fundação do serviço de planilha: SpreadsheetApp, Spreadsheet, Sheet, e Range. Esta seção descreve essas classes e para que são usadas.

 

A classe SpreadsheetApp

Antes de aprofundar em planilhas, folhas, e intervalos, você deve olhar para o seu sua classe pai: SpreadsheetApp. Muitos scripts começam chamando SpreadsheetApp métodos, pois eles podem fornecer o ponto inicial de acesso aos arquivos do Planilhas Google. Você pode considerar SpreadsheetApp a classe principal do serviço de planilha . A SpreadsheetApp classe não é explorada em profundidade aqui. No entanto, posteriormente neste laboratório, você encontrará exemplos e exercícios para ajudá-lo a entender esta aula.

 

Planilhas, planilhas e suas classes

Como um termo do Planilhas, uma planilha é um arquivo do Planilhas Google (armazenado no Google Drive) que contém dados organizados por linhas e colunas. Uma planilha às vezes é chamada de ‘Planilha Google’, da mesma forma que um documento é chamado de ‘Documento Google’.

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

 

 

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

 

 

Em resumo, a Spreadsheet classe opera na coleção de planilhas e define um arquivo do Planilhas Google no Google Drive. A Sheet aula funciona em folhas individuais dentro de uma planilha.

 

A 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) exige que você defina a quais células a operação se aplica. Você pode usar a Range classe para selecionar conjuntos específicos de células em uma planilha. As instâncias desta classe representam um intervalo – um grupo de uma ou mais células adjacentes em uma folha. Você pode especificar intervalos por seus números de linha e coluna ou usando a notação A1.

 

 

As próximas seções mostram exemplos de scripts que funcionam com essas classes e seus métodos.

 

Configurando

Antes de continuar, você precisa de uma planilha com alguns dados. Fornecemos um para você: clique neste link para copiar a planilha de dados e, em seguida, clique no botão Fazer uma cópia .

 

 

Uma cópia da planilha de exemplo para você usar deve ser colocada na pasta inicial do Google Drive com o nome “Cópia da planilha sem título”. Use esta planilha para completar os exercícios deste codelab.

Como um lembrete, você pode abrir o editor de script selecionando Ferramentas> Editor de scripts no menu do Planilhas Google:

 

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

No codelab anterior, você já havia criado um script de macro antes de entrar no editor de script. Seu arquivo não possui macros, então o editor fornece um arquivo de script chamado Code.gs com o pré-gerado myFunction()

.

    Tudo pronto! Vamos ver agora como usar a Spreadsheet classe para melhorar esta planilha.

 

 

Acessando e modificando planilhas

Nesta seção, você pode aprender como usar as classes SpreadsheetApp e Spreadsheet para alterar e criar planilhas nesta página. Especificamente, os exercícios ensinam como renomear uma planilha e duplicar planilhas dentro de uma planilha.

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

 

Renomeando a planilha ativa

Suponha que você queira alterar o nome padrão, “Cópia da planilha sem título” para um título que reflita melhor o propósito da planilha. Com a classe SpreadsheetApp e Spreadsheet, você pode fazer exatamente isso.

  1. No editor de script, substitua o myFunction() bloco de código padrão pelo seguinte código:

 

function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. Select , File> Save , ou Ctrl + S ( Cmd + S em Macs) para salvar seu script.
  2. Quando solicitado a nomear o projeto de script, denomine “Preços do abacate”.
  3. Execute o seu script selecionando o nome da função na lista suspensa da barra de ferramentas e clicando no botão.
  4. Quando solicitado a autorizar o script, siga as instruções na tela para fazer isso. Se você receber uma mensagem “Este aplicativo não foi verificado”, clique em Avançado e , em seguida, clique em Ir para Preços de abacate (não seguro) . Na próxima tela, clique em Permitir .

 

 

Assim que a função for executada, o nome do arquivo da sua planilha deve mudar:

 

 

Vamos dar uma olhada no código que você inseriu. O getActiveSpreadsheet() método retorna um objeto que representa a planilha ativa; ou seja, a cópia da planilha de exercícios que você fez. Este objeto de planilha é armazenado na mySS variável. Chamando rename(newName) no mySS muda o nome do arquivo de planilha no Google Drive para “2017 abacate Preços em Portland, Seattle. ”

Como a mySS variável é uma referência à planilha, você pode tornar seu código mais limpo e eficiente chamando Spreadsheet métodos em em mySS vez de getActiveSpreadsheet() repetidamente.

 

Duplicando a página ativa

Em sua planilha atual, você tem atualmente apenas uma página. Você pode chamar o Spreadsheet.duplicateActiveSheet() método para fazer uma cópia dessa folha:

  1. Adicione a seguinte nova função abaixo da renameSpreadsheet()função que já está em seu projeto de script:
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. Salve seu projeto de script.
  2. Execute a nova função selecionando duplicateAndOrganizeActiveSheet na barra de ferramentas e clicando no botão.

Agora você deve ver uma nova página em sua planilha! Volte para o Planilhas e uma nova guia de planilha “Cópia da Planilha_Original” deverá existir.

 

    Nesta nova função, o duplicateActiveSheet() método cria, ativa e retorna a folha duplicada em sua planilha. Essa planilha resultante é armazenada em duplicateSheet, mas o código ainda não está fazendo nada com essa variável.

 

Formatando sua planilha com a classe Planilha

A Sheet classe fornece métodos que permitem aos scripts ler e atualizar planilhas. Nesta seção, você pode aprender como alterar o nome e o espaçamento de uma planilha com métodos da Sheet classe.

 

Quando você chama Spreadsheet.deleteColumn(columnPosition), o método remove colunas da planilha atualmente ativa (aberta) dentro da planilha; você pode obter o mesmo resultado chamando getActiveSheet()then . A versão simplesmente economiza algum tempo.Sheet.deleteColumn(columnPosition)Spreadsheet

 

 

Alterar o nome da folha

Renomear planilhas é tão simples quanto renomear a planilha em renameSpreadsheet(). Apenas uma única chamada de método é necessária.

  1. No Planilhas Google, clique na Sheets_Originalpágina para ativá-la.
  2. Ajuste sua duplicateAndOrganizeActiveSheet()função 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, você pode ver outra planilha duplicada criada e renomeada ao executar a função agora:

 

 

No código adicionado, o setName(name) método altera o nome de duplicateSheet, usando getSheetID() para obter o número de identificação exclusivo da folha. O + operador concatena o ID da folha ao final de "Sheet_".

 

Ajustando as colunas e linhas de uma planilha

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

  1. No Planilhas Google, clique na Sheets_Original página para ativá-la.
  2. Ajuste sua duplicateAndOrganizeActiveSheet() função 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, você pode ver uma nova planilha criada, renomeada, ativada e formatada:

 

 

O código que você acabou de adicionar usa autoResizeColumns(startColumn, numColumns)para redimensionar as colunas da planilha para facilitar a leitura. O setFrozenRows(rows)método congela um determinado número de linhas (duas, neste caso), o que faz com que as linhas permaneçam visíveis enquanto o leitor rola a planilha para baixo.

 

Reorganizando dados com a classe Range

A Range classe e seus métodos fornecem a maior parte da manipulação e formatação de dados no serviço de planilha .

Esta seção apresenta a manipulação de dados básicos com intervalos. Esses exercícios se concentram mais em como utilizar intervalos no Apps Script, enquanto os codelabs nesta lista de reprodução se aprofundam na manipulação e formatação de dados.

Alcances móveis

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

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

  1. No Planilhas Google, clique na Sheets_Original página para ativá-la.
  2. Ajuste sua duplicateAndOrganizeActiveSheet() função 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 esta função, você pode ver outra folha duplicada criada, ativada e formatada. Desta vez, porém, o conteúdo da coluna F é movido para a coluna C:

 

O novo código usa o método getRange(a1Notation) para identificar qual intervalo de dados você deseja mover. 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 getRange(a1Notation) método retornará sua Range instância. O código armazena a instância na myRange variável para facilidade de uso.

Uma vez que o intervalo é identificado, o moveTo(target) método pega o conteúdo de myRange (os valores e a formatação) e os move. O destino (coluna C) é especificado pela notação A1 “C2”. Esta é uma única célula, em vez de uma coluna. Ao mover dados, você não precisa corresponder exatamente os tamanhos aos intervalos de destino e destino; O Apps Script simplesmente alinhará a primeira célula de cada um.

Intervalos de classificação

A Range classe permite que você leia, atualize e organize grupos de células. Por exemplo, você pode classificar um intervalo de dados usando o Range.sort(sortSpecObj) método:

  1. No Planilhas Google, clique na Sheets_Original página para ativá-la.
  2. Ajuste sua duplicateAndOrganizeActiveSheet() função 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 de nossa formatação anterior, classificará todos os dados da tabela usando as informações de preço na coluna C:

    O novo código usa getRange(a1Notation) para especificar um novo intervalo abrangendo A3: D55 (ou seja, toda a tabela excluindo os cabeçalhos das colunas). O código então chama o sort(sortSpecObj) método para classificar a tabela. Aqui, o sortSpecObj parâmetro é apenas um número de coluna para classificar; o método classifica o intervalo de forma que os valores da coluna indicados vão do menor ao maior (valores crescentes). O sort(sortSpecObj) método é capaz de atender a requisitos de classificação mais complexos, mas você não precisa deles aqui; você pode ver todas as diferentes maneiras de chamar intervalos de classificação na documentação de referência do método .

 

Nesse tutorial nós vimos: