Mit der Google Sheets API können Sie Pivot-Tabellen in Tabellen erstellen und aktualisieren. Die Beispiele auf dieser Seite zeigen, 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 dargestellt, um sprachneutral zu sein. Informationen zum Implementieren eines Batch-Updates 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 müssen. Sie finden die Tabellen
ID 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 Sheet1!A1:D5.
Außerdem gibt der Platzhalter SOURCE_SHEET_ID das Tabellenblatt mit den Quelldaten an. In diesen Beispielen ist dies die Tabelle, die unter Quelldaten für Pivot-Tabellen aufgeführt ist.
Quelldaten für Pivot-Tabellen
Für diese Beispiele wird davon ausgegangen, dass die verwendete Tabelle die folgenden Quelldaten für Verkäufe im ersten Tabellenblatt („Sheet1“) enthält. Die Strings in der ersten Zeile sind Labels für die einzelnen Spalten. Beispiele zum 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 | Rad | W-24 | 20,50 $ | 4 | West | Beth | 01.03.2016 |
| 3 | Tür | D-01X | 15,00 $ | 2 | South | Amir | 15.03.2016 |
| 4 | Engine | ENG-0134 | 100,00 $ | 1 | North | Carmen | 20.03.2016 |
| 5 | Bettgestell | FR-0B1 | 34,00 $ | 8 | East | Hannah | 12.03.2016 |
| 6 | Feld | P-034 | 6,00 $ | 4 | North | Devyn | 02.04.2016 |
| 7 | Feld | P-052 | 11,50 $ | 7 | East | Erik | 16.05.2016 |
| 8 | Rad | W-24 | 20,50 $ | 11 | South | Sheldon | 30.04.2016 |
| 9 | Engine | ENG-0161 | 330,00 $ | 2 | North | Jessie | 02.07.2016 |
| 10 | Tür | D-01Y | 29,00 $ | 6 | West | Armando | 13.03.2016 |
| 11 | Bettgestell | FR-0B1 | 34,00 $ | 9 | South | 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 | North | Amir | 21.06.2016 |
| 15 | Tür | D-01X | 15,00 $ | 2 | West | Armando | 03.07.2016 |
| 16 | Bettgestell | FR-0B1 | 34,00 $ | 6 | South | Carmen | 15.07.2016 |
| 17 | Rad | W-25 | 20,00 $ | 8 | South | Hannah | 02.05.2016 |
| 18 | Rad | W-11 | 29,00 $ | 13 | East | Erik | 19.05.2016 |
| 19 | Tür | D-05 | 17,70 $ | 7 | West | Beth | 28.06.2016 |
| 20 | Bettgestell | FR-0B1 | 34,00 $ | 8 | North | Sheldon | 30.03.2016 |
Pivot-Tabelle hinzufügen
Das folgende
spreadsheets.batchUpdate
Codebeispiel zeigt, wie Sie mit dem
UpdateCellsRequest
eine Pivot-Tabelle aus den Quelldaten erstellen und in Zelle A50 des
durch SHEET_ID angegebenen Tabellenblatts verankern.
Die Anfrage konfiguriert die Pivot-Tabelle mit den folgenden Attributen:
- Eine Wertegruppe (Quantity), die die Anzahl der Verkäufe angibt. Da es nur eine Wertegruppe gibt, sind die beiden möglichen
valueLayoutEinstellungen gleichwertig. - Zwei Zeilengruppen (Item Category und Model Number). Die erste Gruppe sortiert nach aufsteigendem Wert der Gesamtmenge (Quantity) aus der Region West. Daher wird „Engine“ (ohne Verkäufe in der Region West) über „Door“ (mit 15 Verkäufen in der Region West) angezeigt. Die Gruppe Model Number sortiert in absteigender Reihenfolge nach dem Gesamtumsatz in allen Regionen. Daher wird „W-24“ (15 Verkäufe) ü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.
Auch hier ist
valueBucketauf{}gesetzt. Die Region „North“ hat den geringsten Gesamtumsatz und wird daher als erste Spalte für die 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
Das folgende
spreadsheets.batchUpdate
-Codebeispiel zeigt, wie Sie mit
UpdateCellsRequest
eine Pivot-Tabelle mit einer Gruppe für berechnete Werte aus den Quelldaten erstellen und
in Zelle A50 des durch SHEET_ID angegebenen Tabellenblatts verankern.
Die Anfrage konfiguriert die Pivot-Tabelle mit den folgenden Attributen:
- Zwei Wertegruppen (Quantity und Total Price). Die erste Gruppe gibt die Anzahl der Verkäufe an. Die zweite Gruppe ist ein berechneter Wert, der auf dem Produkt der Kosten eines Teils und der Gesamtzahl der Verkäufe basiert. Die Formel lautet:
=Cost*SUM(Quantity). - Drei Zeilengruppen (Item Category, Model Number und Cost).
- Eine Spaltengruppe (Region).
- Die Zeilen- und Spaltengruppen werden nach Namen sortiert (nicht nach Quantity) und die Tabelle wird alphabetisch sortiert. Dazu wird das
valueBucketFeld aus demPivotGroupweggelassen.- Um die Tabelle übersichtlicher zu gestalten, werden mit der Anfrage die Teilergebnisse für alle Zeilen- und Spaltengruppen außer den Hauptgruppen ausgeblendet.
- Mit der Anfrage wird
valueLayoutaufVERTICALgesetzt, um die Tabelle übersichtlicher zu gestalten.valueLayoutist nur wichtig, wenn es mindestens zwei Wertegruppen 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
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 Tabellenblatts verankert ist.
Mit einer UpdateCellsRequest kann eine Pivot-Tabelle entfernt werden, indem Sie „pivotTable“ in den Parameter fields aufnehmen und gleichzeitig 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 in Pivot-Tabellen bearbeiten
Das folgende
spreadsheets.batchUpdate
Codebeispiel zeigt, wie Sie mit
UpdateCellsRequest
die in Pivot-Tabelle hinzufügen erstellte Pivot-Tabelle bearbeiten.
Teilmengen des
pivotTable
Felds in der
CellData Ressource
können nicht einzeln mit dem fields Parameter geändert werden. Wenn Sie Änderungen vornehmen möchten, müssen Sie das gesamte Feld pivotTable angeben. Im Grunde erfordert das Bearbeiten einer Pivot-Tabelle, dass sie durch eine neue ersetzt wird.
Mit der 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.
- Eine Spaltengruppe (Salesperson) wird hinzugefügt. Die Spalten werden in absteigender Reihenfolge nach der Gesamtzahl der Verkäufe von Panel sortiert. „Carmen“ (15 Verkäufe von Panel) wird links von „Jessie“ (13 Verkäufe von Panel) angezeigt.
- Die Spalte für jede Region wird minimiert, mit Ausnahme von „West“. Die Gruppe
Salesperson wird für diese Region ausgeblendet. Dazu wird
collapsedauftruein dervalueMetadatafür diese Spalte in der Region Spaltengruppe gesetzt.
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
Das folgende
spreadsheets.get-Codebeispiel
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 Zellwertdaten).
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.
Informationen zur Pivot-Tabelle sind in der
CellData-Ressource des Tabellenblatts
für die Zelle enthalten, in der die Tabelle verankert ist (d. h. die linke obere
Ecke der Tabelle). Wenn ein Antwortfeld auf den Standardwert gesetzt ist, wird es aus der Antwort weggelassen.
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 in Zelle B3 verankert ist. Die leeren geschweiften Klammern geben Tabellenblätter oder Zellen an, die keine Pivot-Tabellendaten enthalten. Zur Referenz werden mit 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} } ], }