資料透視表

GoogleSheet API 可讓你在試算表中建立及更新資料透視表。 本頁的範例將說明如何使用試算表 API 執行一些常見的資料透視表作業。

這些範例是以 HTTP 要求形式呈現,採用不受語言限制。如要瞭解如何使用 Google API 用戶端程式庫以不同語言實作批次更新,請參閱「更新試算表」。

在這些範例中,預留位置 SPREADSHEET_IDSHEET_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
      }
    }
  ],
}