ピボット テーブル

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

これらの例は、HTTP リクエストという形式で示されています。これは、 ニュートラルです。異なる言語のバッチ アップデートを実装する方法については、 Google API クライアント ライブラリについては、 スプレッドシート

これらの例では、プレースホルダ SPREADSHEET_IDSHEET_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 の列(「西」を除く)を折りたたみ、 その地域の営業担当者グループです。これを行うには、collapsedtruevalueMetadata [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
      }
    }
  ],
}