Google Sheets API memungkinkan Anda membuat dan memperbarui tabel pivot dalam {i>spreadsheet<i}. Contoh di halaman ini menunjukkan cara melakukan beberapa operasi tabel pivot umum dengan Sheets API.
Contoh-contoh ini ditampilkan dalam bentuk permintaan HTTP dengan bahasa yang netral. Untuk mempelajari cara mengimplementasikan update 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 dengan menggunakan
metode spreadsheets.get
. Rentang
ditentukan menggunakan notasi A1. Contoh
rentangnya adalah Sheet1!A1:D5.
Selain itu, placeholder SOURCE_SHEET_ID
menunjukkan sheet Anda
yang berisi data sumber. Dalam contoh berikut, ini adalah tabel yang tercantum di bagian Data
sumber tabel pivot.
Data sumber tabel pivot
Untuk contoh ini, anggaplah spreadsheet yang digunakan memiliki data "penjualan" sumber berikut di 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 | D | E | F | G | |
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 | Mesin | ENG-0134 | Rp1.000.000 | 1 | Utara | Carmen | 20/3/2016 |
5 | Bingkai | FR-0B1 | $34,00 | 8 | Timur | Hanna | 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 | 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 | 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 | H-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 | 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, menambatkannya 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
valueLayout
akan setara. - Dua grup baris (Item Category dan Model Number). Yang pertama mengurutkan
nilai total Quantity secara menaik dari Region "West". Oleh karena itu, "Engine" (tanpa penjualan di wilayah Barat) 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 menyetel
kolom
valueBucket
ke{}
. - Satu grup kolom (Region) yang mengurutkan sebagian besar penjualan dalam urutan menaik.
Sekali lagi,
valueBucket
disetel ke{}
. "North" memiliki total penjualan paling sedikit, sehingga 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:
Tambahkan tabel pivot dengan nilai yang dihitung
Contoh kode
spreadsheets.batchUpdate
berikut menunjukkan cara menggunakan
UpdateCellsRequest
untuk membuat tabel pivot dengan grup nilai penghitungan dari data sumber,
yang menambatkannya pada 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 rumus ini:
=Cost*SUM(Quantity)
. - Tiga grup baris (Item Category, Model Number, dan Cost).
- Satu grup kolom (Region).
- Grup baris dan kolom mengurutkan berdasarkan nama (bukan Jumlah) di setiap
grup, sesuai abjad. Hal ini dilakukan dengan menghapus kolom
valueBucket
dariPivotGroup
. - Untuk menyederhanakan tampilan tabel, permintaan tersebut menyembunyikan subtotal untuk semua kecuali grup kolom dan baris utama.
- Permintaan ini menetapkan
valueLayout
keVERTICAL
untuk tampilan tabel yang lebih baik.valueLayout
hanya penting jika ada 2 atau lebih grup nilai.
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 sheet
yang ditentukan oleh SHEET_ID
.
UpdateCellsRequest
dapat menghapus tabel pivot dengan menyertakan "pivotTable" di
parameter fields
, sekaligus menghilangkan kolom pivotTable
pada 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 dalam Menambahkan tabel pivot.
Subset kolom
pivotTable
di
resource CellData
tidak dapat diubah satu per satu dengan parameter fields
. Untuk mengedit, seluruh kolom pivotTable
harus diberikan. Pada dasarnya, mengedit tabel pivot
memerlukan penggantiannya dengan yang baru.
Permintaan tersebut membuat perubahan berikut pada tabel pivot asli:
- Menghapus grup baris kedua dari tabel pivot asli (Model Number).
- Menambahkan grup kolom (Salesperson). Kolom diurutkan dalam urutan menurun berdasarkan jumlah total penjualan Panel. "Carmen" (15 penjualan Panel) muncul di sebelah kiri "Jessie" (13 penjualan Panel).
- Menciutkan kolom untuk setiap Region, kecuali untuk "West", menyembunyikan grup Salesperson untuk wilayah tersebut. Hal ini dilakukan dengan menetapkan
collapsed
ketrue
divalueMetadata
untuk kolom tersebut dalam 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
. Ada juga array elemen
GridData
yang berisi informasi tentang
PivotTable
.
Informasi tabel pivot dimuat dalam resource CellData
sheet untuk sel tempat tabel ditambatkan (yaitu di 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. Untuk 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
} } ], }