使用关联工作表

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

常见的关联工作表操作

使用 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 表示法引用的网格工作表中的常规数据不同，来自数据源的数据通常通过列名称引用。因此，数据源对象上的大多数属性设置器都使用列名称作为输入。

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

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

使用触发器和事件自动执行关联的 Google 表格数据源函数。例如，使用时间驱动的触发器在特定时间反复刷新数据源对象，并使用电子表格事件触发器触发对预定义事件的数据执行。

以下示例会添加包含查询参数的 BigQuery 数据源，并在修改查询参数时刷新数据源工作表。

将以下 <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 订阅了特定服务的用户。 请与您的管理员联系以启用此功能。