您可以使用 Google Sheets API 在試算表中建立及更新樞紐分析表。 本頁的範例說明如何透過 Google 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 | 西 | Beth | 2016 年 3 月 1 日 |
| 3 | 門 | D-01X | $15.00 | 2 | 南 | Amir | 2016 年 3 月 15 日 |
| 4 | 引擎 | ENG-0134 | $100.00 | 1 | 北 | Carmen | 2016 年 3 月 20 日 |
| 5 | 頁框 | 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 | 北 | Jessie | 2016 年 7 月 2 日 |
| 10 | 門 | D-01Y | $29.00 | 6 | 西 | Armando | 2016 年 3 月 13 日 |
| 11 | 頁框 | FR-0B1 | $34.00 | 9 | 南 | Yuliana | 2/27/2016 |
| 12 | 面板 | P-102 | $3.00 美元 | 15 | 西 | Carmen | 2016 年 4 月 18 日 |
| 13 | 面板 | P-105 | $8.25 | 13 | 西 | Jessie | 2016 年 6 月 20 日 |
| 14 | 引擎 | ENG-0211 | $283.00 | 1 | 北 | Amir | 2016 年 6 月 21 日 |
| 15 | 門 | D-01X | $15.00 | 2 | 西 | Armando | 2016 年 7 月 3 日 |
| 16 | 頁框 | 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 | 西 | Beth | 2016 年 6 月 28 日 |
| 20 | 頁框 | FR-0B1 | $34.00 | 8 | 北 | Sheldon | 2016 年 3 月 30 日 |
新增資料透視表
下列
spreadsheets.batchUpdate
程式碼範例說明如何使用
UpdateCellsRequest
從來源資料建立樞紐分析表，並將其錨定在 SHEET_ID 指定工作表的 A50 儲存格。
這項要求會使用下列屬性設定樞紐分析表：
- 一組值 (數量)，表示銷售次數。由於只有一個值群組，因此 2 種可能的
valueLayout設定是等效的。 - 兩個資料列群組 (「商品類別」和「型號」)。第一個排序依據是「西」區域的總數量值升序。因此，「引擎」(沒有西區銷售額) 會顯示在「門」(西區銷售額為 15) 上方。「型號」群組會依所有區域的總銷售量降序排序，因此「W-24」(15 筆銷售) 會顯示在「W-25」(8 筆銷售) 上方。方法是將
valueBucket欄位設為{}。 - 一個資料欄群組 (「區域」)，依銷售額遞增排序。
同樣地，
valueBucket會設為{}。「North」的銷售總額最低，因此會顯示在第一個「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)。 - 三個資料列群組 (「商品類別」、「型號」和「費用」)。
- 1 個欄群組 (「區域」)。
- 資料列和資料欄群組會依名稱排序 (而非依「數量」)，並依字母順序排列資料表。方法是從
PivotGroup中省略valueBucket欄位。- 為簡化表格外觀，這項要求會隱藏所有小計，但主要列和欄群組除外。
- 要求會將
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 刪除以 SHEET_ID 指定工作表 A50 儲存格為錨點的樞紐分析表 (如有)。UpdateCellsRequest
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 編輯在「新增樞紐分析表」中建立的樞紐分析表。
CellData 資源的 pivotTable 欄位子集無法透過 fields 參數個別變更。如要進行編輯，必須提供整個 pivotTable 欄位。基本上，如要編輯資料透視表，必須替換成新的資料透視表。
這項要求會對原始資料透視表進行下列變更：
- 從原始樞紐分析表中移除第二個列群組 (「型號」)。
- 新增資料欄群組 (「業務人員」)。系統會依面板總銷售量，以遞減順序排序各欄。「Carmen」(15 Panel 銷售量) 會顯示在「Jessie」(13 Panel 銷售量) 的左側。
- 除了「西區」以外，其餘每個「區域」的資料欄都會收合，並隱藏該區域的「銷售人員」群組。方法是在「Region」資料欄群組中，將該資料欄的
collapsed設為true。valueMetadata
要求通訊協定如下所示。
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 元素。此外，還有包含 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} } ], }