Pivot tablolar

Google Sheets API, e-tablolarda pivot tablolar oluşturmanıza ve güncellemenize olanak tanır. Bu sayfadaki örnekler, Sheets API ile bazı yaygın pivot tablo işlemlerini nasıl gerçekleştirebileceğinizi gösterir.

Bu örnekler, dilden bağımsız olması için HTTP istekleri şeklinde sunulur. Google API istemci kitaplıklarını kullanarak farklı dillerde toplu güncelleme işlemini nasıl uygulayacağınızı öğrenmek için E-tabloları güncelleme başlıklı makaleyi inceleyin.

Bu örneklerde, SPREADSHEET_ID ve SHEET_ID yer tutucuları, bu kimlikleri nerede sağlayacağınızı gösterir. E-tablo kimliğini e-tablo URL'sinde bulabilirsiniz. Sayfa kimliğini spreadsheets.get yöntemini kullanarak alabilirsiniz. Aralıklar A1 gösterimi kullanılarak belirtilir. Örnek bir aralık: Sayfa1!A1:D5.

Ayrıca, SOURCE_SHEET_ID yer tutucusu, kaynak verileri içeren sayfanızı gösterir. Bu örneklerde, Pivot tablo kaynak verileri altında listelenen tablo budur.

Pivot tablo kaynak verileri

Bu örneklerde, kullanılan e-tablonun ilk sayfasında ("Sayfa1") aşağıdaki kaynak "satış" verilerinin bulunduğunu varsayalım. İlk satırdaki dizeler, sütunların etiketleridir. E-tablonuzdaki diğer sayfalardan nasıl veri okunacağına dair örnekleri görmek için A1 notasyonu başlıklı makaleyi inceleyin.

A B C D E F G
1 Öğe Kategorisi Model Numarası Maliyet Miktar Bölge Satış görevlisi Kargo Tarihi
2 Tekerlek W-24 20,50 ABD doları 4 Batı Beth 01.03.2016
3 Kapı D-01X 15 TL 2 Güney Amir 15.03.2016
4 Motor ENG-0134 100,00 TL 1 Kuzey Carmen 20.03.2016
5 Çerçeve FR-0B1 34,00 ABD doları 8 Doğu Hannah 12.03.2016
6 Panel P-034 6,00 ABD doları 4 Kuzey Devyn 02.04.2016
7 Panel P-052 11,50 ABD doları 7 Doğu Erik 16.05.2016
8 Tekerlek W-24 20,50 ABD doları 11 Güney Sheldon 30.04.2016
9 Motor ENG-0161 $330,00 2 Kuzey Jessie 02.07.2016
10 Kapı D-01Y 29,00 ABD doları 6 Batı Armando 13.03.2016
11 Çerçeve FR-0B1 34,00 ABD doları 9 Güney Yuliana 27.02.2016
12 Panel P-102 3,00 ABD doları 15 Batı Carmen 18.04.2016
13 Panel P-105 8,25 ABD doları 13 Batı Jessie 20.06.2016
14 Motor ENG-0211 283,00 ABD doları 1 Kuzey Amir 21.06.2016
15 Kapı D-01X 15 TL 2 Batı Armando 03.07.2016
16 Çerçeve FR-0B1 34,00 ABD doları 6 Güney Carmen 15.07.2016
17 Tekerlek W-25 20,00 TL 8 Güney Hannah 02.05.2016
18 Tekerlek W-11 29,00 ABD doları 13 Doğu Erik 19.05.2016
19 Kapı D-05 17,70 ABD doları 7 Batı Beth 28.06.2016
20 Çerçeve FR-0B1 34,00 ABD doları 8 Kuzey Sheldon 30.03.2016

Pivot tablo ekleme

Aşağıdaki spreadsheets.batchUpdate kod örneğinde, SHEET_ID ile belirtilen sayfadaki A50 hücresine sabitlenmiş bir kaynak veriden pivot tablo oluşturmak için UpdateCellsRequest işlevinin nasıl kullanılacağı gösterilmektedir.

İstek, aşağıdaki özelliklerle pivot tabloyu yapılandırır:

  • Satış sayısını gösteren bir değer grubu (Miktar). Yalnızca bir değer grubu olduğundan, olası 2 valueLayout ayarı eşdeğerdir.
  • İki satır grubu (Öğe Kategorisi ve Model Numarası). İlk sıralama, "Batı" Bölgesi'ndeki toplam Miktar'ın artan değerine göre yapılır. Bu nedenle, "Motor" (Batı bölgesinde satış yok) "Kapı"nın (Batı bölgesinde 15 satış) üzerinde gösterilir. Model Numarası grubu, tüm bölgelerdeki toplam satışlara göre azalan düzende sıralanır. Bu nedenle "W-24" (15 satış), "W-25"in (8 satış) üzerinde görünür. Bu işlem, valueBucket alanının {} olarak ayarlanmasıyla yapılır.
  • En çok satışa göre artan düzende sıralanan bir sütun grubu (Bölge). Yine valueBucket, {} olarak ayarlanır. "Kuzey" en düşük toplam satışa sahip olduğundan ilk Bölge sütunu olarak görünür.

İstek protokolü aşağıda gösterilmektedir.

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

İstek, aşağıdaki gibi bir pivot tablo oluşturur:

Pivot tablo tarifi sonucu ekleme

