連結試算表

連結試算表可讓您在 Google 試算表中直接分析 PB 規模的資料。您可以將試算表與 BigQuery 資料倉儲連結，並使用您熟悉的試算表工具 (例如資料透視表、圖表與公式) 進行分析。

本指南會使用 Shakespeare 公開資料集，說明如何使用連結試算表。該資料集包含下列資訊：

欄位	類型	說明
文字	STRING	從語料庫中擷取的單一不重複字詞 (空格為分隔符號)。
word_count	INTEGER	這個字出現在此語料庫內的次數。
corpus	STRING	擷取這個字詞的作品。
corpus_date	INTEGER	此語料庫的發布日期。

如果您的應用程式要求任何連結試算表資料，除了一般 GoogleSheet API 要求所需的其他範圍外，也必須提供已授予 bigquery.readonly 範圍的 OAuth 2.0 權杖。詳情請參閱「選擇 Google 試算表 API 範圍」。

管理資料來源

資料來源可指定外部找到資料的位置。然後資料來源就會連結至試算表。

新增 BigQuery 資料來源

如要新增資料來源，請使用 spreadsheets.batchUpdate 方法提供 AddDataSourceRequest。要求主體應指定 DataSource 物件類型的 dataSource 欄位。

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"PROJECT_ID",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

將 PROJECT_ID 替換為有效的 Google Cloud 專案 ID。

建立資料來源後，系統會建立相關聯的 DATA_SOURCE 工作表，讓您預覽最多 500 個資料列。無法立即提供預覽。系統會非同步觸發執行作業，以便匯入 BigQuery 資料。

AddDataSourceResponse 包含下列欄位：

• dataSource：建立的 DataSource 物件。dataSourceId 是試算表範圍的專屬 ID。系統會填入和參照資料，從資料來源建立每個 DataSource 物件。

• dataExecutionStatus：將 BigQuery 資料匯入預覽樣張的執行作業狀態。詳情請參閱「資料執行狀態」一節。

更新或刪除資料來源

請使用 spreadsheets.batchUpdate 方法，並據此提供 UpdateDataSourceRequest 或 DeleteDataSourceRequest 要求。

管理資料來源物件

將資料來源新增至試算表後，即可從中建立資料來源物件。資料來源物件是一般試算表工具，例如與連結試算表整合的資料透視表、圖表和公式，可執行資料分析作業。

物件分為四種類型：

• DataSource 個資料表
• DataSource pivotTable
• 「DataSource」圖表
• DataSource 公式

新增資料來源表格

資料表物件在試算表編輯器中稱為「擷取」，會將資料來源的靜態傾印資料匯入 Google 試算表。與資料透視表類似，指定的表格會固定在左上方的儲存格上。

以下程式碼範例說明如何使用 spreadsheets.batchUpdate 方法和 UpdateCellsRequest，建立最多 1000 個資料列的資料來源資料表 (word 和 word_count)。

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

將 DATA_SOURCE_ID 替換成可識別資料來源的試算表範圍專屬 ID。

資料來源資料表建立後，系統不會立即提供資料。在試算表編輯器中，則會顯示為預覽畫面。您必須重新整理資料來源資料表，才能擷取 BigQuery 資料。您可以在同一個 batchUpdate 中指定 RefreshDataSourceRequest。請注意，所有資料來源物件的運作方式都類似。 詳情請參閱「重新整理資料來源物件」。

重新整理完成且 BigQuery 資料擷取完成後，資料來源資料表會填入下列值：

新增資料來源資料透視表

與傳統資料透視表不同，資料來源的資料透視表是由資料來源支援，並依據資料欄名稱參照資料。以下程式碼範例說明如何使用 spreadsheets.batchUpdate 方法和 UpdateCellsRequest 建立資料透視表，以顯示語料庫的總字數。

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
    }

將 DATA_SOURCE_ID 替換成可識別資料來源的試算表範圍專屬 ID。

擷取 BigQuery 資料後，資料來源資料透視表會填入如下：

新增資料來源圖表

以下程式碼範例說明如何使用 spreadsheets.batchUpdate 方法和 AddChartRequest 建立含有 chartType 為 COLUMN 的資料來源圖表，並按語料庫顯示總字數。

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"DATA_SOURCE_ID"
      }
   }
}

將 DATA_SOURCE_ID 替換成可識別資料來源的試算表範圍專屬 ID。

擷取 BigQuery 資料後，資料來源圖表會呈現如下：

新增資料來源公式

以下程式碼範例說明如何使用 spreadsheets.batchUpdate 方法和 UpdateCellsRequest 建立資料來源公式，計算平均字數。

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

擷取 BigQuery 資料後，系統會填入資料來源公式，如下所示：

重新整理資料來源物件

您可以重新整理資料來源物件，以根據目前的資料來源規格和物件設定，從 BigQuery 擷取最新資料。您可以使用 spreadsheets.batchUpdate 方法呼叫 RefreshDataSourceRequest。接著，請使用 DataSourceObjectReferences 物件，指定要重新整理的一或多個物件參照。

請注意，您可以在單一 batchUpdate 要求內建立及重新整理資料來源物件。

資料執行狀態

建立資料來源或重新整理資料來源物件時，系統會建立背景執行，以便從 BigQuery 擷取資料，並傳回含有 DataExecutionStatus 的回應。如果執行成功，DataExecutionState 通常會處於 RUNNING 狀態。

由於程序為非同步性質，因此應用程式應實作輪詢模型，以定期擷取資料來源物件的狀態。使用 spreadsheets.get 方法，直到狀態傳回 SUCCEEDED 或 FAILED 狀態為止。在大部分情況下，執行作業很快就能完成，但仍須視資料來源的複雜度而定。執行作業通常不會超過 10 分鐘。

相關主題

• 選擇 GoogleSheet API 範圍
• 開始在 Google 試算表中使用 BigQuery 資料
• BigQuery 說明文件
• BigQuery：使用連結試算表
• 連結試算表教學影片