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 đối với bảng tổng hợp 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 để đảm bảo trung lập về ngôn ngữ. Để tìm hiểu cách triển khai tính năng cập nhật theo lô cho nhiều ngôn ngữ bằ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 các ví dụ này, phần giữ chỗ SPREADSHEET_ID và SHEET_ID cho biết nơi bạn sẽ cung cấp các mã nhận dạng đó. Bạn có thể tìm thấy mã
bảng tính trong URL của bảng tính. Bạn có thể lấy mã 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. Dải ô ví dụ là Sheet1!A1:D5.
Ngoài ra, phần giữ chỗ SOURCE_SHEET_ID cho biết trang tính của bạn có dữ liệu nguồn. Trong những ví dụ này, đây là bảng liệt kê trong phần Dữ liệu nguồn bảng tổng hợp.
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 dữ liệu "doanh số" sau đây trong trang tính đầu tiên ("Trang tính1"). Các chuỗi trong hàng đầu tiên là nhãn cho từng cột. Để xem ví dụ về cách đọc từ các trang tính khác trong bảng tính của bạn, hãy xem phần Ký hiệu A1.
| Đáp | 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 | Vòng đu quay | 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 | Vòng đu quay | 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 | Tiếng Armando | Ngày 13 tháng 3 năm 2016 |
| 11 | Khung | FR-0B1 | 34 đô la | 9 | Nam | Ngọc | 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 | Tiếng Armando | 3/7/2016 |
| 16 | Khung | FR-0B1 | 34 đô la | 6 | Nam | Carmen | 15/7/2016 |
| 17 | Vòng đu quay | W-25 | 20 USD | 8 | Nam | Hannah | 2/5/2016 |
| 18 | Vòng đu quay | 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
Mã mẫu spreadsheets.batchUpdate sau đây 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, neo bảng đó 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. Vì chỉ có một nhóm giá trị, nên 2 chế độ cài đặt
valueLayoutcó thể có là tương đương. - Hai nhóm hàng (Danh mục mặt hàng và Số mô hình). Tiêu chí đầu tiên sắp xếp theo giá trị tăng dần của tổng số Số lượng từ Khu vực "Tây". Do đó, "Động cơ" (không có doanh số bán hàng ở phía Tây) xuất hiện phía trên "Cửa" (với 15 doanh số bán hàng ở phía Tây). 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ả
các khu vực, vì vậy, "W-24" (15 lượt bán hàng) sẽ xuất hiện phía trên "W-25" (8 lượt bán hàng). Bạn có thể thực hiện việc này bằng cách đặt trường
valueBucketthành{}. - 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ố bán hàng ít nhất và do đó, 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 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 một bảng tổng hợp có các giá trị được tính toán
Mã mẫu spreadsheets.batchUpdate sau đây cho biết cách sử dụng UpdateCellsRequest để tạo bảng tổng hợp có nhóm giá trị tính toán 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 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ượng và Tổng giá). Giá trị đầu tiên cho biết số lượt bán hàng. Giá trị thứ hai là giá trị được tính toán dựa trên tích chi phí của một bộ phận và tổng doanh số bán hàng theo công thức sau:
=Cost*SUM(Quantity). - Ba nhóm hàng (Danh mục mặt hàng, Số mô hình và Chi 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, sắp xếp theo thứ tự bảng chữ cái. Bạn có thể thực hiện việc này bằng cách bỏ trường
valueBucketkhỏiPivotGroup. - Để đơn giản hoá giao diện bảng, yêu cầu sẽ ẩ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 sẽ đặt
valueLayoutthànhVERTICALđể cải thiện giao diện bảng.valueLayoutchỉ 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 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:
Xoá bảng tổng hợp
Mã mẫu 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 trên ô 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 ô 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
Mã mẫu spreadsheets.batchUpdate sau đây 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 từng tập hợp con của trường pivotTable 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 một bảng tổng hợp đòi hỏi bạn phải thay thế bảng đó bằng một bảng 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 được sắp xếp theo thứ tự giảm dần theo tổng số lượt bán hàng trong Bảng điều khiển. "Carmen" (15 Bảng bán hàng) xuất hiện ở bên trái của "Jessie" (13 Bảng bán hàng).
- Thu gọn cột cho từng Khu vực, ngoại trừ "Miền Tây", ẩn nhóm Nhân viên bán hàng ở khu vực đó. Bạn có thể thực hiện việc này bằng cách đặt
collapsedthànhtruetrongvalueMetadatacho cột đó trong nhóm cột Region (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:
Đọ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ừ một bảng tính. Tham số truy vấn fields chỉ định rằng chỉ trả về dữ liệu bảng tổng hợp (thay vì trả về dữ liệu giá trị của ô).
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 một tài nguyên Spreadsheet chứa đối tượng Sheet với các phần tử SheetProperties. Ngoài ra còn có một mảng các phần tử GridData chứa thông tin về PivotTable.
Thông tin về bảng tổng hợp nằm trong tài nguyên CellData của trang tính cho ô mà bảng được neo vào (tức là góc trên 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ị loại khỏi 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 trống cho biết 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
}
}
],
}