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
valueLayoutayarı 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,
valueBucketalanı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:
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,
valueBucketalanı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
valueLayoutdeğeriniVERTICALolarak ayarlar.valueLayoutyalnı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 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
valueMetadataiçindecollapseddeğerinitrueolarak 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 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} } ], }