Interfejs Google Sheets API umożliwia tworzenie i aktualizowanie tabel przestawnych w arkuszach kalkulacyjnych. Przykłady na tej stronie pokazują, jak wykonywać typowe operacje na tabelach przestawnych za pomocą interfejsu Sheets API.
Przykłady te są przedstawione w formie żądań HTTP, aby były neutralne językowo. Aby dowiedzieć się, jak za pomocą bibliotek klienta interfejsu API Google wdrożyć zbiorcze aktualizowanie w różnych językach, przeczytaj artykuł Aktualizowanie arkuszy kalkulacyjnych.
W tych przykładach obiekty zastępcze SPREADSHEET_ID i SHEET_ID wskazują, gdzie należy podać te identyfikatory. Identyfikator arkusza kalkulacyjnego znajdziesz w adresie URL arkusza. Identyfikator arkusza możesz uzyskać, korzystając z metody spreadsheets.get. Zakresy są określane za pomocą notacji A1. Przykładowy zakres to Arkusz1!A1:D5.
Dodatkowo miejsce zarezerwowane dla danych źródłowych SOURCE_SHEET_ID wskazuje arkusz z danymi źródłowymi. W tych przykładach jest to tabela wymieniona w sekcji Dane źródłowe tabeli przestawnej.
Źródłowe dane tabeli przestawnej
W tych przykładach zakładamy, że używany arkusz kalkulacyjny zawiera na pierwszym arkuszu („Arkusz1”) dane źródłowe „sprzedaż”. Ciągi w pierwszym wierszu to etykiety poszczególnych kolumn. Przykłady odczytywania danych z innych arkuszy w arkuszu kalkulacyjnym znajdziesz w sekcji Notacja A1.
| A | B | C | D | E | F | G | |
| 1 | Kategoria produktu | Numer modelu | Koszt | Ilość | Region | Pracownik działu sprzedaży | Data wysyłki |
| 2 | Diabelski młyn | W-24 | 20,50 zł | 4 | zachód | Beth | 01.03.2016 |
| 3 | Drzwi | D-01X | 15 zł | 2 | południe | Amir | 15.03.2016 r. |
| 4 | Silnik | ENG-0134 | 300 PLN | 1 | północ | Carmen | 20.03.2016 |
| 5 | Rama | FR-0B1 | 34,00 USD | 8 | wschód | Hannah | 12.03.2016 |
| 6 | Panel | P-034 | 18 PLN | 4 | północ | Devyn | 2.04.2016 r. |
| 7 | Panel | P-052 | 11,50 PLN | 7 | wschód | Erik | 16.05.2016 r. |
| 8 | Diabelski młyn | W-24 | 20,50 zł | 11 | południe | Sheldon | 30.04.2016 r. |
| 9 | Silnik | ENG-0161 | 330 PLN | 2 | północ | Jessie | 2.07.2016 |
| 10 | Drzwi | D-01Y | 29,00 USD | 6 | zachód | Armando | 13.03.2016 |
| 11 | Rama | FR-0B1 | 34,00 USD | 9 | południe | Juliana | 27.02.2016 r. |
| 12 | Panel | P-102 | 3 USD | 15 | zachód | Carmen | 18.04.2016 r. |
| 13 | Panel | P-105 | 8,25 PLN | 13 | zachód | Jessie | 20.06.2016 r. |
| 14 | Silnik | ENG-0211 | 283,00 PLN | 1 | północ | Amir | 21.06.2016 r. |
| 15 | Drzwi | D-01X | 15 zł | 2 | zachód | Armando | 3.07.2016 r. |
| 16 | Rama | FR-0B1 | 34,00 USD | 6 | południe | Carmen | 15.07.2016 r. |
| 17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hannah | 2.05.2016 r. |
| 18 | Diabelski młyn | W-11 | 29,00 USD | 13 | wschód | Erik | 19.05.2016 r. |
| 19 | Drzwi | D-05 | 17,70 USD | 7 | zachód | Beth | 28.06.2016 r. |
| 20 | Rama | FR-0B1 | 34,00 USD | 8 | północ | Sheldon | 30.03.2016 r. |
Dodawanie tabeli przestawnej
Ten przykładowy kod spreadsheets.batchUpdate pokazuje, jak utworzyć tabelę przestawną na podstawie danych źródłowych za pomocą funkcji UpdateCellsRequest, a następnie ją zakotwiczyć w komórce A50 arkusza określonego przez argument SHEET_ID.
W żądaniu konfigurujesz tabelę przestawną za pomocą tych właściwości:
- 1 grupa wartości (Ilość), która wskazuje liczbę transakcji sprzedaży. Ponieważ istnieje tylko 1 grupa wartości, 2 możliwe ustawienia
valueLayoutsą równoważne. - 2 grupy wierszy (Kategoria produktu i Numer modelu). Pierwszy sortuje w kolejności rosnącej według łącznej wartości ilości w regionie „West”. Dlatego „Silnik” (bez sprzedaży w Zachodniej Australii) pojawia się nad „Drzwiami” (ze sprzedażą 15 w Zachodniej Australii). Grupa Numer modelu jest sortowana w kolejności malejącej według łącznej liczby sprzedaży we wszystkich regionach, więc „W-24” (15 sprzedaży) pojawia się nad „W-25” (8 sprzedaży). Aby to zrobić, ustaw wartość pola
valueBucketna{}. - Grupa 1 kolumny (Region), która jest sortowana w kolejności rosnącej według największej liczby sprzedaży.
Ponownie
valueBucketma wartość{}. „Północ” ma najmniejszą łączną sprzedaż, dlatego pojawia się jako pierwsza kolumna Region.
Protokół żądania przedstawiono poniżej.
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"
}
}
]
}
Żądanie tworzy tabelę przestawną, która wygląda tak:
Dodawanie tabeli przestawnej z obliczonymi wartościami
Ten przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji UpdateCellsRequest utworzyć tabelę przestawną z grupą wartości obliczeniowych na podstawie danych źródłowych, a następnie ją zakotwiczyć w komórce A50 arkusza określonego przez parametr SHEET_ID.
W żądaniu konfigurujesz tabelę przestawną za pomocą tych właściwości:
- 2 grupy wartości (Ilość i Łączna cena). Pierwszy z nich wskazuje liczbę sprzedaży. Druga to wartość obliczona na podstawie mnożenia kosztu części przez łączną liczbę jej sprzedaży według wzoru:
=Cost*SUM(Quantity). - 3 grupy wierszy (Kategoria produktu, Numer modelu i Koszt).
- Jedna grupa kolumn (Region).
- Grupy wierszy i kolumn są sortowane według nazwy (a nie według liczby) w każdej grupie, co powoduje uporządkowanie tabeli w kolejności alfabetycznej. Aby to zrobić, pomiń pole
valueBucketwPivotGroup. - Aby uprościć wygląd tabeli, żądanie ukrywa sumy częściowe we wszystkich grupach wierszy i kolumn oprócz głównych.
- Prośba ustawia wartość
valueLayoutnaVERTICAL, aby ulepszyć wygląd tabeli.valueLayoutma znaczenie tylko wtedy, gdy występuje co najmniej 2 grupy wartości.
Protokół żądania przedstawiono poniżej.
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"
}
}
]
}
Żądanie tworzy tabelę przestawną, która wygląda tak:
Usuwanie tabeli przestawnej
Ten przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji UpdateCellsRequest usunąć tabelę przestawną (jeśli jest obecna), która jest zakotwiczona w komórce A50 arkusza określonego przez parametr SHEET_ID.
UpdateCellsRequest może usunąć tabelę przestawną, podając w parametrze fields wartość „pivotTable”, a także pomijając pole pivotTable w komórce kotwicy.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
Edytowanie kolumn i wierszy tabeli przestawnej
Ten przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji UpdateCellsRequest edytować tabelę przestawną utworzoną w sekcji Dodawanie tabeli przestawnej.
Podzbiory pola pivotTable w zasobach CellData nie można zmieniać indywidualnie za pomocą parametru fields. Aby wprowadzić zmiany, musisz podać całe pole pivotTable. W zasadzie edytowanie tabeli przestawnej wymaga jej zastąpienia nową.
W ramach tego żądania w pierwotnej tabeli przestawnej zostaną wprowadzone następujące zmiany:
- Usuwa drugą grupę wierszy z pierwotnej tabeli przestawnej (Numer modelu).
- Dodaje grupę kolumn (Sprzedawca). Kolumny są sortowane malejąco według łącznej liczby transakcji sprzedaży Panelu. Po lewej stronie „Jessie” (13 Panel sprzedaży) znajduje się „Carmen” (15 Panel sprzedaży).
- Zwija kolumnę dla każdego regionu, z wyjątkiem „West”, ukrywając w tym regionie grupę Salesperson. Aby to zrobić, ustaw wartość
collapsednatruew kolumnievalueMetadataw grupie kolumn Region.
Protokół żądania przedstawiono poniżej.
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"
}
}
]
}
Żądanie tworzy tabelę przestawną, która wygląda tak:
Odczytywanie danych z tabeli przestawnej
Poniższy przykładowy kod spreadsheets.get pokazuje, jak pobierać dane z tabeli przestawnej z arkusza kalkulacyjnego. Parametr zapytania fields określa, że zwracane mają być tylko dane tabeli przestawnej (a nie dane wartości komórki).
Protokół żądania przedstawiono poniżej.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
Odpowiedź składa się z zasosobu Spreadsheet, który zawiera obiekt Sheet z elementami SheetProperties. Jest też tablica elementów
GridData
z informacjami o
PivotTable.
Informacje o tabeli przestawnej znajdują się w zasobie CellData arkusza w komórce, do której jest przypięta tabela (czyli w lewym górnym rogu tabeli). Jeśli pole odpowiedzi ma wartość domyślną, jest pomijane w odpowiedzi.
W tym przykładzie pierwszy arkusz (SOURCE_SHEET_ID) zawiera dane źródłowe tabeli nieprzetworzonej, a drugi arkusz (SHEET_ID) zawiera tabelę przestawną zablokowaną w komórce B3. Pusty nawias klamrowy wskazuje arkusze lub komórki, które nie zawierają danych tabeli przestawnej. W ramach tego żądania zwracane są też identyfikatory arkuszy.
{
"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
}
}
],
}