借助 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 符号表示法。
| A | B | C | D | E | F | G | |
| 1 | 商品类别 | 型号 | 费用 | 数量 | 区域 | 销售人员 | 发货日期 |
| 2 | 车轮 | W-24 | 20.50 美元 | 4 | 西 | Beth | 2016 年 3 月 1 日 |
| 3 | 门 | D-01X | 15.00 美元 | 2 | 南 | Amir | 2016 年 3 月 15 日 |
| 4 | 引擎 | ENG-0134 | ￥100.00 | 1 | 北 | Carmen | 2016 年 3 月 20 日 |
| 5 | Frame | FR-0B1 | 34.00 美元 | 8 | 东 | Hannah | 2016 年 3 月 12 日 |
| 6 | 面板 | P-034 | 6.00 美元 | 4 | 北 | Devyn | 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 | 北 | Jessie | 2016 年 7 月 2 日 |
| 10 | 门 | D-01Y | 29.00 美元 | 6 | 西 | Armando | 2016 年 3 月 13 日 |
| 11 | Frame | FR-0B1 | 34.00 美元 | 9 | 南 | Yuliana | 2016 年 2 月 27 日 |
| 12 | 面板 | P-102 | $3.00 | 15 | 西 | Carmen | 2016 年 4 月 18 日 |
| 13 | 面板 | P-105 | $8.25 | 13 | 西 | Jessie | 2016 年 6 月 20 日 |
| 14 | 引擎 | ENG-0211 | $283.00 | 1 | 北 | Amir | 2016 年 6 月 21 日 |
| 15 | 门 | D-01X | 15.00 美元 | 2 | 西 | Armando | 2016 年 7 月 3 日 |
| 16 | Frame | FR-0B1 | 34.00 美元 | 6 | 南 | Carmen | 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 | 西 | Beth | 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设置为{}。“North”的总销售额最低，因此显示为第一个地区列。
请求协议如下所示。
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)。 - 三个行组（商品类别、型号和费用）。
- 1 个列组（区域）。
- 行组和列组会按名称（而非按数量）在每个组中进行排序，从而使表格按字母顺序排列。为此，请从
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 修改在添加透视表中创建的透视表。
CellData 资源中 pivotTable 字段的子集无法通过 fields 参数单独更改。如需进行修改，必须提供整个 pivotTable 字段。从本质上讲，修改数据透视表需要将其替换为新的数据透视表。
该请求对原始数据透视表进行了以下更改：
- 从原始透视表中移除第二个行组（型号）。
- 添加列组（销售人员）。各列按平板电脑的总销量降序排序。“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} } ], }