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-Vorgänge 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 mit den 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 würden. Die Tabellen-ID finden Sie in der Tabellen-URL.
Sie können die Tabellenblatt-ID mit der Methode spreadsheets.get abrufen. Die Bereiche werden mit der A1-Notation angegeben. Ein Beispielbereich ist Tabelle1!A1:D5.
Der Platzhalter SOURCE_SHEET_ID steht für das Tabellenblatt mit den Quelldaten. In diesen Beispielen ist das die Tabelle, die unter Quelldaten für Pivot-Tabelle aufgeführt ist.
Quelldaten für Pivot-Tabellen
In diesen Beispielen wird davon ausgegangen, dass die verwendete Tabelle die folgenden Quelldaten für „sales“ (Umsatz) im ersten Tabellenblatt („Sheet1“) enthält. Die Strings in der ersten Zeile sind Labels für die einzelnen Spalten. Beispiele dafür, wie Sie Daten aus anderen Tabellenblättern in Ihrer Tabelle lesen, 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 | 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 | D-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-Tabelle hinzufügen
Im folgenden spreadsheets.batchUpdate-Codebeispiel wird gezeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle aus den Quelldaten erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Blatts verankern.
Mit der Anfrage wird die Pivot-Tabelle mit den folgenden Attributen konfiguriert:
- Eine Wertegruppe (Menge), die die Anzahl der Verkäufe angibt. Da es nur eine Wertegruppe gibt, sind die beiden möglichen
valueLayout-Einstellungen gleichwertig. - Zwei Zeilengruppen (Artikelkategorie und Modellnummer). Die erste Sortierung erfolgt nach aufsteigendem 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 allen Regionen in absteigender Reihenfolge nach Gesamtumsatz 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 Umsatz sortiert wird.
Auch hier ist
valueBucketauf{}gesetzt. „North“ hat den geringsten Gesamtumsatz und wird daher als erste Region-Spalte angezeigt.
Das Anfrageprotokoll wird unten angezeigt.
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
Das folgende spreadsheets.batchUpdate-Codebeispiel zeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle mit einer Gruppe von berechneten Werten aus den Quelldaten erstellen und sie in Zelle A50 des durch SHEET_ID angegebenen Blatts verankern.
Mit der Anfrage wird die Pivot-Tabelle mit den folgenden Attributen konfiguriert:
- Zwei Wertegruppen (Menge und 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. Er wird mit dieser Formel berechnet:
=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, wodurch die Tabelle alphabetisch sortiert wird. Dazu lassen Sie das Feld
valueBucketimPivotGroupweg.- Um die Darstellung der Tabelle zu vereinfachen, werden in der Anfrage Zwischensummen für alle Gruppen außer den Hauptzeilen- und ‑spaltengruppen ausgeblendet.
- Mit der Anfrage wird
valueLayoutaufVERTICALgesetzt, um die Darstellung der Tabelle zu verbessern.valueLayoutist nur wichtig, wenn es zwei oder mehr Wertgruppen gibt.
Das Anfrageprotokoll wird unten angezeigt.
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
Das folgende spreadsheets.batchUpdate-Codebeispiel zeigt, wie Sie mit UpdateCellsRequest eine Pivot-Tabelle löschen (falls vorhanden), die in Zelle A50 des durch SHEET_ID angegebenen Blatts verankert ist.
Mit einem UpdateCellsRequest kann eine Pivot-Tabelle entfernt werden, indem „pivotTable“ im Parameter fields angegeben und das Feld pivotTable in der Ankerzelle weggelassen wird.
Das Anfrageprotokoll wird unten angezeigt.
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 spreadsheets.batchUpdate-Codebeispiel zeigt, wie Sie die Pivot-Tabelle, die in Pivot-Tabelle hinzufügen erstellt wurde, mit UpdateCellsRequest 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öchten, müssen Sie sie im Grunde durch eine neue ersetzen.
Durch die 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 (Salesperson) hinzu. 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.
- Die Spalte für jede Region wird minimiert, mit Ausnahme von „West“. Die Gruppe Salesperson für diese Region wird ausgeblendet. Dazu wird
collapsedin der Spaltengruppe Region für diese Spalte auftruegesetzt.valueMetadata
Das Anfrageprotokoll wird unten angezeigt.
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
Das folgende spreadsheets.get-Codebeispiel zeigt, 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 Zellwertdaten).
Das Anfrageprotokoll wird unten angezeigt.
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 von GridData-Elementen, die Informationen zum PivotTable enthalten.
Informationen zu Pivot-Tabellen sind in der CellData-Ressource des Blatts für die Zelle enthalten, an der die Tabelle verankert ist (d. h. die obere linke Ecke der Tabelle). Wenn ein Antwortfeld auf den Standardwert festgelegt ist, wird es aus 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 geben Tabellenblätter oder Zellen an, die keine Pivot-Tabellendaten enthalten. Zur Referenz werden in dieser Anfrage auch die Tabellenblatt-IDs zurückgegeben.
{ "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} } ], }