Google Sheets API memungkinkan Anda membuat dan memperbarui tabel pivot dalam spreadsheet. Contoh di halaman ini menggambarkan bagaimana Anda dapat mencapai beberapa tabel pivot umum operasi dengan Sheets API.
Contoh ini disajikan dalam bentuk permintaan HTTP untuk memilih bahasa netral. Untuk mempelajari cara mengimplementasikan update secara batch dalam berbagai bahasa menggunakan library klien Google API, lihat Memperbarui di spreadsheet.
Dalam contoh ini, placeholder SPREADSHEET_ID dan SHEET_ID
menunjukkan di mana Anda akan memberikan ID tersebut. Anda dapat menemukan spreadsheet
ID di URL spreadsheet. Anda bisa mendapatkan
sheet ID dengan menggunakan
Metode spreadsheets.get. Tujuan
rentang ditentukan menggunakan notasi A1. Channel
contohnya adalah Sheet1!A1:D5.
Selain itu, placeholder SOURCE_SHEET_ID menunjukkan sheet Anda
dengan data sumber. Dalam contoh ini, ini adalah tabel yang tercantum di bagian Pivot
data sumber tabel.
Data sumber tabel pivot
Untuk contoh-contoh ini, asumsikan {i>spreadsheet <i}yang digunakan memiliki sumber berikut "penjualan" data dalam {i>sheet <i}pertamanya ("Sheet1"). {i>String<i} di baris pertama berisi label untuk setiap kolom. Untuk melihat contoh cara membaca dari di spreadsheet, 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 | Bagas | 1/3/2016 |
| 3 | Pintu | H-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 | 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 | 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 | Carmen | 18/4/2016 |
| 13 | Panel | P-105 | $8,25 | 13 | Barat | Jelita | 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
Hal berikut
spreadsheets.batchUpdate
contoh kode menunjukkan cara menggunakan
UpdateCellsRequest
untuk membuat tabel pivot dari data sumber, dengan menempatkannya di sel A50 pada
yang ditentukan oleh SHEET_ID.
Permintaan tersebut mengonfigurasi tabel pivot dengan properti berikut:
- Satu grup nilai (Jumlah) yang menunjukkan jumlah penjualan. Sejak
hanya ada satu grup nilai, 2 kemungkinan
valueLayoutsetelannya sama. - Dua grup baris (Kategori Item dan Nomor Model). Yang pertama
mengurutkan dalam
nilai total Jumlah yang menaik dari kolom "Barat" Wilayah. Oleh karena itu,
"Mesin" (tanpa penjualan di wilayah Barat) muncul di atas "Door" (dengan 15 penjualan West). Tujuan
Grup Nomor Model mengurutkan total penjualan dalam urutan menurun di semua
wilayah, jadi "W-24" (15 penjualan) muncul di atas "W-25" (8 penjualan). Hal ini sudah selesai
dengan menetapkan
valueBucketke{}. - Satu grup kolom (Region) yang mengurutkan sebagian besar penjualan dalam urutan menaik.
Sekali lagi,
valueBucketdisetel ke{}. "Utara" memiliki total penjualan paling sedikit, dan agar muncul sebagai kolom Wilayah 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
Hal berikut
spreadsheets.batchUpdate
contoh kode menunjukkan cara menggunakan
UpdateCellsRequest
untuk membuat tabel pivot dengan grup nilai
hitung dari data sumber,
menambatkannya di sel A50 pada {i>sheet<i} 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 dari
biaya suku cadang dan jumlah total penjualannya, dengan menggunakan rumus ini:
=Cost*SUM(Quantity). - Tiga grup baris (Kategori Item, Nomor Model, dan Biaya).
- Satu grup kolom (Wilayah).
- Grup baris dan kolom diurutkan menurut nama (bukan menurut Jumlah) di setiap
mengelompokkan tabel sesuai abjad. Hal ini dilakukan dengan menghilangkan
valueBucketdari kolomPivotGroup - 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 atau lebih kelompok 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
Hal berikut
spreadsheets.batchUpdate
contoh kode menunjukkan cara menggunakan
UpdateCellsRequest
untuk menghapus tabel pivot (jika ada) yang ditambatkan pada sel A50 di {i>sheet<i}
ditentukan oleh SHEET_ID.
UpdateCellsRequest dapat menghapus tabel pivot dengan menyertakan "pivotTable" inci
parameter fields, sekaligus menghilangkan kolom pivotTable pada anchor
sel.
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
Hal berikut
spreadsheets.batchUpdate
contoh kode menunjukkan cara menggunakan
UpdateCellsRequest
untuk mengedit tabel pivot yang dibuat di Menambahkan tabel pivot.
Subset dari
pivotTable
di kolom
Resource CellData
tidak dapat diubah satu per satu dengan parameter fields. Untuk mengedit,
seluruh kolom pivotTable harus disediakan. Pada dasarnya, mengedit tabel pivot
perlu menggantinya 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 diurutkan dengan urutan menurun menurut jumlah total penjualan Panel. "Carmen" (15 penjualan Panel) tampaknya sebelah kiri "Jessie" (13 penjualan Panel).
- Menciutkan kolom untuk setiap Region, kecuali untuk "West", yang menyembunyikan
Grup Salesperson untuk wilayah tersebut. Hal ini dilakukan dengan menyetel
collapsedketruedivalueMetadatauntuk kolom tersebut di grup kolom Wilayah.
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
Hal berikut
Contoh kode spreadsheets.get
menunjukkan cara mendapatkan data tabel pivot dari sebuah {i>spreadsheet<i}. Kueri fields
menentukan bahwa hanya data tabel pivot yang akan ditampilkan (sebagaimana
atau bukan dengan 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
Spreadsheet
, yang berisi
Objek Sheet dengan
SheetProperties
yang kurang penting. Ada juga berbagai
GridData
elemen yang berisi informasi tentang
PivotTable
Informasi tabel pivot dimuat dalam
Referensi CellData
untuk sel tempat tabel bertautan (yaitu, sudut kiri atas tabel
kanan atas). Jika kolom respons ditetapkan ke nilai default, kolom ini akan dihilangkan dari
yang dihasilkan.
Dalam contoh ini, sheet pertama (SOURCE_SHEET_ID) memiliki tabel mentah
sumber data, sedangkan sheet kedua (SHEET_ID) memiliki tabel pivot,
yang didasarkan pada B3. Tanda kurung kurawal kosong menunjukkan sheet atau sel yang tidak
berisi data tabel pivot. Sebagai referensi, permintaan ini juga menampilkan sheet
pelanggan.
{
"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
}
}
],
}