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 | 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 | 北 | Jessie | 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 | 西 | Jessie | 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 つしかないため、2 つの
valueLayout設定は同等です。 - 2 つの行グループ（[Item Category] と [Model Number]）。1 つ目の並べ替えでは、「西」地域の合計数量の値が昇順で並べ替えられます。そのため、「エンジン」（西部の売上高なし）は「ドア」（西部の売上高 15）の上に表示されます。[モデル番号] グループは、すべての地域の総販売額の降順で並べ替えられるため、「W-24」（15 個の販売）が「W-25」（8 個の販売）の上に表示されます。これを行うには、
valueBucketフィールドを{}に設定します。 - 1 つの列グループ（地域）。売上高の多い順に昇順で並べ替えます。ここでも、
valueBucketは{}に設定します。「North」の売上合計が最も少ないため、最初の [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 に固定する方法を示しています。
リクエストは、次のプロパティを使用してピボット テーブルを構成します。
- 2 つの値グループ（[数量] と [合計金額]）。最初の値は販売数を示します。2 つ目は、部品の費用と総販売数の積に基づいて計算された値です。この計算には、
=Cost*SUM(Quantity)という式が使用されます。 - 3 つの行グループ（[アイテムのカテゴリ]、[モデル番号]、[費用]）。
- 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 Panel の販売数）は、「Jessie」（13 Panel の販売数）の左側に表示されます。
- [西] 以外の各地域の列を折りたたみ、その地域の営業担当者グループを非表示にします。これを行うには、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} } ], }