关联工作表

借助关联工作表，您可以直接在 Google 表格中分析 PB 级的数据。您可以将电子表格与 BigQuery 数据仓库相关联，然后使用熟悉的表格工具（如数据透视表、图表和公式）进行分析。

本指南使用 Shakespeare 公共数据集来展示如何使用关联工作表。该数据集包含以下信息：

字段	类型	说明
字词	STRING	从语料库中提取的单个唯一单词（其中空格为分隔符）。
word_count	INTEGER	该字词在此语料库中出现的次数。
corpus	STRING	从中提取此字词的作品。
corpus_date	INTEGER	此语料库的发布年份。

如果您的应用请求任何“关联工作表”数据，除了常规 Google Tables API 请求所需的其他范围之外，还必须提供一个 OAuth 2.0 令牌，用于授予 bigquery.readonly 范围。如需了解详情，请参阅选择 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 表格编辑器中称为“提取”，可将数据源中的数据的静态转储导入 Google 表格。与数据透视表类似，该表也可指定并锚定到左上角的单元格。

以下代码示例展示了如何使用 spreadsheets.batchUpdate 方法和 UpdateCellsRequest 创建一个不超过 1,000 行、两列（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 分钟。

相关主题

• 选择 Google 表格 API 范围
• 开始在 Google 表格中处理 BigQuery 数据
• BigQuery 文档
• BigQuery：使用关联工作表
• “关联工作表”视频教程