关联工作表

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

管理 BigQuery 数据源

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

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

如果您的应用请求任何 BigQuery 关联工作表数据，则必须提供授予 bigquery.readonly 范围的 OAuth 2.0 令牌，以及常规 Google Sheets API 请求所需的其他范围。如需了解详情，请参阅选择 Google Sheets 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 数据导入预览工作表的执行的状态。如需了解详情，请参阅数据执行状态部分。

更新或删除 BigQuery 数据源

使用 spreadsheets.batchUpdate 方法并提供 UpdateDataSourceRequest 或 DeleteDataSourceRequest 请求。

管理 BigQuery 数据源对象

将数据源添加到电子表格后，即可从中创建数据源对象。数据源对象是一种常规的 Google 表格工具，例如数据透视表、图表和公式，它与关联的表格集成在一起，可为您的数据分析提供支持。

对象分为四种类型：

  • DataSource 个表
  • DataSource pivotTable
  • DataSource图表
  • DataSource 公式

添加 BigQuery 数据源表

在 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。

创建数据源表后，数据不会立即可用。在 Google 表格编辑器中，它会显示为预览。您需要刷新数据源表才能提取 BigQuery 数据。您可以在同一 batchUpdate 内指定 RefreshDataSourceRequest。请注意，所有数据源对象的工作方式都类似。 如需了解详情，请参阅刷新数据源对象。

刷新完成后并提取 BigQuery 数据后，数据源表会填充如下所示的数据：

显示莎士比亚公开数据集中的数据的数据源表。

添加 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 数据后，数据源透视表会填充数据，如下所示：

数据源数据透视表，显示来自莎士比亚公开数据集的数据。

添加 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 数据后，数据源图表会呈现如下所示：

数据源图表，显示了莎士比亚公开数据集中的数据。

添加 BigQuery 数据源公式

以下代码示例展示了如何使用 spreadsheets.batchUpdate 方法和 UpdateCellsRequest 创建用于计算平均字数的数据源公式。

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

提取 BigQuery 数据后，系统会填充数据源公式，如下所示：

数据源公式，显示来自莎士比亚公开数据集的数据。

刷新 BigQuery 数据源对象

您可以刷新数据源对象，以根据当前数据源规范和对象配置从 BigQuery 中提取最新数据。您可以使用 spreadsheets.batchUpdate 方法调用 RefreshDataSourceRequest。然后，使用 DataSourceObjectReferences 对象指定要刷新的一个或多个对象引用。

请注意，您可以在单个 batchUpdate 请求中创建和刷新数据源对象。

管理 Looker 数据源

本指南将介绍如何添加 Looker 数据源、更新或删除该数据源、基于该数据源创建数据透视表以及刷新该数据源。

您的应用请求任何 Looker 关联表格数据时，将重复使用您现有的 Google 账号与 Looker 的关联。

添加 Looker 数据源

如需添加数据源，请使用 spreadsheets.batchUpdate 方法提供 AddDataSourceRequest。请求正文应指定类型为 DataSource 对象的 dataSource 字段。

"addDataSource":{    "dataSource":{       "spec":{          "looker":{             "instance_uri":"INSTANCE_URI",             "model":"MODEL",             "explore":"EXPLORE"          }       }    } }

将 INSTANCE_URI、MODEL 和 EXPLORE 分别替换为有效的 Looker 实例 URI、模型名称和探索名称。

创建数据源后，系统会创建一个关联的 DATA_SOURCE 工作表，用于预览所选探索的结构，包括视图、维度、度量和任何字段说明。

AddDataSourceResponse 包含以下字段：

  • dataSource：创建的 DataSource 对象。dataSourceId 是工作表范围内的唯一 ID。系统会填充并引用该对象，以根据数据源创建每个 DataSource 对象。

  • dataExecutionStatus：将 BigQuery 数据导入预览工作表的执行的状态。如需了解详情，请参阅数据执行状态部分。

更新或删除 Looker 数据源

使用 spreadsheets.batchUpdate 方法并提供 UpdateDataSourceRequest 或 DeleteDataSourceRequest 请求。

管理 Looker 数据源对象

将数据源添加到电子表格后，即可从中创建数据源对象。对于 Looker 数据源，您只能从中创建 DataSource pivotTable 对象。

无法根据 Looker 数据源创建 DataSource 公式、提取和图表。

刷新 Looker 数据源对象

您可以刷新数据源对象，以根据当前数据源规范和对象配置从 Looker 中提取最新数据。您可以使用 spreadsheets.batchUpdate 方法调用 RefreshDataSourceRequest。然后，使用 DataSourceObjectReferences 对象指定要刷新的一个或多个对象引用。

请注意，您可以在单个 batchUpdate 请求中创建和刷新数据源对象。

数据执行状态

当您创建数据源或刷新数据源对象时，系统会创建一个后台执行来从 BigQuery 或 Looker 中提取数据，并返回包含 DataExecutionStatus 的响应。如果执行成功开始，DataExecutionState 通常处于 RUNNING 状态。

由于该流程是异步的，因此您的应用应实现轮询模型，以定期检索数据源对象的状态。使用 spreadsheets.get 方法，直到状态返回 SUCCEEDED 或 FAILED 状态为止。在大多数情况下，执行会很快完成，但具体取决于数据源的复杂程度。通常，执行时间不会超过 10 分钟。