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 Google można wykonać niektóre typowe operacje na tabeli przestawnej.
Przykłady te mają postać żądań HTTP, które nie wpływają na język. Aby dowiedzieć się, jak wdrożyć aktualizację zbiorczą w różnych językach za pomocą bibliotek klienta interfejsu API Google, przeczytaj artykuł o aktualizowaniu arkuszy kalkulacyjnych.
W tych przykładach obiekty zastępcze SPREADSHEET_ID i SHEET_ID wskazują, gdzie należy podać te identyfikatory. Ten identyfikator arkusza kalkulacyjnego znajdziesz w adresie URL arkusza kalkulacyjnego. Identyfikator arkusza możesz uzyskać za pomocą metody spreadsheets.get. Zakresy określa się w 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.
Dane źródłowe tabeli przestawnej
W tych przykładach załóżmy, że pierwszy arkusz („Arkusz1”) zawiera podane niżej dane źródłowe o sprzedaży. 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 | Sprzedawca | Data wysyłki |
| 2 | Diabelski młyn | W-24 | 20,50 PLN | 4 | zachód | Beata | 01.03.2016 r. |
| 3 | Drzwi | D-01X | 15 zł | 2 | południe | Amir | 15.03.2016 r. |
| 4 | Silnik | ENG-0134 | 300 PLN | 1 | północ | Karolina | 20.03.2016 r. |
| 5 | Rama | FR-0B1 | 34 USD | 8 | wschód | Hanna | 12.03.2016 r. |
| 6 | Panel | P-034 | 18 PLN | 4 | północ | Dewin | 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 PLN | 11 | południe | Sheldon | 30.04.2016 r. |
| 9 | Silnik | ENG-0161 | 330 USD | 2 | północ | Joanna | 02.07.2016 r. |
| 10 | Drzwi | D-01Y | 29 USD | 6 | zachód | Armando | 13.03.2016 r. |
| 11 | Rama | FR-0B1 | 34 USD | 9 | południe | Juliana | 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 | Joanna | 20.06.2016 r. |
| 14 | Silnik | ENG-0211 | 283 USD | 1 | północ | Amir | 21.06.2016 r. |
| 15 | Drzwi | D-01X | 15 zł | 2 | zachód | Armando | 03.07.2016 r. |
| 16 | Rama | FR-0B1 | 34 USD | 6 | południe | Karolina | 15 lipca 2016 r. |
| 17 | Diabelski młyn | W-25 | 20,00 USD | 8 | południe | Hanna | 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 PLN | 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
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą kodu UpdateCellsRequest utworzyć tabelę przestawną z danych źródłowych i zakotwiczyć ją w komórce A50 arkusza wskazanej przez metodę SHEET_ID.
Żądanie konfiguruje tabelę przestawną przy użyciu tych właściwości:
- Jedna grupa wartości (Quantity), 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 (Kategoria produktów i Numer modelu). Pierwsza opcja sortuje rosnąco wartość ilości z regionu „Zachód”. W związku z tym nad „Drzwiami” wyświetla się „Silnik” (bez sprzedaży na zachód) (przy 15 transakcjach sprzedaży w zachodnim kraju). Grupa Numer modelu jest sortowana malejąco według łącznej wartości sprzedaży we wszystkich regionach, więc „W-24” (15 transakcji sprzedaży) pojawia się nad „W-25” (8 sprzedaży). W tym celu ustaw pole
valueBucketna{}. - Jedna grupa kolumn (Region), która jest sortowana rosnąco według większości transakcji sprzedaży.
valueBucketma wartość{}. Kolumna „Północ” ma najmniejszą łączną sprzedaż, więc pojawia się w pierwszej kolumnie 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 tworzy tabelę przestawną w podobny sposób:
Dodawanie tabeli przestawnej z obliczonymi wartościami
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą kodu UpdateCellsRequest utworzyć tabelę przestawną z grupą wartości obliczonych z danych źródłowych i zakotwiczyć ją w komórce A50 arkusza wskazanej przez metodę SHEET_ID.
Żądanie konfiguruje tabelę przestawną przy użyciu tych właściwości:
- 2 grupy wartości (Quantity (Ilość) i Total Price (Cena całkowita). Pierwszy wskazuje liczbę transakcji sprzedaży. Druga to wartość obliczana na podstawie iloczynu kosztów części i całkowitej liczby transakcji sprzedaży przy użyciu tego wzoru:
=Cost*SUM(Quantity). - 3 grupy wierszy (Kategoria produktu, Numer modelu i Koszt).
- Jedna grupa kolumn (Region).
- Grupy wierszy i kolumn są sortowane w każdej grupie według nazwy (a nie według Ilość) w kolejności alfabetycznej. Należy pominąć pole
valueBucketwPivotGroup. - Aby uprościć wygląd tabeli, żądanie ukrywa sum częściowe dla wszystkich wierszy i grup kolumn oprócz głównego.
- Aby poprawić wygląd tabeli, żądanie ustawia
valueLayoutnaVERTICAL. ElementvalueLayoutjest ważny tylko wtedy, gdy masz 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ą w podobny sposób:
Usuwanie tabeli przestawnej
Poniższy przykładowy kod spreadsheets.batchUpdate pokazuje, jak za pomocą wiersza UpdateCellsRequest usunąć tabelę przestawną (jeśli istnieje) zakotwiczoną w komórce A50 arkusza wskazanej przez parametr SHEET_ID.
Za pomocą UpdateCellsRequest można usunąć tabelę przestawną, dodając „Tabela przestawną” w parametrze fields i pomijając pole pivotTable w komórce zakotwiczonej.
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
Z przykładowego kodu spreadsheets.batchUpdate dowiesz się, jak za pomocą kodu UpdateCellsRequest edytować tabelę przestawną utworzoną za pomocą opcji 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, należy wypełnić całe pole pivotTable. Aby edytować tabelę przestawną, trzeba ją zastąpić nową.
Żądanie wprowadza te zmiany w oryginalnej tabeli przestawnej:
- Usuwa drugą grupę wierszy z pierwotnej tabeli przestawnej (Model Number).
- Dodaje grupę kolumn (Pracownik działu sprzedaży). Kolumny są sortowane malejąco według łącznej liczby transakcji sprzedaży paneli. „Carmen” (15 sprzedaży Panel) pojawia się po lewej stronie pozycji „Jessie” (13 sprzedaży Panel).
- Zwija kolumnę dla każdego regionu z wyjątkiem „Zachód” i ukrywa grupę Sprzedawca dla danego regionu. 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 tworzy tabelę przestawną w podobny 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 zwracane powinny być 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. Istnieje też tablica elementów GridData z informacjami 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 ono pomijane w odpowiedzi.
W tym przykładzie pierwszy arkusz (SOURCE_SHEET_ID) zawiera nieprzetworzone dane źródłowe tabeli, 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. Żądanie to 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
}
}
],
}