Google Sheets API を使用すると、スプレッドシート内でピボット テーブルを作成、更新できます。 このページの例は、一般的なピボット テーブルを作成する方法を示しています。 スプレッドシートのオペレーションを見ていきましょう。
これらの例は、HTTP リクエストという形式で示されています。これは、 ニュートラルです。異なる言語のバッチ アップデートを実装する方法については、 Google API クライアント ライブラリについては、 スプレッドシート。
これらの例では、プレースホルダ SPREADSHEET_ID と SHEET_ID は、
は、これらの ID を指定する場所を示しています。そのスプレッドシートの
ID を入力します。取得できるもの:
シート ID
spreadsheets.get メソッドを使用します。「
範囲は A1 表記で指定します。「
Sheet1!A1:D5 です。
また、プレースホルダ SOURCE_SHEET_ID はシートを示します。
ソースデータと連携しますこの例では、[ピボット
テーブルのソースデータをご覧ください。
ピボット テーブルのソースデータ
これらの例では、使用するスプレッドシートに次のソースがあるとします。 "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 | 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 | 北 | アミール | 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 | 西 | ベス | 2016 年 6 月 28 日 |
| 20 | フレーム | FR-0B1 | $34.00 | 8 | 北 | Sheldon | 2016 年 3 月 30 日 |
ピボット テーブルを追加する
次の
spreadsheets.batchUpdate
コードサンプルを使用して、Terraform で
UpdateCellsRequest
ソースデータからピボット テーブルを作成し、それを
SHEET_ID で指定されたシートです。
このリクエストでは、以下のプロパティを使用してピボット テーブルを構成します。
- 販売数を示す 1 つの値グループ（Quantity）。以降
値グループは 1 つしかなく、2 つのグループがあります。
valueLayout設定は同じです。 - 2 つの行グループ（[Item Category] と [Model Number]）。最初の並べ替えは
「西」からの合計 Quantity の昇順の値リージョン。したがって、
「エンジン」（「ドア」の上に表示されます）（西部地域での販売数は 15 社）。「
[Model Number] グループでは、すべてのトランザクションの合計売上高の降順で並べ替えます。
「W-24」と指定します。（販売数 15）が「W-25」の上に表示されます（販売数: 8）。完了
[
valueBucketフィールドを{}に設定します。 - 1 つの列グループ（Region）。ほとんどの売上の昇順で並べ替えます。
ここでも、
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
コードサンプルを使用して、Terraform で
UpdateCellsRequest
ソースデータから計算値グループを含むピボット テーブルを作成する
SHEET_ID で指定されたシートのセル A50 に固定します。
このリクエストでは、以下のプロパティを使用してピボット テーブルを構成します。
- 2 つの値グループ（数量と合計価格）。1 つ目の例は、
把握できます2 つ目は、ラベルの積に基づいて計算された
部品の原価とその総販売数を
次の式で計算します
=Cost*SUM(Quantity)。 - 3 つの行グループ（[Item Category]、[Model Number]、[Cost]）。
- 1 つの列グループ（Region）。
- 行グループと列グループは、それぞれの行について（数量ではなく）名前で並べ替えられます。
テーブルをあいうえお順に並べ替えます。そのために、環境変数の
valueBucketフィールドをPivotGroup。 - 表をシンプルにするため、このリクエストでは小計が非表示になります。 すべてのグループに適用されます。
- リクエストで
valueLayoutが設定されている。VERTICALに変更して、表の外観を改善します。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
コードサンプルを使用して、Terraform で
UpdateCellsRequest
シートのセル A50 に固定されているピボット テーブル（存在する場合）を削除する
SHEET_ID で指定されます。
UpdateCellsRequest に「pivotTable」を含めることで、ピボット テーブルを削除できます。
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
コードサンプルを使用して、Terraform で
UpdateCellsRequest
ピボット テーブルを追加するで作成したピボット テーブルを編集します。
サブセット
pivotTable
フィールドを
CellData リソース
fields パラメータで個別に変更することはできません。編集するには、
pivotTable フィールド全体を指定する必要があります。ピボットテーブルを編集すると
新しいものに交換する必要があります。
このリクエストにより、元のピボット テーブルに次の変更が行われます。
- 元のピボット テーブル（Model Number）から 2 番目の行グループを削除します。
- 列グループ（Salesperson）を追加します。列は、 Panel の販売数の合計。カルメン（15 Panel 販売）は、 「Jessie」の左側（13 Panel 販売）。
- 各 Region の列（「西」を除く）を折りたたみ、
その地域の営業担当者グループです。これを行うには、
collapsedをtrueのvalueMetadata[Region] 列グループのその列の 1 つの列を見つけます。
リクエスト プロトコルを以下に示します。
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）に未加工のテーブルがあります。
2 番目のシート（SHEET_ID）にはピボット テーブルがあり、
B3 に固定されています。空の中かっこは、
ピボットテーブルのデータを格納できますこのリクエストでは、参照用にシートも返されます。
あります。
{
"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
}
}
],
}