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.
Diese Beispiele werden in Form von sprachneutralen HTTP-Anfragen präsentiert. 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 Tabellenblatt-ID können Sie mit der Methode spreadsheets.get abrufen. Die Bereiche werden in der A1-Schreibweise angegeben. Ein Beispielbereich ist Sheet1!A1:D5.
Außerdem gibt der Platzhalter SOURCE_SHEET_ID Ihr Tabellenblatt mit den Quelldaten an. In diesen Beispielen ist dies die Tabelle unter Quelldaten von Pivot-Tabellen.
Quelldaten in Pivot-Tabellen
Für diese Beispiele wird angenommen, dass das erste Tabellenblatt der verwendeten Tabelle (Sheet1) die folgenden „Umsatz“-Quelldaten enthält. Die Strings in der ersten Zeile sind Labels 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 | T-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 | Jessie | 02.07.2016 |
| 10 | Tür | D-01Y | 29,00 $ | 6 | West | Armando | 13.03.2016 |
| 11 | Frame | FR-0B1 | 34 $ | 9 | Süd | Juliana | 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 | 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 für spreadsheets.batchUpdate zeigt, wie Sie UpdateCellsRequest verwenden, um eine Pivot-Tabelle aus den Quelldaten zu erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts zu verankern.
In der Anfrage wird die Pivot-Tabelle mit den folgenden Eigenschaften konfiguriert:
- 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 (Artikelkategorie und Modellnummer). Im ersten Beispiel wird die Quantity (Menge) in der Region „West“ in aufsteigendem Wert sortiert. Daher wird „Engine“ (ohne Westverkäufe) über „Door“ (mit 15 West-Verkäufen) angezeigt. Die Gruppe Modellnummer sortiert in absteigender Reihenfolge den Gesamtumsatz in allen Regionen, sodass „W-24“ (15 Verkäufe) über „W-25“ (8 Verkäufe) angezeigt wird. Dazu wird das Feld
valueBucketauf{}gesetzt. - Eine Spaltengruppe (Region), die in aufsteigender Reihenfolge nach den meisten Verkäufen sortiert.
valueBucketist wieder auf{}gesetzt. „North“ hat den geringsten Gesamtumsatz und wird daher als erste Spalte Region angezeigt.
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:
Eine Pivot-Tabelle mit berechneten Werten hinzufügen
Das folgende Codebeispiel für spreadsheets.batchUpdate zeigt, wie Sie UpdateCellsRequest verwenden, um eine Pivot-Tabelle mit einer Gruppe berechneter Werte aus den Quelldaten zu erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts zu verankern.
In der Anfrage wird die Pivot-Tabelle mit den folgenden Eigenschaften konfiguriert:
- Zwei Wertegruppen (Quantity (Menge) und Total Price (Gesamtpreis) Die erste gibt die
Anzahl der Verkäufe an. Der zweite Wert ist ein berechneter Wert, der auf dem Produkt der Kosten eines Teils und der Gesamtzahl der Verkäufe basiert. Dazu 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 Namen und nicht nach Menge sortiert, wobei die Tabelle alphabetisch sortiert wird. Dazu lässt du das Feld
valueBucketinPivotGroupweg. - Zur Vereinfachung der Tabellendarstellung blendet die Anfrage Zwischensummen für alle Gruppen mit Ausnahme der Hauptzeilen- und -spaltengruppen aus.
- In der Anfrage wird
valueLayoutaufVERTICALgesetzt, um die Darstellung der Tabelle zu verbessern.valueLayoutist 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-Tabellen löschen
Im folgenden Codebeispiel für spreadsheets.batchUpdate wird gezeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle löschen (falls vorhanden), die in Zelle A50 des mit SHEET_ID angegebenen Tabellenblatts verankert ist.
Ein UpdateCellsRequest kann eine Pivot-Tabelle entfernen, indem er „pivotTable“ in den Parameter fields einfügt und gleichzeitig das Feld pivotTable in der Ankerzelle auslässt.
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
Im folgenden Codebeispiel für spreadsheets.batchUpdate sehen Sie, wie Sie mit UpdateCellsRequest die unter 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. Beim Bearbeiten einer Pivot-Tabelle muss sie
durch eine neue 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 werden in absteigender Reihenfolge nach der Gesamtzahl der Panel-Verkäufe sortiert. "Carmen" (15 Panel-Verkäufe) wird links von "Jessie" angezeigt (13 Panel-Verkäufe).
- Minimiert die Spalte für jede Region mit Ausnahme von "West" und blendet die Gruppe Vertriebsmitarbeiter für diese Region aus. Setzen Sie dazu
collapsedinvalueMetadatafür diese Spalte in der Spaltengruppe Region auftrue.
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:
Daten aus 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 Daten der Pivot-Tabelle zurückgegeben werden sollen (im Gegensatz zu den 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 der Ressource Spreadsheet, die ein Sheet-Objekt mit SheetProperties-Elementen enthält. Außerdem gibt es ein Array von GridData-Elementen mit Informationen zu PivotTable.
Informationen zu Pivot-Tabellen sind in der Ressource CellData des Tabellenblatts für die Zelle enthalten, in der die Tabelle verankert ist (d. h. in der oberen linken Ecke der Tabelle). Wenn ein Antwortfeld auf den Standardwert gesetzt ist, wird es in der Antwort ausgelassen.
In diesem Beispiel enthält das erste Tabellenblatt (SOURCE_SHEET_ID) die Rohdaten der Tabelle, während das zweite Tabellenblatt (SHEET_ID) die Pivot-Tabelle enthält, die auf B3 verankert ist. Die leeren geschweiften Klammern kennzeichnen Tabellenblätter oder Zellen, die keine Pivot-Tabellendaten 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
}
}
],
}