Google Sheets API 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 thường gặp trên bảng tổng hợp bằng Sheets API.
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 một bản cập nhật hàng loạt bằng 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 vị trí mà 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. Ví dụ về một dải ô là Sheet1!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 những ví dụ này, giả sử bảng tính đang dùng có dữ liệu nguồn "sales" (doanh số) sau đây trong trang tính đầu tiên ("Sheet1"). 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 dữ liệu từ các trang tính khác 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ố kiểu máy | 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 USD | 4 | Tây | Beth | 1/3/2016 |
| 3 | Cửa ra vào | D-01X | 15 USD | 2 | Nam | Amir | 15/3/2016 |
| 4 | Động cơ | ENG-0134 | $100,00 | 1 | Bắc | Carmen | 20/3/2016 |
| 5 | Khung | FR-0B1 | 34 USD | 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 | 115.000 VND | 7 | Đông | Erik | 16/5/2016 |
| 8 | Bánh xe | W-24 | 20,5 USD | 11 | Nam | Sheldon | 30/4/2016 |
| 9 | Động cơ | ENG-0161 | 330 USD | 2 | Bắc | Jessie | 2/7/2016 |
| 10 | Cửa ra vào | D-01Y | 29 USD | 6 | Tây | Armando | 13/3/2016 |
| 11 | Khung | FR-0B1 | 34 USD | 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 đô la | 13 | Tây | Jessie | 20/6/2016 |
| 14 | Động cơ | ENG-0211 | 283 USD | 1 | Bắc | Amir | 21/6/2016 |
| 15 | Cửa ra vào | D-01X | 15 USD | 2 | Tây | Armando | 3/7/2016 |
| 16 | Khung | FR-0B1 | 34 USD | 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 USD | 13 | Đông | Erik | 19/5/2016 |
| 19 | Cửa ra vào | D-05 | 17,7 USD | 7 | Tây | Beth | 28/6/2016 |
| 20 | Khung | FR-0B1 | 34 USD | 8 | Bắc | Sheldon | 30/3/2016 |
Thêm bảng tổng hợp
Mẫu mã spreadsheets.batchUpdate sau đây cho thấy 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 định cấu hình bảng tổng hợp bằng các thuộc tính sau:
- Một nhóm giá trị (Số lượng) cho biết số lượng 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ố hiệu mẫu). Sắp xếp theo giá trị tăng dần của tổng Số lượng của Khu vực "Miền Tây". Do đó, "Động cơ" (không có doanh số ở miền Tây) xuất hiện phía trên "Cửa" (có 15 doanh số ở miền Tây). Nhóm Số hiệu mẫu 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 lượt bán hàng) 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 doanh số bán hàng cao nhất.
Một lần nữa,
valueBucketđược đặt thành{}. "Miền Bắc" có tổng doanh số thấp nhất, vì vậy, miền 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 sẽ tạo một bảng tổng hợp như sau:
Thêm bảng tổng hợp có giá trị được tính
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 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 bằng các thuộc tính sau:
- Hai nhóm giá trị (Số lượng và Tổng giá). Chỉ số đầu tiên cho biết số lượt bán hàng. Giá trị thứ hai là giá trị được tính dựa trên tích của chi phí của một bộ phận và tổng số lượt bán hàng của bộ phận đó, theo công thức sau:
=Cost*SUM(Quantity). - 3 nhóm hàng (Danh mục mặt hàng, Số hiệu mẫu 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
valueBuckettrongPivotGroup.- Để đơn giản hoá giao diện bảng, yêu cầu này sẽ ẩn các số liệu bán phần cho tất cả các nhóm hàng và cột, ngoại trừ nhóm hàng và cột chính.
- Yêu cầu đặ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 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 sẽ tạo một bảng tổng hợp như sau:
Xoá bảng tổng hợp
Mẫu mã spreadsheets.batchUpdate sau đây cho thấy cách dùng UpdateCellsRequest để xoá một bảng tổng hợp (nếu có) được cố định trên ô A50 của trang tính do SHEET_ID chỉ định.
UpdateCellsRequest có thể xoá một bảng tổng hợp bằng cách thêm "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ẫu mã spreadsheets.batchUpdate sau đây cho biết cách 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 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, để chỉnh sửa bảng tổng hợp, bạn cần thay thế bảng đó bằng một bảng mới.
Yêu cầu này thực hiện những 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ố hiệu mẫu).
- 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 Bảng điều khiển. "Carmen" (15 lượt bán Bảng điều khiển) xuất hiện ở bên trái "Jessie" (13 lượt bán Bảng điều khiển).
- 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 cho 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 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 sẽ tạo một bảng tổng hợp như sau:
Đọc dữ liệu trong bảng tổng hợp
Mẫu mã spreadsheets.get sau đây cho thấy 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ỉ dữ liệu bảng tổng hợp được trả về (thay vì 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 tài nguyên Spreadsheet, trong đó có một đố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 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 cố định trên ô B3. Cặp dấu ngoặc nhọn trống cho biết những 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} } ], }