Google E-Tablolar API'sı, e-tablolarda pivot tablolar oluşturmanıza ve bunları 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östermektedir.
Bu örnekler, dilden bağımsız olarak HTTP istekleri şeklinde sunulur. Google API istemci kitaplıklarını kullanarak farklı dillerde toplu güncelleme yapmayı öğrenmek için E-tabloları güncelleme bölümüne bakın.
Bu örneklerde SPREADSHEET_ID ve SHEET_ID yer tutucuları, bu kimlikleri nerede sağlayacağınızı belirtir. E-tablo kimliğini e-tablonun URL'sinde bulabilirsiniz. Sayfa kimliğini, spreadsheets.get yöntemini kullanarak alabilirsiniz. Aralıklar, A1 gösterimi kullanılarak belirtilir. Örneğin Sayfa1!A1:D5.
Ayrıca SOURCE_SHEET_ID yer tutucusu, kaynak verilerin bulunduğu sayfanızı belirtir. Aşağıdaki örneklerde bu, Pivot tablo kaynak verileri bölümünde listelenen tablodur.
Pivot tablo kaynak verileri
Bu örneklerde, kullanılan e-tablonun ilk sayfasında aşağıdaki kaynak "satış" verilerinin ("Sayfa1") bulunduğunu varsayalım. İlk satırdaki dizeler, bağımsız sütunlar için etiketlerdir. E-tablonuzdaki diğer sayfalardan nasıl okuma yapacağınıza ilişkin örnekleri görmek için A1 gösterimine bakın.
| CEVAP | B | C | G | E | F | Y | |
| 1 | Öğe Kategorisi | Model Numarası | Maliyet | Miktar | Bölge | Satış görevlisi | Gönderim Tarihi |
| 2 | Dönme dolap | W-24 | 20,50 TL | 4 | Batı | Banu | 01.03.2016 |
| 3 | Kapı | D-01X | 15 TL | 2 | Güney | Emir | 15.03.2016 |
| 4 | Motor | ENG-0134 | 100,00 TL | 1 | Kuzey | Carmen Dili | 20.03.2016 |
| 5 | Çerçeve | FR-0B1 | 34,00 TL | 8 | Doğu | Hannah | 12.03.2016 |
| 6 | Panel | P-034 | 6,00 ABD doları | 4 | Kuzey | Deniz | 02.04.2016 |
| 7 | Panel | P-052 | 11,50 TL | 7 | Doğu | Eren | 16.05.2016 |
| 8 | Dönme dolap | W-24 | 20,50 TL | 11 | Güney | Sholdon | 30.04.2016 |
| 9 | Motor | ENG-0161 | 330,00 TL | 2 | Kuzey | Jale | 02.07.2016 |
| 10 | Kapı | G-01Y | 29,00 TL | 6 | Batı | Armando | 13.03.2016 |
| 11 | Çerçeve | FR-0B1 | 34,00 TL | 9 | Güney | Yuliana | 27.02.2016 |
| 12 | Panel | P-102 | 3,00 ABD doları | 15 | Batı | Carmen Dili | 18.04.2016 |
| 13 | Panel | P-105 | 8,25 TL | 13 | Batı | Jale | 20.06.2016 |
| 14 | Motor | ENG-0211 | 283,00 TL | 1 | Kuzey | Emir | 21.06.2016 |
| 15 | Kapı | D-01X | 15 TL | 2 | Batı | Armando | 03.07.2016 |
| 16 | Çerçeve | FR-0B1 | 34,00 TL | 6 | Güney | Carmen Dili | 15.07.2016 |
| 17 | Dönme dolap | W-25 | 20,00 TL | 8 | Güney | Hannah | 02.05.2016 |
| 18 | Dönme dolap | W-11 | 29,00 TL | 13 | Doğu | Eren | 19.05.2016 |
| 19 | Kapı | D-05 | 17,70 TL | 7 | Batı | Banu | 28.06.2016 |
| 20 | Çerçeve | FR-0B1 | 34,00 TL | 8 | Kuzey | Sholdon | 30.03.2016 |
Pivot tablo ekleme
Aşağıdaki spreadsheets.batchUpdate kod örneğinde, SHEET_ID tarafından belirtilen sayfanın A50 hücresine sabitlenerek kaynak verilerden pivot tablo oluşturmak için UpdateCellsRequest öğesinin nasıl kullanılacağı gösterilmektedir.
İstek, pivot tabloyu aşağıdaki özelliklerle yapılandırır:
- Satış sayısını gösteren bir değer grubu (Miktar). Yalnızca bir değer grubu olduğundan 2 olası
valueLayoutayarı eşdeğerdir. - İki satır grubu (Öğe Kategorisi ve Model Numarası). İlki, "Batı" Bölgesi'nden toplam Miktar'ın artan değerine göre sıralanır. Bu nedenle, "Motor" (Batı satışları olmadan) "Kapı"nın üzerinde görünür (Batı'da 15 satışla). Model Numarası grubu, tüm bölgelerdeki toplam satışları azalan düzende sıralar. Dolayısıyla, "W-25"in (8 satış) üzerinde "W-24" (15 satış) görünür. Bu işlem
valueBucketalanı{}olarak ayarlanarak yapılır. - En çok satışa göre artan düzende sıralayan bir sütun grubu (Bölge).
Yine
valueBucket,{}olarak ayarlandı. "Kuzey", en az toplam satışa sahiptir. Bu nedenle, ilk Bölge sütunu olarak görünür.
İstek protokolü aşağıda gösterilmiştir.
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:
Hesaplanmış değerlere sahip 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 öğesinin nasıl kullanılacağı gösterilmektedir. Bu tablo, SHEET_ID tarafından belirtilen e-tablonun A50 hücresine sabitlenmiştir.
İstek, pivot tabloyu aşağıdaki özelliklerle yapılandırır:
- İki değer grubu (quantity ve Total Price). İlki satış sayısını gösterir. İkincisi, şu formül kullanılarak parçanın maliyetinin ve toplam satış sayısının çarpımına bağlı olarak hesaplanan bir değerdir:
=Cost*SUM(Quantity). - Üç satır grubu (Öğe Kategorisi, Model Numarası ve Maliyet).
- Bir sütun grubu (Bölge).
- Satır ve sütun grupları, tabloyu alfabetik olarak her grupta ada göre (Miktar yerine) sıralar. Bu işlem,
valueBucketalanıPivotGroupöğesinden çıkarılarak yapılır. - Tablo görünümünü basitleştirmek için istek, ana satır ve sütun grupları hariç tümü için alt toplamları gizler.
- İstek, daha iyi bir tablo görünümü için
valueLayoutdeğeriniVERTICALolarak ayarlar.valueLayoutyalnızca 2 veya daha fazla değer grubu varsa önemlidir.
İstek protokolü aşağıda gösterilmiştir.
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 tablo silme
Aşağıdaki spreadsheets.batchUpdate kod örneğinde, SHEET_ID tarafından belirtilen sayfanın A50 hücresine bağlı bir pivot tabloyu (varsa) silmek için UpdateCellsRequest öğesinin nasıl kullanılacağı gösterilmektedir.
UpdateCellsRequest, hem fields parametresine "pivotTable"ı dahil ederek bir pivot tabloyu hem de bağlantı hücresindeki pivotTable alanını çıkararak bir pivot tabloyu kaldırabilir.
İstek protokolü aşağıda gösterilmiştir.
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 öğesinin nasıl kullanılacağı gösterilmektedir.
CellData kaynağındaki pivotTable alanının alt kümeleri, fields parametresiyle tek tek değiştirilemez. Düzenleme yapmak için pivotTable alanının tamamı sağlanmalıdır. Esasen, bir pivot tabloyu düzenlemek için
yeni bir pivot tablonun değiştirilmesi gerekir.
İstek, orijinal pivot tabloda aşağıdaki değişiklikleri yapar:
- İkinci satır grubunu orijinal pivot tablodan kaldırır (Model Numarası).
- Bir sütun grubu (Satış görevlisi) ekler. Sütunlar, toplam Panel satış sayısına göre azalan düzende sıralanır. "Cemre"nin (13 Panel satışı) solunda "Carmen" (15 Panel satış) görünür.
- "Batı" dışındaki her Bölge'nin sütununu daraltarak Satış görevlisi grubunu söz konusu bölge için gizler. Bu işlem, Bölge sütun grubundaki ilgili sütun için
valueMetadataalanındacollapsedöğesinintrueolarak ayarlanmasıyla yapılır.
İstek protokolü aşağıda gösterilmiştir.
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 verilerini okuma
Aşağıdaki spreadsheets.get kod örneğinde, pivot tablo verilerinin bir e-tablodan nasıl alınacağı gösterilmektedir. fields sorgu parametresi, (hücre değeri verilerinin aksine) yalnızca pivot tablo verilerinin döndürülmesi gerektiğini belirtir.
İstek protokolü aşağıda gösterilmiştir.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
Yanıt, SheetProperties öğesine sahip bir Sheet nesnesi içeren bir Spreadsheet kaynağından oluşur. Ayrıca PivotTable hakkında bilgi içeren bir GridData öğeleri dizisi de bulunur.
Pivot tablo bilgileri, tablonun bağlı olduğu hücre için (yani tablonun sol üst köşesinde) sayfanın CellData kaynağında 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 üzerinde sabitlenmiş pivot tablo yer almaktadır. Boş küme parantezleri, pivot tablo verileri içermeyen sayfaları veya hücreleri gösterir. Bu istek, referans için e-tablo 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
}
}
],
}