此页面由 Cloud Translation API 翻译。

使用关联工作表

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

关联工作表的常见操作

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

操作	Google Apps 脚本类	使用方法
将工作表连接到支持的数据源	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
选择数据源	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
添加数据源工作表	`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();
  }

如需访问 Looker 数据，您必须在 Google Apps 脚本代码中添加 enableLookerExecution() 方法。在 Apps Script 中访问 Looker 将会重复使用您现有的 Google 账号与 Looker 之间的关联。

以下示例展示了在函数内调用的 SpreadsheetApp.enableLookerExecution() 方法：

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

向清单文件添加其他 OAuth 范围

与 BigQuery 建立连接时，系统会根据代码中使用的函数自动将大多数 OAuth 范围添加到清单文件中。如果您需要其他范围才能访问某些 BigQuery 数据，可以设置明确的范围。

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

以下示例展示了清单文件的 oauthScopes 部分。除了最低要求的 spreadsheet 和 bigquery.readonly OAuth 范围之外，它还添加了云端硬盘 OAuth 范围：

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

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

以下示例展示了如何添加数据源、从数据源创建数据源对象、刷新数据源对象以及获取执行状态。

添加数据源

以下示例分别展示了如何添加 BigQuery 数据源和 Looker 数据源。

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

Looker

如需向电子表格添加 Looker 数据源，请插入包含数据源规范的数据源工作表。系统会自动刷新数据源工作表以提取预览数据。

将以下示例中的 <INSTANCE_URL>、<MODEL_NAME>、<EXPLORE_NAME> 分别替换为有效的 Looker 实例网址、模型名称和探索名称。

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

添加数据源对象

将数据源添加到电子表格后，即可根据该数据源创建数据源对象。在此示例中，我们使用 DataSourcePivotTable 在添加 BigQuery 数据源的代码示例中创建的 BigQuery dataSource 上创建了一个透视表。

与通过单元格索引或 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 中提取最新数据。

刷新数据的过程是异步的。如需刷新数据源对象，请使用以下方法：

refreshData() 开始执行数据刷新。
waitForCompletion() 在数据执行完成后返回最终状态。这样就不需要不断轮询执行状态。
如果数据执行失败，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());
}

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

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

以下示例添加了一个带有查询参数的 BigQuery 数据源，并在修改查询参数时刷新数据源工作表。