Usar páginas conectadas

As páginas conectadas são um recurso das Planilhas Google que permite analisar dados do BigQuery diretamente no app. Você pode acessar as páginas conectadas de maneira programática com o serviço de planilhas.

Ações comuns das páginas conectadas

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

Ação Aula do Google Apps Script Método de uso
Conectar uma página ao BigQuery DataSourceSpec SpreadsheetApp.newDataSourceSpec()
Escolher uma fonte de dados do BigQuery DataSource Spreadsheet.insertDataSourceSheet().getDataSource()
Adicionar uma planilha de fonte de dados do BigQuery 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, você precisa incluir o método enableBigQueryExecution() no seu código do Google Apps Script. Esse método adiciona o escopo obrigatório do OAuth bigquery.readonly ao seu projeto do Google 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();
  }

Adicionar outros escopos de OAuth ao arquivo de manifesto

A maioria dos escopos OAuth é adicionada automaticamente ao arquivo de manifesto com base nas funções usadas no seu código. Se você precisar de outros 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 de OAuth do Drive ao arquivo de manifesto.

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

{ ...
  "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

O exemplo a seguir mostra como adicionar uma fonte de dados do BigQuery, criar um objeto da fonte de dados usando essa fonte, atualizar esse objeto e conferir o status de execução. Neste exemplo, os snippets de código são executados em sequência.

Adicionar uma fonte de dados do 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. Essa planilha é atualizada automaticamente para buscar dados de visualização.

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

// 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();

Adicionar um objeto de fonte de dados

Quando a fonte de dados é adicionada à planilha, os objetos da fonte de dados podem ser criados usando a fonte de dados. Neste exemplo, uma tabela dinâmica é criada usando DataSourcePivotTable.

Ao contrário dos dados normais em planilhas de grade que são referenciados pelo índice de células ou por notação A1, os dados das fontes de dados geralmente são referenciados por nomes de coluna. Portanto, a maioria dos setters 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

Você pode atualizar 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 do objeto.

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

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

O exemplo abaixo 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 acionadores com as páginas conectadas

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

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

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

// 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 acima, a função addDataSource() adiciona uma fonte de dados à planilha. Depois de executar addDataSource(), crie um acionador de evento no editor do Apps Script. Para saber como criar um acionador de evento, consulte Acionadores instaláveis.

Selecione as seguintes opções para o gatilho:

  • Fonte do evento: Da planilha
  • Tipo de evento: Em edição
  • Função a ser executada: refreshOnParameterEdit

Depois que o acionador é criado, a planilha da fonte de dados é atualizada automaticamente sempre que a célula do 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 é chamado antes de buscar dados do BigQuery.
Chame SpreadsheetApp.enableBigQueryExecution() em funções que usam métodos para a execução do BigQuery.
Por exemplo, 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 tem permissão para realizar ações nas fontes de dados.
Entre em contato com seu administrador para ativar o recurso.		 Esse erro indica que o recurso páginas conectadas não está ativado na conta.
As páginas conectadas só está disponível para Google Workspace usuários com determinadas assinaturas.
Entre em contato com seu administrador para ativar o recurso.