Thông tin về lớp học lập trình này
1. Giới thiệu
Chào mừng bạn đến với phần thứ tư của danh sách phát lớp học lập trình Kiến thức cơ bản về Apps Script với Google Trang tính.
Khi hoàn thành lớp học lập trình này, bạn có thể tìm hiểu cách định dạng dữ liệu trong bảng tính bằng Apps Script và viết các hàm để tạo bảng tính có tổ chức chứa đầy dữ liệu được định dạng lấy từ một API công khai.
Kiến thức bạn sẽ học được
- Cách áp dụng nhiều thao tác định dạng trong Google Trang tính bằng Apps Script.
- Cách chuyển đổi danh sách các đối tượng JSON và thuộc tính của các đối tượng đó thành một trang dữ liệu có tổ chức bằng Apps Script.
Trước khi bắt đầu
Đây là lớp học lập trình thứ tư trong danh sách phát Kiến thức cơ bản về Apps Script với Google Trang tính. Trước khi bắt đầu lớp học lập trình này, hãy nhớ hoàn thành các lớp học lập trình trước đó:
Bạn cần có
- Hiểu rõ các chủ đề cơ bản về Apps Script đã được khám phá trong các lớp học lập trình trước đây của danh sách phát này.
- Hiểu biết cơ bản về trình chỉnh sửa Apps Script
- Hiểu biết cơ bản về Google Trang tính
- Có thể đọc Ký hiệu A1 của Trang tính
- Hiểu biết cơ bản về JavaScript và lớp
Stringcủa ngôn ngữ này
2. Thiết lập
Trước khi tiếp tục, bạn cần có một bảng tính chứa một số dữ liệu. Như trước đây, chúng tôi đã cung cấp một bảng dữ liệu mà bạn có thể sao chép cho các bài tập này. Hãy làm theo các bước sau:
- Nhấp vào đường liên kết này để sao chép trang dữ liệu, rồi nhấp vào Tạo bản sao. Bảng tính mới sẽ được đặt trong thư mục Google Drive của bạn và có tên là "Bản sao của Định dạng dữ liệu".
- Nhấp vào tiêu đề bảng tính rồi thay đổi từ "Bản sao của Định dạng dữ liệu" thành "Định dạng dữ liệu". Trang tính của bạn sẽ có dạng như sau, với một số thông tin cơ bản về 3 phần đầu của loạt phim Chiến tranh giữa các vì sao:
- Chọn Tiện ích mở rộng > Apps Script để mở trình chỉnh sửa tập lệnh.
- Nhấp vào tiêu đề dự án Apps Script rồi thay đổi từ "Dự án chưa có tiêu đề" thành "Định dạng dữ liệu". Nhấp vào Đổi tên để lưu nội dung thay đổi về tiêu đề.
Với bảng tính và dự án này, bạn đã sẵn sàng bắt đầu lớp học lập trình. Chuyển sang phần tiếp theo để bắt đầu tìm hiểu về định dạng cơ bản trong Apps Script.
3. Tạo một trình đơn tuỳ chỉnh
Bạn có thể áp dụng một số phương thức định dạng cơ bản trong Apps Script cho Trang tính. Các bài tập sau đây minh hoạ một số cách định dạng dữ liệu. Để giúp bạn kiểm soát các thao tác định dạng, hãy tạo một trình đơn tuỳ chỉnh có các mục bạn cần. Quy trình tạo trình đơn tuỳ chỉnh đã được mô tả trong lớp học lập trình Làm việc với dữ liệu, nhưng chúng ta sẽ tóm tắt lại quy trình đó tại đây.
Triển khai
Hãy tạo một trình đơn tuỳ chỉnh.
- Trong trình chỉnh sửa Apps Script, hãy thay thế mã trong dự án tập lệnh bằng mã sau:
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the spreadsheet's user-interface object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addToUi();
}
- Lưu dự án tập lệnh.
- Trong trình chỉnh sửa tập lệnh, hãy chọn
onOpentrong danh sách hàm rồi nhấp vào Chạy. Thao tác này sẽ chạyonOpen()để tạo lại trình đơn bảng tính, vì vậy, bạn không phải tải lại bảng tính.
Đánh giá mã
Hãy xem xét đoạn mã này để hiểu cách hoạt động của nó. Trong onOpen(), dòng đầu tiên sử dụng phương thức getUi() để lấy đối tượng Ui đại diện cho giao diện người dùng của bảng tính đang hoạt động mà tập lệnh này được liên kết.
Các dòng tiếp theo sẽ tạo một trình đơn (Quick formats), thêm các mục trong trình đơn (Format row header, Format column header và Format dataset) vào trình đơn, rồi thêm trình đơn vào giao diện của bảng tính. Việc này được thực hiện bằng các phương thức createMenu(caption), addItem(caption, functionName) và addToUi().
Phương thức addItem(caption, functionName) tạo mối liên kết giữa nhãn mục trong trình đơn và một hàm Apps Script chạy khi mục trong trình đơn được chọn. Ví dụ: khi bạn chọn mục Format row header trong trình đơn, Trang tính sẽ cố gắng chạy hàm formatRowHeader() (hàm này chưa tồn tại).
Kết quả
Trong bảng tính, hãy nhấp vào trình đơn Quick formats để xem các mục mới trong trình đơn:
Việc nhấp vào những mục này sẽ gây ra lỗi vì bạn chưa triển khai các hàm tương ứng. Vì vậy, hãy triển khai các hàm đó ở bước tiếp theo.
4. Định dạng hàng tiêu đề
Các tập dữ liệu trong bảng tính thường có hàng tiêu đề để xác định dữ liệu trong mỗi cột. Bạn nên định dạng các hàng tiêu đề để tách riêng chúng với phần còn lại của dữ liệu trong bảng tính.
Trong lớp học lập trình đầu tiên, bạn đã tạo một macro cho tiêu đề và điều chỉnh mã của macro đó. Tại đây, bạn sẽ định dạng một hàng tiêu đề từ đầu bằng Apps Script. Hàng tiêu đề mà bạn sẽ tạo sẽ in đậm văn bản tiêu đề, tô màu nền thành màu xanh dương đậm, tô màu văn bản thành màu trắng và thêm một số đường viền liền.
Triển khai
Để triển khai thao tác định dạng, bạn sẽ sử dụng các phương thức Dịch vụ bảng tính mà bạn đã dùng trước đây, nhưng giờ đây, bạn cũng sẽ sử dụng một số phương thức định dạng của dịch vụ này. Hãy làm theo các bước sau:
- Trong trình chỉnh sửa Apps Script, hãy thêm hàm sau vào cuối dự án tập lệnh:
/**
* Formats top row of sheet using our header row style.
*/
function formatRowHeader() {
// Get the current active sheet and the top row's range.
var sheet = SpreadsheetApp.getActiveSheet();
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
// Apply each format to the top row: bold white text,
// blue-green background, and a solid black border
// around the cells.
headerRange
.setFontWeight('bold')
.setFontColor('#ffffff')
.setBackground('#007272')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
- Lưu dự án tập lệnh.
Đánh giá mã
Giống như nhiều tác vụ định dạng khác, mã Apps Script để triển khai tác vụ này rất đơn giản. Hai dòng đầu tiên sử dụng các phương thức mà bạn đã thấy trước đây để lấy thông tin tham chiếu đến trang tính đang hoạt động (sheet) và hàng trên cùng của trang tính (headerRange). Phương thức Sheet.getRange(row, column, numRows, numColumns) chỉ định hàng trên cùng, chỉ bao gồm những cột có dữ liệu. Phương thức Sheet.getLastColumn() trả về chỉ mục cột của cột cuối cùng chứa dữ liệu trong trang tính. Trong ví dụ của chúng tôi, đó là cột E (url).
Phần còn lại của mã chỉ cần gọi các phương thức Range khác nhau để áp dụng các lựa chọn định dạng cho tất cả các ô trong headerRange. Để giữ cho mã dễ đọc, chúng ta sử dụng chuỗi phương thức để gọi từng phương thức định dạng lần lượt:
Range.setFontWeight(fontWeight)dùng để đặt độ đậm của phông chữ thành đậm.Range.setFontColor(color)dùng để đặt màu phông chữ thành màu trắng.Range.setBackground(color)được dùng để đặt màu nền thành màu xanh dương pha xanh lục.setBorder(top, left, bottom, right, vertical, horizontal, color, style)đặt một đường viền đen liền xung quanh các ô trong dải ô.
Phương thức cuối cùng có một số tham số, vì vậy, hãy xem xét từng tham số. Bốn tham số đầu tiên ở đây (tất cả đều được đặt thành true) cho Apps Script biết rằng đường viền sẽ được thêm vào phía trên, phía dưới, bên trái và bên phải của dải ô. Tham số thứ năm và thứ sáu (null và null) hướng dẫn Apps Script tránh thay đổi bất kỳ đường viền nào trong phạm vi đã chọn. Tham số thứ bảy (null) cho biết màu của đường viền phải mặc định là màu đen. Cuối cùng, tham số cuối cùng chỉ định loại kiểu đường viền cần sử dụng, lấy từ các lựa chọn do SpreadsheetApp.BorderStyle cung cấp.
Kết quả
Bạn có thể xem chức năng định dạng hoạt động bằng cách làm như sau:
- Nếu bạn chưa thực hiện, hãy lưu dự án tập lệnh trong trình chỉnh sửa Apps Script.
- Nhấp vào mục trình đơn Định dạng nhanh > Định dạng tiêu đề hàng.
Kết quả sẽ có dạng như sau:
Giờ đây, bạn đã tự động hoá một thao tác định dạng. Phần tiếp theo áp dụng cùng một kỹ thuật để tạo một kiểu định dạng khác cho tiêu đề cột.
5. Định dạng tiêu đề cột
Nếu có thể tạo tiêu đề hàng phù hợp với bạn, thì bạn cũng có thể tạo tiêu đề cột. Tiêu đề cột giúp tăng khả năng đọc cho một số tập dữ liệu. Ví dụ: bạn có thể cải thiện cột tiêu đề trong bảng tính này bằng các lựa chọn định dạng sau:
- In đậm văn bản
- In nghiêng văn bản
- Thêm đường viền ô
- Chèn siêu liên kết bằng nội dung trong cột url. Sau khi thêm các siêu liên kết này, bạn có thể xoá cột url để giúp trang tính gọn gàng hơn.
Tiếp theo, bạn sẽ triển khai hàm formatColumnHeader() để áp dụng những thay đổi này cho cột đầu tiên trong trang tính. Để giúp mã dễ đọc hơn, bạn cũng sẽ triển khai 2 hàm trợ giúp.
Triển khai
Như trước đây, bạn cần thêm một hàm để tự động định dạng tiêu đề cột. Hãy làm theo các bước sau:
- Trong trình chỉnh sửa Apps Script, hãy thêm hàm
formatColumnHeader()sau vào cuối dự án tập lệnh:
/**
* Formats the column header of the active sheet.
*/
function formatColumnHeader() {
var sheet = SpreadsheetApp.getActiveSheet();
// Get total number of rows in data range, not including
// the header row.
var numRows = sheet.getDataRange().getLastRow() - 1;
// Get the range of the column header.
var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
// Apply text formatting and add borders.
columnHeaderRange
.setFontWeight('bold')
.setFontStyle('italic')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
// Call helper method to hyperlink the first column contents
// to the url column contents.
hyperlinkColumnHeaders_(columnHeaderRange, numRows);
}
- Thêm các hàm trợ giúp sau vào cuối dự án tập lệnh, sau hàm
formatColumnHeader():
/**
* Helper function that hyperlinks the column header with the
* 'url' column contents. The function then removes the column.
*
* @param {object} headerRange The range of the column header
* to update.
* @param {number} numRows The size of the column header.
*/
function hyperlinkColumnHeaders_(headerRange, numRows) {
// Get header and url column indices.
var headerColIndex = 1;
var urlColIndex = columnIndexOf_('url');
// Exit if the url column is missing.
if(urlColIndex == -1)
return;
// Get header and url cell values.
var urlRange =
headerRange.offset(0, urlColIndex - headerColIndex);
var headerValues = headerRange.getValues();
var urlValues = urlRange.getValues();
// Updates header values to the hyperlinked header values.
for(var row = 0; row < numRows; row++){
headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
+ '","' + headerValues[row] + '")';
}
headerRange.setValues(headerValues);
// Delete the url column to clean up the sheet.
SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}
/**
* Helper function that goes through the headers of all columns
* and returns the index of the column with the specified name
* in row 1. If a column with that name does not exist,
* this function returns -1. If multiple columns have the same
* name in row 1, the index of the first one discovered is
* returned.
*
* @param {string} colName The name to find in the column
* headers.
* @return The index of that column in the active sheet,
* or -1 if the name isn't found.
*/
function columnIndexOf_(colName) {
// Get the current column names.
var sheet = SpreadsheetApp.getActiveSheet();
var columnHeaders =
sheet.getRange(1, 1, 1, sheet.getLastColumn());
var columnNames = columnHeaders.getValues();
// Loops through every column and returns the column index
// if the row 1 value of that column matches colName.
for(var col = 1; col <= columnNames[0].length; col++)
{
if(columnNames[0][col-1] === colName)
return col;
}
// Returns -1 if a column named colName does not exist.
return -1;
}
- Lưu dự án tập lệnh.
Đánh giá mã
Hãy xem xét mã trong từng hàm trong số 3 hàm này một cách riêng biệt:
formatColumnHeader()
Như bạn có thể đã dự đoán, vài dòng đầu tiên của hàm này sẽ đặt các biến tham chiếu đến trang tính và dải ô mà chúng ta quan tâm:
- Trang tính đang hoạt động được lưu trữ trong
sheet. - Số hàng trong tiêu đề cột được tính và lưu trong
numRows. Ở đây, mã sẽ trừ đi một để số hàng không bao gồm tiêu đề cột:title. - Phạm vi bao gồm tiêu đề cột được lưu trữ trong
columnHeaderRange.
Sau đó, mã sẽ áp dụng đường viền và in đậm cho dải tiêu đề cột, giống như trong formatRowHeader(). Ở đây, Range.setFontStyle(fontStyle) cũng được dùng để in nghiêng văn bản.
Việc thêm siêu liên kết vào cột tiêu đề phức tạp hơn, vì vậy formatColumnHeader() gọi hyperlinkColumnHeaders_(headerRange, numRows) để xử lý việc này. Điều này giúp mã gọn gàng và dễ đọc.
hyperlinkColumnHeaders_(headerRange, numRows)
Hàm trợ giúp này trước tiên xác định chỉ mục cột của tiêu đề (giả sử là chỉ mục 1) và cột url. Thao tác này gọi columnIndexOf_('url') để lấy chỉ mục cột url. Nếu không tìm thấy cột url, phương thức sẽ thoát mà không sửa đổi bất kỳ dữ liệu nào.
Hàm này lấy một dải ô mới (urlRange) bao gồm các URL tương ứng với các hàng cột tiêu đề. Việc này được thực hiện bằng phương thức Range.offset(rowOffset, columnOffset), đảm bảo rằng hai dải ô sẽ có cùng kích thước. Sau đó, các giá trị trong cả cột headerColumn và url sẽ được truy xuất (headerValues và urlValues).
Sau đó, hàm này sẽ lặp lại từng giá trị ô tiêu đề cột và thay thế giá trị đó bằng một công thức =HYPERLINK() của Trang tính được tạo bằng tiêu đề và nội dung cột url. Sau đó, các giá trị tiêu đề đã được sửa đổi sẽ được chèn vào trang tính bằng cách sử dụng Range.setValues(values).
Cuối cùng, để giữ cho trang tính gọn gàng và loại bỏ thông tin thừa, Sheet.deleteColumn(columnPosition) được gọi để xoá cột url.
columnIndexOf_(colName)
Hàm trợ giúp này chỉ là một hàm tiện ích đơn giản, tìm kiếm một tên cụ thể trong hàng đầu tiên của trang tính. Ba dòng đầu tiên sử dụng các phương thức mà bạn đã thấy để lấy danh sách tên tiêu đề cột từ hàng 1 của bảng tính. Các tên này được lưu trữ trong biến columnNames.
Sau đó, hàm sẽ xem xét từng tên theo thứ tự. Nếu tìm thấy một cột trùng khớp với tên đang được tìm kiếm, thì phương thức này sẽ dừng và trả về chỉ mục của cột. Nếu đến cuối danh sách tên mà không tìm thấy tên, hàm này sẽ trả về -1 để báo hiệu rằng tên không được tìm thấy.
Kết quả
Bạn có thể xem chức năng định dạng hoạt động bằng cách làm như sau:
- Nếu bạn chưa thực hiện, hãy lưu dự án tập lệnh trong trình chỉnh sửa Apps Script.
- Nhấp vào mục trình đơn Định dạng nhanh > Định dạng tiêu đề cột.
Kết quả sẽ có dạng như sau:
Giờ đây, bạn đã tự động hoá một nhiệm vụ định dạng khác. Sau khi định dạng tiêu đề cột và hàng, phần tiếp theo sẽ hướng dẫn cách định dạng dữ liệu.
6. Định dạng tập dữ liệu
Bây giờ bạn đã có tiêu đề, hãy tạo một hàm định dạng phần còn lại của dữ liệu trong trang tính. Chúng ta sẽ sử dụng các lựa chọn định dạng sau:
- Màu nền xen kẽ của hàng (còn gọi là phân dải)
- Thay đổi định dạng ngày
- Áp dụng đường viền
- Tự động điều chỉnh kích thước tất cả các cột và hàng
Giờ đây, bạn sẽ tạo một hàm formatDataset() và một phương thức trợ giúp bổ sung để áp dụng các định dạng này cho dữ liệu trang tính.
Triển khai
Như trước đây, hãy thêm một hàm để tự động hoá việc định dạng dữ liệu. Hãy làm theo các bước sau:
- Trong trình chỉnh sửa Apps Script, hãy thêm hàm
formatDataset()sau vào cuối dự án tập lệnh:
/**
* Formats the sheet data, excluding the header row and column.
* Applies the border and banding, formats the 'release_date'
* column, and autosizes the columns and rows.
*/
function formatDataset() {
// Get the active sheet and data range.
var sheet = SpreadsheetApp.getActiveSheet();
var fullDataRange = sheet.getDataRange();
// Apply row banding to the data, excluding the header
// row and column. Only apply the banding if the range
// doesn't already have banding set.
var noHeadersRange = fullDataRange.offset(
1, 1,
fullDataRange.getNumRows() - 1,
fullDataRange.getNumColumns() - 1);
if (! noHeadersRange.getBandings()[0]) {
// The range doesn't already have banding, so it's
// safe to apply it.
noHeadersRange.applyRowBanding(
SpreadsheetApp.BandingTheme.LIGHT_GREY,
false, false);
}
// Call a helper function to apply date formatting
// to the column labeled 'release_date'.
formatDates_( columnIndexOf_('release_date') );
// Set a border around all the data, and resize the
// columns and rows to fit.
fullDataRange.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
- Thêm hàm trợ giúp sau vào cuối dự án tập lệnh, sau hàm
formatDataset():
/**
* Helper method that applies a
* "Month Day, Year (Day of Week)" date format to the
* indicated column in the active sheet.
*
* @param {number} colIndex The index of the column
* to format.
*/
function formatDates_(colIndex) {
// Exit if the given column index is -1, indicating
// the column to format isn't present in the sheet.
if (colIndex < 0)
return;
// Set the date format for the date column, excluding
// the header row.
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
.setNumberFormat("mmmm dd, yyyy (dddd)");
}
- Lưu dự án tập lệnh.
Đánh giá mã
Hãy xem xét mã trong từng hàm riêng biệt trong số 2 hàm này:
formatDataset()
Hàm này tuân theo một mẫu tương tự như các hàm định dạng trước đó mà bạn đã triển khai. Trước tiên, hàm này sẽ lấy các biến để lưu trữ thông tin tham chiếu đến trang tính đang hoạt động (sheet) và dải ô dữ liệu (fullDataRange).
Thứ hai, phương thức này sử dụng phương thức Range.offset(rowOffset, columnOffset, numRows, numColumns) để tạo một dải ô (noHeadersRange) bao gồm tất cả dữ liệu trong trang tính, ngoại trừ tiêu đề cột và hàng. Sau đó, mã sẽ xác minh xem dải ô mới này có dải ô hiện có hay không (bằng cách sử dụng Range.getBandings()). Điều này là cần thiết vì Apps Script sẽ báo lỗi nếu bạn cố gắng áp dụng dải ô mới khi đã có dải ô. Nếu không có hiện tượng sọc, hàm sẽ thêm một sọc màu xám nhạt bằng cách sử dụng Range.applyRowBanding(bandingTheme, showHeader, showFooter). Nếu không, hàm sẽ chuyển sang bước tiếp theo.
Bước tiếp theo sẽ gọi hàm trợ giúp formatDates_(colIndex) để định dạng ngày trong cột có nhãn "release_date" (được mô tả bên dưới). Cột được chỉ định bằng hàm trợ giúp columnIndexOf_(colName) mà bạn đã triển khai trước đó.
Cuối cùng, quá trình định dạng sẽ kết thúc bằng việc thêm một đường viền khác (như trước) và tự động đổi kích thước mọi cột và hàng cho phù hợp với dữ liệu mà chúng chứa bằng cách sử dụng các phương thức Sheet.autoResizeColumns(columnPosition) và Sheet.autoResizeColumns(columnPosition).
formatDates_(colIndex)
Hàm trợ giúp này áp dụng một định dạng ngày cụ thể cho một cột bằng cách sử dụng chỉ mục cột được cung cấp. Cụ thể, hàm này định dạng các giá trị ngày dưới dạng "Tháng Ngày, Năm (Ngày trong tuần)".
Trước tiên, hàm này sẽ xác minh chỉ mục cột được cung cấp có hợp lệ hay không (tức là 0 trở lên). Nếu không, hàm sẽ trả về mà không làm gì cả. Bước kiểm tra này giúp ngăn chặn các lỗi có thể xảy ra nếu, chẳng hạn như trang tính không có cột "release_date".
Sau khi chỉ mục cột được xác thực, hàm sẽ lấy dải ô bao gồm cột đó (không bao gồm hàng tiêu đề) và sử dụng Range.setNumberFormat(numberFormat) để áp dụng định dạng.
Kết quả
Bạn có thể xem chức năng định dạng hoạt động bằng cách làm như sau:
- Nếu bạn chưa thực hiện, hãy lưu dự án tập lệnh trong trình chỉnh sửa Apps Script.
- Nhấp vào mục trình đơn Định dạng nhanh > Định dạng tập dữ liệu.
Kết quả sẽ có dạng như sau:
Bạn đã tự động hoá một nhiệm vụ định dạng khác. Giờ đây, bạn đã có các lệnh định dạng này, hãy thêm nhiều dữ liệu hơn để áp dụng các lệnh đó.
7. Tìm nạp và định dạng dữ liệu API
Cho đến thời điểm này trong lớp học lập trình này, bạn đã thấy cách sử dụng Apps Script như một phương tiện thay thế để định dạng bảng tính. Tiếp theo, bạn sẽ viết mã để lấy dữ liệu từ một API công khai, chèn dữ liệu đó vào bảng tính và định dạng dữ liệu đó để có thể đọc được.
Trong lớp học lập trình gần đây nhất, bạn đã tìm hiểu cách lấy dữ liệu từ một API. Bạn sẽ sử dụng các kỹ thuật tương tự ở đây. Trong bài tập này, chúng ta sẽ sử dụng Star Wars API (SWAPI) công khai để điền dữ liệu vào bảng tính. Cụ thể, bạn sẽ dùng API này để lấy thông tin về các nhân vật chính xuất hiện trong 3 phần phim Star Wars đầu tiên.
Mã của bạn sẽ gọi API để lấy một lượng lớn dữ liệu JSON, phân tích cú pháp phản hồi, đặt dữ liệu vào một trang tính mới, rồi định dạng trang tính đó.
Triển khai
Trong phần này, bạn sẽ thêm một số mục khác vào trình đơn. Mỗi mục trong trình đơn sẽ gọi một tập lệnh bao bọc truyền các biến dành riêng cho mục đến hàm chính (createResourceSheet_()). Bạn sẽ triển khai hàm này và 3 hàm trợ giúp bổ sung. Như trước đây, các hàm trợ giúp giúp tách biệt các phần được phân chia một cách hợp lý của tác vụ và giúp mã dễ đọc.
Thực hiện các thao tác sau:
- Trong trình chỉnh sửa Apps Script, hãy cập nhật hàm
onOpen()trong dự án tập lệnh của bạn sao cho khớp với nội dung sau:
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the Ui object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addSeparator()
.addSubMenu(ui.createMenu('Create character sheet')
.addItem('Episode IV', 'createPeopleSheetIV')
.addItem('Episode V', 'createPeopleSheetV')
.addItem('Episode VI', 'createPeopleSheetVI')
)
.addToUi();
}
- Lưu dự án tập lệnh.
- Trong trình chỉnh sửa tập lệnh, hãy chọn
onOpentrong danh sách hàm rồi nhấp vào Chạy. Thao tác này sẽ chạyonOpen()để tạo lại trình đơn bảng tính bằng các lựa chọn mới mà bạn đã thêm. - Để tạo một tệp Apps Script, bên cạnh Tệp, hãy nhấp vào biểu tượng Thêm tệp
> Tập lệnh. - Đặt tên cho tập lệnh mới là "API" rồi nhấn Enter. (Apps Script sẽ tự động thêm đuôi
.gsvào tên tệp tập lệnh.) - Thay thế mã trong tệp API.gs mới bằng mã sau:
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode IV.
*/
function createPeopleSheetIV() {
createResourceSheet_('characters', 1, "IV");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode V.
*/
function createPeopleSheetV() {
createResourceSheet_('characters', 2, "V");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode VI.
*/
function createPeopleSheetVI() {
createResourceSheet_('characters', 3, "VI");
}
/**
* Creates a formatted sheet filled with user-specified
* information from the Star Wars API. If the sheet with
* this data exists, the sheet is overwritten with the API
* information.
*
* @param {string} resourceType The type of resource.
* @param {number} idNumber The identification number of the film.
* @param {number} episodeNumber The Star Wars film episode number.
* This is only used in the sheet name.
*/
function createResourceSheet_(
resourceType, idNumber, episodeNumber) {
// Fetch the basic film data from the API.
var filmData = fetchApiResourceObject_(
"https://swapi.dev/api/films/" + idNumber);
// Extract the API URLs for each resource so the code can
// call the API to get more data about each individually.
var resourceUrls = filmData[resourceType];
// Fetch each resource from the API individually and push
// them into a new object list.
var resourceDataList = [];
for(var i = 0; i < resourceUrls.length; i++){
resourceDataList.push(
fetchApiResourceObject_(resourceUrls[i])
);
}
// Get the keys used to reference each part of data within
// the resources. The keys are assumed to be identical for
// each object since they're all the same resource type.
var resourceObjectKeys = Object.keys(resourceDataList[0]);
// Create the sheet with the appropriate name. It
// automatically becomes the active sheet when it's created.
var resourceSheet = createNewSheet_(
"Episode " + episodeNumber + " " + resourceType);
// Add the API data to the new sheet, using each object
// key as a column header.
fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
// Format the new sheet using the same styles the
// 'Quick Formats' menu items apply. These methods all
// act on the active sheet, which is the one just created.
formatRowHeader();
formatColumnHeader();
formatDataset();
}
- Thêm các hàm trợ giúp sau vào cuối tệp dự án tập lệnh API.gs:
/**
* Helper function that retrieves a JSON object containing a
* response from a public API.
*
* @param {string} url The URL of the API object being fetched.
* @return {object} resourceObject The JSON object fetched
* from the URL request to the API.
*/
function fetchApiResourceObject_(url) {
// Make request to API and get response.
var response =
UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
// Parse and return the response as a JSON object.
var json = response.getContentText();
var responseObject = JSON.parse(json);
return responseObject;
}
/**
* Helper function that creates a sheet or returns an existing
* sheet with the same name.
*
* @param {string} name The name of the sheet.
* @return {object} The created or existing sheet
* of the same name. This sheet becomes active.
*/
function createNewSheet_(name) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Returns an existing sheet if it has the specified
// name. Activates the sheet before returning.
var sheet = ss.getSheetByName(name);
if (sheet) {
return sheet.activate();
}
// Otherwise it makes a sheet, set its name, and returns it.
// New sheets created this way automatically become the active
// sheet.
sheet = ss.insertSheet(name);
return sheet;
}
/**
* Helper function that adds API data to the sheet.
* Each object key is used as a column header in the new sheet.
*
* @param {object} resourceSheet The sheet object being modified.
* @param {object} objectKeys The list of keys for the resources.
* @param {object} resourceDataList The list of API
* resource objects containing data to add to the sheet.
*/
function fillSheetWithData_(
resourceSheet, objectKeys, resourceDataList) {
// Set the dimensions of the data range being added to the sheet.
var numRows = resourceDataList.length;
var numColumns = objectKeys.length;
// Get the resource range and associated values array. Add an
// extra row for the column headers.
var resourceRange =
resourceSheet.getRange(1, 1, numRows + 1, numColumns);
var resourceValues = resourceRange.getValues();
// Loop over each key value and resource, extracting data to
// place in the 2D resourceValues array.
for (var column = 0; column < numColumns; column++) {
// Set the column header.
var columnHeader = objectKeys[column];
resourceValues[0][column] = columnHeader;
// Read and set each row in this column.
for (var row = 1; row < numRows + 1; row++) {
var resource = resourceDataList[row - 1];
var value = resource[columnHeader];
resourceValues[row][column] = value;
}
}
// Remove any existing data in the sheet and set the new values.
resourceSheet.clear()
resourceRange.setValues(resourceValues);
}
- Lưu dự án tập lệnh.
Đánh giá mã
Bạn vừa thêm rất nhiều mã. Hãy xem xét từng hàm riêng lẻ để hiểu cách chúng hoạt động:
onOpen()
Ở đây, bạn đã thêm một số mục vào trình đơn Quick formats. Bạn đã đặt một đường phân cách, sau đó sử dụng phương thức Menu.addSubMenu(menu) để tạo cấu trúc trình đơn lồng nhau với 3 mục mới. Các mục mới được thêm bằng phương thức Menu.addItem(caption, functionName).
Hàm bao bọc
Tất cả các mục trong trình đơn được thêm đều thực hiện một thao tác tương tự: chúng đang cố gắng tạo một trang tính có dữ liệu được lấy từ SWAPI. Điểm khác biệt duy nhất là mỗi video tập trung vào một bộ phim khác nhau.
Sẽ rất thuận tiện nếu bạn viết một hàm duy nhất để tạo trang tính và hàm này chấp nhận một tham số để xác định bộ phim cần dùng. Tuy nhiên, phương thức Menu.addItem(caption, functionName) không cho phép bạn truyền các tham số đến phương thức này khi được trình đơn gọi. Vậy làm cách nào để tránh viết cùng một mã ba lần?
Câu trả lời là hàm bao bọc. Đây là những hàm đơn giản mà bạn có thể gọi để gọi ngay một hàm khác với các tham số cụ thể đã đặt.
Ở đây, mã sử dụng 3 hàm bao bọc: createPeopleSheetIV(), createPeopleSheetV() và createPeopleSheetVI(). Các mục trong trình đơn được liên kết với những chức năng này. Khi người dùng nhấp vào một mục trong trình đơn, hàm bao bọc sẽ thực thi và gọi ngay hàm tạo trang tính chính createResourceSheet_(resourceType, idNumber, episodeNumber), truyền các tham số phù hợp cho mục trong trình đơn. Trong trường hợp này, điều đó có nghĩa là yêu cầu hàm tạo trang tính tạo một trang tính chứa dữ liệu về các nhân vật chính trong một trong các bộ phim Star Wars.
createResourceSheet_(resourceType, idNumber, episodeNumber)
Đây là hàm chính của trình tạo trang tính cho bài tập này. Với sự trợ giúp của một số hàm trợ giúp, tập lệnh này sẽ lấy dữ liệu API, phân tích cú pháp dữ liệu đó, tạo một trang tính, ghi dữ liệu API vào trang tính, rồi định dạng trang tính bằng các hàm mà bạn đã tạo trong các phần trước. Hãy xem xét thông tin chi tiết:
Trước tiên, hàm này sử dụng fetchApiResourceObject_(url) để đưa ra yêu cầu về API nhằm truy xuất thông tin cơ bản về phim. Phản hồi của API bao gồm một tập hợp các URL mà mã có thể dùng để biết thêm thông tin chi tiết về những người cụ thể (được gọi là tài nguyên) trong các bộ phim. Mã này thu thập tất cả dữ liệu trong mảng resourceUrls.
Tiếp theo, mã này sử dụng fetchApiResourceObject_(url) nhiều lần để gọi API cho mọi URL tài nguyên trong resourceUrls. Kết quả được lưu trữ trong mảng resourceDataList. Mỗi phần tử của mảng này là một đối tượng mô tả một nhân vật khác trong phim.
Các đối tượng dữ liệu tài nguyên có một số khoá chung liên kết đến thông tin về nhân vật đó. Ví dụ: khoá "name" ánh xạ đến tên của nhân vật trong phim. Chúng tôi giả định rằng các khoá cho mỗi đối tượng dữ liệu tài nguyên đều giống nhau, vì chúng được dùng để sử dụng các cấu trúc đối tượng chung. Sau này, bạn sẽ cần danh sách khoá, vì vậy mã này sẽ lưu trữ danh sách khoá trong resourceObjectKeys bằng phương thức Object.keys() của JavaScript.
Tiếp theo, hàm tạo sẽ gọi hàm trợ giúp createNewSheet_(name) để tạo trang tính nơi dữ liệu mới sẽ được đặt. Việc gọi hàm trợ giúp này cũng sẽ kích hoạt trang tính mới.
Sau khi trang tính được tạo, hàm trợ giúp fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) sẽ được gọi để thêm tất cả dữ liệu API vào trang tính.
Cuối cùng, tất cả các hàm định dạng mà bạn đã tạo trước đó sẽ được gọi để áp dụng cùng các quy tắc định dạng cho dữ liệu mới. Vì trang tính mới là trang tính đang hoạt động, nên mã có thể sử dụng lại các hàm này mà không cần sửa đổi.
fetchApiResourceObject_(url)
Hàm trợ giúp này tương tự như hàm trợ giúp fetchBookData_(ISBN) được dùng trong lớp học lập trình trước đó Làm việc với dữ liệu. Thao tác này sẽ lấy URL đã cho và sử dụng phương thức UrlFetchApp.fetch(url, params) để nhận phản hồi. Sau đó, phản hồi sẽ được phân tích cú pháp thành một đối tượng JSON bằng cách sử dụng HTTPResponse.getContextText() và các phương thức JSON.parse(json) của JavaScript. Sau đó, đối tượng JSON thu được sẽ được trả về.
createNewSheet_(name)
Hàm trợ giúp này khá đơn giản. Trước tiên, hàm này sẽ xác minh xem có trang tính nào có tên đã cho trong bảng tính hay không. Nếu có, hàm sẽ kích hoạt và trả về trang tính đó.
Nếu không có trang tính nào, hàm sẽ tạo trang tính bằng Spreadsheet.insertSheet(sheetName), kích hoạt trang tính đó và trả về trang tính mới.
fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)
Hàm trợ giúp này chịu trách nhiệm điền dữ liệu API vào trang tính mới. Hàm này lấy trang tính mới, danh sách khoá đối tượng và danh sách đối tượng tài nguyên API làm tham số. Mỗi khoá đối tượng đại diện cho một cột trong trang tính mới và mỗi đối tượng tài nguyên đại diện cho một hàng.
Trước tiên, hàm này tính toán số lượng hàng và cột cần thiết để trình bày dữ liệu API mới. Đây là kích thước của danh sách tài nguyên và khoá tương ứng. Sau đó, hàm này sẽ xác định một dải ô đầu ra (resourceRange) nơi dữ liệu sẽ được đặt, đồng thời thêm một hàng bổ sung để lưu trữ tiêu đề cột. Biến resourceValues lưu giữ một mảng giá trị 2D được trích xuất từ resourceRange.
Sau đó, hàm này sẽ lặp lại mọi khoá đối tượng trong danh sách objectKeys. Khoá được đặt làm tiêu đề cột, sau đó vòng lặp thứ hai sẽ duyệt qua mọi đối tượng tài nguyên. Đối với mỗi cặp (hàng, cột), thông tin API tương ứng sẽ được sao chép vào phần tử resourceValues[row][column].
Sau khi resourceValues được điền, trang đích sẽ bị xoá bằng Sheet.clear() trong trường hợp trang này chứa dữ liệu từ các lượt nhấp vào mục trong trình đơn trước đó. Cuối cùng, các giá trị mới sẽ được ghi vào trang tính.
Kết quả
Bạn có thể xem kết quả của mình bằng cách làm như sau:
- Nếu bạn chưa thực hiện, hãy lưu dự án tập lệnh trong trình chỉnh sửa Apps Script.
- Nhấp vào mục trình đơn Định dạng nhanh > Tạo bảng thông tin nhân vật > Tập IV.
Kết quả sẽ có dạng như sau:
Giờ đây, bạn đã viết mã để nhập dữ liệu vào Trang tính và tự động định dạng dữ liệu đó.
8. Kết luận
Chúc mừng bạn đã hoàn thành lớp học lập trình này. Bạn đã thấy một số lựa chọn định dạng của Trang tính mà bạn có thể đưa vào các dự án Apps Script, đồng thời đã tạo một ứng dụng ấn tượng có thể nhập và định dạng một tập dữ liệu lớn từ API.
Bạn có thấy lớp học lập trình này hữu ích không?
Kiến thức bạn học được
- Cách áp dụng nhiều thao tác định dạng Trang tính bằng Apps Script.
- Cách tạo trình đơn con bằng hàm
onOpen(). - Cách định dạng danh sách đối tượng JSON đã tìm nạp thành một trang dữ liệu mới bằng Apps Script.
Bước tiếp theo
Lớp học lập trình tiếp theo trong danh sách phát này sẽ hướng dẫn bạn cách dùng Apps Script để trực quan hoá dữ liệu trong biểu đồ và xuất biểu đồ sang bản trình bày trên Google Trang trình bày.
Bạn có thể tìm thấy lớp học lập trình tiếp theo tại Biểu đồ và trình bày dữ liệu trong Trang trình bày.