关联工作表

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

管理 BigQuery 数据源

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

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

如果您的应用请求任何 BigQuery 关联工作表数据，除了常规 Google Sheets API 请求所需的其他范围之外，还必须提供授予 bigquery.readonly 范围的 OAuth 2.0 令牌。如需了解详情，请参阅选择 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。系统会填充并引用该 ID，以根据数据源创建每个 DataSource 对象。

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

更新或删除 BigQuery 数据源

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

管理 BigQuery 数据源对象

将数据源添加到电子表格后，您可以根据该数据源创建数据源对象。数据源对象是常规工作表工具，例如数据透视表、图表和公式，这些工具与关联工作表集成，可为您的数据分析提供支持。

对象有四种类型：

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

添加 BigQuery 数据源表

该表对象在工作表编辑器中称为“提取”，用于将数据源中的静态数据转储导入到工作表中。 与数据透视表类似，该表已指定并锚定到左上角的单元格。

以下代码示例展示了如何使用 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 数据后，系统会填充数据源表，如图所示：

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

添加 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 提取最新数据。您可以使用 the 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 对象。The dataSourceId 是电子表格范围内的唯一 ID。系统会填充并引用该 ID，以根据数据源创建每个 DataSource 对象。

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

更新或删除 Looker 数据源

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

管理 Looker 数据源对象

将数据源添加到电子表格后，您可以根据该数据源创建数据源对象。对于 Looker 数据源，您只能根据该数据源创建 DataSource 数据透视表对象。

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

刷新 Looker 数据源对象

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

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

数据执行状态

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

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