連結試算表是一項 Google 試算表功能,可讓您直接在試算表中分析 BigQuery 資料。你可以使用試算表服務,透過程式輔助方式存取連結試算表。
常見的連結試算表操作
使用 DataSource
類別和物件連線至 BigQuery 並分析資料。下表列出最常見的 DataSource
動作,以及如何在 Apps Script 中建立這些動作:
動作 | Google Apps Script 類別 | 使用方法 |
---|---|---|
將試算表連結至 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 Script 程式碼中加入 enableBigQueryExecution()
方法。這個方法會將必要的 bigquery.readonly
OAuth 範圍新增至 Google Apps Script 專案。
以下範例顯示在函式中呼叫的 SpreadsheetApp.enableBigQueryExecution()
方法:
function addDataSource() { SpreadsheetApp.enableBigQueryExecution(); var spreadsheet = SpreadsheetApp.getActive(); }
在資訊清單檔案中新增其他 OAuth 範圍
系統會根據程式碼中使用的函式,將大多數的 OAuth 範圍自動新增至資訊清單檔案。如果您需要其他範圍來存取特定 BigQuery 資料,可以設定明確範圍。
舉例來說,如要查詢在 Google 雲端硬碟內代管的 BigQuery 資料,您必須在資訊清單檔案中新增雲端硬碟 OAuth 範圍。
以下範例顯示資訊清單檔案的 oauthScopes
部分。除了所需的 spreadsheet
和 bigquery.readonly
OAuth 範圍下限之外,這個 API 還會新增雲端硬碟 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 Script 編輯器中建立事件觸發條件。如要瞭解如何建立事件觸發條件,請參閱「可安裝觸發條件」一文。
請為觸發條件選取下列選項:
- 事件來源:使用試算表
- 事件類型:編輯時
- 要執行的函式:
refreshOnParameterEdit
觸發條件建立完成後,每次編輯參數儲存格時,資料來源工作表就會自動重新整理。
疑難排解
錯誤訊息 | 解析度 |
---|---|
使用 enableBigQuery() 即可為 BIGQUERY 資料來源啟用資料執行功能。 |
這項錯誤表示系統不會在擷取 BigQuery 資料前呼叫 SpreadsheetApp.enableBigQueryExecution() 。請在使用 BigQuery 執行方法的函式中呼叫 SpreadsheetApp.enableBigQueryExecution() 。例如, refreshData() 資料來源物件、Spreadsheet.insertDataSourceTable() 和 DataSource.updateSpec() 。這些方法需要額外的 bigquery.readonly OAuth 範圍才能運作。 |
無權對資料來源執行操作。 請與管理員聯絡,要求對方啟用這項功能。 |
這個錯誤代表該帳戶未啟用連結試算表。 只有訂閱特定訂閱的使用者才能使用連結試算表功能。 Google Workspace 請與管理員聯絡,要求對方啟用這項功能。 |