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」）に以下のソース「販売」データがあると想定しています。最初の行の文字列は、各列のラベルです。スプレッドシート内の他のシートから読み取る方法の例については、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 | 10,000 円 | 1 | 北 | Carmen | 2016 年 3 月 20 日 |
| 5 | フレーム | FR-0B1 | $34.00 | 8 | 東 | ハンナ | 2016 年 3 月 12 日 |
| 6 | パネル | P-034 | $6.00 | 4 | 北 | Devyn | 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 | 西 | Armando | 2016 年 3 月 13 日 |
| 11 | フレーム | FR-0B1 | $34.00 | 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 | 北 | 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 | 南 | ハンナ | 2016 年 5 月 2 日 |
| 18 | ホイール | W-11 | $29.00 | 13 | 東 | エリック | 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 に固定する方法を示しています。
このリクエストでは、次のプロパティを使用してピボット テーブルを構成します。
- 販売数を示す 1 つの値グループ（数量）。値グループは 1 つだけなので、
valueLayout設定で有効な 2 つの値は同等になります。 - 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 つの値グループ（数量と合計価格）。最初のグループは、販売数量を示します。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 番目の行グループ（モデル番号）を削除します。
- 列グループ（Salesperson）を追加します。列は、パネルの合計販売数の降順で並べ替えられます。「Carmen」（パネルの販売数 15）は、「Jessie」（パネルの販売数 13）の左側に表示されます。
- 「西部」を除く各地域の列を折りたたみ、その地域の販売員グループを非表示にします。これを行うには、地域列グループで該当する列の
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）に未加工のテーブルのソースデータがあり、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
}
}
],
}