Usa hojas conectadas

Hojas conectadas es una función de Hojas de cálculo de Google que te permite analizar datos de BigQuery directamente en Hojas de cálculo. Puede acceder a Hojas conectadas de manera programática con el servicio de Hojas de cálculo.

Acciones comunes de Hojas conectadas

Usa las clases y los objetos DataSource para conectarte a BigQuery y analizar datos. En la siguiente tabla, se enumeran las acciones de DataSource más comunes y cómo crearlas en Apps Script:

Acción Clase de Google Apps Script Método de uso
Conectar una hoja de cálculo a BigQuery DataSourceSpec SpreadsheetApp.newDataSourceSpec()
Elige una fuente de datos de BigQuery DataSource Spreadsheet.insertDataSourceSheet().getDataSource()
Agregar una hoja de fuente de datos de BigQuery DataSourceSheet Spreadsheet.insertDataSourceSheet()
Cómo agregar una tabla dinámica DataSourcePivotTable Range.insertDataSourcePivotTable()
Extrae datos en un extracto DataSourceTable Range.insertDataSourceTable()
Cómo usar una fórmula DataSourceFormula Range.setFormula()
Add a chart DataSourceChart Sheet.insertDataSourceChart()

Agrega los permisos de autorización obligatorios

Para acceder a los datos de BigQuery, debes incluir el método enableBigQueryExecution() en tu código de Google Apps Script. Con este método, se agrega el permiso de OAuth bigquery.readonly requerido a tu proyecto de Google Apps Script.

En el siguiente ejemplo, se muestra el método SpreadsheetApp.enableBigQueryExecution() al que se llama dentro de una función:

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

Agrega permisos de OAuth adicionales al archivo de manifiesto

La mayoría de los permisos de OAuth se agregan automáticamente al archivo de manifiesto según las funciones que se usan en tu código. Si necesitas permisos adicionales para acceder a ciertos datos de BigQuery, puedes configurar permisos explícitos.

Por ejemplo, para consultar datos de BigQuery alojados en Google Drive, debes agregar un alcance de OAuth de Drive a tu archivo de manifiesto.

En el siguiente ejemplo, se muestra la parte oauthScopes de un archivo de manifiesto. Agrega un permiso de OAuth de Drive, además de los permisos mínimos de OAuth spreadsheet y bigquery.readonly requeridos:

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

Ejemplo: Crea y actualiza un objeto de fuente de datos

En el siguiente ejemplo, se muestra cómo agregar una fuente de datos de BigQuery, crear un objeto de fuente de datos a partir de la fuente de datos, actualizar el objeto de fuente de datos y obtener el estado de ejecución. En este ejemplo, los fragmentos de código se ejecutan en secuencia.

Agregar una fuente de datos de BigQuery

Para agregar una fuente de datos de BigQuery a una hoja de cálculo, inserta una hoja de fuente de datos con una especificación de fuente de datos. La hoja de fuente de datos se actualiza automáticamente para recuperar datos de vista previa.

Reemplaza <YOUR_PROJECT_ID> a continuación por un ID del proyecto de 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();

Agregar un objeto de fuente de datos

Una vez que se agrega la fuente de datos a la hoja de cálculo, se pueden crear objetos de fuente de datos a partir de la fuente de datos. En este ejemplo, se crea una tabla dinámica con DataSourcePivotTable.

A diferencia de los datos normales en las hojas de cuadrícula a las que se hace referencia mediante el índice de celda o las notaciones A1, por lo general, se hace referencia a los datos de las fuentes de datos mediante nombres de columna. Por lo tanto, la mayoría de los métodos set de propiedades en objetos de fuente de datos usan el nombre de columna 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);

Cómo actualizar un objeto de fuente de datos

Puedes actualizar los objetos de fuente de datos para recuperar los datos más recientes de BigQuery según las especificaciones de la fuente de datos y la configuración de los objetos.

El proceso para actualizar datos es asíncrono. Para actualizar un objeto de fuente de datos, usa los siguientes métodos:

  1. refreshData() inicia la ejecución de la actualización de datos.
  2. waitForCompletion() muestra el estado final una vez que se completa la ejecución de los datos. Esto elimina la necesidad de seguir sondeando el estado de ejecución.
  3. DataExecutionStatus.getErrorCode() obtiene el código de error en caso de que falle la ejecución de los datos.

En el siguiente ejemplo se muestra una actualización de los datos de la tabla 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());
}

Usa activadores con Hojas conectadas

Automatiza las funciones de fuentes de datos de Hojas conectadas con activadores y eventos. Por ejemplo, usa activadores basados en el tiempo para actualizar los objetos de fuente de datos repetidamente en un momento específico y usa activadores de eventos de hoja de cálculo para activar la ejecución de datos en un evento predefinido.

En el siguiente ejemplo, se agrega una fuente de datos con un parámetro de consulta y se actualiza la hoja de la fuente de datos cuando se edita el parámetro de consulta.

Reemplaza <YOUR_PROJECT_ID> a continuación por un ID del proyecto de 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();
}

En el ejemplo anterior, la función addDataSource() agrega una fuente de datos a la hoja de cálculo. Después de ejecutar addDataSource(), crea un activador de eventos en el editor de Apps Script. Para aprender a crear un activador de eventos, consulta Activadores instalables.

Selecciona las siguientes opciones para tu activador:

  • Fuente del evento: Desde una hoja de cálculo
  • Tipo de evento: Al editar
  • Función que se ejecutará: refreshOnParameterEdit

Una vez que se crea el activador, la hoja de la fuente de datos se actualiza automáticamente cada vez que se edita la celda de parámetro.

Solución de problemas

Mensaje de error Resolución
Usa enableBigQuery() a fin de habilitar ejecuciones de datos para fuentes de datos de BIGQUERY. Este error indica que no se llama a SpreadsheetApp.enableBigQueryExecution() antes de recuperar datos de BigQuery.
Llama a SpreadsheetApp.enableBigQueryExecution() en funciones que usan métodos para la ejecución de BigQuery.
Por ejemplo, refreshData() en los objetos de fuente de datos, Spreadsheet.insertDataSourceTable() y DataSource.updateSpec().
Estos métodos requieren un alcance de OAuth bigquery.readonly adicional para funcionar.
No tiene permiso para realizar acciones en fuentes de datos.
Comunícate con tu administrador para habilitar la función.		 Este error indica que la cuenta no tiene habilitadas Hojas conectadas.
Hojas conectadas solo está disponible para Google Workspace los usuarios que tienen determinadas suscripciones.
Comunícate con el administrador para habilitar la función.