Google Sheets API memungkinkan Anda membuat dan memperbarui tabel pivot dalam spreadsheet. Contoh di halaman ini mengilustrasikan cara melakukan beberapa operasi tabel pivot umum dengan Sheets API.
Contoh ini disajikan dalam bentuk permintaan HTTP agar tidak menggunakan bahasa. Untuk mempelajari cara mengimplementasikan update secara batch dalam berbagai bahasa menggunakan library klien Google API, lihat Mengupdate 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
rentangnya adalah Sheet1!A1:D5.
Selain itu, placeholder SOURCE_SHEET_ID menunjukkan sheet Anda
dengan data sumber. Dalam contoh tersebut, ini adalah tabel yang tercantum di bagian Data
sumber tabel pivot.
Data sumber tabel pivot
Untuk contoh ini, asumsikan spreadsheet yang digunakan memiliki data "penjualan" sumber berikut dalam sheet pertamanya ("Sheet1"). {i>String<i} di baris pertama adalah label untuk masing-masing kolom. Untuk melihat contoh cara membaca dari sheet lain di spreadsheet Anda, lihat notasi A1.
| A | B | C | H | E | F | H | |
| 1 | Kategori Item | Nomor Model | Biaya | Jumlah | Wilayah | Staf Penjualan | Tanggal Pengiriman |
| 2 | Wheel | W-24 | $20,50 | 4 | Barat | Bagas | 1/3/2016 |
| 3 | Pintu | H-01X | $15,00 | 2 | Selatan | Amir | 15/3/2016 |
| 4 | Engine | ENG-0134 | Rp1.000.000 | 1 | Utara | Karmen | 20/3/2016 |
| 5 | Bingkai | FR-0B1 | $34,00 | 8 | Timur | Hanna | 12/3/2016 |
| 6 | Panel | P-034 | $6,00 | 4 | Utara | Devina | 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 | Engine | ENG-0161 | $330,00 | 2 | Utara | Jelita | 2/7/2016 |
| 10 | Pintu | H-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 | Karmen | 18/4/2016 |
| 13 | Panel | P-105 | $8,25 | 13 | Barat | Jelita | 20/6/2016 |
| 14 | Engine | ENG-0211 | $283,00 | 1 | Utara | Amir | 21/6/2016 |
| 15 | Pintu | H-01X | $15,00 | 2 | Barat | Armando | 3/7/2016 |
| 16 | Bingkai | FR-0B1 | $34,00 | 6 | Selatan | Karmen | 15/7/2016 |
| 17 | Wheel | W-25 | $20,00 | 8 | Selatan | Hanna | 2/5/2016 |
| 18 | Wheel | W-11 | $29,00 | 13 | Timur | Erik | 19/5/2016 |
| 19 | Pintu | D-05 | $17,70 | 7 | Barat | Bagas | 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
sheet yang ditentukan oleh SHEET_ID.
Permintaan tersebut mengonfigurasi tabel pivot dengan properti berikut:
- Satu grup nilai (Jumlah) yang menunjukkan jumlah penjualan. Karena
hanya ada satu grup nilai, 2 kemungkinan
setelan
valueLayoutadalah setara. - Dua grup baris (Kategori Item dan Nomor Model). Pertama mengurutkan
nilai total Quantity yang meningkat dari Region "West". Oleh karena itu,
"Engine" (tanpa penjualan di wilayah Barat) akan muncul di atas "Door" (dengan 15 penjualan di wilayah Barat). Grup Nomor Model mengurutkan total penjualan di semua wilayah dalam urutan menurun, sehingga "W-24" (15 penjualan) muncul di atas "W-25" (8 penjualan). Hal ini dilakukan
dengan menetapkan kolom
valueBucketke{}. - Satu grup kolom (Region) yang mengurutkan sebagian besar penjualan dalam urutan menaik.
Sekali lagi,
valueBucketdisetel ke{}. "North" memiliki total penjualan paling sedikit, dan muncul sebagai kolom Region pertama.
Protokol permintaan ditampilkan di bawah ini.
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 kalkulasi dari data sumber,
yang menambatkannya di sel A50 sheet yang ditentukan oleh SHEET_ID.
Permintaan tersebut mengonfigurasi tabel pivot dengan properti berikut:
- Dua grup nilai (Jumlah dan Harga Total). Yang pertama menunjukkan
jumlah penjualan. Yang kedua adalah nilai yang dihitung berdasarkan produk
biaya suku cadang dan jumlah total penjualannya, menggunakan formula ini:
=Cost*SUM(Quantity). - Tiga grup baris (Kategori Item, Nomor Model, dan Biaya).
- Satu grup kolom (Wilayah).
- Grup baris dan kolom mengurutkan berdasarkan nama (bukan menurut Jumlah) di setiap
kelompok, sesuai abjad pada tabel. Hal ini dilakukan dengan menghapus kolom
valueBucketdariPivotGroup. - Untuk menyederhanakan tampilan tabel, permintaan tersebut menyembunyikan subtotal untuk semua, kecuali grup baris dan kolom utama.
- Permintaan ini ditetapkan
valueLayoutkeVERTICALuntuk tampilan tabel yang lebih baik.valueLayouthanya penting jika ada 2 grup nilai atau lebih.
Protokol permintaan ditampilkan di bawah ini.
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 pada sheet
yang ditentukan oleh SHEET_ID.
UpdateCellsRequest dapat menghapus tabel pivot dengan menyertakan "pivotTable" dalam
parameter fields, sekaligus juga menghilangkan kolom pivotTable di sel
anchor.
Protokol permintaan ditampilkan di bawah ini.
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.
Subset kolom
pivotTable
di
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 penggantian dengan yang baru.
Permintaan tersebut membuat perubahan berikut pada tabel pivot asli:
- Menghapus grup baris kedua dari tabel pivot asli (Nomor Model).
- Menambahkan grup kolom (Salesperson). Kolom mengurutkan dalam urutan menurun berdasarkan jumlah total penjualan Panel. "Carmen" (15 penjualan Panel) muncul di sebelah kiri "Jessie" (13 penjualan Panel).
- Ciutkan kolom untuk setiap Region, kecuali untuk "West", menyembunyikan
grup Salesperson untuk region tersebut. Hal ini dilakukan dengan menyetel
collapsedketruedivalueMetadatauntuk kolom tersebut di grup kolom Region.
Protokol permintaan ditampilkan di bawah ini.
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 akan ditampilkan (bukan
data nilai sel).
Protokol permintaan ditampilkan di bawah ini.
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. Terdapat juga array elemen
GridData
yang berisi informasi tentang
PivotTable.
Informasi tabel pivot dimuat 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 ditautkan pada B3. 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
}
}
],
}