使用关联工作表

关联工作表是 Google 表格的一项功能,可让您直接在 Google 表格中分析 BigQuery 数据。您可以通过电子表格服务以编程方式访问关联工作表。

“关联工作表”的常见操作

使用 DataSource 类和对象连接到 BigQuery 并分析数据。下表列出了最常见的 DataSource 操作以及如何在 Apps 脚本中创建这些操作:

操作 Google Apps 脚本类 要使用的方法
将工作表连接到 BigQuery DataSourceSpec SpreadsheetApp.newDataSourceSpec()
选择 BigQuery 数据源 DataSource Spreadsheet.insertDataSourceSheet().getDataSource()
添加 BigQuery 数据源表格 DataSourceSheet Spreadsheet.insertDataSourceSheet()
添加数据透视表 DataSourcePivotTable Range.insertDataSourcePivotTable()
将数据提取到提取内容 DataSourceTable Range.insertDataSourceTable()
使用公式 DataSourceFormula Range.setFormula()
添加图表 DataSourceChart Sheet.insertDataSourceChart()

添加所需的授权范围

如需访问 BigQuery 数据,您必须在 Google Apps 脚本代码中添加 enableBigQueryExecution() 方法。此方法会将所需的 bigquery.readonly OAuth 范围添加到您的 Google Apps 脚本项目。

以下示例展示了在函数中调用的 SpreadsheetApp.enableBigQueryExecution() 方法:

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

在清单文件中添加额外的 OAuth 范围

系统会根据代码中使用的函数将大多数 OAuth 范围自动添加到清单文件中。如果您需要通过额外的范围来访问某些 BigQuery 数据,可以设置显式范围

例如,如需查询托管在 Google 云端硬盘中的 BigQuery 数据,您必须向清单文件添加云端硬盘 OAuth 范围。

以下示例展示了清单文件的 oauthScopes 部分。除了所需的最小 spreadsheetbigquery.readonly OAuth 范围外,它还添加了云端硬盘 OAuth 范围:

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

示例:创建和刷新数据源对象

以下示例展示了如何添加 BigQuery 数据源、根据数据源创建数据源对象、刷新数据源对象以及获取执行状态。在此示例中,代码段将按顺序执行。

添加 BigQuery 数据源

如需将 BigQuery 数据源添加到电子表格,请插入包含数据源规范的数据源表格。系统会自动刷新数据源表格以提取预览数据。

将下面的 <YOUR_PROJECT_ID> 替换为有效的 Google Cloud 项目 ID。

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

添加数据源对象

将数据源添加到电子表格后,您就可以通过该数据源创建数据源对象。在此示例中,数据透视表是使用 DataSourcePivotTable 创建的。

与网格工作表中通过单元格索引或 A1 表示法引用的常规数据不同,来自数据源的数据通常通过列名称引用。因此,数据源对象的大多数属性 setter 都使用列名称作为输入。

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

刷新数据源对象

您可以刷新数据源对象,以根据数据源规范和对象配置从 BigQuery 提取最新数据。

刷新数据的过程是异步进行的。如需刷新数据源对象,请使用以下方法:

  1. refreshData() 用于开始执行数据刷新。
  2. 数据执行完成后,waitForCompletion() 将返回结束状态。这样您就不需要继续轮询执行状态。
  3. 如果数据执行失败,DataExecutionStatus.getErrorCode() 会获取错误代码。

以下示例展示了如何刷新数据透视表数据:

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

将触发器与“关联工作表”搭配使用

使用触发器和事件自动执行关联工作表数据源函数。 例如,使用时间驱动型触发器在特定时间重复刷新数据源对象,并使用电子表格事件触发器针对预定义事件触发数据执行。

以下示例添加了带有查询参数的数据源,并在修改查询参数时刷新数据源表格。

将下面的 <YOUR_PROJECT_ID> 替换为有效的 Google Cloud 项目 ID。

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

在上面的示例中,addDataSource() 函数向电子表格添加了数据源。执行 addDataSource() 后,在 Apps 脚本编辑器中创建事件触发器。如需了解如何创建事件触发器,请参阅可安装的触发器

为您的触发器选择以下选项:

  • 事件来源来自电子表格
  • 活动类型编辑时
  • 要运行的函数refreshOnParameterEdit

创建触发器后,每次修改参数单元格时,数据源工作表都会自动刷新。

问题排查

错误消息 分辨率
使用 enableBigQuery() 可为 BIGQUERY 数据源启用数据执行功能。 此错误表示在提取 BigQuery 数据之前未调用 SpreadsheetApp.enableBigQueryExecution()
在使用 BigQuery 执行方法的函数中调用 SpreadsheetApp.enableBigQueryExecution()
例如,对数据源对象 Spreadsheet.insertDataSourceTable()DataSource.updateSpec()refreshData()
这些方法需要额外的 bigquery.readonly OAuth 范围才能发挥作用。
您无权对数据源执行操作。
要启用此功能,请与管理员联系。
此错误表示该账号未启用“关联工作表”。
“关联工作表”仅供拥有特定订阅的 Google Workspace 用户使用。
如需启用此功能,请与您的管理员联系。