Usar páginas conectadas

As Páginas Conectadas são um recurso do Google Planilhas que permite analisar dados do BigQuery e do Looker diretamente nas Planilhas. Acesse as páginas conectadas de forma programática com o serviço Planilhas.

Ações comuns das Páginas Conectadas

Use as classes e os objetos DataSource para se conectar ao BigQuery ou ao Looker e analisar dados. A tabela a seguir lista as ações DataSource mais comuns e como criá-las no Google Apps Script:

Ação	Classe do Apps Script	Método a ser usado
Conectar uma planilha a uma fonte de dados compatível	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Escolha uma fonte de dados	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Adicionar uma página de fonte de dados	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Adicionar uma tabela dinâmica	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Extrair dados	`DataSourceTable`	`Range.insertDataSourceTable()`
Usar uma fórmula	`DataSourceFormula`	`Range.setFormula()`
Adicionar um gráfico	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Adicionar os escopos de autorização necessários

Para acessar os dados do BigQuery, inclua o método enableBigQueryExecution() no código do Apps Script. Esse método adiciona o escopo do OAuth bigquery.readonly necessário ao seu projeto do Apps Script.

O exemplo a seguir mostra o método SpreadsheetApp.enableBigQueryExecution() chamado em uma função:

function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();
  }

Para acessar os dados do Looker, inclua o método enableLookerExecution() no código do Apps Script. O acesso ao Looker no Apps Script reutiliza sua vinculação da Conta do Google com o Looker.

O exemplo a seguir mostra o método SpreadsheetApp.enableLookerExecution() chamado em uma função:

function addDataSource() {
  SpreadsheetApp.enableLookerExecution();
  var spreadsheet = SpreadsheetApp.getActive();
  }

Adicionar outros escopos do OAuth ao arquivo de manifesto

Ao se conectar com o BigQuery, a maioria dos escopos do OAuth é adicionada automaticamente ao arquivo de manifesto com base nas funções usadas no seu código. Se você precisar de mais escopos para acessar determinados dados do BigQuery, defina escopos explícitos.

Por exemplo, para consultar dados do BigQuery hospedados no Google Drive, adicione um escopo do OAuth do Drive ao arquivo de manifesto.

O exemplo a seguir mostra a parte oauthScopes de um arquivo de manifesto. Ele adiciona um escopo do OAuth do Drive além dos escopos mínimos obrigatórios spreadsheet e bigquery.readonly do OAuth:

{ ...
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery.readonly",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive" ],
... }

Exemplo: criar e atualizar um objeto de fonte de dados

Os exemplos a seguir mostram como adicionar uma fonte de dados, criar um objeto de fonte de dados com base nela, atualizar o objeto e receber o status de execução.

Adicionar uma fonte de dados

Os exemplos a seguir mostram como adicionar uma fonte de dados do BigQuery e do Looker, respectivamente.

BigQuery

Para adicionar uma fonte de dados do BigQuery a uma planilha, insira uma página de fonte de dados com uma especificação de fonte de dados. A página é atualizada automaticamente para buscar dados de prévia.

Substitua <YOUR_PROJECT_ID> abaixo por um ID de projeto do Google Cloud válido.

// For operations that fetch data from BigQuery, enableBigQueryExecution() must be called.
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());

// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
    .asBigQuery()
    .setProjectId('<YOUR_PROJECT_ID>')
    .setTableProjectId('bigquery-public-data')
    .setDatasetId('ncaa_basketball')
    .setTableId('mbb_historical_tournament_games')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

Looker

Para adicionar uma fonte de dados do Looker a uma planilha, insira uma página de fonte de dados com uma especificação de fonte de dados. Essa página é atualizada automaticamente para buscar dados de prévia.

Substitua <INSTANCE_URL>,<MODEL_NAME> e <EXPLORE_NAME> no exemplo a seguir por um URL de instância do Looker, um nome de modelo e um nome de análise válidos, respectivamente.

// For operations that fetch data from Looker, enableLookerExecution() must be called.
SpreadsheetApp.enableLookerExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());

// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
    .asLooker()
    .setInstanceUrl('<INSTANCE_URL>')
    .setModelName('<MODEL_NAME>')
    .setExploreName('<EXPLORE_NAME>')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

Adicionar um objeto de fonte de dados

Depois que a fonte de dados é adicionada à planilha, é possível criar objetos de fonte de dados com base nela. Neste exemplo, uma tabela dinâmica é criada usando DataSourcePivotTable no dataSource do BigQuery criado em o exemplo de código que adiciona uma fonte de dados do BigQuery.

Ao contrário dos dados comuns em planilhas de grade, que são referenciados por índice de célula ou notações A1, os dados de fontes de dados geralmente são referenciados por nomes de colunas. Portanto, a maioria dos setters de propriedades em objetos de fonte de dados usa o nome da coluna como entrada.

var rootCell = spreadsheet.insertSheet('pivotTableSheet').getRange('A1');

