Các bảng tổng hợp

API Google Trang tính cho phép bạn tạo và cập nhật bảng tổng hợp trong bảng tính. Các ví dụ trên trang này minh hoạ cách bạn có thể thực hiện một số thao tác phổ biến trên bảng tổng hợp bằng API Trang tính.

Các ví dụ này được trình bày dưới dạng yêu cầu HTTP để không phụ thuộc vào ngôn ngữ. Để tìm hiểu cách triển khai bản cập nhật theo lô bằng nhiều ngôn ngữ thông qua thư viện ứng dụng API Google, hãy xem bài viết Cập nhật bảng tính.

Trong các ví dụ này, phần giữ chỗ SPREADSHEET_ID và SHEET_ID cho biết vị trí bạn sẽ cung cấp các mã nhận dạng đó. Bạn có thể tìm thấy mã nhận dạng bảng tính trong URL của bảng tính. Bạn có thể lấy mã nhận dạng trang tính bằng cách sử dụng phương thức spreadsheets.get. Các dải ô được chỉ định bằng ký hiệu A1. Một dải ô ví dụ là Trang1!A1:D5.

Ngoài ra, phần giữ chỗ SOURCE_SHEET_ID cho biết trang tính có dữ liệu nguồn. Trong các ví dụ này, đây là bảng được liệt kê trong phần Dữ liệu nguồn của bảng tổng hợp.

Dữ liệu nguồn của bảng tổng hợp

Đối với các ví dụ này, hãy giả sử bảng tính đang sử dụng có dữ liệu nguồn "doanh số" sau đây trong trang tính đầu tiên ("Trang1"). Các chuỗi trong hàng đầu tiên là nhãn cho từng cột. Để xem các ví dụ về cách đọc từ các trang tính khác trong bảng tính, hãy xem bài viết Ký hiệu A1.

A B C D E F G
1 Danh mục mặt hàng Số kiểu máy Chi phí Số lượng Vùng Nhân viên bán hàng Ngày giao hàng
2 Bánh xe W-24 $20,50 4 Tây Beth 1/3/2016
3 Cửa ra vào D-01X $15,00 2 Nam Amir 15/3/2016
4 Công cụ ENG-0134 $100,00 1 Bắc Carmen 20/3/2016
5 Khung FR-0B1 $34,00 8 Đông Hannah 12/3/2016
6 Bảng điều khiển P-034 6 USD 4 Bắc Devyn 2/4/2016
7 Bảng điều khiển P-052 $11,50 7 Đông Erik 16/5/2016
8 Bánh xe W-24 $20,50 11 Nam Sheldon 30/4/2016
9 Công cụ ENG-0161 $330,00 2 Bắc Jessie 2/7/2016
10 Cửa ra vào D-01Y $29,00 6 Tây Armando 13/3/2016
11 Khung FR-0B1 $34,00 9 Nam Yuliana 27/2/2016
12 Bảng điều khiển P-102 3 đô la 15 Tây Carmen 18/4/2016
13 Bảng điều khiển P-105 $8,25 13 Tây Jessie 20/6/2016
14 Công cụ ENG-0211 $283,00 1 Bắc Amir 21/6/2016
15 Cửa ra vào D-01X $15,00 2 Tây Armando 3/7/2016
16 Khung FR-0B1 $34,00 6 Nam Carmen 15/7/2016
17 Bánh xe W-25 $20,00 8 Nam Hannah 2/5/2016
18 Bánh xe W-11 $29,00 13 Đông Erik 19/5/2016
19 Cửa ra vào D-05 $17,70 7 Tây Beth 28/6/2016
20 Khung FR-0B1 $34,00 8 Bắc Sheldon 30/3/2016

Thêm bảng tổng hợp

Mã mẫu sau đây spreadsheets.batchUpdate cho biết cách sử dụng UpdateCellsRequest để tạo Bảng tổng hợp từ dữ liệu nguồn, neo bảng này vào ô A50 của trang tính do SHEET_ID chỉ định.

Yêu cầu này định cấu hình bảng tổng hợp với các thuộc tính sau:

  • Một nhóm giá trị (Số lượng) cho biết số lượt bán hàng. Vì chỉ có một nhóm giá trị, nên 2 chế độ cài đặt có thể có là tương đương.valueLayout
  • Hai nhóm hàng (Danh mục mặt hàng và Số kiểu máy). Nhóm đầu tiên sắp xếp theo giá trị tăng dần của tổng Số lượng từ Khu vực "Tây". Do đó, "Công cụ" (không có doanh số ở khu vực Tây) xuất hiện phía trên "Cửa ra vào" (có 15 doanh số ở khu vực Tây). Nhóm Số kiểu máy sắp xếp theo thứ tự giảm dần của tổng doanh số ở tất cả các khu vực, vì vậy, "W-24" (15 doanh số) xuất hiện phía trên "W-25" (8 doanh số). Bạn có thể thực hiện việc này bằng cách đặt trường valueBucket thành {}.
  • Một nhóm cột (Khu vực) sắp xếp theo thứ tự tăng dần của doanh số cao nhất. Tương tự, valueBucket được đặt thành {}. "Bắc" có tổng doanh số thấp nhất, vì vậy, cột này xuất hiện dưới dạng cột Khu vực đầu tiên.

Giao thức yêu cầu được trình bày bên dưới.

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

Yêu cầu này tạo một bảng tổng hợp như sau:

Thêm kết quả về công thức làm bánh tổng hợp

Thêm bảng tổng hợp có giá trị được tính

