このページは Cloud Translation API によって翻訳されました。

ピボットテーブル

Google Sheets API を使用すると、スプレッドシート内でピボットテーブルを作成および更新できます。このページの例は、Sheets API を使用して一般的なピボットテーブル操作を行う方法を示しています。

これらの例は、言語に依存しない HTTP リクエストの形式で提供されています。Google API クライアントライブラリを使用してバッチアップデートをさまざまな言語で実装する方法については、スプレッドシートを更新するをご覧ください。

以下の例では、プレースホルダ SPREADSHEET_ID と SHEET_ID が、これらの ID を指定する場所を示しています。スプレッドシート ID はスプレッドシートの URL で確認できます。シート ID を取得するには、spreadsheets.get メソッドを使用します。範囲は A1 表記で指定します。範囲の例は「Sheet1!A1:D5」です。

また、プレースホルダ SOURCE_SHEET_ID は、ソースデータを含むシートを示します。これらの例では、これは [ピボットテーブルのソースデータ] の下に表示されるテーブルです。

ピボットテーブルのソースデータ

これらの例では、使用するスプレッドシートの最初のシート（「Sheet1」）に次のソース「sales」データがあるとします。最初の行の文字列は、個々の列のラベルです。スプレッドシート内の他のシートから読み取る方法の例については、A1 表記をご覧ください。

	A	B	C	D	E	F	1 階
1	アイテムのカテゴリ	モデル番号	費用	数量	諸国	営業担当者	発送日
2	観覧車	W-24	20.50 ドル	4	西	ベス	2016 年 3 月 1 日
3	ドア	D-01X	$15.00	2	南	アミル	2016 年 3 月 15 日
4	エンジン	ENG-0134	10,000 円	1	北	カルメン	2016 年 3 月 20 日
5	フレーム	FR-0B1	$34.00	8	東	ハンナ	2016 年 3 月 12 日
6	パネル	P-034	$6.00	4	北	デビン	2016 年 4 月 2 日
7	パネル	P-052	11.50 ドル	7	東	エリック	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	西	アルマンド	2016 年 3 月 13 日
11	フレーム	FR-0B1	$34.00	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	28,300 円	1	北	アミル	2016 年 6 月 21 日
15	ドア	D-01X	$15.00	2	西	アルマンド	2016 年 7 月 3 日
16	フレーム	FR-0B1	$34.00	6	南	カルメン	7/15/2016
17	観覧車	W-25	$20.00	8	南	ハンナ	2016 年 5 月 2 日
18	観覧車	W-11	29.00 ドル	13	東	エリック	2016 年 5 月 19 日
19	ドア	D-05	17.70 ドル	7	西	ベス	2016 年 6 月 28 日
20	フレーム	FR-0B1	$34.00	8	北	Sheldon	2016 年 3 月 30 日

ピボットテーブルを追加する

次の spreadsheets.batchUpdate コードサンプルは、UpdateCellsRequest を使ってソースデータからピボットテーブルを作成し、SHEET_ID で指定されたシートのセル A50 に固定する方法を示しています。

このリクエストでは、次のプロパティを使用してピボットテーブルを設定します。

販売数を示す 1 つの値グループ（Quantity）。値グループは 1 つしかないため、2 つの valueLayout の設定は同等です。
2 つの行グループ（[Item Category] と [Model Number]）。最初の並べ替えは、西部のリージョンの合計数量を昇順で並べ替えます。そのため、「エンジン」（西部地域での販売なし）は「ドア」（西部地域での販売が 15 件）の上に表示されます。モデル番号グループは、すべてのリージョンの合計売上高の降順で並べ替えられます。したがって、「W-24」（売上 15）は「W-25」（売上 8）の上に表示されます。これを行うには、valueBucket フィールドを {} に設定します。
ほとんどの売上の昇順で並べ替える 1 つの列グループ（リージョン）。ここでも、valueBucket は {} に設定されています。「北」は総売上が最も少ないため、最初の [リージョン] 列に表示されます。

リクエストプロトコルを以下に示します。

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 に固定する方法を示しています。

このリクエストでは、次のプロパティを使用してピボットテーブルを設定します。

2 つの値グループ（「数量」と「合計金額」）。1 つ目は販売数です2 つ目は、部品の費用と合計販売数量の積に基づいて計算された値です。計算式は =Cost*SUM(Quantity) です。
3 つの行グループ（[Item Category]、[Model Number]、[Cost]）
1 つの列グループ（リージョン）。
行と列のグループは、各グループで（数量ではなく）名前で並べ替えられ、テーブルがアルファベット順に並べ替えられます。これを行うには、PivotGroup から valueBucket フィールドを省略します。
テーブルの外観を簡素化するために、このリクエストでは、メインの行グループとメインの列グループを除くすべてのグループの小計を非表示にします。
このリクエストでは 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 を使用して、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 を使って編集する方法を示しています。

CellData リソースの pivotTable フィールドのサブセットは、fields パラメータで個別に変更することはできません。編集するには、pivotTable フィールド全体を指定する必要があります。ピボットテーブルを編集するには新しいテーブルに置き換える必要があります

このリクエストにより、元のピボットテーブルに次の変更が行われます。

元のピボットテーブル（モデル番号）から 2 番目の行グループを削除します。
列グループ（営業担当者）を追加します。列は、Panel の販売の合計数で降順に並べ替えられます。「Carmen」（15 パネルの売り上げ）は「Jessie」（13 パネルの売り上げ）の左側に表示されます。
各 Region の列（西部を除く）を折りたたみ、そのリージョンの営業担当者グループを非表示にします。これを行うには、[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)

レスポンスは、SheetProperties 要素を持つ Sheet オブジェクトを含む Spreadsheet リソースで構成されます。また、PivotTable に関する情報を含む GridData 要素の配列もあります。ピボットテーブルの情報は、テーブルが固定されているセル（テーブルの左上隅）のシートの CellData リソースに格納されます。レスポンスフィールドがデフォルト値に設定されている場合は、レスポンスから除外されます。

この例では、最初のシート（SOURCE_SHEET_ID）には生のテーブルソースデータがあり、2 番目のシート（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
      }
    }
  ],
}

ピボット テーブル

ピボット テーブルのソースデータ

ピボット テーブルを追加する

計算値を含むピボット テーブルを追加する

ピボット テーブルを削除する

ピボット テーブルの列と行を編集する

ピボット テーブルのデータを読み取る

ピボットテーブル

ピボットテーブルのソースデータ

ピボットテーブルを追加する

計算値を含むピボットテーブルを追加する

ピボットテーブルを削除する

ピボットテーブルの列と行を編集する

ピボットテーブルのデータを読み取る