Tabel pivot

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:

Hasil resep tambahkan tabel pivot

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 dari PivotGroup.
  • Untuk menyederhanakan tampilan tabel, permintaan tersebut menyembunyikan subtotal untuk semua kecuali grup kolom dan baris utama.
  • Permintaan ini menetapkan valueLayout ke VERTICAL 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:

Tambahkan hasil resep grup nilai pivot

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 ke true di valueMetadata 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:

Hasil resep edit tabel pivot

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
      }
    }
  ],
}