借助 Google 表格 API,您可以在电子表格中创建和更新数据透视表。 本页面上的示例说明了如何使用 Tables API 实现一些常见的数据透视表操作。
出于中性考虑,这些示例以 HTTP 请求的形式呈现。如需了解如何使用 Google API 客户端库以不同语言实现批量更新,请参阅更新电子表格。
在这些示例中,占位符 SPREADSHEET_ID
和 SHEET_ID
表示您要提供这些 ID 的位置。您可以在电子表格网址中找到电子表格 ID。您可以使用 spreadsheets.get
方法获取工作表 ID。范围使用 A1 表示法指定。例如,“Sheet1!A1:D5”是范围。
此外,占位符 SOURCE_SHEET_ID
表示包含源数据的工作表。在这些示例中,这是数据透视表源数据下列出的表。
数据透视表源数据
这些示例假设所使用的电子表格的第一个工作表(“Sheet1”)中包含以下源“销售”数据。第一行中的字符串是各个列的标签。如需查看有关如何从电子表格中的其他工作表读取数据的示例,请参阅 A1 表示法。
A | B | C | D | E | F | G | |
1 | 商品类别 | 型号 | 费用 | 数量 | 区域 | 销售人员 | 发货日期 |
2 | 摩天轮 | W-24 | 20.50 美元 | 4 | 西 | 白丝 | 2016 年 3 月 1 日 |
3 | 门 | D-01X 星 | 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 | 门 | D-01Y 天 | 29.00 美元 | 6 | 西 | 阿曼多 | 2016 年 3 月 13 日 |
11 | Frame | FR-0B1 | 34.00 美元 | 9 | 南 | 尤利安纳 | 2016/02/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 | 门 | D-01X 星 | 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 | 门 | D-05 | 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
设置是等效的。 - 两个行组(Item Category 和 Model Number)。第一个元素按“West”Region 中总数量的升序排序。因此,“Engine”(没有西部地区销量)显示在“Door”(西侧销售额为 15 次)上方。型号组按所有地区总销量的降序排序,因此“W-24”(销量为 15)显示在“W-25”(销量为 8)上方。将
valueBucket
字段设置为{}
即可实现。 - 一个列组 (Region),按大多数销售额的升序排序。
同样,
valueBucket
设置为{}
。“North”区域的总销售额最少,因此显示为第一个 Region 列。
请求协议如下所示。
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)
。 - 三个行组(商品类别、型号和费用)。
- 一个列组 (Region)。
- 行组和列组在每个组中按名称(而不是 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
字段来移除数据透视表。
请求协议如下所示。
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
} } ], }