Google E-Tablolar API, e-tablolarda pivot tablolar oluşturmanıza ve güncellemenize olanak tanır. Bu sayfada, E-Tablolar API'si ile bazı yaygın pivot tablo işlemlerini nasıl gerçekleştirebileceğiniz gösterilmektedir.
Bu örnekler, dil açısından tarafsız olması için HTTP istekleri biçiminde sunulur. Google API istemci kitaplıklarını kullanarak farklı dillerde toplu güncelleme yapmayı öğ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. spreadsheets.get yöntemini kullanarak e-tablo kimliğini alabilirsiniz. Aralıklar A1 gösterimi kullanılarak belirtilir. Örnek aralık: Sayfa1!A1:D5.
Ayrıca, yer tutucu SOURCE_SHEET_ID, kaynak verileri içeren e-tablonuzu gösterir. Bu örneklerde, Pivot tablo kaynak verileri altında listelenen tablodur.
Pivot tablo kaynak verileri
Bu örneklerde, kullanılan e-tablonun ilk sayfasında ("Sayfa1") aşağıdaki kaynak "satış" verilerinin bulunduğu varsayılır. İlk satırdaki dizeler, sütunların etiketleridir. E-tablonuzdaki diğer sayfalardan nasıl veri okuyacağınıza dair örnekleri görmek için A1 gösterimi bölümüne bakın.
| A | B | C | D | E | F | G | |
| 1 | Öğe Kategorisi | Model Numarası | Maliyet | Miktar | Bölge | Satış görevlisi | Gönderim Tarihi |
| 2 | Tekerlek | W-24 | 20,50 ABD doları | 4 | Batı | Beth | 3/1/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 | Handan | 12.03.2016 |
| 6 | Panel | P-034 | 6,00 ABD doları | 4 | Kuzey | Devyn | 2.04.2016 |
| 7 | Panel | P-052 | 11,50 ABD doları | 7 | Doğu | Erkan | 16.05.2016 |
| 8 | Tekerlek | W-24 | 20,50 ABD doları | 11 | Güney | Sheldon | 30.04.2016 |
| 9 | Motor | ENG-0161 | 330,00 ABD doları | 2 | Kuzey | Ceylan | 2.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ı | Ceylan | 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 | 3.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 | Handan | 2.05.2016 |
| 18 | Tekerlek | W-11 | 29,00 ABD doları | 13 | Doğu | Erkan | 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, kaynak verilerden pivot tablo oluşturmak için UpdateCellsRequest işlevinin nasıl kullanılacağı gösterilmektedir. Pivot tablo, SHEET_ID tarafından belirtilen sayfanın A50 hücresine sabitlenir.
İstek, pivot tabloyu aşağıdaki özelliklerle yapılandırır:
- Satış sayısını belirten 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 değerine göre artan düzende yapılır. Bu nedenle, "Motor" (Batı'da satış yok) "Kapı"nın (Batı'da 15 satış) üzerinde görünür. 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" (8 satış) üzerinde görünür. Bu,
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 de
valueBucket,{}olarak ayarlanmıştır. "Kuzey", toplam satışların en az olduğu bölge olduğundan 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ğerler içeren bir pivot tablo ekleme
Aşağıdaki spreadsheets.batchUpdate kod örneğinde, kaynak verilerden hesaplanan değerler grubu içeren bir pivot tablo oluşturmak için UpdateCellsRequest işlevinin nasıl kullanılacağı gösterilmektedir. Bu pivot tablo, SHEET_ID tarafından belirtilen sayfanın A50 hücresine sabitlenir.
İ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, bir parçanın maliyeti ile toplam satış sayısının çarpımına dayalı bir hesaplanmış değerdir. Bu 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 Miktar yerine ada göre sıralanır ve tablo alfabetik olarak sıralanır. Bu işlem,
PivotGroupalanındanvalueBucketalanının çıkarılması ile yapılır. - Tablo görünümünü basitleştirmek için istek, ana satır ve sütun grupları dışındaki tüm alt toplamları 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ö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 tabloları 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 işlevinin nasıl kullanılacağı gösterilmektedir.
Bir UpdateCellsRequest, fields parametresine "pivotTable" ekleyerek ve aynı zamanda ana hücredeki pivotTable alanını atlayarak 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 işlevinin nasıl kullanılacağı gösterilmektedir.
CellData kaynağındaki pivotTable alanının alt kümeleri, fields parametresi kullanılarak ayrı ayrı değiştirilemez. Düzenleme yapmak için pivotTable alanının tamamının sağlanmış olması gerekir. Pivot tabloları düzenlemek için genellikle 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.
- Bir sütun grubu (Satıcı) 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örünür.
- "Batı" hariç her Bölge sütununu daraltır ve ilgili bölgenin Satış temsilcisi grubunu gizler. Bu, Bölge sütun grubundaki ilgili sütun için
valueMetadatabölümündecollapseddeğerinintrueolarak 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, e-tablodan pivot tablo verilerinin nasıl alınacağı gösterilmektedir. fields sorgu parametresi, yalnızca pivot tablo verilerinin (hücre değeri verilerinin aksine) 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 öğeleri içeren bir Sheet nesnesini barındıran bir Spreadsheet kaynağından oluşur. Ayrıca, PivotTable ile ilgili bilgileri içeren bir GridData öğeleri dizisi de vardır.
Pivot tablo bilgileri, tablonun sabitlendiği hücre (yani tablonun sol üst köşesi) için sayfanın CellData kaynağında bulunur. Bir yanıt alanı varsayılan değere ayarlanmışsa yanıttan çıkarılır.
Bu örnekte, ilk e-tabloda (SOURCE_SHEET_ID) ham tablo kaynak verileri, ikinci e-tabloda (SHEET_ID) ise B3'e sabitlenmiş pivot tablo bulunur. Boş parantezler, pivot tablo verileri içermeyen sayfaları veya hücreleri gösterir. 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} } ], }