Pivot-Tabellen

Mit der Google Sheets API können Sie Pivot-Tabellen in Tabellen erstellen und aktualisieren. Die Beispiele auf dieser Seite veranschaulichen, wie Sie einige gängige Pivot-Tabellen erstellen können. mit der Sheets API.

Diese Beispiele werden in Form von HTTP-Anfragen neutral sein. Um zu erfahren, wie Sie eine Batch-Aktualisierung in verschiedenen Sprachen mithilfe der Google API-Clientbibliotheken, siehe Aktualisieren Tabellen.

In diesen Beispielen haben die Platzhalter SPREADSHEET_ID und SHEET_ID gibt an, wo Sie diese IDs angeben würden. Sie finden die Tabelle ID in der Tabellen-URL. Sie erhalten Tabellenblatt-ID mithilfe des spreadsheets.get-Methode. Die Bereiche werden in der A1-Notation angegeben. Eine Beispielbereich ist Sheet1!A1:D5.

Außerdem kennzeichnet der Platzhalter SOURCE_SHEET_ID Ihr Tabellenblatt mit den Quelldaten. In diesen Beispielen ist dies die Tabelle unter Pivot-Tabelle Tabellenquelldaten.

Quelldaten in Pivot-Tabellen

Für diese Beispiele wird angenommen, dass die verwendete Tabellenkalkulation aus der folgenden Quelle stammt. „Umsätze“ Daten im ersten Tabellenblatt („Sheet1“). Die Zeichenfolgen in der ersten Zeile sind für die einzelnen Spalten. Um Beispiele für das Lesen aus anderen Quellen anzuzeigen finden Sie unter A1-Notation.

A B C D E F G
1 Artikelkategorie Modellnummer Kosten Menge Region Vertriebsmitarbeiter Versanddatum
2 Wheel Riesenrad W-24 20,50 $ 4 West Beth 01.03.2016
3 Tür T-01X 15,00 $ 2 Süd Amir 15.03.2016
4 Engine ENG-0134 100,00 € 1 Norden Carmen 20.03.2016
5 Rahmen FR-0B1 34 $ 8 Ost Hannah 12.03.2016
6 Feld P-034 6,00 $ 4 Norden Devyn 02.04.2016
7 Feld P-052 11,50 $ 7 Ost Erik 16.05.2016
8 Wheel Riesenrad W-24 20,50 $ 11 Süd Sheldon 30.04.2016
9 Engine ENG-0161 330,00 $ 2 Norden Jessie 02.07.2016
10 Tür D-01Y 29,00 $ 6 West Armando 13.03.2016
11 Rahmen FR-0B1 34 $ 9 Süd Yuliana 27.02.2016
12 Feld P-102 3,00 $ 15 West Carmen 18.04.2016
13 Feld P-105 8,25 $ 13 West Jessie 20.06.2016
14 Engine ENG-0211 283,00 $ 1 Norden Amir 21.06.2016
15 Tür T-01X 15,00 $ 2 West Armando 03.07.2016
16 Rahmen FR-0B1 34 $ 6 Süd Carmen 15.07.2016
17 Wheel Riesenrad W-25 20,00 $ 8 Süd Hannah 02.05.2016
18 Wheel Riesenrad W-11 29,00 $ 13 Ost Erik 19.05.2016
19 Tür D-05 17,70 $ 7 West Beth 28.06.2016
20 Rahmen FR-0B1 34 $ 8 Norden Sheldon 30.03.2016

Pivot-Tabellen hinzufügen

Die folgenden spreadsheets.batchUpdate wird gezeigt, wie Sie mit der UpdateCellsRequest eine Pivot-Tabelle aus den Quelldaten erstellen, indem Sie sie in Zelle A50 des Tabellenblatt angegeben durch SHEET_ID.

In der Anfrage wird die Pivot-Tabelle mit den folgenden Eigenschaften konfiguriert:

  • Eine Wertegruppe (Menge), die die Anzahl der Verkäufe angibt. Seit gibt es nur eine Wertegruppe, die 2 möglichen valueLayout Einstellungen identisch sind.
  • Zwei Zeilengruppen (Artikelkategorie und Modellnummer). Bei der ersten Methode aufsteigender Wert der Quantity (Gesamtmenge) aus der Spalte „West“ (Westen) Region: Dementsprechend wird „Engine“ (ohne Westverkäufe) wird über „Tür“ angezeigt. (mit 15 West-Verkäufen). Die Bei der Gruppe Modellnummer werden die Daten in absteigender Reihenfolge also „W-24“, (15 Verkäufe) erscheint über „W-25“. (8 Verkäufe). Fertig indem Sie das Attribut valueBucket auf {}.
  • Eine Spaltengruppe (Region), die in aufsteigender Reihenfolge nach den meisten Verkäufen sortiert. valueBucket ist wieder auf {} gesetzt. „Norden“ den niedrigsten Gesamtumsatz hat und sodass sie als erste Spalte Region angezeigt wird.

Das Anfrageprotokoll ist unten zu sehen.

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"
      }
    }
  ]
}

Die Anfrage erstellt eine Pivot-Tabelle wie diese:

Schemaergebnis für Pivot-Tabelle hinzufügen

Eine Pivot-Tabelle mit berechneten Werten hinzufügen

Die folgenden spreadsheets.batchUpdate wird in einem Codebeispiel gezeigt, wie Sie UpdateCellsRequest eine Pivot-Tabelle mit einer Gruppe berechneter Werte aus den Quelldaten erstellen. und verankert sie in Zelle A50 des mit SHEET_ID angegebenen Tabellenblatts.

