关联工作表是 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
部分。除了所需的最小 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 数据源、根据数据源创建数据源对象、刷新数据源对象以及获取执行状态。在此示例中,代码段将按顺序执行。
添加 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 提取最新数据。
刷新数据的过程是异步进行的。如需刷新数据源对象,请使用以下方法:
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());
}
将触发器与“关联工作表”搭配使用
使用触发器和事件自动执行关联工作表数据源函数。 例如,使用时间驱动型触发器在特定时间重复刷新数据源对象,并使用电子表格事件触发器针对预定义事件触发数据执行。
以下示例添加了带有查询参数的数据源,并在修改查询参数时刷新数据源表格。
将下面的 <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 用户使用。 如需启用此功能,请与您的管理员联系。 |