Interfejs API Arkuszy Google umożliwia tworzenie i aktualizowanie tabel przestawnych w arkuszach kalkulacyjnych. Przykłady na tej stronie pokazują, jak za pomocą interfejsu Arkuszy API można wykonywać niektóre typowe operacje na tabelach przestawnych.
Mają one postać żądań HTTP, które mają być neutralne pod względem języka. Aby dowiedzieć się, jak wdrożyć aktualizację zbiorczą w różnych językach przy użyciu bibliotek klienta interfejsu API Google, przeczytaj artykuł Aktualizowanie arkuszy kalkulacyjnych.
W tych przykładach obiekty zastępcze SPREADSHEET_ID i SHEET_ID wskazują, gdzie należy je umieścić. Identyfikator arkusza kalkulacyjnego znajdziesz w adresie URL arkusza kalkulacyjnego. Identyfikator arkusza możesz uzyskać, korzystając z metody spreadsheets.get. Zakresy są określane w notacji A1. Przykładowy zakres to Arkusz1!A1:D5.
Dodatkowo symbol zastępczy 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.
Dane źródłowe tabeli przestawnej
Załóżmy, że w pierwszym arkuszu używany arkusz kalkulacyjny („Arkusz1”) zawiera dane „sprzedaż”: Ciągi w pierwszym wierszu to etykiety poszczególnych kolumn. Przykłady odczytu z innych arkuszy w arkuszu kalkulacyjnym znajdziesz w artykule Zapis A1.
| O | B | C | D | E | F | G | |
| 1 | Kategoria produktu | Numer modelu | Koszt | Liczba | Region | Sprzedawca | Data wysyłki |
| 2 | Diabelski młyn | W-24 | 20,50 USD | 4 | zachód | Beata | 1.03.2016 r. |
| 3 | Drzwi | D-01X | 15 PLN | 2 | południe | Amir | 15.03.2016 r. |
| 4 | Silnik | ENG-0134 | 300 PLN | 1 | północ | Karolina | 20.03.2016 r. |
| 5 | Ramka | FR-0B1 | 34 PLN | 8 | wschód | Hania | 12.03.2016 r. |
| 6 | Panel | P-034 | 18 PLN | 4 | północ | Dewyn | 02.04.2016 r. |
| 7 | Panel | P-052 | 11,50 PLN | 7 | wschód | Eryk | 16.05.2016 r. |
| 8 | Diabelski młyn | W-24 | 20,50 USD | 11 | południe | Sheldon | 30.04.2016 r. |
| 9 | Silnik | ENG-0161 | 330 USD | 2 | północ | Jessie | 02.07.2016 r. |
| 10 | Drzwi | D-01Y | 29 USD | 6 | zachód | Armanda | 13.03.2016 r. |
| 11 | Ramka | FR-0B1 | 34 PLN | 9 | południe | Yuliana | 27.02.2016 r. |
| 12 | Panel | P-102 | 12 PLN | 15 | zachód | Karolina | 18.04.2016 r. |
| 13 | Panel | P-105 | 8,25 USD | 13 | zachód | Jessie | 20.06.2016 r. |
| 14 | Silnik | ENG-0211 | 283 USD | 1 | północ | Amir | 21.06.2016 r. |
| 15 | Drzwi | D-01X | 15 PLN | 2 | zachód | Armanda | 03.07.2016 r. |
| 16 | Ramka | FR-0B1 | 34 PLN | 6 | południe | Karolina | 15.07.2016 r. |
| 17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hania | 02.05.2016 r. |
| 18 | Diabelski młyn | W-11 | 29 USD | 13 | wschód | Eryk | 19.05.2016 r. |
| 19 | Drzwi | D-05 | 17,70 USD | 7 | zachód | Beata | 28.06.2016 r. |
| 20 | Ramka | FR-0B1 | 34 PLN | 8 | północ | Sheldon | 30.03.2016 r. |
Dodawanie tabeli przestawnej
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak użyć UpdateCellsRequest do utworzenia tabeli przestawnej na podstawie danych źródłowych, zakotwiczając ją w komórce A50 arkusza określonej przez SHEET_ID.
Żądanie konfiguruje tabelę przestawną z użyciem następujących właściwości:
- Jedna grupa wartości (Liczba), która wskazuje liczbę transakcji sprzedaży. Istnieje tylko 1 grupa wartości, więc 2 możliwe ustawienia
valueLayoutsą równoważne. - 2 grupy wierszy (Item Category (Kategoria produktu) i Model Number (Numer modelu). Pierwsze sortowanie danych ilość sortuje rosnąco według wartości z regionu „Zachód”. Dlatego też słowo „Engine” (bez sprzedaży w Zachodniej) pojawia się nad sekcją „Drzwi” (z 15 sprzedażą na zachód). Grupa Numer modelu jest posortowana w kolejności malejącej według łącznej sprzedaży we wszystkich regionach, więc „W-24” (15 transakcji sprzedaży) pojawia się nad „W-25” (8 transakcji sprzedaży). Aby to zrobić, ustaw w polu
valueBucketwartość{}. - Jedna grupa kolumn (Region), która sortuje rosnąco według największej liczby transakcji sprzedaży.
Tak jak poprzednio,
valueBucketma wartość{}. „Północ” ma najmniejszą łączną sprzedaż, więc pojawia się jako pierwsza kolumna Region.
Protokół żądania jest pokazany 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ą w następujący sposób:
Dodawanie tabeli przestawnej z obliczonymi wartościami
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak użyć UpdateCellsRequest do utworzenia tabeli przestawnej z grupą wartości obliczonych na podstawie danych źródłowych, zakotwiczając ją w komórce A50 arkusza określonej przez SHEET_ID.
Żądanie konfiguruje tabelę przestawną z użyciem następujących właściwości:
- Dwie grupy wartości (Ilość i Łączna cena). Pierwszy wskazuje liczbę transakcji sprzedaży. Druga wartość to wartość obliczona na podstawie iloczynu kosztu części i łącznej liczby transakcji sprzedaży za pomocą tego wzoru:
=Cost*SUM(Quantity). - Trzy grupy wierszy (Kategoria produktu, Numer modelu i Koszt).
- Jedna grupa kolumn (Region).
- Grupy wierszy i kolumn są w każdej grupie sortowane według nazwy (a nie według Ilość) w kolejności alfabetycznej. W tym celu należy pominąć pole
valueBucketwPivotGroup. - Aby uprościć wygląd tabeli, żądanie ukrywa sumy częściowe dla wszystkich grup wierszy i kolumn oprócz głównego.
- Aby poprawić wygląd tabeli, żądanie ustawia
valueLayoutnaVERTICAL.valueLayoutjest ważny tylko wtedy, gdy masz co najmniej 2 grupy wartości.
Protokół żądania jest pokazany 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ą w następujący sposób:
Usuwanie tabeli przestawnej
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak użyć elementu UpdateCellsRequest do usunięcia tabeli przestawnej (jeśli istnieje) zakotwiczonej na komórce A50 arkusza wskazanej przez atrybut SHEET_ID.
Element UpdateCellsRequest może usunąć tabelę przestawną, dodając element „tabela przestawna” w parametrze fields, a jednocześnie pomijając pole pivotTable w komórce zakotwiczonej.
Protokół żądania jest pokazany 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
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą UpdateCellsRequest edytować tabelę przestawną utworzoną w sekcji Dodawanie tabeli przestawnej.
Podzbiorów pola pivotTable w zasobie CellData nie można zmieniać pojedynczo za pomocą parametru fields. Aby można było wprowadzić zmiany, trzeba wypełnić całe pole pivotTable. Ogólnie rzecz biorąc, edytowanie tabeli przestawnej
wymaga zastąpienia jej nową tabelą.
Żądanie wprowadza następujące zmiany w oryginalnej tabeli przestawnej:
- Usuwa drugą grupę wierszy z pierwotnej tabeli przestawnej (Numer modelu).
- Dodaje grupę kolumn (Sprzedawca). Kolumny są sortowane w kolejności malejącej według łącznej liczby transakcji sprzedaży w panelu. „Carmen” (15 sprzedaży w panelu Panel) pojawia się po lewej stronie pozycji „Jessie” (13 sprzedaży w panelu Panel).
- Zwija kolumnę dla każdego regionu (oprócz „Zachodni”) i ukrywa grupę Sprzedawca dla danego regionu. Aby to zrobić, ustaw
collapsednatruew kolumnievalueMetadatadla tej kolumny w grupie kolumn Region.
Protokół żądania jest pokazany 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ą w następujący sposób:
Odczytywanie danych tabeli przestawnej
Poniższy przykładowy kod spreadsheets.get pokazuje, jak pobrać dane tabeli przestawnej z arkusza kalkulacyjnego. Parametr zapytania fields określa, że powinny zostać zwrócone tylko dane z tabeli przestawnej (w przeciwieństwie do danych wartości komórki).
Protokół żądania jest pokazany poniżej.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
Odpowiedź składa się z zasobu Spreadsheet, który zawiera obiekt Sheet z elementami SheetProperties. Dostępny jest też tablica elementów GridData z informacjami o PivotTable.
Informacje o tabeli przestawnej znajdują się w zasobie CellData arkusza dotyczącym komórki, w której jest zakotwiczona tabela (czyli w lewym górnym rogu tabeli). Jeśli pole odpowiedzi ma wartość domyślną, jest ono pomijane w odpowiedzi.
W tym przykładzie pierwszy arkusz (SOURCE_SHEET_ID) zawiera nieprzetworzone dane źródłowe tabeli, a drugi (SHEET_ID) zawiera tabelę przestawną zakotwiczoną w komórce B3. Puste nawiasy klamrowe wskazują arkusze lub komórki niezawierające danych tabeli przestawnej. W celach informacyjnych zwracamy 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
}
}
],
}