Tabele przestawne

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 valueLayout są 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 valueBucket na {}.
  • Jedna grupa kolumn (Region), która jest sortowana rosnąco według największej sprzedaży. W tym przypadku valueBucket ma 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 wyniku przepisu na tabelę przestawną

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

Dodawanie wyniku przepisu na grupę wartości przestawnych

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ść collapsed na true w kolumnie valueMetadata 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 utworzy tabelę przestawną w ten sposób:

Edytowanie wyniku przepisu na tabelę przestawną

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