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ể có được một số bảng tổng hợp phổ biến các thao tác bằng API Trang tính.

Những ví dụ này được trình bày dưới dạng các yêu cầu HTTP để trở thành ngôn ngữ bình thường. Để tìm hiểu cách triển khai quá trình cập nhật theo lô bằng nhiều ngôn ngữ bằng cách sử dụng thư viện ứng dụng API của Google, hãy xem phần Cập nhật bảng tính.

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

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

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

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

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

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

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

Yêu cầu này sẽ đị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. Từ chỉ có một nhóm giá trị, 2 nhóm giá trị có thể valueLayout tương đương.
  • Hai nhóm hàng (Danh mục mặt hàngSố mô hình). Lựa chọn đầu tiên sắp xếp theo giá trị tăng dần của tổng Số lượng từ "Phương Tây" Khu vực. Do đó, "Động cơ" (không có bán hàng ở phía Tây) xuất hiện phía trên "Cửa" (với 15 giao dịch ở miền Tây). Chiến lược phát hành đĩa đơn Nhóm Số mô hình sắp xếp theo thứ tự giảm dần của tổng doanh số bán hàng trong tất cả nên "W-24" (15 lượt bán hàng) xuất hiện phía trên "W-25" (8 lượt bán hàng). Đã xong bằng cách thiết lập valueBucket sang {}.
  • Một nhóm cột (Khu vực) sắp xếp theo thứ tự tăng dần của hầu hết các lượt bán hàng. Xin nhắc lại, valueBucket được thiết lập thành {}. "Bắc" có tổng doanh số thấp nhất và để nó 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 dưới đây.

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 sẽ tạo một bảng tổng hợp như sau:

Thêm kết quả công thức nấu ăn trong bảng tổng hợp

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

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

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

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

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 sẽ tạo một bảng tổng hợp như sau:

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

Xoá bảng tổng hợp

Nội dung sau đây spreadsheets.batchUpdate mã mẫu cho biết cách sử dụng UpdateCellsRequest để xoá một 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 thêm "bảng tổng hợp" inch tham số fields, đồng thời bỏ qua trường pivotTable trên liên kết ô.

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

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ác hàng và cột trong bảng tổng hợp

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

Tập hợp con của pivotTable trong Tài nguyên CellData không thể thay đổi riêng lẻ 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 một bảng tổng hợp yêu cầu thay thế bằng một máy mới.

Yêu cầu 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ố mô hình).
  • 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 tổng số lượt bán hàng qua bảng điều khiển. "Carmen" (15 Doanh số bán hàng qua bảng điều khiển) có vẻ như bên trái của "Jessie" (13 Bán hàng qua 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 dưới đây.

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 sẽ tạo một bảng tổng hợp như sau:

Chỉnh sửa kết quả công thức trong bảng tổng hợp

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

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

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

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

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

Trong ví dụ này, trang tính đầu tiên (SOURCE_SHEET_ID) có bảng thô dữ liệu nguồn, trong khi trang tính thứ hai (SHEET_ID) có bảng tổng hợp, neo trên B3. Dấu ngoặc nhọn trống biểu thị 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ề trang tính Mã nhận dạng.

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