Pivot Tables

The Sheets API lets you create and update pivot tables within spreadsheets as needed. The examples on this page illustrate how you can achieve some common pivot table operations with the API.

In these examples, the placeholders spreadsheetId and sheetId are used to indicate where you would provide those IDs. The spreadsheet ID can be discovered from the spreadsheet URL; the sheet ID can be obtained from the spreadsheet.get method.

Pivot table source data

For these examples, assume the spreadsheet being used has the following source "sales" data in its first sheet (whose ID is sourceSheetId). The strings in the first row are used as labels for the individual columns.

A B C D E F G
1 Item Category Model Number Cost Quantity Region Salesperson Ship Date
2 Wheel W-24 $20.50 4 West Beth 3/1/2016
3 Door D-01X $15.00 2 South Amir 3/15/2016
4 Engine ENG-0134 $100.00 1 North Carmen 3/20/2016
5 Frame FR-0B1 $34.00 8 East Hannah 3/12/2016
6 Panel P-034 $6.00 4 North Devyn 4/2/2016
7 Panel P-052 $11.50 7 East Erik 5/16/2016
8 Wheel W-24 $20.50 11 South Sheldon 4/30/2016
9 Engine ENG-0161 $330.00 2 North Jessie 7/2/2016
10 Door D-01Y $29.00 6 West Armando 3/13/2016
11 Frame FR-0B1 $34.00 9 South Yuliana 2/27/2016
12 Panel P-102 $3.00 15 West Carmen 4/18/2016
13 Panel P-105 $8.25 13 West Jessie 6/20/2016
14 Engine ENG-0211 $283.00 1 North Amir 6/21/2016
15 Door D-01X $15.00 2 West Armando 7/3/2016
16 Frame FR-0B1 $34.00 6 South Carmen 7/15/2016
17 Wheel W-25 $20.00 8 South Hannah 5/2/2016
18 Wheel W-11 $29.00 13 East Erik 5/19/2016
19 Door D-05 $17.70 7 West Beth 6/28/2016
20 Frame FR-0B1 $34.00 8 North Sheldon 3/30/2016

Add a pivot table

The following spreadsheets.batchUpdate request creates a new pivot table from the source data, anchoring it at A50 on the sheet indicated by sheetId. The pivot table has the following properties:

  • One values group, Quantity, which indicates the number of sales. Since there is only one values group, the two possible valuesLayout settings are equivalent.
  • Two row groups (Item Category and Model Number). The first of these sorts in ascending value of the total Quantity from the "West" Region. Thus "Engine" (no West sales) appears above "Door" (15 West sales). The Model Number group sorts in descending order of total sales in all regions, so "W-24" (15 sales) appears above "W-25" (8 sales). This is accomplished by setting the valueBucket field to {}.
  • One column group (Region). It sorts in ascending order of most sales. Again, valueBucket is set to {}. "North" had the least total sales, and so appears as the first Region column.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "updateCells": {
        "rows": {
          "values": [
            {
              "pivotTable": {
                "source": {
                  "sheetId": sourceSheetId,
                  "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": sheetId,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

The request creates a pivot table that looks like this:

Add pivot table recipe result

Add a pivot table with calculated values

The following spreadsheets.batchUpdate request creates a pivot table with a calculate values group. The pivot table has the following properties:

  • Two values groups (Quantity and Total Price). The first indicates the number of sales. The second is a calculated value based on the product of a part's cost and its total number of sales, using this formula: =Cost*SUM(Quantity).
  • Three row groups (Item Category, Model Number and Cost).
  • One column group (Region).
  • The row and column groups sort by name (rather than Quantity) in each group, alphabetizing the table. This is done by omitting the valueBucket field from the PivotGroup.
  • The request hides subtotals for all but the main row and column groups, to simplify the table appearance.
  • The request sets valueLayout to VERTICAL for an improved table appearance. Value layout is only important if there are two or more value groups.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "updateCells": {
        "rows": {
          "values": [
            {
              "pivotTable": {
                "source": {
                  "sheetId": sourceSheetId,
                  "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": sheetId,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

The request creates a pivot table that looks like this:

Add pivot values group recipe result

Delete a pivot table

The following spreadsheets.batchUpdate request removes a pivot table (if present) that is anchored at cell B4 of the sheet specified by sheetId. A updateCells request can remove a pivot table by including "pivotTable" in the fields parameter while also omitting the pivotTable field on the anchor cell.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "updateCells": {
        "rows": {
          "values": [
            {}
          ]
        },
        "start": {
          "sheetId": sheetId,
          "rowIndex": 3,
          "columnIndex": 1
        },
        "fields": "pivotTable"
      }
    }
  ]
}

Edit pivot table columns and rows

The following spreadsheets.batchUpdate request edits the pivot table created in the Add a pivot table recipe. Subsets of the pivotTable field in CellData cannot be changed individually with the fields parameter; the entire pivotTable field must be supplied to make edits. Essentially, editing a pivot table requires replacing it with a new one.

This example makes the following changes to the original pivot table:

  • Drops the second row group from the original pivot table (Model Number).
  • Adds a new column group (Salesperson). Its columns sort in descending order by the total number of Panel sales. "Carmen" (15 Panel sales) appears to the left of "Jessie" (13 Panel sales).
  • Collapses the column for each Region, with the exception of "West", hiding the Salesperson group for that region. This is done by setting collapsed to true in the valueMetadata for that column in the Region column group.

The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
  "requests": [
    {
      "updateCells": {
        "rows": {
          "values": [
            {
              "pivotTable": {
                "source": {
                  "sheetId": sourceSheetId,
                  "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": sheetId,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

After this request the pivot table looks like this:

Edit pivot table recipe result

Read pivot table data

The following spreadsheets.get request gets pivot table data from a spreadsheet. The fields query parameter specifies that only the pivot table data should be returned.

The response to this method call is a Spreadsheet object, which contains an array of Sheet objects. Pivot table information is contained within the sheet's CellData for the cell the table is anchored to (that is, the table's upper-left corner). If a given response field is currently set to the default value, it is omitted from the response.

In this example, the first sheet (specified by sourceSheetId) has the raw table source data, while the second (specified by sheetId) has the pivot table, anchored at cell B3. The empty braces indicate sheets or cells that do not contain pivot table data. This request also returns the sheet IDs, for reference.

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
{
  "sheets": [
    {
      "data": [{}],
      "properties": {
        "sheetId": sourceSheetId
      }
    },
    {
      "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": sourceSheetId,
                      "startColumnIndex": 0,
                      "endColumnIndex": 7,
                      "startRowIndex": 0,
                      "endRowIndex": 20
                    },
                    "values": [
                      {
                        "sourceColumnOffset": 3,
                        "summarizeFunction": "SUM"
                      }
                    ]
                  }
                }
              ]
            }
          ]
        }
      ],
      "properties": {
        "sheetId": sheetId
      }
    }
  ],
}

发送以下问题的反馈:

此网页
Sheets API
Sheets API