In der Anfrage wird die Pivot-Tabelle mit den folgenden Eigenschaften konfiguriert:

  • Zwei Wertegruppen: Menge und Gesamtpreis Das erste gibt an, Anzahl der Verkäufe. Das zweite ist ein berechneter Wert, der auf dem Produkt eines die Kosten des Teils und die Gesamtanzahl der Verkäufe. Dazu wird folgende Formel verwendet: =Cost*SUM(Quantity)
  • Drei Zeilengruppen (Artikelkategorie, Modellnummer und Kosten).
  • Eine Spaltengruppe (Region).
  • Die Zeilen- und Spaltengruppen werden jeweils nach Namen und nicht nach Menge sortiert. und die Tabelle nach dem Alphabet anordnen. Hierzu wird der Parameter valueBucket aus dem Feld PivotGroup
  • Zur Vereinfachung der Tabellendarstellung werden in der Anfrage Zwischensummen ausgeblendet für alle bis auf die Hauptzeilen- und Spaltengruppen.
  • In der Anfrage wird valueLayout festgelegt. auf VERTICAL, um die Tabellendarstellung zu verbessern. valueLayout ist nur wichtig, wenn es 2 oder mehr Wertgruppen gibt.

Das Anfrageprotokoll ist unten zu sehen.

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"
      }
    }
  ]
}

Die Anfrage erstellt eine Pivot-Tabelle wie diese:

Pivot-Werte für Gruppenschemaergebnis hinzufügen

Pivot-Tabellen löschen

Die folgenden spreadsheets.batchUpdate wird in einem Codebeispiel gezeigt, wie Sie UpdateCellsRequest zum Löschen einer Pivot-Tabelle (falls vorhanden), die in Zelle A50 des Tabellenblatts verankert ist angegeben durch SHEET_ID.

Ein UpdateCellsRequest kann eine Pivot-Tabelle entfernen, indem er „pivotTable“ einfügt in den Parameter fields, während das Feld pivotTable für den Anchor weggelassen wird Zelle.

Das Anfrageprotokoll ist unten zu sehen.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateCells": {
          "rows": [ 
            {
            "values": [
              {}
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

Spalten und Zeilen von Pivot-Tabellen bearbeiten

Die folgenden spreadsheets.batchUpdate wird in einem Codebeispiel gezeigt, wie Sie UpdateCellsRequest um die im Abschnitt Pivot-Tabelle hinzufügen erstellte Pivot-Tabelle zu bearbeiten.

Teilmengen von pivotTable im Feld Ressource CellData kann nicht einzeln mit dem Parameter fields geändert werden. Wenn Sie Änderungen vornehmen möchten, Das gesamte Feld pivotTable muss angegeben werden. Das Bearbeiten einer Pivot-Tabelle muss durch einen neuen ersetzt werden.

Mit der Anfrage werden die folgenden Änderungen an der ursprünglichen Pivot-Tabelle vorgenommen:

  • Entfernt die zweite Zeilengruppe aus der ursprünglichen Pivot-Tabelle (Modellnummer).
  • Fügt eine Spaltengruppe hinzu (Vertriebsmitarbeiter). Die Spalten sortieren in absteigender Reihenfolge nach Die Gesamtzahl der Panel-Verkäufe. „Carmen“ (15 Panel-Verkäufe) scheinen links von "Jessie" (13 Panel-Verkäufe).
  • Minimiert die Spalte für jede Region mit Ausnahme von "West" und blendet den Vertriebsmitarbeiter für diese Region. Dazu setzt du collapsed auf true im valueMetadata für diese Spalte in der Spaltengruppe Region.

Das Anfrageprotokoll ist unten zu sehen.

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"
      }
    }
  ]
}

Die Anfrage erstellt eine Pivot-Tabelle wie diese:

Schemaergebnis für Pivot-Tabelle bearbeiten

Daten aus Pivot-Tabellen lesen

Die folgenden spreadsheets.get-Codebeispiel zeigt, wie Sie Pivot-Tabellendaten aus einer Tabellenkalkulation abrufen. Die fields-Abfrage gibt an, dass nur die Daten der Pivot-Tabelle zurückgegeben werden sollen (als im Gegensatz zu Zellenwertdaten).

Das Anfrageprotokoll ist unten zu sehen.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

Die Antwort besteht aus einem Spreadsheet Ressource mit einem Sheet-Objekt mit SheetProperties Elemente. Es gibt auch eine Reihe von GridData Elemente, die Informationen über die PivotTable Pivot-Tabellen werden in der Tabelle CellData-Ressource für die Zelle, in der die Tabelle verankert ist (d. h. die linke obere Ecke der Tabelle). Ecke). Wenn ein Antwortfeld auf den Standardwert festgelegt ist, wird es im Antwort.

In diesem Beispiel enthält das erste Tabellenblatt (SOURCE_SHEET_ID) die Rohtabelle. Quelldaten, während das zweite Tabellenblatt (SHEET_ID) die Pivot-Tabelle enthält, auf B3 verankert. Die leeren geschweiften Klammern kennzeichnen Tabellenblätter oder Zellen, Pivot-Tabellendaten enthalten. Zu Referenzzwecken wird bei dieser Anfrage auch das Tabellenblatt IDs.

{
  "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
      }
    }
  ],
}