Google Sheets API memungkinkan Anda membuat dan memperbarui tabel pivot dalam spreadsheet. Contoh di halaman ini menggambarkan cara Anda dapat melakukan beberapa operasi tabel pivot umum dengan Sheets API.
Contoh ini disajikan dalam bentuk permintaan HTTP agar netral terhadap bahasa. Untuk mempelajari cara menerapkan update batch dalam berbagai bahasa menggunakan library klien Google API, lihat Memperbarui spreadsheet.
Dalam contoh ini, placeholder SPREADSHEET_ID dan
SHEET_ID
menunjukkan tempat Anda akan memberikan ID tersebut. Anda dapat menemukan ID
spreadsheet di URL spreadsheet.
Anda bisa mendapatkan ID sheet menggunakan
metode
spreadsheets.get. Rentang ditentukan menggunakan notasi A1. Contoh rentang adalah
Sheet1!A1:D5.
Selain itu, placeholder SOURCE_SHEET_ID menunjukkan
sheet Anda dengan data sumber. Dalam contoh ini, tabel tersebut adalah tabel yang tercantum
di bagian Data sumber tabel pivot.
Data sumber tabel pivot
Untuk contoh ini, asumsikan spreadsheet yang digunakan memiliki data sumber "penjualan" berikut di sheet pertamanya ("Sheet1"). String di baris pertama adalah label untuk setiap kolom. Untuk melihat contoh cara membaca dari sheet lain di spreadsheet Anda, lihat Notasi A1.
| A | B | C | D | E | F | G | |
| 1 | Kategori Item | Nomor Model | Biaya | Kuantitas | Wilayah | Staf Penjualan | Tanggal Pengiriman |
| 2 | Wheel | W-24 | $20,50 | 4 | Barat | Beth | 1/3/2016 |
| 3 | Pintu | D-01X | $15,00 | 2 | Selatan | Amir | 15/3/2016 |
| 4 | Mesin | ENG-0134 | $100,00 | 1 | Utara | Carmen | 20/3/2016 |
| 5 | Bingkai | FR-0B1 | $34,00 | 8 | Timur | Hannah | 12/3/2016 |
| 6 | Panel | P-034 | $6,00 | 4 | Utara | Devyn | 2/4/2016 |
| 7 | Panel | P-052 | $11,50 | 7 | Timur | Erik | 16/5/2016 |
| 8 | Wheel | W-24 | $20,50 | 11 | Selatan | Sheldon | 30/4/2016 |
| 9 | Mesin | ENG-0161 | $330,00 | 2 | Utara | Jessie | 2/7/2016 |
| 10 | Pintu | D-01Y | $29,00 | 6 | Barat | Armando | 13/3/2016 |
| 11 | Bingkai | FR-0B1 | $34,00 | 9 | Selatan | Yuliana | 27/2/2016 |
| 12 | Panel | P-102 | $3,00 | 15 | Barat | Carmen | 18/4/2016 |
| 13 | Panel | P-105 | $8,25 | 13 | Barat | Jessie | 20/6/2016 |
| 14 | Mesin | ENG-0211 | $283,00 | 1 | Utara | Amir | 21/6/2016 |
| 15 | Pintu | D-01X | $15,00 | 2 | Barat | Armando | 3/7/2016 |
| 16 | Bingkai | FR-0B1 | $34,00 | 6 | Selatan | Carmen | 15/7/2016 |
| 17 | Wheel | W-25 | $20,00 | 8 | Selatan | Hannah | 2/5/2016 |
| 18 | Wheel | W-11 | $29,00 | 13 | Timur | Erik | 19/5/2016 |
| 19 | Pintu | D-05 | $17,70 | 7 | Barat | Beth | 28/6/2016 |
| 20 | Bingkai | FR-0B1 | $34,00 | 8 | Utara | Sheldon | 30/3/2016 |
Menambahkan tabel pivot
Contoh kode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateCellsRequest
untuk membuat tabel pivot dari data sumber, dengan menempatkannya di sel A50 pada
sheet yang ditentukan oleh SHEET_ID.
Permintaan mengonfigurasi tabel pivot dengan properti berikut:
- Satu grup nilai (Kuantitas) yang menunjukkan jumlah penjualan. Karena hanya ada satu grup nilai, 2 setelan
valueLayoutyang mungkin setara. - Dua grup baris (Kategori Item dan Nomor Model). Urutan pertama dalam
nilai total Kuantitas yang menaik dari Wilayah "Barat". Oleh karena itu,
"Mesin" (tanpa penjualan di wilayah Barat) muncul di atas "Pintu" (dengan 15 penjualan di wilayah Barat). Grup
Nomor Model diurutkan dalam urutan menurun berdasarkan total penjualan di semua
wilayah, sehingga "W-24" (15 penjualan) muncul di atas "W-25" (8 penjualan). Hal ini dilakukan
dengan menetapkan
kolom
valueBucketke{}. - Satu grup kolom (Region) yang diurutkan dari atas ke bawah berdasarkan penjualan terbanyak.
Sekali lagi,
valueBucketdisetel ke{}. "Utara" memiliki total penjualan paling sedikit, sehingga muncul sebagai kolom Region pertama.
Protokol permintaan ditampilkan di bawah.
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" } } ] }
Permintaan tersebut membuat tabel pivot seperti ini:
Menambahkan tabel pivot dengan nilai yang dihitung
Contoh kode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateCellsRequest
untuk membuat tabel pivot dengan grup nilai yang dihitung dari data sumber,
yang ditambatkan pada sel A50 di sheet yang ditentukan oleh SHEET_ID.
Permintaan mengonfigurasi tabel pivot dengan properti berikut:
- Dua grup nilai (Kuantitas dan Harga Total). Yang pertama menunjukkan
jumlah penjualan. Yang kedua adalah nilai yang dihitung berdasarkan produk biaya suku cadang dan jumlah total penjualannya, menggunakan rumus ini:
=Cost*SUM(Quantity). - Tiga grup baris (Kategori Item, Nomor Model, dan Biaya).
- Satu grup kolom (Region).
- Grup baris dan kolom diurutkan berdasarkan nama (bukan berdasarkan Kuantitas) di setiap grup, sehingga tabel diurutkan berdasarkan abjad. Hal ini dilakukan dengan menghilangkan kolom
valueBucketdariPivotGroup.- Untuk menyederhanakan tampilan tabel, permintaan menyembunyikan subtotal untuk semua kecuali grup baris dan kolom utama.
- Permintaan menetapkan
valueLayoutkeVERTICALuntuk tampilan tabel yang lebih baik.valueLayouthanya penting jika ada 2 grup nilai atau lebih.
Protokol permintaan ditampilkan di bawah.
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" } } ] }
Permintaan tersebut membuat tabel pivot seperti ini:
Menghapus tabel pivot
Contoh kode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateCellsRequest
untuk menghapus tabel pivot (jika ada) yang ditambatkan pada sel A50 di sheet
yang ditentukan oleh SHEET_ID.
UpdateCellsRequest dapat menghapus tabel pivot dengan menyertakan "pivotTable" dalam parameter fields, sekaligus menghilangkan kolom pivotTable pada sel penanda.
Protokol permintaan ditampilkan di bawah.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}Mengedit kolom dan baris tabel pivot
Contoh kode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateCellsRequest
untuk mengedit tabel pivot yang dibuat di Menambahkan tabel pivot.
Subkumpulan kolom
pivotTable
dalam resource
CellData
tidak dapat diubah satu per satu dengan parameter fields. Untuk melakukan pengeditan, seluruh kolom pivotTable harus diisi. Pada dasarnya, mengedit tabel pivot
memerlukan penggantiannya dengan tabel pivot baru.
Permintaan membuat perubahan berikut pada tabel pivot asli:
- Menghapus grup baris kedua dari tabel pivot asli (Nomor Model).
- Menambahkan grup kolom (Tenaga Penjual). Kolom diurutkan dalam urutan menurun berdasarkan total penjualan Panel. "Carmen" (15 penjualan Panel) muncul di sebelah kiri "Jessie" (13 penjualan Panel).
- Menciutkan kolom untuk setiap Wilayah, kecuali "West", menyembunyikan grup Salesperson untuk wilayah tersebut. Hal ini dilakukan dengan menyetel
collapsedketruedivalueMetadatauntuk kolom tersebut dalam grup kolom Region.
Protokol permintaan ditampilkan di bawah.
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" } } ] }
Permintaan tersebut membuat tabel pivot seperti ini:
Membaca data tabel pivot
Contoh kode
spreadsheets.get berikut
menunjukkan cara mendapatkan data tabel pivot dari spreadsheet. Parameter kueri fields
menentukan bahwa hanya data tabel pivot yang harus ditampilkan (bukan
data nilai sel).
Protokol permintaan ditampilkan di bawah.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)Respons terdiri dari resource
Spreadsheet, yang berisi objek
Sheet dengan elemen
SheetProperties. Ada juga array elemen
GridData
yang berisi informasi tentang
PivotTable.
Informasi tabel pivot terdapat dalam resource
CellData sheet
untuk sel tempat tabel ditambatkan (yaitu, sudut kiri atas
tabel). Jika kolom respons ditetapkan ke nilai default, kolom tersebut akan dihilangkan dari
respons.
Dalam contoh ini, sheet pertama (SOURCE_SHEET_ID) memiliki data sumber tabel mentah, sedangkan sheet kedua (SHEET_ID) memiliki tabel pivot, yang ditambatkan di B3. Tanda kurung kurawal kosong menunjukkan sheet atau sel yang tidak
berisi data tabel pivot. Sebagai referensi, permintaan ini juga menampilkan ID sheet.
{ "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} } ], }