Mit der Google Sheets API können Sie Pivot-Tabellen in Tabellen erstellen und aktualisieren. In den Beispielen auf dieser Seite wird gezeigt, wie Sie mit der Google Tabellen API einige gängige Pivot-Tabellenvorgänge ausführen können.
Diese Beispiele werden in Form von HTTP-Anfragen dargestellt, um sprachenunabhängig zu sein. Informationen zum Implementieren eines Batch-Updates in verschiedenen Sprachen mit den Clientbibliotheken der Google API finden Sie unter Listen aktualisieren.
In diesen Beispielen geben die Platzhalter SPREADSHEET_ID und SHEET_ID an, wo Sie diese IDs angeben würden. Sie finden die Tabellen-ID in der Tabellen-URL. Sie können die Tabellen-ID mit der Methode spreadsheets.get abrufen. Die Bereiche werden in A1-Notation angegeben. Ein Beispiel für einen Bereich ist Tabelle1!A1:D5.
Außerdem steht der Platzhalter SOURCE_SHEET_ID für das Tabellenblatt mit den Quelldaten. In diesen Beispielen ist das die Tabelle, die unter Pivot-Tabellen-Quelldaten aufgeführt ist.
Quelldaten der Pivot-Tabelle
Angenommen, in der verwendeten Tabelle befinden sich auf dem ersten Tabellenblatt („Tabelle1“) die folgenden „Umsatz“-Daten. Die Strings in der ersten Zeile sind Labels für die einzelnen Spalten. Beispiele zum Lesen aus anderen Tabellen 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 | Rahmen | FR-0B1 | 34 $ | 8 | Ost | Hannah | 12.03.2016 |
| 6 | Feld | P-034 | 6,00 $ | 4 | Norden | Devyn | 2. April 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 | 2. Juli 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 | D-01X | 15,00 $ | 2 | West | Armando | 3. Juli 2016 |
| 16 | Rahmen | FR-0B1 | 34 $ | 6 | Süd | Carmen | 15.07.2016 |
| 17 | Wheel Riesenrad | W-25 | 20,00 $ | 8 | Süd | Hannah | 2. Mai 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-Tabelle hinzufügen
Im folgenden Codebeispiel für spreadsheets.batchUpdate wird gezeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle aus den Quelldaten erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts verankern.
In der Anfrage werden die folgenden Eigenschaften für die Pivot-Tabelle konfiguriert:
- Eine Wertegruppe (Menge), die die Anzahl der Verkäufe angibt. Da es nur eine Wertegruppe gibt, sind die beiden möglichen
valueLayout-Einstellungen äquivalent. - Zwei Zeilengruppen (Artikelkategorie und Modellnummer). Die erste sortiert nach dem aufsteigenden Wert der Gesamtmenge Quantity aus der Region „West“. Daher wird „Motor“ (ohne Verkäufe im Westen) über „Tür“ (mit 15 Verkäufen im Westen) angezeigt. Die Gruppe Modellnummer wird in absteigender Reihenfolge nach dem Gesamtumsatz in allen Regionen sortiert. „W-24“ (15 Verkäufe) wird also über „W-25“ (8 Verkäufe) angezeigt. Dazu wird das Feld
valueBucketauf{}gesetzt. - Eine Spaltengruppe (Region), die in aufsteigender Reihenfolge nach den meisten Verkäufen sortiert wird.
valueBucketist wieder auf{}gesetzt. „Nord“ hat den geringsten Gesamtumsatz und wird daher als erste Spalte für 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"
}
}
]
}
Mit der Anfrage wird eine Pivot-Tabelle wie diese erstellt:
Pivot-Tabelle mit berechneten Werten hinzufügen
Im folgenden Codebeispiel für spreadsheets.batchUpdate wird gezeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle mit einer Gruppe berechneter Werte aus den Quelldaten erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts verankern.
In der Anfrage werden die folgenden Eigenschaften für die Pivot-Tabelle konfiguriert:
- Zwei Wertegruppen (Menge und Gesamtpreis) Die erste gibt die Anzahl der Verkäufe an. Der zweite Wert ist ein berechneter Wert, der sich aus dem Produkt aus den Kosten eines Teils und der Gesamtzahl der Verkäufe mithilfe der folgenden Formel ergibt:
=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, sodass die Tabelle alphabetisch sortiert wird. Dazu wird das Feld
valueBucketausPivotGroupentfernt. - Um die Darstellung der Tabelle zu vereinfachen, werden in der Anfrage die Zwischensummen für alle Zeilen- und Spaltengruppen außer den Hauptgruppen ausgeblendet.
- In der Anfrage wird
valueLayoutaufVERTICALfestgelegt, um die Tabellendarstellung zu verbessern.valueLayoutist nur dann 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"
}
}
]
}
Mit der Anfrage wird eine Pivot-Tabelle wie diese erstellt:
Pivot-Tabelle löschen
Im folgenden spreadsheets.batchUpdate-Codebeispiel wird gezeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle (falls vorhanden) löschen, die an Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts angedockt ist.
Mit einem UpdateCellsRequest können Sie eine Pivot-Tabelle entfernen, indem Sie „pivotTable“ in den fields-Parameter einfügen und das Feld pivotTable in der Ankerzelle weglassen.
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 von Pivot-Tabellen bearbeiten
Im folgenden Codebeispiel spreadsheets.batchUpdate wird gezeigt, wie Sie mit UpdateCellsRequest die Pivot-Tabelle bearbeiten, die Sie unter Pivot-Tabelle hinzufügen erstellt haben.
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. Um eine Pivot-Tabelle zu 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 wird aus der ursprünglichen Pivot-Tabelle (Modellnummer) entfernt.
- Eine Spaltengruppe (Verkäufer) wird hinzugefügt. Die Spalten werden in absteigender Reihenfolge nach der Gesamtzahl der Panel-Verkäufe sortiert. „Carmen“ (15 Panel-Verkäufe) wird links neben „Jessie“ (13 Panel-Verkäufe) angezeigt.
- Die Spalte für jede Region wird minimiert, mit Ausnahme von „West“, wodurch die Gruppe Vertriebsmitarbeiter für diese Region ausgeblendet wird. Dazu wird für diese Spalte in der Spaltengruppe Region in
valueMetadatacollapsedauftruegesetzt.
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"
}
}
]
}
Mit der Anfrage wird eine Pivot-Tabelle wie diese erstellt:
Pivot-Tabellendaten lesen
Im folgenden spreadsheets.get-Codebeispiel wird gezeigt, wie Sie Pivot-Tabellendaten aus einer Tabelle abrufen. Mit dem Abfrageparameter fields wird angegeben, dass nur die Pivot-Tabellendaten zurückgegeben werden sollen (im Gegensatz zu 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. Außerdem gibt es ein Array von GridData-Elementen mit Informationen zur PivotTable.
Pivot-Tabelleninformationen sind in der CellData-Ressource des Tabellenblatts für die Zelle enthalten, an der die Tabelle angedockt ist (also die linke obere Ecke der Tabelle). Wenn ein Antwortfeld auf den Standardwert gesetzt ist, wird es aus der Antwort entfernt.
In diesem Beispiel enthält das erste Tabellenblatt (SOURCE_SHEET_ID) die Rohdaten der Quelltabelle, während das zweite Tabellenblatt (SHEET_ID) die Pivot-Tabelle enthält, die an B3 angedockt ist. Leere geschweifte Klammern geben an, dass die Tabellen oder Zellen keine Pivot-Tabellendaten enthalten. Diese Anfrage gibt auch die Tabellen-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
}
}
],
}