Tabel pivot

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 valueLayout setelannya 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 valueBucket ke {}.
  • Satu grup kolom (Region) yang mengurutkan sebagian besar penjualan dalam urutan menaik. Sekali lagi, valueBucket disetel 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:

Tambahkan hasil resep tabel pivot

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

Tambahkan hasil resep grup nilai pivot

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

Edit hasil resep tabel pivot

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