使用关联工作表

关联工作表 是一项 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 数据，您必须添加 enableBigQueryExecution() 方法 。此方法会将所需的 bigquery.readonly 将 OAuth 范围应用于您的 Google Apps 脚本项目。

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

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

如需访问 Looker 数据，您必须在enableLookerExecution() 您的 Google Apps 脚本代码。在 Apps 脚本中访问 Looker 将重复使用您现有的 Google 账号关联 Looker。

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

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

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

与 BigQuery 连接时，系统会自动将大多数 OAuth 范围添加到 创建清单文件如果您需要更多 访问特定 BigQuery 数据的权限， 设置显式范围。

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

以下示例展示了清单文件的 oauthScopes 部分。它会将 云端硬盘 OAuth 范围以及所需的最低 spreadsheet 和 bigquery.readonly 个 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();

添加数据源对象

将数据源添加到电子表格后，即可创建数据源对象 根据数据源创建的数据在本例中，数据透视表是使用 针对在以下创建日期中创建的 BigQuery dataSource 的DataSourcePivotTable权限： 添加 BigQuery 数据源的代码示例。

与通过单元格索引或 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 特定订阅的用户使用。 请与您的管理员联系以启用此功能。