Google E-Tablolar API'si, e-tablolarda pivot tablolar oluşturmanıza ve güncellemenize olanak tanır. Bu sayfadaki örneklerde, E-Tablolar API'yi kullanarak bazı yaygın pivot tablo işlemlerini nasıl gerçekleştirebileceğiniz gösterilmektedir.
Bu örnekler, dilsiz olması için HTTP istekleri biçiminde sunulur. Google API istemci kitaplıklarını kullanarak farklı dillerde toplu güncellemenin nasıl uygulanacağını öğrenmek için E-tabloları güncelleme bölümüne bakın.
Bu örneklerde SPREADSHEET_ID ve SHEET_ID yer tutucuları, bu kimlikleri nereye gireceğinizi 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. Sayfa1!A1:D5 örnek aralıktır.
Ayrıca, SOURCE_SHEET_ID yer tutucusu, sayfanızı kaynak verilerle birlikte gösterir. Bu örneklerde, Pivot tablo kaynak verileri bölümünde listelenen tablo budur.
Pivot tablo kaynak verileri
Bu örnekler için, kullanılan e-tablonun ilk sayfasında ("Sheet1") aşağıdaki kaynak "sales" (satış) verileri 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örüntülemek için A1 notasyonuna bakın.
| CEVAP | B | C | D | E | F | G | |
| 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 ABD doları | 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 ABD doları | 8 | Doğu | Hannah | 12.03.2016 |
| 6 | Panel | P-034 | 6,00 ABD doları | 4 | Kuzey | Devin | 02.04.2016 |
| 7 | Panel | P-052 | 11,50 TL | 7 | Doğu | Erik | 16.05.2016 |
| 8 | Dönme dolap | W-24 | 20,50 TL | 11 | Güney | Sheldon'dan daha fazla içerik | 30.04.2016 |
| 9 | Motor | ENG-0161 | 330,00 ABD doları | 2 | Kuzey | Jale | 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 Dili | 18.04.2016 |
| 13 | Panel | P-105 | 8,25 $ | 13 | Batı | Jale | 20.06.2016 |
| 14 | Motor | ENG-0211 | 283,00 ABD doları | 1 | Kuzey | Emir | 21.06.2016 |
| 15 | Kapı | D-01X | 15 ABD doları | 2 | Batı | Armando | 03.07.2016 |
| 16 | Çerçeve | FR-0B1 | 34,00 ABD doları | 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 ABD doları | 13 | Doğu | Erik | 19.05.2016 |
| 19 | Kapı | D-05 | 17,70 ABD doları | 7 | Batı | Banu | 28.06.2016 |
| 20 | Çerçeve | FR-0B1 | 34,00 ABD doları | 8 | Kuzey | Sheldon'dan daha fazla içerik | 30.03.2016 |
Pivot tablo ekleme
Aşağıdaki spreadsheets.batchUpdate kod örneğinde, kaynak verilerden bir pivot tablo oluşturmak için UpdateCellsRequest etiketinin nasıl kullanılacağı ve SHEET_ID tarafından belirtilen e-tablonun A50 hücresine sabitlenmesi gösterilmektedir.
İstek, pivot tabloyu aşağıdaki özelliklerle yapılandırır:
- Satış sayısını gösteren bir değer grubu (quantity). Yalnızca bir değer grubu olduğundan olası 2
valueLayoutayarı eşdeğerdir. - İki satır grubu (Öğe Kategorisi ve Model Numarası). Birincisi, "Batı" Bölgesi'ndeki toplam Miktar'ı artan değerinde sıralar. Bu nedenle, "Kapı"nın üzerinde "Motor" (Batı satışları olmadan) görünür (15 Batı satışıyla). Model Numarası grubu, tüm bölgelerdeki toplam satışlara göre azalan düzende sıralar. Bu nedenle "W-24"ün (8 satış) üzerinde "W-24" (15 satış) görünür. Bu işlem,
valueBucketalanı{}olarak ayarlanarak yapılır. - En çok satışı artan düzende sıralayan bir sütun grubu (Bölge).
valueBucketde{}olarak ayarlandı. "Kuzey" en az toplam satışa sahiptir ve 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 şuna benzer bir pivot tablo oluşturur:
Hesaplanmış değerler içeren bir pivot tablo ekleme
Aşağıdaki spreadsheets.batchUpdate kod örneğinde, kaynak verilerden değer grubu hesaplamasını içeren bir pivot tablo oluşturmak ve SHEET_ID tarafından belirtilen e-tablonun A50 hücresine sabitlenmiş bir pivot tablo oluşturmak için UpdateCellsRequest etiketinin nasıl kullanılacağı gösterilmektedir.
İstek, pivot tabloyu aşağıdaki özelliklerle yapılandırır:
- İki değer grubu (Miktar ve Toplam Fiyat). İlki satış sayısını gösterir. İkincisi, şu formül kullanılarak parçanın maliyeti ile toplam satış sayısı çarpımı sonucunda 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ı, her gruptaki ada göre (Miktar yerine) tabloyu alfabetik sıraya göre 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 ara toplamları gizler.
- İstek, iyileştirilmiş bir tablo görünümü için
valueLayoutöğesiniVERTICALolarak 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 şuna benzer bir pivot tablo oluşturur:
Pivot tabloyu silme
Aşağıdaki spreadsheets.batchUpdate kod örneğinde, SHEET_ID tarafından belirtilen sayfanın A50 hücresine sabitlenmiş bir pivot tabloyu (varsa) silmek için UpdateCellsRequest kodunun nasıl kullanılacağı gösterilmektedir.
UpdateCellsRequest, bir pivot tabloyu, bağlantı hücresinde fields parametresine "pivotTable"ı dahil ederken ve pivotTable alanını atlayarak 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 kodunun 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. Esas olarak, bir pivot tabloyu düzenlemek için
Pivot tabloyu yeni bir tabloyla değiştirmek gerekir.
İstek, orijinal pivot tabloda aşağıdaki değişiklikleri yapar:
- Orijinal pivot tablodan ikinci satır grubunu 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. "Jessie"nin (13 Panel satışı) sol tarafında "Carmen" (15 Panel satışı) görünür.
- "Batı" dışındaki her Bölge'ye ait sütunu daraltır ve bu bölgenin Satış görevlisi grubunu gizler. Bu işlem, Bölge sütun grubunda, ilgili sütun için
valueMetadatacollapsedolarak ayarlanaraktrueolarak ayarlanı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 şuna benzer bir pivot tablo oluşturur:
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österilmiştir.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
Yanıt, SheetProperties öğelerine sahip bir Sheet nesnesi içeren bir Spreadsheet kaynağından oluşur. Ayrıca, PivotTable hakkında bilgi içeren bir GridData öğe dizisi de vardır.
Pivot tablo bilgileri, tablonun sabitlendiği hücrenin (tablonun sol üst köşesi) 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 bulunurken ikinci sayfada (SHEET_ID) B3'e bağlı pivot tablo yer alır. Boş küme parantezleri, pivot tablo verisi içermeyen sayfaları veya hücreleri belirtir. Referans olarak, bu istek 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
}
}
],
}