Interfejs Google Sheets API umożliwia tworzenie i aktualizowanie tabel przestawnych w arkuszach kalkulacyjnych. Przykłady na tej stronie pokazują, jak za pomocą interfejsu Sheets API można wykonywać niektóre typowe operacje na tabelach przestawnych.
Przykłady te są przedstawione w formie żądań HTTP, aby były niezależne od języka. Aby dowiedzieć się, jak zaimplementować aktualizację zbiorczą w różnych językach za pomocą bibliotek klienta interfejsu API Google, przeczytaj artykuł Aktualizowanie arkuszy kalkulacyjnych.
W tych przykładach symbole 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ć za pomocą metody spreadsheets.get. Zakresy są określone w notacji A1. Przykładowy zakres to Arkusz1!A1:D5.
Symbol zastępczy SOURCE_SHEET_ID oznacza 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
W tych przykładach załóżmy, że używany arkusz kalkulacyjny zawiera w pierwszym arkuszu („Arkusz1”) dane źródłowe „sprzedaż”. Ciągi znaków w pierwszym wierszu to etykiety poszczególnych kolumn. Przykłady odczytywania danych z innych arkuszy w arkuszu kalkulacyjnym znajdziesz w artykule 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 USD | 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 r. |
| 6 | Panel | P-034 | 18 PLN | 4 | północ | Devyn | 2.04.2016 |
| 7 | Panel | P-052 | 11,50 USD | 7 | wschód | Erik | 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 | Armando | 13.03.2016 |
| 11 | Rama | FR-0B1 | 34 USD | 9 | południe | Yuliana | 27.02.2016 r. |
| 12 | Panel | P-102 | 3 USD | 15 | zachód | Carmen | 18.04.2016 r. |
| 13 | Panel | P-105 | 8,25 USD | 13 | zachód | Jessie | 20.06.2016 r. |
| 14 | Silnik | ENG-0211 | 283 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 USD | 6 | południe | Carmen | 15.07.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 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 USD | 8 | północ | Sheldon | 30.03.2016 r. |
Dodawanie tabeli przestawnej
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak użyć funkcji UpdateCellsRequest do utworzenia tabeli przestawnej na podstawie danych źródłowych i umieszczenia jej w komórce A50 arkusza określonego przez SHEET_ID.
Żądanie konfiguruje tabelę przestawną z tymi właściwościami:
- Jedna 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). Pierwsze sortowanie odbywa się według rosnącej wartości łącznej ilości z regionu „Zachód”. Dlatego „Silnik” (bez sprzedaży na Zachodzie) pojawia się nad „Drzwiami” (z 15 sprzedażami na Zachodzie). Grupa Numer modelu jest sortowana w kolejności malejącej według łącznej sprzedaży we wszystkich regionach, więc „W-24” (15 transakcji sprzedaży) znajduje się nad „W-25” (8 transakcji sprzedaży). Możesz to zrobić, ustawiając pole
valueBucketna{}. - Jedna grupa kolumn (Region), która jest sortowana rosnąco według największej sprzedaży.
W tym przypadku
valueBucketma wartość{}. „Północ” ma najmniejszą łączną sprzedaż, dlatego pojawia się jako pierwsza kolumna Region.
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", "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 utworzy tabelę przestawną w ten sposób:
Dodawanie tabeli przestawnej z obliczonymi wartościami
Poniższy przykładowy kod
spreadsheets.batchUpdate
pokazuje, jak za pomocą
UpdateCellsRequest
utworzyć tabelę przestawną z grupą wartości obliczonych na podstawie danych źródłowych, zakotwiczając ją w komórce A50 arkusza określonego przez SHEET_ID.
Żądanie konfiguruje tabelę przestawną z tymi właściwościami:
- 2 grupy wartości (Ilość i Cena całkowita). Pierwsza z nich wskazuje liczbę sprzedaży. Druga to obliczona wartość na podstawie iloczynu kosztu części i łącznej liczby sprzedaży, obliczona według tego 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 ilości) w każdej grupie, co powoduje alfabetyzację tabeli. Aby to zrobić, pomiń pole
valueBucketwPivotGroup.- Aby uprościć wygląd tabeli, żądanie ukrywa sumy częściowe dla wszystkich grup wierszy i kolumn z wyjątkiem głównych.
- Żądanie ustawia wartość
valueLayoutnaVERTICAL, aby poprawić wygląd tabeli.valueLayoutma znaczenie tylko wtedy, gdy istnieją 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 utworzy tabelę przestawną w ten sposób:
Usuwanie tabeli przestawnej
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą metody UpdateCellsRequest usunąć tabelę przestawną (jeśli istnieje), która jest zakotwiczona w komórce A50 arkusza określonego przez SHEET_ID.
UpdateCellsRequest może usunąć tabelę przestawną, umieszczając „pivotTable” w parametrze fields i pomijając pole pivotTable w komórce zakotwiczenia.
Protokół żądania jest widoczny 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 używać
UpdateCellsRequest
do edytowania tabeli przestawnej utworzonej w artykule Dodawanie tabeli przestawnej.
Podzbiorów pola pivotTable w zasobie CellData nie można zmieniać pojedynczo za pomocą parametru fields. Aby wprowadzić zmiany, musisz podać całe pole pivotTable. Edytowanie tabeli przestawnej wymaga zastąpienia jej nową.
Żądanie wprowadza w pierwotnej tabeli przestawnej te zmiany:
- Usuwa drugą grupę wierszy z pierwotnej tabeli przestawnej (Numer modelu).
- Dodaje grupę kolumn Sprzedawca. Kolumny są sortowane malejąco według łącznej liczby sprzedaży paneli. „Carmen” (15 paneli) pojawi się po lewej stronie „Jessie” (13 paneli).
- Zwija kolumnę dla każdego regionu z wyjątkiem regionu „Zachód”, ukrywając grupę Sprzedawca w tym regionie. Aby to zrobić, ustaw wartość
collapsednatruew kolumnievalueMetadataw grupie kolumn Region.
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", "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 utworzy tabelę przestawną w ten sposób:
Odczytywanie danych z tabeli przestawnej
Poniższy przykład koduspreadsheets.get pokazuje, jak pobrać dane z tabeli przestawnej z arkusza kalkulacyjnego. Parametr zapytania fields określa, że mają być zwracane tylko dane tabeli przestawnej (w przeciwieństwie do danych wartości komórek).
Protokół żądania jest widoczny 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. Jest też tablica elementów GridData zawierających informacje o PivotTable.
Informacje o tabeli przestawnej znajdują się w zasobie CellData arkusza w przypadku komórki, do której jest ona przypięta (czyli lewego górnego rogu tabeli). Jeśli pole odpowiedzi ma wartość domyślną, jest pomijane w odpowiedzi.
W tym przykładzie pierwszy arkusz (SOURCE_SHEET_ID) zawiera źródłowe dane tabeli pierwotnej, a drugi arkusz (SHEET_ID) zawiera tabelę przestawną zakotwiczoną w komórce B3. Puste nawiasy klamrowe wskazują arkusze lub komórki, które nie zawierają danych tabeli przestawnej. W celach informacyjnych to żądanie zwraca 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} } ], }