GoogleSheet API 可讓你在試算表中建立及更新資料透視表。 本頁的範例將說明如何使用試算表 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 | 西 | 貝斯 | 2016 年 3 月 1 日 |
3 | 門 | D-01X | $15.00 | 2 | 南 | 阿米爾 | 2016 年 3 月 15 日 |
4 | 引擎 | ENG-0134 | $100.00 | 1 | North | 卡門 | 2016 年 3 月 20 日 |
5 | 頁框 | 法文-0B1 | NT$1,020 元 | 8 | 東 | Hannah | 2016 年 3 月 12 日 |
6 | 面板 | P-034 | $6.00 | 4 | North | Devyn (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 | NT$9,900 元 | 2 | North | 潔西 | 2016 年 7 月 2 日 |
10 | 門 | D-01Y | $29.00 美元 | 6 | 西 | 阿曼多 | 2016 年 3 月 13 日 |
11 | 頁框 | 法文-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 | NT$8,390 元 | 1 | North | 阿米爾 | 2016 年 6 月 21 日 |
15 | 門 | D-01X | $15.00 | 2 | 西 | 阿曼多 | 2016 年 7 月 3 日 |
16 | 頁框 | 法文-0B1 | NT$1,020 元 | 6 | 南 | 卡門 | 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 | 頁框 | 法文-0B1 | NT$1,020 元 | 8 | North | Sheldon | 2016 年 3 月 30 日 |
新增資料透視表
以下 spreadsheets.batchUpdate
程式碼範例說明如何使用 UpdateCellsRequest
,根據來源資料建立資料透視表,將其固定在 SHEET_ID
指定工作表的儲存格 A50 上。
此要求會設定資料透視表中的下列屬性:
- 一個值組 (數量),代表銷售次數。由於只有一個值群組,因此 2 個可能的
valueLayout
設定相等。 - 兩個資料列群組 (「Item Category」和「Model Number」)。第一個按「西區」的「數量」總值遞增排序。因此,「引擎」(沒有西方銷售) 會顯示在「Door」上方 (共有 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」)。
- 一個欄群組 (區域)。
- 資料列和資料欄群組會在每個群組中依名稱 (而非依「Quantity」) 排序,並依字母順序排列資料表。系統會從
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
編輯您在「新增資料透視表」中建立的資料透視表。
無法使用 fields
參數個別變更 CellData
資源中 pivotTable
欄位的子集。如要編輯,您必須提供整個 pivotTable
欄位。基本上,編輯資料透視表時
必須改用新的資料透視表
要求會對原始資料透視表進行下列變更:
- 從原始資料透視表 (型號) 中移除第二列群組。
- 新增資料欄群組 (銷售專員)。資料欄會依「Panel」銷售總數遞減排序。「Jessie」左側顯示「Carmen」(15 個「Panel」銷售) (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
物件。此外,還有一個包含 PivotTable
相關資訊的 GridData
元素陣列。資料透視表資訊位於表格錨定所在儲存格的工作表 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
} } ], }