Mã mẫu sau đây spreadsheets.batchUpdate cho biết cách sử dụng UpdateCellsRequest để tạo Bảng tổng hợp có nhóm giá trị được tính từ dữ liệu nguồn, neo bảng này vào ô A50 của trang tính do SHEET_ID chỉ định.

Yêu cầu này định cấu hình bảng tổng hợp với các thuộc tính sau:

  • Hai nhóm giá trị (Số lượng và Tổng giá). Nhóm đầu tiên cho biết số lượt bán hàng. Nhóm thứ hai là giá trị được tính dựa trên tích của chi phí một bộ phận và tổng số lượt bán hàng của bộ phận đó, sử dụng công thức sau: =Cost*SUM(Quantity).
  • Ba nhóm hàng (Danh mục mặt hàng, Số kiểu máy và Chi phí).
  • Một nhóm cột (Khu vực).
  • Các nhóm hàng và cột sắp xếp theo tên (thay vì theo Số lượng) trong mỗi nhóm, sắp xếp bảng theo bảng chữ cái. Bạn có thể thực hiện việc này bằng cách bỏ qua trường valueBucket khỏi PivotGroup.
    • Để đơn giản hoá giao diện bảng, yêu cầu này sẽ ẩn tổng phụ cho tất cả các nhóm, ngoại trừ nhóm hàng và cột chính.
  • Yêu cầu này đặt valueLayout thành VERTICAL để cải thiện giao diện bảng. valueLayout chỉ quan trọng nếu có từ 2 nhóm giá trị trở lên.

Giao thức yêu cầu được trình bày bên dưới.

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

Yêu cầu này tạo một bảng tổng hợp như sau:

Thêm kết quả công thức nhóm giá trị bảng tổng hợp

Xoá bảng tổng hợp

Mẫu mã spreadsheets.batchUpdate sau đây cho biết cách sử dụng UpdateCellsRequest để xoá Bảng tổng hợp (nếu có) được neo vào ô A50 của trang tính do SHEET_ID chỉ định.

UpdateCellsRequest có thể xoá bảng tổng hợp bằng cách đưa "pivotTable" vào tham số fields, đồng thời bỏ qua trường pivotTable trên ô neo.

Giao thức yêu cầu được trình bày bên dưới.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
 
{
  "requests": [
    {
      "updateCells": {
          "rows": [ 
            {
            "values": [
              {}
            ]
          }
        ],
        "start": {
          "sheetId": SHEET_ID,
          "rowIndex": 49,
          "columnIndex": 0
        },
        "fields": "pivotTable"
      }
    }
  ]
}

Chỉnh sửa cột và hàng của bảng tổng hợp

Mã mẫu sau đây spreadsheets.batchUpdate cho biết cách sử dụng UpdateCellsRequest để chỉnh sửa bảng tổng hợp được tạo trong phần Thêm bảng tổng hợp.

Bạn không thể thay đổi riêng lẻ các tập hợp con của pivotTable trường trong tài nguyên CellData bằng tham số fields. Để chỉnh sửa, bạn phải cung cấp toàn bộ trường pivotTable. Về cơ bản, việc chỉnh sửa bảng tổng hợp đòi hỏi phải thay thế bảng đó bằng một bảng mới.

Yêu cầu này thực hiện các thay đổi sau đối với bảng tổng hợp ban đầu:

  • Xoá nhóm hàng thứ hai khỏi bảng tổng hợp ban đầu (Số kiểu máy).
  • Thêm một nhóm cột (Nhân viên bán hàng). Các cột sắp xếp theo thứ tự giảm dần theo tổng số lượt bán hàng Bảng điều khiển. "Carmen" (15 lượt bán hàng Bảng điều khiển) xuất hiện ở bên trái "Jessie" (13 lượt bán hàng Bảng điều khiển).
  • Thu gọn cột cho từng Khu vực, ngoại trừ "Tây", ẩn nhóm Nhân viên bán hàng cho khu vực đó. Bạn có thể thực hiện việc này bằng cách đặt collapsed thành true trong valueMetadata cho cột đó trong nhóm cột Khu vực.

Giao thức yêu cầu được trình bày bên dưới.

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

Yêu cầu này tạo một bảng tổng hợp như sau:

Chỉnh sửa kết quả về công thức làm bánh táo

Đọc dữ liệu bảng tổng hợp

Mã mẫu spreadsheets.get sau đây cho biết cách lấy dữ liệu bảng tổng hợp từ bảng tính. Tham số truy vấn fields chỉ định rằng chỉ dữ liệu bảng tổng hợp được trả về (trái ngược với dữ liệu giá trị ô).

Giao thức yêu cầu được trình bày bên dưới.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)

Phản hồi bao gồm một Spreadsheet tài nguyên, chứa một Sheet đối tượng có SheetProperties các phần tử. Ngoài ra, còn có một mảng các GridData phần tử chứa thông tin về PivotTable. Thông tin bảng tổng hợp được chứa trong tài nguyên CellDatacủa trang tính cho ô mà bảng được neo vào (tức là góc trên cùng bên trái của bảng). Nếu một trường phản hồi được đặt thành giá trị mặc định, thì trường đó sẽ bị bỏ qua trong phản hồi.

Trong ví dụ này, trang tính đầu tiên (SOURCE_SHEET_ID) có dữ liệu nguồn của bảng thô, trong khi trang tính thứ hai (SHEET_ID) có bảng tổng hợp , được neo vào B3. Dấu ngoặc nhọn rỗng cho biết các trang tính hoặc ô không chứa dữ liệu bảng tổng hợp. Để tham khảo, yêu cầu này cũng trả về mã nhận dạng trang tính.

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