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 erstellen können. mit der Sheets API.
Diese Beispiele werden in Form von HTTP-Anfragen neutral sein. Um zu erfahren, wie Sie eine Batch-Aktualisierung in verschiedenen Sprachen mithilfe der Google API-Clientbibliotheken, siehe Aktualisieren Tabellen.
In diesen Beispielen haben die Platzhalter SPREADSHEET_ID und SHEET_ID
gibt an, wo Sie diese IDs angeben würden. Sie finden die Tabelle
ID in der Tabellen-URL. Sie erhalten
Tabellenblatt-ID mithilfe des
spreadsheets.get-Methode. Die
Bereiche werden in der A1-Notation angegeben. Eine
Beispielbereich ist Sheet1!A1:D5.
Außerdem kennzeichnet der Platzhalter SOURCE_SHEET_ID Ihr Tabellenblatt
mit den Quelldaten. In diesen Beispielen ist dies die Tabelle unter Pivot-Tabelle
Tabellenquelldaten.
Quelldaten in Pivot-Tabellen
Für diese Beispiele wird angenommen, dass die verwendete Tabellenkalkulation aus der folgenden Quelle stammt. „Umsätze“ Daten im ersten Tabellenblatt („Sheet1“). Die Zeichenfolgen in der ersten Zeile sind für die einzelnen Spalten. Um Beispiele für das Lesen aus anderen Quellen anzuzeigen 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 | 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 | T-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-Tabellen hinzufügen
Die folgenden
spreadsheets.batchUpdate
wird gezeigt, wie Sie mit der
UpdateCellsRequest
eine Pivot-Tabelle aus den Quelldaten erstellen, indem Sie sie in Zelle A50 des
Tabellenblatt angegeben durch SHEET_ID.
In der Anfrage wird die Pivot-Tabelle mit den folgenden Eigenschaften konfiguriert:
- Eine Wertegruppe (Menge), die die Anzahl der Verkäufe angibt. Seit
gibt es nur eine Wertegruppe, die 2 möglichen
valueLayoutEinstellungen identisch sind. - Zwei Zeilengruppen (Artikelkategorie und Modellnummer). Bei der ersten Methode
aufsteigender Wert der Quantity (Gesamtmenge) aus der Spalte „West“ (Westen) Region: Dementsprechend wird
„Engine“ (ohne Westverkäufe) wird über „Tür“ angezeigt. (mit 15 West-Verkäufen). Die
Bei der Gruppe Modellnummer werden die Daten in absteigender Reihenfolge
also „W-24“, (15 Verkäufe) erscheint über „W-25“. (8 Verkäufe). Fertig
indem Sie das Attribut
valueBucketauf{}. - Eine Spaltengruppe (Region), die in aufsteigender Reihenfolge nach den meisten Verkäufen sortiert.
valueBucketist wieder auf{}gesetzt. „Norden“ den niedrigsten Gesamtumsatz hat und sodass sie als erste Spalte Region angezeigt wird.
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
Die folgenden
spreadsheets.batchUpdate
wird in einem Codebeispiel gezeigt, wie Sie
UpdateCellsRequest
eine Pivot-Tabelle mit einer Gruppe berechneter Werte
aus den Quelldaten erstellen.
und verankert sie in Zelle A50 des mit SHEET_ID angegebenen Tabellenblatts.
In der Anfrage wird die Pivot-Tabelle mit den folgenden Eigenschaften konfiguriert:
- Zwei Wertegruppen: Menge und Gesamtpreis Das erste gibt an,
Anzahl der Verkäufe. Das zweite ist ein berechneter Wert,
der auf dem Produkt eines
die Kosten des Teils und die Gesamtanzahl der Verkäufe. Dazu wird folgende Formel verwendet:
=Cost*SUM(Quantity) - Drei Zeilengruppen (Artikelkategorie, Modellnummer und Kosten).
- Eine Spaltengruppe (Region).
- Die Zeilen- und Spaltengruppen werden jeweils nach Namen und nicht nach Menge sortiert.
und die Tabelle nach dem Alphabet
anordnen. Hierzu wird der Parameter
valueBucketaus dem FeldPivotGroup - Zur Vereinfachung der Tabellendarstellung werden in der Anfrage Zwischensummen ausgeblendet für alle bis auf die Hauptzeilen- und Spaltengruppen.
- In der Anfrage wird
valueLayoutfestgelegt. aufVERTICAL, um die Tabellendarstellung 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
Die folgenden
spreadsheets.batchUpdate
wird in einem Codebeispiel gezeigt, wie Sie
UpdateCellsRequest
zum Löschen einer Pivot-Tabelle (falls vorhanden), die in Zelle A50 des Tabellenblatts verankert ist
angegeben durch SHEET_ID.
Ein UpdateCellsRequest kann eine Pivot-Tabelle entfernen, indem er „pivotTable“ einfügt in
den Parameter fields, während das Feld pivotTable für den Anchor weggelassen wird
Zelle.
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
Die folgenden
spreadsheets.batchUpdate
wird in einem Codebeispiel gezeigt, wie Sie
UpdateCellsRequest
um die im Abschnitt Pivot-Tabelle hinzufügen erstellte Pivot-Tabelle zu bearbeiten.
Teilmengen von
pivotTable
im Feld
Ressource CellData
kann nicht einzeln mit dem Parameter fields geändert werden. Wenn Sie Änderungen vornehmen möchten,
Das gesamte Feld pivotTable muss angegeben werden. Das Bearbeiten einer Pivot-Tabelle
muss durch einen neuen 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 sortieren in absteigender Reihenfolge nach Die Gesamtzahl der Panel-Verkäufe. „Carmen“ (15 Panel-Verkäufe) scheinen links von "Jessie" (13 Panel-Verkäufe).
- Minimiert die Spalte für jede Region mit Ausnahme von "West" und blendet den
Vertriebsmitarbeiter für diese Region. Dazu setzt du
collapsedauftrueimvalueMetadatafür diese Spalte in der Spaltengruppe Region.
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
Die folgenden
spreadsheets.get-Codebeispiel
zeigt, wie Sie Pivot-Tabellendaten aus einer Tabellenkalkulation abrufen. Die fields-Abfrage
gibt an, dass nur die Daten der Pivot-Tabelle zurückgegeben werden sollen (als
im Gegensatz zu 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 einem
Spreadsheet
Ressource mit einem
Sheet-Objekt mit
SheetProperties
Elemente. Es gibt auch eine Reihe von
GridData
Elemente, die Informationen über die
PivotTable
Pivot-Tabellen werden in der Tabelle
CellData-Ressource
für die Zelle, in der die Tabelle verankert ist (d. h. die linke obere Ecke der Tabelle).
Ecke). Wenn ein Antwortfeld auf den Standardwert festgelegt ist, wird es im
Antwort.
In diesem Beispiel enthält das erste Tabellenblatt (SOURCE_SHEET_ID) die Rohtabelle.
Quelldaten, während das zweite Tabellenblatt (SHEET_ID) die Pivot-Tabelle enthält,
auf B3 verankert. Die leeren geschweiften Klammern kennzeichnen Tabellenblätter oder Zellen,
Pivot-Tabellendaten enthalten. Zu Referenzzwecken wird bei dieser Anfrage auch das Tabellenblatt
IDs.
{
"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
}
}
],
}