// Add data source pivot table and set data source specific configurations.
var dataSourcePivotTable = rootCell.createDataSourcePivotTable(dataSource);
var rowGroup = dataSourcePivotTable.addRowGroup('season');
rowGroup.sortDescending().setGroupLimit(5);
dataSourcePivotTable.addColumnGroup('win_school_ncaa');
dataSourcePivotTable.addPivotValue('win_pts',
SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
dataSourcePivotTable.addPivotValue('game_date',
SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
var filterCriteria = SpreadsheetApp.newFilterCriteria()
    .whenTextEqualToAny(['Duke', 'North Carolina'])
    .build();
dataSourcePivotTable.addFilter('win_school_ncaa', filterCriteria);

// Get a regular pivot table instance and set shared configurations.
var pivotTable = dataSourcePivotTable.asPivotTable();
pivotTable.setValuesDisplayOrientation(SpreadsheetApp.Dimension.ROWS);

Atualizar um objeto da fonte de dados

Atualize os objetos da fonte de dados para buscar os dados mais recentes do BigQuery com base nas especificações da fonte de dados e nas configurações de objetos.

O processo de atualização de dados é assíncrono. Para atualizar um objeto de fonte de dados, use os seguintes métodos:

refreshData() inicia a execução da atualização de dados.
waitForCompletion() retorna o estado final quando a execução de dados é concluída. Isso elimina a necessidade de continuar pesquisando o status da execução.
DataExecutionStatus.getErrorCode() recebe o código de erro caso a execução de dados falhe.

O exemplo a seguir ilustra uma atualização dos dados da tabela dinâmica:

var status = dataSourcePivotTable.getStatus();
Logger.log('Initial state: %s', status.getExecutionState());

dataSourcePivotTable.refreshData();

status = dataSourcePivotTable.waitForCompletion(/* timeoutInSeconds= */ 60);
Logger.log('Ending state: %s', status.getExecutionState());
if (status.getExecutionState() == SpreadsheetApp.DataExecutionState.ERROR) {
  Logger.log('Error: %s (%s)', status.getErrorCode(),
  status.getErrorMessage());
}

Usar gatilhos com as páginas conectadas

Automatize as funções da fonte de dados das Planilhas Google conectadas com gatilhos e eventos. Por exemplo, use gatilhos baseados em tempo para atualizar objetos de fonte de dados repetidamente em um horário específico e gatilhos de eventos de planilha para acionar a execução de dados em um evento predefinido.

O exemplo a seguir adiciona uma fonte de dados do BigQuery com um parâmetro de consulta e atualiza a página da fonte de dados quando o parâmetro de consulta é editado.

Substitua <YOUR_PROJECT_ID> por um ID de projeto do Google Cloud válido.

// Add data source with query parameter.
function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();

  // Add a new sheet and use A1 cell as the parameter cell.
  var parameterCell = spreadsheet.insertSheet('parameterSheet').getRange('A1');
  parameterCell.setValue('Duke');

  // Add data source with query parameter.
  var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
      .asBigQuery()
      .setProjectId('<YOUR_PROJECT_ID>')
      .setRawQuery('select * from `bigquery-public-data`.`ncaa_basketball`.`mbb_historical_tournament_games` WHERE win_school_ncaa = @SCHOOL')
      .setParameterFromCell('SCHOOL', 'parameterSheet!A1')
      .build();
  var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
  dataSourceSheet.asSheet().setName('ncaa_data');
}

// Function used to configure event trigger to refresh data source sheet.
function refreshOnParameterEdit(e) {
  var editedRange = e.range;
  if (editedRange.getSheet().getName() != 'parameterSheet') {
    return;
  }
  // Check that the edited range includes A1.
  if (editedRange.getRow() > 1 || editedRange.getColumn() > 1) {
     return;
  }

  var spreadsheet = e.source;
  SpreadsheetApp.enableBigQueryExecution();
  spreadsheet.getSheetByName('ncaa_data').asDataSourceSheet().refreshData();
}

No exemplo anterior, a função addDataSource() adiciona uma fonte de dados à planilha. Depois de executar addDataSource(), crie um gatilho de evento no editor do Apps Script. Para saber como criar um gatilho de evento, consulte Gatilhos instaláveis.

Selecione as seguintes opções para o acionador:

Origem do evento: De planilha
Tipo de evento: Ao editar
Função a ser executada: refreshOnParameterEdit

Depois que o gatilho é criado, a página da fonte de dados é atualizada automaticamente sempre que a célula de parâmetro é editada.

Resolver problemas

Mensagem de erro Resolução

Use enableBigQuery() para ativar execuções de dados para fontes de dados do BIGQUERY. Esse erro indica que SpreadsheetApp.enableBigQueryExecution() não foi chamado antes de buscar os dados do BigQuery.
Chame SpreadsheetApp.enableBigQueryExecution() em funções que usam métodos para execução do BigQuery.
Como refreshData() em objetos de fonte de dados, Spreadsheet.insertDataSourceTable() e DataSource.updateSpec().
Esses métodos exigem um escopo OAuth bigquery.readonly adicional para funcionar.

Não é permitido realizar ações nas fontes de dados.
Entre em contato com o administrador para ativar o recurso. Esse erro indica que a conta não tem as Planilhas Google Conectadas ativadas.
O Google Planilhas Conectadas está disponível apenas para usuários do Google Workspace com determinadas assinaturas.
Entre em contato com o administrador para ativar o recurso.

Mensagem de erro	Resolução
Use `enableBigQuery()` para ativar execuções de dados para fontes de dados do BIGQUERY.	Esse erro indica que `SpreadsheetApp.enableBigQueryExecution()` não foi chamado antes de buscar os dados do BigQuery. Chame `SpreadsheetApp.enableBigQueryExecution()` em funções que usam métodos para execução do BigQuery. Como `refreshData()` em objetos de fonte de dados, `Spreadsheet.insertDataSourceTable()` e `DataSource.updateSpec()`. Esses métodos exigem um escopo OAuth bigquery.readonly adicional para funcionar.
Não é permitido realizar ações nas fontes de dados. Entre em contato com o administrador para ativar o recurso.	Esse erro indica que a conta não tem as Planilhas Google Conectadas ativadas. O Google Planilhas Conectadas está disponível apenas para usuários do Google Workspace com determinadas assinaturas. Entre em contato com o administrador para ativar o recurso.

Usar páginas conectadas Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.