Hesaplanmış değerler içeren bir pivot tablo ekleme

Aşağıdaki spreadsheets.batchUpdate kod örneğinde, kaynak verilerden hesaplanmış değerler grubu içeren bir pivot tablo oluşturmak için UpdateCellsRequest işlevinin nasıl kullanılacağı gösterilmektedir. Bu tablo, SHEET_ID ile belirtilen sayfadaki A50 hücresine sabitlenir.

İstek, aşağıdaki özelliklerle pivot tabloyu yapılandırır:

  • İki değer grubu (Miktar ve Toplam Fiyat). İlk değer, satış sayısını gösterir. İkincisi ise bir parçanın maliyeti ile toplam satış sayısının çarpımına dayalı olarak hesaplanan bir değerdir ve şu formül kullanılır: =Cost*SUM(Quantity).
  • Üç satır grubu (Öğe Kategorisi, Model Numarası ve Maliyet).
  • Bir sütun grubu (Bölge).
  • Satır ve sütun grupları, her grupta ada göre (Miktar'a göre değil) sıralanır ve tablo alfabetik olarak düzenlenir. Bu işlem, valueBucket alanını PivotGroup öğesinden çıkararak yapılır.
    • Tablonun görünümünü basitleştirmek için istek, ana satır ve sütun grupları dışındaki tüm grupların alt toplamlarını gizler.
  • İstek, tablo görünümünü iyileştirmek için valueLayout değerini VERTICAL olarak ayarlar. valueLayout yalnızca 2 veya daha fazla değer grubu varsa önemlidir.

İstek protokolü aşağıda gösterilmektedir.

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

İstek, aşağıdaki gibi bir pivot tablo oluşturur:

Pivot değerleri grup tarifi sonucu ekleme

Pivot tabloyu silme

Aşağıdaki spreadsheets.batchUpdate kod örneğinde, SHEET_ID ile belirtilen sayfadaki A50 hücresine sabitlenmiş bir pivot tabloyu (varsa) silmek için UpdateCellsRequest yönteminin nasıl kullanılacağı gösterilmektedir.

Bir UpdateCellsRequest, fields parametresine "pivotTable"ı ekleyerek ve aynı zamanda sabitleme hücresindeki pivotTable alanını atlayarak bir pivot tabloyu kaldırabilir.

İstek protokolü aşağıda gösterilmektedir.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
 
{
  "requests": [
    {
      "updateCells": {
          "rows": [ 
            {
            "values": [
              {}
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

Pivot tablo sütunlarını ve satırlarını düzenleme

Aşağıdaki spreadsheets.batchUpdate kod örneğinde, Pivot tablo ekleme bölümünde oluşturulan pivot tabloyu düzenlemek için UpdateCellsRequest işlevinin nasıl kullanılacağı gösterilmektedir.

CellData kaynağındaki pivotTable alanının alt kümeleri, fields parametresiyle ayrı ayrı değiştirilemez. Düzenleme yapmak için pivotTable alanının tamamı sağlanmalıdır. Pivot tabloyu düzenlemek için yeni bir tabloyla değiştirmeniz gerekir.

İstek, orijinal pivot tabloda aşağıdaki değişiklikleri yapar:

  • İkinci satır grubunu orijinal pivot tablodan (Model Numarası) kaldırır.
  • Sütun grubu (Satış görevlisi) ekler. Sütunlar, toplam Panel satış sayısına göre azalan düzende sıralanır. "Carmen" (15 Panel satışı), "Jessie"nin (13 Panel satışı) solunda gösterilir.
  • "Batı" hariç her Bölge için sütunu daraltarak söz konusu bölgedeki Satış elemanı grubunu gizler. Bu işlem, Bölge sütun grubundaki ilgili sütun için valueMetadata içinde collapsed değerini true olarak ayarlayarak yapılır.

İstek protokolü aşağıda gösterilmektedir.

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

İstek, aşağıdaki gibi bir pivot tablo oluşturur:

Pivot tablo tarifi sonucunu düzenleme

Pivot tablo verilerini okuma

Aşağıdaki spreadsheets.get kod örneğinde, e-tablodan pivot tablo verilerinin nasıl alınacağı gösterilmektedir. fields sorgu parametresi, yalnızca pivot tablo verilerinin döndürülmesi gerektiğini belirtir (hücre değeri verilerinin aksine).

İstek protokolü aşağıda gösterilmektedir.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

Yanıt, Spreadsheet kaynağıyla SheetProperties öğelerini içeren bir Sheet nesnesinden oluşur. Ayrıca, GridData öğeleri dizisi de vardır. Bu öğeler, PivotTable hakkında bilgi içerir. Pivot tablo bilgileri, tablonun sabitlendiği hücre için sayfanın CellData kaynağında (yani tablonun sol üst köşesinde) yer alır. Bir yanıt alanı varsayılan değere ayarlanırsa yanıttan çıkarılır.

Bu örnekte, ilk sayfada (SOURCE_SHEET_ID) ham tablo kaynak verileri, ikinci sayfada (SHEET_ID) ise B3'e sabitlenmiş pivot tablo yer alıyor. Boş küme parantezleri, pivot tablo verisi içermeyen sayfaları veya hücreleri gösterir. Bu istek, referans olarak sayfa kimliklerini de döndürür.

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