Interfejs API Arkuszy Google umożliwia tworzenie i aktualizowanie tabel przestawnych w arkuszach kalkulacyjnych. Przykłady na tej stronie pokazują, jak utworzyć jedną z popularnych tabel przestawnych za pomocą interfejsu Arkuszy API.
Przykłady te są przedstawione w formie żądań HTTP, które stanowią język. i nie mam zdania. Aby dowiedzieć się, jak wdrożyć aktualizację zbiorczą w różnych językach za pomocą bibliotek klienta interfejsów API Google, przeczytaj artykuł Aktualizacja arkusze kalkulacyjne.
W tych przykładach zmienne SPREADSHEET_ID i SHEET_ID
wskazuje, gdzie należy je podać. Arkusz kalkulacyjny można znaleźć
ID w adresie URL arkusza kalkulacyjnego. Możesz uzyskać
identyfikator arkusza za pomocą funkcji
spreadsheets.get.
zakresy określa się w notacji A1. An
przykładowy zakres to Arkusz1!A1:D5.
Dodatkowo obiekt zastępczy SOURCE_SHEET_ID wskazuje Twój arkusz.
z danymi źródłowymi. W tych przykładach jest to tabela wymieniona w sekcji Tabela przestawna
danych źródłowych tabeli.
Dane źródłowe tabeli przestawnej
W tych przykładach załóżmy, że używany arkusz kalkulacyjny ma następujące źródło „sprzedaż” danych w pierwszym arkuszu („Arkusz1”). Ciągi w pierwszym wierszu są etykiety poszczególnych kolumn. Aby zobaczyć przykłady czytania innych tekstów arkuszy w arkuszu kalkulacyjnym, przeczytaj opis 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 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 | Carmen | 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 | Devyn | 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 | 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 | 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 | Carmen | 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żej
spreadsheets.batchUpdate
przykładowy kod pokazuje, jak używać parametru
UpdateCellsRequest
aby utworzyć tabelę przestawną z danych źródłowych i umieścić ją w komórce A50
arkusz określony przez 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. Od
jest tylko jedna grupa wartości, dwie możliwe
valueLayoutsą takie same. - 2 grupy wierszy (Kategoria produktów i Numer modelu). Pierwsze określa
rosnąco wartości łącznej ilości z regionu „Zachodnia” Region. Dlatego
„Silnik” (bez sprzedaży na zachód) nad „Drzwiami”. (przy 15 transakcjach sprzedaży zachodniej).
Grupa Numer modelu sortuje w kolejności malejącej łącznej sprzedaży we wszystkich
regionów, więc „W-24” (15 transakcji sprzedaży) pojawia się nad „W-25” (8 transakcji sprzedaży). Gotowe
Ustaw wartość
valueBucketdo{}. - Jedna grupa kolumn (Region), która jest sortowana rosnąco według większości transakcji sprzedaży.
valueBucketma wartość{}. „Północ” generuje najmniejszą łączną sprzedaż, więc wyświetla 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 tworzy tabelę przestawną w podobny sposób:
Dodawanie tabeli przestawnej z obliczonymi wartościami
Poniżej
spreadsheets.batchUpdate
przykładowy kod pokazuje, jak używać parametru
UpdateCellsRequest
aby utworzyć tabelę przestawną z grupą wartości obliczonych z danych źródłowych,
zakotwiczenie w komórce A50 arkusza określonego przez 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
i liczbę transakcji sprzedaży. Druga to wartość obliczana na podstawie iloczynu
kosztów części i łącznej liczby transakcji sprzedaży 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ść) w każdym
w kolejności alfabetycznej. Odbywa się to poprzez pominięcie
valueBucketz kolumnyPivotGroup - Aby uprościć wygląd tabeli, żądanie ukrywa sum częściowe dla wszystkich grup wierszy i kolumn oprócz głównych grup wierszy.
- Żądanie ustawia
valueLayoutnaVERTICAL, aby poprawić wygląd tabeli.valueLayoutto tylko jest ważne, jeśli 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żej
spreadsheets.batchUpdate
przykładowy kod pokazuje, jak używać parametru
UpdateCellsRequest
aby usunąć tabelę przestawną (jeśli istnieje) zakotwiczoną w komórce A50 arkusza
określone przez SHEET_ID.
Element UpdateCellsRequest może usunąć tabelę przestawną, dodając do niej element „tabela przestawna” cale
parametr fields, pomijając pole pivotTable w reklamie zakotwiczonej.
komórki.
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żej
spreadsheets.batchUpdate
przykładowy kod pokazuje, jak używać parametru
UpdateCellsRequest
aby edytować tabelę przestawną utworzoną w opcji Dodaj tabelę przestawną.
Podzbiory
pivotTable
w polu
CellData zasób
nie można zmienić pojedynczo za pomocą parametru fields. Aby wprowadzić zmiany,
należy wypełnić całe pole pivotTable. Zasadniczo edytowanie tabeli przestawnej
wymaga wymiany.
Żą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 łączną liczbę transakcji sprzedaży za pomocą panelu. „Carmen” (15 paneli) po lewej stronie „Jessie” (13 paneli sprzedaży).
- Zwija kolumnę dla każdego regionu z wyjątkiem „Zachód”, ukrywając
Pracownik działu sprzedaży w tym regionie. Można to zrobić, ustawiając
collapsednatruewvalueMetadata. dla tej kolumny w 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żej
spreadsheets.get – przykładowy kod
pokazuje, jak pobrać dane tabeli przestawnej z arkusza kalkulacyjnego. Zapytanie fields
określa, że powinny zostać zwrócone tylko dane tabeli przestawnej (jako
w przeciwieństwie do 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
Spreadsheet
, który zawiera
Sheet obiekt z
SheetProperties
. Jest też tablica
GridData
elementów zawierających informacje o
PivotTable
Informacje o tabeli przestawnej są zawarte w kodzie arkusza
CellData zasób
dla komórki, na której jest zakotwiczona tabela (tzn. lewy górny róg tabeli
w rogu ekranu). Jeśli pole odpowiedzi ma wartość domyślną, jest ono pomijane w
.
W tym przykładzie pierwszy arkusz (SOURCE_SHEET_ID) zawiera tabelę nieprzetworzoną
dane źródłowe, a drugi arkusz (SHEET_ID) zawiera tabelę przestawną,
zakotwiczonych na B3. Puste nawiasy klamrowe wskazują arkusze lub komórki, które nie zawierają
zawierają dane tabeli przestawnej. To żądanie zwraca też arkusz.
Identyfikatory.
{
"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
}
}
],
}