借助 Google Sheets API，您可以在电子表格中创建和更新数据透视表。 本页上的示例说明了如何使用 Sheets API 实现一些常见的数据透视表操作。
这些示例以语言中立的 HTTP 请求的形式呈现。如需了解如何使用 Google API 客户端库以多种语言实现批量更新，请参阅更新电子表格。
在此示例中，占位符 SPREADSHEET_ID 和 SHEET_ID 表示您将提供这些 ID 的位置。您可以在电子表格网址中找到电子表格 ID。您可以使用 spreadsheets.get 方法获取工作表 ID。这些范围是使用 A1 表示法指定的。Sheet1!A1:D5 就是一个示例范围。
此外，占位符 SOURCE_SHEET_ID 表示包含源数据的工作表。在这些示例中，这是数据透视表来源数据下列出的表格。
数据透视表源数据
对于这些示例，假设要使用的电子表格在其第一个工作表（“Sheet1”）中具有以下来源“销售”数据。第一行中的字符串是各个列的标签。如需查看有关如何从电子表格的其他工作表中读取数据的示例，请参阅 A1 表示法。
| 答 | B | C | D | E | F | G | |
| 1 | 商品类别 | 型号 | 费用 | 数量 | 区域 | 销售人员 | 发货日期 |
| 2 | 摩天轮 | W-24 用户 | 20.50 美元 | 4 | 西 | 白晶石 | 2016 年 3 月 1 日 |
| 3 | 门 | 广告内容描述 | $15.00 | 2 | 南 | 阿米尔语 | 2016 年 3 月 15 日 |
| 4 | 引擎 | ENG-0134 | ￥100.00 | 1 | 北 | 卡门 | 2016 年 3 月 20 日 |
| 5 | Frame | FR-0B1 | 34.00 美元 | 8 | 东 | Hannah | 2016 年 3 月 12 日 |
| 6 | 面板 | P-034 | $6.00 | 4 | 北 | 代芬 | 2016 年 4 月 2 日 |
| 7 | 面板 | P-052 | 11.50 美元 | 7 | 东 | Erik | 2016 年 5 月 16 日 |
| 8 | 摩天轮 | W-24 系列 | 20.50 美元 | 11 | 南 | Sheldon | 2016 年 4 月 30 日 |
| 9 | 引擎 | ENG-0161 | 330.00 元 | 2 | 北 | 小杰 | 2016 年 7 月 2 日 |
| 10 | 门 | 0-01 年 | 29.00 美元 | 6 | 西 | 阿尔曼多语 | 2016 年 3 月 13 日 |
| 11 | Frame | FR-0B1 | 34.00 美元 | 9 | 南 | 尤里亚纳 | 2016 年 2 月 27 日 |
| 12 | 面板 | P-102 | $3.00 | 15 | 西 | 卡门 | 2016 年 4 月 18 日 |
| 13 | 面板 | P-105 | 8.25 美元 | 13 | 西 | 小杰 | 2016 年 6 月 20 日 |
| 14 | 引擎 | ENG-0211 | 283.00 美元 | 1 | 北 | 阿米尔语 | 2016 年 6 月 21 日 |
| 15 | 门 | 广告内容描述 | $15.00 | 2 | 西 | 阿尔曼多语 | 2016 年 7 月 3 日 |
| 16 | Frame | FR-0B1 | 34.00 美元 | 6 | 南 | 卡门 | 7/15/2016 |
| 17 | 摩天轮 | W-25 系列 | $20.00 | 8 | 南 | Hannah | 2016 年 5 月 2 日 |
| 18 | 摩天轮 | W-11 考试 | 29.00 美元 | 13 | 东 | Erik | 2016 年 5 月 19 日 |
| 19 | 门 | 广告内容描述 5 | 17.70 美元 | 7 | 西 | 白晶石 | 2016 年 6 月 28 日 |
| 20 | Frame | FR-0B1 | 34.00 美元 | 8 | 北 | Sheldon | 2016 年 3 月 30 日 |
添加数据透视表
以下 spreadsheets.batchUpdate 代码示例演示了如何使用 UpdateCellsRequest 根据源数据创建数据透视表，并将其锚定到 SHEET_ID 指定的工作表的 A50 单元格中。
该请求会使用以下属性配置数据透视表：
- 一个表示销售次数的值组（数量）。由于只有一个值组，因此 2 项可能的
valueLayout设置是等效的。 - 两行组（商品类别和型号）。第一种方式是按“西”地区从总数量开始进行升序排序。因此，“引擎”（未销量为“西方”）位于“门”之上（销量为 15 次）。由于型号组会按所有地区总销量按降序排序，因此“W-24”（销量为 15）将高于“W-25”（销量为 8）。将
valueBucket字段设置为{}即可完成此操作。 - 一个列组（地区），按销售额从高到低排序。
同样，
valueBucket会设为{}。“北部”的总销售额最低，因此显示为第一个区域列。
请求协议如下所示。
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{
"pivotTable": {
"source": {
"sheetId": SOURCE_SHEET_ID,
"startRowIndex": 0,
"startColumnIndex": 0,
"endRowIndex": 20,
"endColumnIndex": 7
},
"rows": [
{
"sourceColumnOffset": 0,
"showTotals": true,
"sortOrder": "ASCENDING",
"valueBucket": {
"buckets": [
{
"stringValue": "West"
}
]
}
},
{
"sourceColumnOffset": 1,
"showTotals": true,
"sortOrder": "DESCENDING",
"valueBucket": {}
}
],
"columns": [
{
"sourceColumnOffset": 4,
"sortOrder": "ASCENDING",
"showTotals": true,
"valueBucket": {}
}
],
"values": [
{
"summarizeFunction": "SUM",
"sourceColumnOffset": 3
}
],
"valueLayout": "HORIZONTAL"
}
}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
该请求会创建一个数据透视表，如下所示：
添加包含计算值的数据透视表
以下 spreadsheets.batchUpdate 代码示例展示了如何使用 UpdateCellsRequest 创建包含基于源数据计算得出值的数据透视表，并将数据透视表锚定到 SHEET_ID 指定的工作表的 A50 单元格中。
该请求会使用以下属性配置数据透视表：
- 两个值组（数量和总价）。第一个值代表销售量。第二个值是根据零件费用和总销售数量计算得出的数值，计算公式如下：
=Cost*SUM(Quantity)。 - 三个行组（商品类别、型号和费用）。
- 一个列组（区域）。
- 行和列组在每个组中按名称（而不是数量）排序，并按表的字母顺序排序。通过省略
PivotGroup中的valueBucket字段即可完成此操作。 - 为了简化表的外观，该请求隐藏了除主行和列组之外的所有组的小计。
- 为了改进表格外观，该请求将
valueLayout设置为VERTICAL。仅当存在 2 个或更多值组时，valueLayout才重要。
请求协议如下所示。
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{
"pivotTable": {
"source": {
"sheetId": SOURCE_SHEET_ID,
"startRowIndex": 0,
"startColumnIndex": 0,
"endRowIndex": 20,
"endColumnIndex": 7
},
"rows": [
{
"sourceColumnOffset": 0,
"showTotals": true,
"sortOrder": "ASCENDING"
},
{
"sourceColumnOffset": 1,
"showTotals": false,
"sortOrder": "ASCENDING",
},
{
"sourceColumnOffset": 2,
"showTotals": false,
"sortOrder": "ASCENDING",
}
],
"columns": [
{
"sourceColumnOffset": 4,
"sortOrder": "ASCENDING",
"showTotals": true
}
],
"values": [
{
"summarizeFunction": "SUM",
"sourceColumnOffset": 3
},
{
"summarizeFunction": "CUSTOM",
"name": "Total Price",
"formula": "=Cost*SUM(Quantity)"
}
],
"valueLayout": "VERTICAL"
}
}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
该请求会创建一个数据透视表，如下所示：
删除数据透视表
以下 spreadsheets.batchUpdate 代码示例演示了如何使用 UpdateCellsRequest 删除锚定在 SHEET_ID 指定的工作表 A50 的单元格中的数据透视表（如果存在）。
UpdateCellsRequest 可以通过在 fields 参数中包含“pivotTable”来移除数据透视表，同时在锚点单元格中省略 pivotTable 字段。
请求协议如下所示。
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
编辑数据透视表的列和行
以下 spreadsheets.batchUpdate 代码示例展示了如何使用 UpdateCellsRequest 修改在添加数据透视表中创建的数据透视表。
无法使用 fields 参数单独更改 CellData 资源中 pivotTable 字段的子集。如需进行修改，必须提供整个 pivotTable 字段。基本上，修改数据透视表需要将其替换为新的数据透视表。
该请求对原始数据透视表做出以下更改：
- 从原始数据透视表（型号）中移除第二个行组。
- 添加列组（销售人员）。此类列按 Panel 的总销量按降序排序。“Carmen”（销量为 15 面板）位于“Jessie”（13 面板销量）的左侧。
- 收起除“西”以外的每个区域的列，并隐藏该区域的销售人员组。可通过在区域列组的该列的
valueMetadata中将collapsed设置为true来完成此操作。
请求协议如下所示。
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{
"pivotTable": {
"source": {
"sheetId": SOURCE_SHEET_ID,
"startRowIndex": 0,
"startColumnIndex": 0,
"endRowIndex": 20,
"endColumnIndex": 7
},
"rows": [
{
"sourceColumnOffset": 0,
"showTotals": true,
"sortOrder": "ASCENDING",
"valueBucket": {
"buckets": [
{
"stringValue": "West"
}
]
}
}
],
"columns": [
{
"sourceColumnOffset": 4,
"sortOrder": "ASCENDING",
"showTotals": true,
"valueBucket": {},
"valueMetadata": [
{
"value": {
"stringValue": "North"
},
"collapsed": true
},
{
"value": {
"stringValue": "South"
},
"collapsed": true
},
{
"value": {
"stringValue": "East"
},
"collapsed": true
}
]
},
{
"sourceColumnOffset": 5,
"sortOrder": "DESCENDING",
"showTotals": false,
"valueBucket": {
"buckets": [
{
"stringValue": "Panel"
}
]
},
}
],
"values": [
{
"summarizeFunction": "SUM",
"sourceColumnOffset": 3
}
],
"valueLayout": "HORIZONTAL"
}
}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
该请求会创建一个数据透视表，如下所示：
读取数据透视表数据
以下 spreadsheets.get 代码示例展示了如何从电子表格获取数据透视表数据。fields 查询参数指定应仅返回数据透视表数据（而不是单元格值数据）。
请求协议如下所示。
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
响应由一个 Spreadsheet 资源组成，该资源包含一个具有 SheetProperties 元素的 Sheet 对象。此外，还有一个 GridData 元素数组，其中包含有关 PivotTable 的信息。数据透视表信息包含在表格所锚定单元格的 CellData 资源（即表格的左上角）内。如果响应字段设置为默认值，响应中会将其省略。
在此示例中，第一张工作表 (SOURCE_SHEET_ID) 包含原始表源数据，而第二张工作表 (SHEET_ID) 锚定在 B3 上。空大括号表示不包含数据透视表数据的工作表或单元格。此要求还将返回工作表 ID，以供参考。
{
"sheets": [
{
"data": [{}],
"properties": {
"sheetId": SOURCE_SHEET_ID
}
},
{
"data": [
{
"rowData": [
{},
{},
{
"values": [
{},
{
"pivotTable": {
"columns": [
{
"showTotals": true,
"sortOrder": "ASCENDING",
"sourceColumnOffset": 4,
"valueBucket": {}
}
],
"rows": [
{
"showTotals": true,
"sortOrder": "ASCENDING",
"valueBucket": {
"buckets": [
{
"stringValue": "West"
}
]
}
},
{
"showTotals": true,
"sortOrder": "DESCENDING",
"valueBucket": {},
"sourceColumnOffset": 1
}
],
"source": {
"sheetId": SOURCE_SHEET_ID,
"startColumnIndex": 0,
"endColumnIndex": 7,
"startRowIndex": 0,
"endRowIndex": 20
},
"values": [
{
"sourceColumnOffset": 3,
"summarizeFunction": "SUM"
}
]
}
}
]
}
]
}
],
"properties": {
"sheetId": SHEET_ID
}
}
],
}