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.
You can find the spreadsheet ID in
the spreadsheet URL. You can get the sheet
ID by using the
spreadsheets.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
valuesLayoutsettings 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
valueBucketfield to{}. - One column group (Region). It sorts in ascending order of most sales. Again,
valueBucketis 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 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
valueBucketfield from the PivotGroup. - The request hides subtotals for all but the main row and column groups, to simplify the table appearance.
- The request sets
valueLayouttoVERTICALfor 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:
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
collapsedtotruein thevalueMetadatafor 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:
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
}
}
],
}