Tabele przestawne

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 valueLayout są 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ść valueBucket do {}.
  • Jedna grupa kolumn (Region), która jest sortowana rosnąco według większości transakcji sprzedaży. valueBucket ma 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:

Dodaj wynik przepisu na tabelę przestawną

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 valueBucket z kolumny PivotGroup
  • 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 valueLayout na VERTICAL, aby poprawić wygląd tabeli. valueLayout to 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:

Dodaj wynik przepisu grupy wartości w tabeli przestawnej

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 collapsed na true w valueMetadata. 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:

Edytuj wynik przepisu tabeli przestawnej

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
      }
    }
  ],
}