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 Vorgänge für Pivot-Tabellen mit der Sheets API ausführen können.
Diese Beispiele werden in Form von HTTP-Anfragen präsentiert, um sprachneutral zu sein. Informationen zum Implementieren einer Batch-Aktualisierung in verschiedenen Sprachen mithilfe der Google API-Clientbibliotheken finden Sie unter Tabellen aktualisieren.
In diesen Beispielen geben die Platzhalter SPREADSHEET_ID und SHEET_ID an, wo Sie diese IDs angeben müssen. Die Tabellen-ID finden Sie in der Tabellen-URL. Die Tabellen-ID lässt sich mit der Methode spreadsheets.get abrufen. Die Bereiche werden in der A1-Notation angegeben. Ein Beispielbereich ist Sheet1!A1:D5.
Darüber hinaus gibt der Platzhalter SOURCE_SHEET_ID Ihr Tabellenblatt mit den Quelldaten an. In diesen Beispielen ist dies die Tabelle unter Quelldaten einer Pivot-Tabelle.
Quelldaten der Pivot-Tabelle
Nehmen wir für diese Beispiele an, dass die verwendete Tabellenkalkulation die folgenden „Umsatzdaten“ im ersten Tabellenblatt enthält („Sheet1“). Die Zeichenfolgen in der ersten Zeile sind Beschriftungen für die einzelnen Spalten. Beispiele für das Lesen aus anderen Tabellenblättern in Ihrer Tabelle 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 | D-01X | 15,00 $ | 2 | Süd | Amir | 15.03.2016 |
| 4 | Engine | ENG-0134 | 100,00 € | 1 | Norden | Carmen | 20.03.2016 |
| 5 | Frame | FR-0B1 | 34 $ | 8 | Ost | Hanna | 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 | Sandra | 02.07.2016 |
| 10 | Tür | D-01Y | 29,00 $ | 6 | West | Armando | 13.03.2016 |
| 11 | Frame | 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 | Sandra | 20.06.2016 |
| 14 | Engine | ENG-0211 | 283,00 $ | 1 | Norden | Amir | 21.06.2016 |
| 15 | Tür | D-01X | 15,00 $ | 2 | West | Armando | 03.07.2016 |
| 16 | Frame | FR-0B1 | 34 $ | 6 | Süd | Carmen | 15.07.2016 |
| 17 | Wheel Riesenrad | W-25 | 20,00 $ | 8 | Süd | Hanna | 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 | Frame | FR-0B1 | 34 $ | 8 | Norden | Sheldon | 30.03.2016 |
Pivot-Tabellen hinzufügen
Das folgende Codebeispiel spreadsheets.batchUpdate zeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle aus den Quelldaten erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts verankern.
Die Anfrage konfiguriert die Pivot-Tabelle mit den folgenden Eigenschaften:
- Eine Wertegruppe (Menge), die die Anzahl der Verkäufe angibt. Da es nur eine Wertegruppe gibt, sind die beiden möglichen Einstellungen für
valueLayoutgleichwertig. - Zwei Zeilengruppen (Item Category (Artikelkategorie) und Model Number) Zuerst wird nach dem aufsteigenden Wert der Quantity (Gesamtmenge) aus der Region „West“ sortiert. Daher wird „Engine“ (ohne West-Verkäufe) über „Tür“ (mit 15 West-Verkäufen) angezeigt. Die Gruppe Modellnummer sortiert in absteigender Reihenfolge des Gesamtumsatzes in allen Regionen, sodass „W-24“ (15 Verkäufe) über „W-25“ (8 Verkäufe) angezeigt wird. Setzen Sie dazu das Feld
valueBucketauf{}. - Eine Spaltengruppe (Region), die in aufsteigender Reihenfolge der meisten Verkäufe sortiert.
Auch hier ist
valueBucketauf{}gesetzt. „Nord“ hat den geringsten Gesamtumsatz und wird daher in der ersten Spalte Region angezeigt.
Das Anfrageprotokoll ist unten dargestellt.
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 folgt:
Pivot-Tabelle mit berechneten Werten hinzufügen
Das folgende Codebeispiel spreadsheets.batchUpdate zeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle mit einer Gruppe von Werten aus den Quelldaten erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts verankern.
Die Anfrage konfiguriert die Pivot-Tabelle mit den folgenden Eigenschaften:
- Zwei Wertegruppen (Menge und Gesamtpreis). Das erste gibt die
Anzahl der Verkäufe an. Das zweite Element ist ein berechneter Wert, der auf dem Produkt der Kosten eines Teils und der Gesamtzahl der Verkäufe basiert. Dabei wird die folgende Formel verwendet:
=Cost*SUM(Quantity). - Drei Zeilengruppen (Artikelkategorie, Modellnummer und Kosten)
- Eine Spaltengruppe (Region).
- Die Zeilen- und Spaltengruppen werden in jeder Gruppe nach Name (nicht nach Menge) sortiert und die Tabelle wird alphabetisch sortiert. Dazu wird das Feld
valueBucketinPivotGroupweggelassen. - Um die Darstellung der Tabelle zu vereinfachen, blendet die Anfrage Zwischensummen für alle mit Ausnahme der Hauptzeilen- und -spaltengruppen aus.
- In der Anfrage wird
valueLayoutaufVERTICALgesetzt, um die Tabelle zu verbessern.valueLayoutist nur wichtig, wenn es zwei oder mehr Wertgruppen gibt.
Das Anfrageprotokoll ist unten dargestellt.
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 folgt:
Pivot-Tabellen löschen
Das folgende Codebeispiel spreadsheets.batchUpdate zeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle (falls vorhanden) löschen, die auf Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts verankert ist.
Ein UpdateCellsRequest kann eine Pivot-Tabelle entfernen, indem „pivotTable“ in den Parameter fields aufgenommen und gleichzeitig das Feld pivotTable in der Ankerzelle weggelassen wird.
Das Anfrageprotokoll ist unten dargestellt.
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 in Pivot-Tabellen bearbeiten
Das folgende Codebeispiel spreadsheets.batchUpdate zeigt, wie Sie mit UpdateCellsRequest die in Pivot-Tabelle hinzufügen erstellte Pivot-Tabelle bearbeiten.
Teilmengen des Felds pivotTable in der Ressource CellData können nicht einzeln mit dem Parameter fields geändert werden. Wenn Sie Änderungen vornehmen möchten, muss das gesamte Feld pivotTable angegeben werden. Wenn Sie eine Pivot-Tabelle bearbeiten, müssen Sie sie durch eine neue ersetzen.
Durch die Anfrage werden die folgenden Änderungen an der ursprünglichen Pivot-Tabelle vorgenommen:
- Die zweite Zeilengruppe (Model Number) wird aus der ursprünglichen Pivot-Tabelle entfernt.
- Fügt eine Spaltengruppe hinzu (Vertriebsmitarbeiter). Die Spalten werden in absteigender Reihenfolge nach der Gesamtzahl der Panel-Verkäufe sortiert. „Carmen“ (15 Panel-Verkäufe) wird links von „Jessie“ (13 Panel-Verkäufe) angezeigt.
- Minimiert die Spalte für jede Region mit Ausnahme von "West". Die Gruppe Verkäufer für diese Region wird ausgeblendet. Dazu setzen Sie
collapsedin der Spaltengruppe Region invalueMetadataauftrue.
Das Anfrageprotokoll ist unten dargestellt.
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 folgt:
Daten in Pivot-Tabellen lesen
Das folgende Codebeispiel für spreadsheets.get zeigt, wie Sie Pivot-Tabellendaten aus einer Tabelle abrufen. Der Abfrageparameter fields gibt an, dass nur die Pivot-Tabellendaten zurückgegeben werden sollen (im Gegensatz zu den Zellenwertdaten).
Das Anfrageprotokoll ist unten dargestellt.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
Die Antwort besteht aus einer Spreadsheet-Ressource, die ein Sheet-Objekt mit SheetProperties-Elementen enthält. Es gibt auch ein Array mit GridData-Elementen, die Informationen zu PivotTable enthalten.
Informationen zu Pivot-Tabellen befinden sich in der CellData-Ressource des Tabellenblatts für die Zelle, in der die Tabelle verankert ist (d. h. in der oberen linken Ecke der Tabelle). Wenn ein Antwortfeld auf den Standardwert festgelegt ist, wird es in der Antwort weggelassen.
In diesem Beispiel enthält das erste Tabellenblatt (SOURCE_SHEET_ID) die Rohdaten der Tabelle (Rohdaten), während das zweite Tabellenblatt (SHEET_ID) die Pivot-Tabelle enthält, die auf B3 verankert ist. Leere geschweifte Klammern kennzeichnen Tabellenblätter oder Zellen, die keine Daten aus Pivot-Tabellen enthalten. Zu Referenzzwecken gibt diese Anfrage auch die Tabellenblatt-IDs zurück.
{
"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
}
}
],
}