Interfejs API Arkuszy Google 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 mają postać żądań HTTP, które nie wpływają na język. 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 obiekt zastępczy SOURCE_SHEET_ID wskazuje arkusz z danymi źródłowymi. W przykładach ta tabela zawiera 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 są etykietami poszczególnych kolumn. Przykłady odczytywania innych arkuszy w arkuszu kalkulacyjnym znajdziesz w sekcji Zapis 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 r. |
| 5 | Rama | FR-0B1 | 34 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 USD | 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 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 |
| 14 | Silnik | ENG-0211 | 283 USD | 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 lipca 2016 r. |
| 17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hannah | 02.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 | Beata | 28.06.2016 r. |
| 20 | Rama | FR-0B1 | 34 USD | 8 | północ | Sheldon | 30.03.2016 r. |
Dodawanie tabeli przestawnej
Ten przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji UpdateCellsRequest utworzyć tabelę przestawną na podstawie danych źródłowych, przypinając ją do komórki A50 w arkuszu określonym przez zmienną SHEET_ID.
Żądanie konfiguruje tabelę przestawną za pomocą tych właściwości:
- Jedna grupa wartości (Quantity), 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 produktów i Numer modelu). Pierwsza opcja sortuje rosnąco wartość ilości z regionu „Zachód”. 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 posortowana w kolejności rosnącej według największej liczby sprzedaży.
Ponownie
valueBucketma wartość{}. „Północ” ma najmniejszą łączną sprzedaż, dlatego ta kolumna pojawia się jako pierwsza w kolumnie 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 Cena łączna). 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.
- Aby poprawić wygląd tabeli, żądanie ustawia
valueLayoutnaVERTICAL. WartośćvalueLayoutjest istotna tylko wtedy, gdy występują co najmniej 2 grupy wartości.
Protokół żądania jest widoczny 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
Z przykładowego kodu spreadsheets.batchUpdate dowiesz się, jak za pomocą kodu UpdateCellsRequest edytować tabelę przestawną utworzoną za pomocą opcji 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 oprócz „West” (Zachodnia część Stanów Zjednoczonych), ukrywając grupę Salesperson (Przedstawiciel handlowy) w tym regionie. 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:
Czytanie danych 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 powinny być tylko dane tabeli przestawnej (w przeciwieństwie do danych wartości komórek).
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
zawierających informacje o
PivotTable.
Informacje o tabeli przestawnej są zawarte w zasobie CellData arkusza w komórce, z którą jest ona zakotwiczona (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} } ], }