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」) 中有下列「sales」來源資料。第一列的字串是個別資料欄的標籤。如要查看如何讀取試算表中其他工作表的範例，請參閱 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 | 頁框 | FR-0B1 | NT$1,020 元 | 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 | NT$870 元 | 6 | 西 | 阿曼多 | 2016 年 3 月 13 日 |
| 11 | 頁框 | FR-0B1 | NT$1,020 元 | 9 | 南 | 尤利安納 | 2016 年 2 月 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 | 頁框 | FR-0B1 | NT$1,020 元 | 6 | 南 | 卡門 | 7/15/2016 |
| 17 | 摩天輪 | W-25 | $20.00 | 8 | 南 | Hannah | 2016 年 5 月 2 日 |
| 18 | 摩天輪 | W-11 | NT$870 元 | 13 | 東 | Erik | 2016 年 5 月 19 日 |
| 19 | 門 | D-05 | $17.70 美元 | 7 | 西 | 貝絲 | 2016 年 6 月 28 日 |
| 20 | 頁框 | FR-0B1 | NT$1,020 元 | 8 | 北 | Sheldon | 2016 年 3 月 30 日 |
新增資料透視表
以下 spreadsheets.batchUpdate 程式碼範例說明如何使用 UpdateCellsRequest 從來源資料建立資料透視表，並錨定在 SHEET_ID 指定的工作表儲存格 A50 上。
這項要求會設定資料透視表並加入下列屬性：
- 一個用來指出銷售數量的值群組 (Quantity)。由於只有一個值群組，因此 2 個可能的
valueLayout設定會相同。 - 兩個資料列群組 (「Item Category」和「Model Number」)。第一是按「西區」「地區」中的「數量」總數遞增排序。因此，「引擎」(無西部銷售) 會出現在「門」上方 (15 個西部銷售)。「Model Number」群組是按所有地區總銷售額的遞減順序排序，因此「W-24」(15 銷售) 會顯示在「W-25」(8 銷售) 上方。方法是將
valueBucket欄位設為{}。 - 一個資料欄群組 (區域)，用於按大部分銷售資料遞增排序。
再次提醒，
valueBucket已設為{}。「北」的總銷售額最低，因此會出現在第一個「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)。 - 三個資料列群組 (「Item Category」、「Model Number」和「Cost」)。
- 一個資料欄群組 (Region)。
- 資料列和資料欄群組會依名稱 (而非「數量」) 排序，並依資料表字母排序。方法是省略
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 參數中加入「CLTable」，同時在錨點儲存格中省略 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 欄位。基本上，編輯資料透視表時需要用新的資料透視表
該要求會對原始資料透視表進行下列變更：
- 從原始資料透視表中移除第二個列群組 (型號)。
- 新增欄群組 (銷售專員)。資料欄會按照「Panel」銷售總數遞減排序。「Carmen」(15 Panel 銷售) 顯示在「Jessie」(Jessie) 的左側 (13 Panel 銷售)。
- 收合每個「Region」(區域) 的資料欄，但「West」(西方) 除外，但隱藏該地區的「Salesperson」(銷售專員) 群組。方法是針對「Region」(區域) 資料欄群組中的
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
}
}
],
}