数据透视表

使用 Google Sheets API,您可以在电子表格中创建和更新数据透视表。 本页面上的示例说明了如何实现一些常见的数据透视表。 操作。

这些示例以 HTTP 请求的形式呈现, 中性。了解如何使用 请参阅更新 电子表格

在这些示例中,占位符 SPREADSHEET_IDSHEET_ID 指明提供这些 ID 的位置。您可以在电子表格 ID。您可以获得 工作表 ID spreadsheets.get 方法。通过 范围使用 A1 表示法指定。一个 示例范围是“Sheet1!A1:D5”。

此外,占位符 SOURCE_SHEET_ID 表示您的工作表 与源数据相关联。在这些示例中,这是数据透视下列出的表格 表源数据

数据透视表源数据

对于这些示例,我们假设所使用的电子表格具有以下来源 “销售”数据。第一行中的字符串是 各列的标签。查看有关如何从其他来源读取数据的示例 工作表,请参阅 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 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 杰西 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 西 杰西 2016 年 6 月 20 日
14 引擎 ENG-0211 283.00 美元 1 阿米尔 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 西 贝丝 2016 年 6 月 28 日
20 Frame FR-0B1 34.00 美元 8 Sheldon 2016 年 3 月 30 日

添加数据透视表

以下 spreadsheets.batchUpdate 代码示例展示了如何使用 UpdateCellsRequest 根据源数据创建一个数据透视表,并将其锚定在 SHEET_ID 指定的工作表。

该请求通过以下属性配置数据透视表:

  • 一个值组 (Quantity),用于表示销量。开始时间 只有一个值组, valueLayout 是等效的
  • 两个行组(Item CategoryModel Number)。排名第一的 从“West”中返回总 Quantity 的升序值区域。因此, “引擎”(无西区销售)显示在“门”上方(West 销量为 15)。通过 “Model Number”组按总销售额的降序排序 所以“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 上。

该请求通过以下属性配置数据透视表:

  • 两个值组(QuantityTotal Price)。第一个表示 销售量。第二个值是基于 的乘积计算得出的值 部分费用和总销售次数,计算公式如下: =Cost*SUM(Quantity)
  • 三个行组(Item CategoryModel NumberCost)。
  • 一个列组(区域)。
  • 行组和列组在每个部分中按名称(而不是按 Quantity)排序 对表格进行字母排序。只需忽略 valueBucket 字段中 PivotGroup
  • 为了简化表格外观,该请求会隐藏小计 。
  • 请求设置 valueLayout VERTICAL,以改进表格外观。只有 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 删除固定在工作表单元格 A50 中的数据透视表(如果存在) 由 SHEET_ID 指定。

UpdateCellsRequest 可以通过添加“pivotTable”来移除数据透视表在 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 修改在添加数据透视表中创建的数据透视表。

pivotTable 字段中的 CellData 资源 不能使用 fields 参数单独更改。要进行修改, 必须提供整个 pivotTable 字段。实质上,修改数据透视表 您需要更换为新品。

该请求将对原始数据透视表进行以下更改:

  • 从原始数据透视表(型号)中移除第二个行组。
  • 添加列组(销售人员)。列按降序排序 面板销售的总次数。《卡门》(15 Panel 销量)看来 《Jessie》的左侧(13 Panel 销售)。
  • 收起除“西”之外的所有区域列,隐藏 该区域的销售人员群组。为此,将 collapsed 设置为 truevalueMetadata 区域列组中的该列。

请求协议如下所示。

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 资源,其中包含 Sheet 对象,其中包含 SheetProperties 元素。还有一个 GridData 元素,包含有关 PivotTable。 数据透视表信息包含在工作表的 CellData 资源 (即表格的左上角) 角)。如果响应字段设置为默认值,则系统会从 响应。

在此示例中,第一个工作表 (SOURCE_SHEET_ID) 包含原始表 第二个工作表 (SHEET_ID) 包含数据透视表, 锚定在 B3 上空的大括号表示 包含数据透视表数据。此请求还会返回工作表 。

{
  "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
      }
    }
  ],
}