本頁面由 Cloud Translation API 翻譯而成。

使用連結試算表

連結試算表是 Google 試算表的一項功能，可讓您直接在試算表中分析 BigQuery 和 Looker 資料。您可以透過程式輔助方式使用試算表服務存取連結試算表。

常見的連結試算表動作

使用 DataSource 類別和物件連結至 BigQuery 或 Looker，並分析資料。下表列出最常見的 DataSource 動作，以及如何在 Apps Script 中建立這些動作：

動作	Google Apps Script 類別	使用方法
將工作表連結至支援的資料來源	`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 Script 程式碼中加入 enableBigQueryExecution() 方法。這個方法會將必要的 bigquery.readonly OAuth 範圍新增至 Google Apps Script 專案。

以下範例顯示在函式中呼叫的 SpreadsheetApp.enableBigQueryExecution() 方法：

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

如要存取 Looker 資料，您必須在 Google Apps Script 程式碼中加入 enableLookerExecution() 方法。在 Apps Script 中存取 Looker 時，系統會重複使用您與 Looker 建立的 Google 帳戶連結。

以下範例顯示在函式中呼叫的 SpreadsheetApp.enableLookerExecution() 方法：

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

在資訊清單檔案中新增其他 OAuth 範圍

連結至 BigQuery 時，系統會根據程式碼中使用的函式，自動將大部分 OAuth 範圍新增至資訊清單檔案。如果需要額外的範圍來存取特定 BigQuery 資料，可以設定明確的範圍。

舉例來說，如果要查詢 Google 雲端硬碟中託管的 BigQuery 資料，您必須在資訊清單檔案中新增 Google 雲端硬碟 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 擷取最新資料。

重新整理資料的程序是非同步的。如要重新整理資料來源物件，請使用下列方法：

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

搭配使用連結試算表和觸發條件

使用觸發條件和事件，自動執行連結的 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 Script 編輯器中建立事件觸發事件。如要瞭解如何建立事件觸發條件，請參閱「可安裝的觸發條件」一文。

選取觸發條件的下列選項：

事件來源：來自試算表
事件類型：編輯文件時
要執行的函式：refreshOnParameterEdit

建立觸發條件後，每次編輯參數儲存格時，資料來源工作表就會自動重新整理。

疑難排解

錯誤訊息	解析度
使用 `enableBigQuery()` 即可為 BIGQUERY 資料來源啟用資料執行程序。	這項錯誤表示在擷取 BigQuery 資料之前，未呼叫 `SpreadsheetApp.enableBigQueryExecution()`。請在使用 BigQuery 執行方法的函式中呼叫 `SpreadsheetApp.enableBigQueryExecution()`。例如資料來源物件的 `refreshData()`、`Spreadsheet.insertDataSourceTable()` 和 `DataSource.updateSpec()`。這些方法需要額外的 bigquery.readonly OAuth 範圍才能運作。
沒有相關權限，無法對資料來源進行操作。如要啟用這項功能，請與管理員聯絡。	這個錯誤表示帳戶未啟用連結試算表。只有訂閱特定方案的使用者才能使用連結試算表。如要啟用這項功能，請與管理員聯絡。