資料透視表

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

這些範例是以 HTTP 要求的形式呈現 中立。如要瞭解如何使用 Google API 用戶端程式庫,請參閱 試算表

在這些範例中,預留位置 SPREADSHEET_IDSHEET_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 $29.00 美元 6 西 Armando 2016 年 3 月 13 日
11 頁框 FR-0B1 NT$1,020 元 9 Yuliana 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 $29.00 美元 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」設為 truevalueMetadata敬上 在「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
      }
    }
  ],
}