Google Sheets API 可讓你在試算表中建立及更新資料透視表。 本頁的範例將說明如何使用一些常見的資料透視表 使用 Sheets API 進行操作
這些範例是以 HTTP 要求的形式呈現 中立。如要瞭解如何使用 Google API 用戶端程式庫,請參閱 試算表。
在這些範例中,預留位置 SPREADSHEET_ID
和 SHEET_ID
代表這些 ID 的提供位置。您可以找到這份試算表
ID。您可以
工作表 ID
spreadsheets.get
方法。
範圍是使用 A1 標記法來指定。一個
範例範圍是 Sheet1!A1:D5
此外,預留位置 SOURCE_SHEET_ID
代表您的工作表
包含來源資料在這些範例中,這是列於「Pivot」下方的表格。
資料表來源資料。
資料透視表來源資料
以下範例假設要使用的試算表有以下來源 「sales」匯入第一張工作表 (「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 | 北 | Carmen | 2016 年 3 月 20 日 |
5 | 頁框 | FR-0B1 | NT$1,020 元 | 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 | 北 | 潔西 | 2016 年 7 月 2 日 |
10 | 門 | D-01Y | NT$870 元 | 6 | 西 | Armando | 2016 年 3 月 13 日 |
11 | 頁框 | FR-0B1 | NT$1,020 元 | 9 | 南 | 尤利安納 | 2016 年 2 月 27 日 |
12 | 面板 | P-102 | $3.00 美元 | 15 | 西 | Carmen | 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 | 西 | Armando | 2016 年 7 月 3 日 |
16 | 頁框 | FR-0B1 | NT$1,020 元 | 6 | 南 | Carmen | 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
指定的工作表。
這項要求會設定資料透視表並加入下列屬性:
- 一個用來指出銷售數量的值群組 (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)。
- 列和欄群組會按照名稱 (而非「數量」) 排序,
按字母順序排列表格方法是省略 [
valueBucket
欄位PivotGroup
。 - 為了簡化表格外觀,該要求會隱藏小計 除了主要列和欄群組外的所有儲存格。
- 要求會設定
valueLayout
將表格外觀設計成VERTICAL
,以改善表格外觀valueLayout
只是 如果有 2 個以上的值組,就很重要。
要求通訊協定如下所示。
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
刪除錨定在工作表 A50 上的資料透視表 (如果有的話)
由 SHEET_ID
指定。
UpdateCellsRequest
可加入「資料透視表」來移除資料透視表英吋
fields
參數,同時省略錨定標記上的 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
編輯在「新增資料透視表」中建立的資料透視表。
部分
pivotTable
欄位
CellData
項資源
您無法使用 fields
參數個別變更。如要編輯,請使用
完整提供 pivotTable
欄位。基本上就是編輯資料透視表
就必須更換新範本
該要求會對原始資料透視表進行下列變更:
- 從原始資料透視表中移除第二個列群組 (型號)。
- 新增欄群組 (銷售專員)。資料欄會按照 Panel 的總銷售量。「卡門」(15 Panel 銷售) 似乎 「Jessie」的左側(13 Panel 銷售)。
- 收合每個區域的資料欄,但「West」的部分除外,
該地區的銷售專員群組。做法是將「
collapsed
」設為true
的valueMetadata
在「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" } ] } } ], "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
元素。此外,也會提供
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
} } ], }