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ı
valueLayout
ayarı 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
valueBucket
alanı{}
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,
valueBucket
alanı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
valueLayout
değeriniVERTICAL
olarak ayarlar.valueLayout
yalnı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
valueMetadata
alanındacollapsed
öğesinintrue
olarak 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
} } ], }