Trang này được dịch bởi Cloud Translation API.

Kiến thức cơ bản về Apps Script với Google Trang tính số 4: Định dạng dữ liệu

1. Giới thiệu

Chào mừng bạn đến với phần thứ tư của Kiến thức cơ bản về Apps Script với danh sách phát lớp học lập trình của 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 bảng tính trong Apps Script và viết hàm để tạo bảng tính có tổ chức chứa toàn bộ dữ liệu được định dạng đã tìm nạp 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 trên Google Trang tính trong Apps Script.
Cách biế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 tập dữ liệu được sắp xếp bằng Apps Script.

Trước khi bắt đầu

Đây là lớp học lập trình thứ tư trong Kiến thức cơ bản về Apps Script với danh sách phát trên 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 đây:

Bạn cần có

Tìm hiểu về các chủ đề cơ bản của 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.
Làm quen với trình chỉnh sửa Apps Script
Làm quen cơ bản với Google Trang tính
Khả năng đọc Trang tính Ký hiệu A1
quen thuộc với JavaScript và String lớp

2. Thiết lập

Trước khi tiếp tục, bạn cần một bảng tính có một số dữ liệu. Như trước đây, chúng tôi đã cung cấp bảng dữ liệu mà bạn có thể sao chép cho các bài tập này. Thực hiện các bước sau:

Nhấp vào đường liên kết này để sao chép bảng dữ liệu rồi nhấp vào Tạo bản sao. Bảng tính mới được đặt trong thư mục Google Drive của bạn và được đặt tên là "Bản sao định dạng dữ liệu"
Nhấp vào tiêu đề bảng tính và thay đổi tiêu đề từ "Copy of Data Format" thành " Định dạng dữ liệu" Trang tính của bạn sẽ trông giống như sau, với một số thông tin cơ bản về ba bộ phim Chiến tranh giữa các vì sao đầu tiên:

Chọn Tiện ích > Apps Script để mở trình chỉnh sửa tập lệnh.
Nhấp vào tiêu đề dự án Apps Script và thay đổi tiêu đề từ "Dự án không có tiêu đề" thành " Định dạng dữ liệu." Nhấp vào Đổi tên để lưu thay đổi 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. Hãy 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 trình đơn tùy chỉnh

Bạn có thể áp dụng một số phương pháp định dạng cơ bản trong Apps Script cho Trang tính của bạn. Các bài tập sau đây minh họa một số cách định dạng dữ liệu. Để giúp kiểm soát các hành động định dạng của bạn, hãy tạo một trình đơn tùy chỉnh có các mục bạn cần. Quy trình tạo trình đơn tùy 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 tôi sẽ tóm tắt lại quá trình này.

Triển khai

Hãy tạo một trình đơn tùy chỉnh.

Trong trình chỉnh sửa Apps Script, hãy thay thế mã trong dự án tập lệnh của bạn bằng cách 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 của bạn.
Trong trình chỉnh sửa tập lệnh, hãy chọn onOpen từ danh sách hàm rồi nhấp vào Chạy. Thao tác này sẽ chạy onOpen() để tạo lại trình đơn bảng tính, nhờ đó, bạn không phải tải lại bảng tính.

Đánh giá mã

Hãy xem lại mã này để hiểu cách hoạt động. 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 liên kết.

Các dòng tiếp theo sẽ tạo thực đơn (Quick formats), thêm thực đơn (Format row header, Format column header và Format dataset) vào thực đơn, sau đó thêm thực đơn vào giao diện của bảng tính. Bạn có thể thực hiện việc này bằng các phương thức createMenu(caption), addItem(caption, functionName) và addToUi() tương ứng.

Phương thức addItem(caption, functionName) tạo kết nối giữa nhãn mục trình đơn và hàm Apps Script sẽ chạy khi mục mục trình đơn được chọn. Ví dụ: việc chọn mục trong trình đơn Format row header sẽ khiến Trang tính cố chạy hàm formatRowHeader() (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 trong trình đơn mới:

Việc nhấp vào các mục này sẽ gây ra lỗi vì bạn chưa triển khai các chức năng tương ứng, vì vậy, hãy để điều đó 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ó các hàng tiêu đề để xác định dữ liệu trong từng cột. Bạn nên định dạng các hàng tiêu đề để phân tách chúng với các dữ liệu khác 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 tiêu đề đó. 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 đề bạn sẽ tạo sẽ in đậm văn bản tiêu đề, tô màu nền màu xanh lục đậm, tô màu văn bản trắng và thêm một số đường viền liền mạch.

Triển khai

Để triển khai hoạt động định dạng, bạn sẽ sử dụng cùng các phương thức dịch vụ Bảng tính mà bạn đã sử 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ụ. Thực hiện 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 của bạn:

/**
 * 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 của bạn.

Đánh giá mã

Giống như nhiều tác vụ định dạng, mã Apps Script để triển khai sẽ đơ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 để tham chiếu đến trang tính đang hoạt động hiện tại (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 trong đó. 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ỉ 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. Để dễ đọc mã, chúng ta dùng chuỗi phương thức để gọi từng phương thức định dạng:

Range.setFontWeight(fontWeight) được dùng để đặt trọng số phông chữ là in đậm.
Range.setFontColor(color) dùng để đặt màu phông chữ thành màu trắng.
Range.setBackground(color) dùng để đặt màu nền thành xanh lục đậm.
setBorder(top, left, bottom, right, vertical, horizontal, color, style) đặt một đường viền màu đen đồng nhất xung quanh các ô của dải ô.

Phương thức cuối cùng có một số tham số, vì vậy, hãy xem xét lại từng tham số. Bốn thông số đầu tiên ở đây (tất cả được đặt thành true) cho Apps Script biết đường viền sẽ được thêm ở trên, bên dưới và bên trái và bên phải của phạm vi. Thông số thứ năm và thứ sáu (null và null) hướng trực tiếp đến Apps Script để tránh thay đổi đường viền bất kỳ trong phạm vi đã chọn. Tham số thứ bảy (null) cho biết màu của đường viền sẽ được đặt mặc định là màu đen. Cuối cùng, thông số cuối cùng chỉ định loại kiểu đường viền cần sử dụng, được lấy từ các tùy chọn do SpreadsheetApp.BorderStyle cung cấp.

Kết quả

Bạn có thể xem hàm định dạng của mình trong thực tế bằng cách làm như sau:

Lưu dự án tập lệnh của bạn vào trình chỉnh sửa Apps Script nếu bạn chưa lưu.
Nhấp vào mục trong trình đơn Định dạng nhanh > tiêu đề hàng định dạng.

Lưu ý: Nếu đang sử dụng tài khoản gmail.com, bạn có thể thấy hộp thoại &quo;Ứng dụng này chưa được xác minh" khi bạn sử dụng tập lệnh lần đầu tiên. Google sử dụng mã này để cảnh báo cho người dùng có thể đang sử dụng mã từ các tác giả không xác định hoặc không đáng tin cậy. Nếu bạn thấy hộp thoại này, có thể tiếp tục vì bạn là tác giả tập lệnh. Làm theo lời nhắc trên màn hình để tiếp tục ủy quyền cho tập lệnh:

Trong hộp thoại ứng dụng chưa được xác minh, hãy nhấp vào Nâng cao.
Nhấp vào Chuyển đến trình đơn thao tác dữ liệu và trình đơn tùy chỉnh (không an toàn).
Hãy nhấp vào Cho phép.

Trong suốt lớp học lập trình này, bạn có thể nhận được một số lời nhắc cấp quyền. Bạn có thể đọc thêm về quy trình này trong phần Ủy quyền cho các dịch vụ của Google.

Kết quả sẽ có dạng sau:

Bạn hiện đã tự động hóa một nhiệm vụ định dạng. Phần tiếp theo cũng áp dụng kỹ thuật tương tự để tạo kiểu định dạng khác cho tiêu đề cột.

5. Định dạng tiêu đề cột

Nếu bạn có thể tạo tiêu đề hàng được cá nhân hóa thì bạn cũng có thể tạo tiêu đề cột. Tiêu đề cột tăng khả năng đọc cho một số tập dữ liệu nhất định. Ví dụ: cột tiêu đề trong bảng tính này có thể được nâng cao với 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 cách sử dụng nội dung cột url. Sau khi thêm các siêu liên kết này, bạn có thể xóa cột url để giúp xóa trang tính.

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 hai chức năng trợ giúp.

Triển khai

Như trước đây, bạn cần thêm một hàm để tự động hóa định dạng tiêu đề cột. Thực hiện 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 của bạn:

/**
 * 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 của bạn.

Đánh giá mã

Hãy xem lại mã trong từng hàm trong ba hàm sau:

`formatColumnHeader()`

Như bạn có thể dự kiến, một số dòng đầu tiên của hàm này đã đặt các biến tham chiếu 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 toán và lưu trong numRows. Ở đây, mã trừ đi một để số lượng hàng không bao gồm tiêu đề cột: title.
Dải ô bao gồm tiêu đề cột được lưu trữ trong columnHeaderRange.

Sau đó, mã này áp dụng đường viền và in đậm vào dải ô tiêu đề cột, giống như trong formatRowHeader(). Ở đây, Range.setFontStyle(fontStyle) cũng được dùng để làm cho văn bản được in nghiêng.

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() sẽ gọi hyperlinkColumnHeaders_(headerRange, numRows) để xử lý việc cần làm này. Điều này giúp mã trở nên gọn gàng và dễ đọc.

`hyperlinkColumnHeaders_(headerRange, numRows)`

Trước tiên, hàm trợ giúp này xác định chỉ mục cột của tiêu đề (được coi là chỉ mục 1) và cột url. Phương thức này gọi columnIndexOf_('url') để nhận chỉ mục cột url. Nếu không tìm thấy cột url, phương thức này sẽ thoát mà không sửa đổi bất kỳ dữ liệu nào.

Hàm nhận được một dải ô mới (urlRange) bao gồm các url tương ứng với các hàng trong cột tiêu đề. Việc này được thực hiện bằng phương thức Range.offset(rowOffset, columnOffset), phương pháp này đảm bảo hai phạm vi sẽ có cùng kích thước. Sau đó, các giá trị trong cả cột headerColumn và cột url được truy xuất (headerValues và urlValues).

Sau đó, hàm này lặp qua từng giá trị ô trong tiêu đề cột và thay thế bằng công thức =HYPERLINK() Trang tính được tạo bằng nội dung tiêu đề và url cột. Sau đó, các giá trị tiêu đề được sửa đổi được chèn vào trang tính bằng Range.setValues(values).

Cuối cùng, để đảm bảo trang tính luôn sạch sẽ và loại bỏ thông tin thừa, hệ thống sẽ gọi Sheet.deleteColumn(columnPosition) để xóa cột url.

`columnIndexOf_(colName)`

Hàm trình trợ giúp này chỉ là một hàm tiện ích đơn giản tìm kiếm hàng đầu tiên của trang tính cho một tên cụ thể. Ba dòng đầu tiên sử dụng các phương thức mà bạn đã thấy để nhận 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 nameName biến.

Sau đó, hàm sẽ xem xét từng tên theo thứ tự. Nếu tìm thấy tên khớp với tên đang được tìm kiếm, tên này sẽ dừng lại và trả về chỉ mục cột. Nếu đến cuối danh sách tên mà không tìm thấy tên, thì hàm sẽ trả về -1 để báo hiệu tên không tìm thấy.

Kết quả

Bạn có thể xem hàm định dạng của mình trong thực tế bằng cách làm như sau:

Lưu dự án tập lệnh của bạn vào trình chỉnh sửa Apps Script nếu bạn chưa lưu.
Nhấp vào mục trong trình đơn Định dạng nhanh > tiêu đề cột Định dạng.

Kết quả sẽ có dạng sau:

Bạn hiện đã tự động hóa một nhiệm vụ định dạng khác. Với các tiêu đề cột và hàng được định dạng, phần tiếp theo sẽ cho biết cách định dạng dữ liệu.

6. Định dạng tập dữ liệu của bạn

Sau khi đã có tiêu đề, hãy tạo một hàm để định dạng các dữ liệu còn lại trong trang tính của bạn. Chúng tôi sẽ sử dụng các tùy chọn định dạng sau:

Màu nền của hàng thay thế (còn gọi là băng tần)
Thay đổi định dạng ngày
Áp dụng đường viền
Đang tự động hóa tất cả các cột và hàng

Bây giờ 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 của mình.

Triển khai

Như trước đây, hãy thêm một hàm để tự động hóa định dạng dữ liệu. Thực hiện 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 của bạn:

/**
 * 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 của bạn.

Đánh giá mã

Hãy xem lại mã riêng trong hai chức năng 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ẽ nhận các biến để lưu giữ thông tin tham chiếu đến trang tính đang hoạt động (trang tính) và dải ô dữ liệu (fullDataRange).

Thứ hai, phương thức này 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ừ các tiêu đề cột và hàng. Sau đó, mã này sẽ xác minh xem dải ô mới này có dải tần hiện có (sử dụng Range.getBandings()) hay không. Việc này là cần thiết vì Apps Script sẽ gặp lỗi nếu bạn cố gắng áp dụng dải tần mới khi có dải này. Nếu không có dải nào, hàm sẽ thêm một dải màu xám nhạt bằng cách dùng Range.applyRowBanding(bandingTheme, showHeader, showFooter). Nếu không, hàm sẽ di chuyển trên.

Bước tiếp theo sẽ gọi hàm trình trợ giúp formatDates_(colIndex) để định dạng các ngày trong cột có nhãn "release_date×39; (mô tả bên dưới). Cột này được chỉ định bằng hàm trợ giúp columnIndexOf_(colName) mà bạn đã triển khai trước đó.

Cuối cùng, việc định dạng này đã hoàn tất bằng cách thêm một đường viền khác (như trước đây) và tự động đổi kích thước mọi cột và hàng để vừa với dữ liệu chứa trong phương thức Sheet.autoResizeColumns(columnPosition) và Sheet.autoResizeColumns(columnPosition).

`formatDates_(colIndex)`

Hàm trợ giúp này áp dụng định dạng ngày cụ thể cho một cột bằng chỉ mục cột đã cung cấp. Cụ thể, lớp này định dạng các giá trị ngày dưới dạng "Tháng, Năm (Ngày trong tuần)".

Trước tiên, hàm xác minh chỉ mục cột đã cung cấp là hợp lệ (tức là từ 0 trở lên). Nếu không, trình thu thập dữ liệu sẽ trả về mà không thực hiện thao tác nào. Quy trình kiểm tra này ngăn chặn các lỗi có thể gây ra nếu ví dụ như trang tính không có cột "release_date#39;".

Sau khi chỉ mục cột được xác thực, hàm sẽ nhận được dải ô bao gồm cột đó (không bao gồm hàng tiêu đề của cột) và sử dụng Range.setNumberFormat(numberFormat) để áp dụng định dạng.

Kết quả

Bạn có thể xem hàm định dạng của mình trong thực tế bằng cách làm như sau:

Lưu dự án tập lệnh của bạn vào trình chỉnh sửa Apps Script nếu bạn chưa lưu.
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 sau:

Bạn đã tự động hóa một nhiệm vụ định dạng khác. Bây giờ, bạn đã có những lệnh định dạng này, hãy thêm dữ liệu để áp dụng.

7. Tìm nạp và định dạng dữ liệu API

Cho đến nay, trong lớp học lập trình này, bạn đã biết cách sử dụng Apps Script để thay thế định dạng bảng tính. Tiếp theo, bạn sẽ viết mã lấy dữ liệu từ API công khai, chèn dữ liệu này vào bảng tính của bạn và định dạng dữ liệu để dữ liệu có thể đọc được.

Trong lớp học lập trình vừa qua, bạn đã học 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ẽ dùng API Star Wars (SWAPI) công khai để điền vào bảng tính. Cụ thể, bạn sẽ sử dụng API để có thông tin về các nhân vật chính xuất hiện trong ba bộ phim Chiến tranh giữa các vì sao ban đầu.

Mã của bạn sẽ gọi API để nhận 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 trong một trang tính mới, sau đó định dạng trang tính.

Triển khai

Trong phần này, bạn sẽ thêm một số mục menu khác. Mỗi mục trong trình đơn gọi một tập lệnh trình bao bọc chuyể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 ngăn một cách hợp lý của việc cần làm và giúp mã dễ đọc.

Thực hiện các hành động 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 để khớp với thông tin 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 của bạn.
Trong trình chỉnh sửa tập lệnh, hãy chọn onOpen từ danh sách hàm rồi nhấp vào Chạy. Thao tác này sẽ chạy onOpen() để tạo lại trình đơn bảng tính có các tùy chọn mới mà bạn đã thêm.
Để tạo tệp Apps Script, bên cạnh mục Tệp, hãy nhấp vào biểu tượng Thêm tệp > Script.
Đặt tên cho tập lệnh mới "API" và nhấn Enter. (Ứng dụng Apps Script tự động thêm một tiện ích .gs vào tên tệp tập lệnh.)
Thay thế mã trong tệp API.gs mới bằng cách 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 của bạn.

Đánh giá mã

Bạn vừa thêm rất nhiều mã. Hãy xem xét từng chức năng để hiểu cách hoạt động của từng chức năng:

`onOpen()`

Tại đây, bạn đã thêm một vài món vào thực đơn vào thực đơn Quick formats. Bạn đã đặt một dòng phân tách và 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 ba mục mới. Bạn có thể thêm các mục mới bằng phương thức Menu.addItem(caption, functionName).

Hàm bao bọc

Các mục trong trình đơn đã thêm đều hoạt động tương tự nhau: 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 bên tập trung vào một bộ phim khác nhau.

Bạn nê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 phim nào cần sử dụng. Tuy nhiên, phương thức Menu.addItem(caption, functionName) không cho phép bạn chuyển các thông số vào phương thức 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à các hàm trình bao bọc. Đây là những hàm nhẹ, bạn có thể gọi ngay để gọi một hàm khác với tập hợp tham số cụ thể.

Ở đâ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 các hàm này. Khi người dùng nhấp vào một mục trong trình đơn, hàm trình bao bọc sẽ thực thi và ngay lập tức gọi hàm trình tạo trang tính chính createResourceSheet_(resourceType, idNumber, episodeNumber), chuyển các thông số phù hợp với 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 có dữ liệu về nhân vật chính trong một trong những bộ phim Chiến tranh giữa các vì sao.

`createResourceSheet_(resourceType, idNumber, episodeNumber)`

Đây là hàm tạo trang tính chính cho bài tập này. Với sự hỗ trợ của một số hàm trình trợ giúp, hệ thống sẽ nhận 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 bạn đã tạo trong các phần trước. Hãy xem lại chi tiết:

Trước tiên, hàm này sử dụng fetchApiResourceObject_(url) để tạo yêu cầu API nhằm truy xuất thông tin phim cơ bản. Phản hồi API bao gồm tập hợp các URL mà mã có thể dùng để nhận thêm thông tin chi tiết về những người cụ thể (còn gọi là tài nguyên) từ các bộ phim. Mã thu thập tất cả 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 ký tự khác trong phim.

Đối tượng dữ liệu tài nguyên có một vài khóa phổ biến ánh xạ tới thông tin về ký tự đó. Ví dụ: phím "name" mọi người" liên kết đến tên của nhân vật trong phim. Chúng tôi giả định các khóa cho từng đối tượng dữ liệu tài nguyên là giống nhau, vì chúng chỉ sử dụng các cấu trúc đối tượng chung. Danh sách các khóa cần thiết sau này, vì vậy mã sẽ lưu trữ danh sách khóa trong resourceObjectKeys bằng phương thức Object.keys() của JavaScript.

Tiếp theo, hàm tạo trình gọi sẽ gọi hàm trình trợ giúp createNewSheet_(name) để tạo trang tính mà dữ liệu mới sẽ được đặt. Việc gọi hàm trình trợ giúp này cũng sẽ kích hoạt trang tính mới.

Sau khi tạo trang tính, 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 bạn đã tạo trước đây đượ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ình trợ giúp này tương tự với hàm trình trợ giúp fetchBookData_(ISBN) được sử dụng trong lớp học làm việc với dữ liệu của lớp học lập trình trước đó. Phương thức này sử dụng URL đã chọn và sử dụng phương thức UrlFetchApp.fetch(url, params) để nhận phản hồi. Sau đó, phản hồi được phân tích cú pháp thành một đối tượng JSON bằng cách sử dụng các phương thức HTTPResponse.getContextText() và JavaScript JSON.parse(json). Sau đó, đối tượng JSON nhận được sẽ được trả về.

`createNewSheet_(name)`

Chức năng trợ giúp này khá đơn giản. Trước tiên, tính năng này xác minh xem trang tính của tên đã cho có tồn tại trong bảng tính hay không. Nếu trường hợp này xảy ra, thì hàm sẽ kích hoạt trang tính và trả về trang tính đó.

Nếu trang tính không tồn tại, hàm sẽ tạo trang tính đó bằng Spreadsheet.insertSheet(sheetName), kích hoạt trang tính này và trả về trang tính mới.

`fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)`

Chức năng trợ giúp này chịu trách nhiệm điền dữ liệu API vào trang tính mới. Các thông số sẽ lấy thông số ở trang tính mới, danh sách khóa đối tượng và danh sách đối tượng tài nguyên API dưới dạng thông số. Mỗi đố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 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 tương ứng của danh sách tài nguyên và khóa. Sau đó, hàm xác định dải ô đầu ra (resourceRange) mà dữ liệu sẽ được đặt, thêm một hàng để giữ tiêu đề cột. Biến resourceValues chứa một mảng giá trị 2D được trích xuất từ resourceRange.

Sau đó, hàm này lặp lại mọi phím đối tượng trong danh sách objectKeys. Khóa được đặt làm tiêu đề cột, sau đó, vòng lặp thứ hai sẽ đi 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 được sao chép vào phần tử resourceValues[row][column].

Sau khi resourceValues được điền, trang tính đích sẽ bị xóa bằng cách sử dụng Sheet.clear() trong trường hợp trang tính chứa dữ liệu từ các lượt nhấp vào mục trước đó trên trình đơn. Cuối cùng, các giá trị mới được ghi vào trang tính.

Kết quả

Bạn có thể xem kết quả công việc của mình bằng cách làm như sau:

Lưu dự án tập lệnh của bạn vào trình chỉnh sửa Apps Script nếu bạn chưa lưu.
Nhấp vào mục Định dạng nhanh > Tạo trang tính ký tự > Tập IV mục.

Kết quả sẽ có dạng sau:

Bạn hiệ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ố tùy chọn định dạng của Trang tính mà bạn có thể đưa vào dự án Apps Script của mình, đồng thời đã xây dựng một ứng dụng ấn tượng để nhập và định dạng tập dữ liệu API lớn.

Bạn có thấy lớp học lập trình này hữu ích không?

Có

Không

Những điều bạn đã học được

Cách áp dụng nhiều thao tác định dạng trong Trang tính bằng Apps Script.
Cách tạo trình đơn phụ bằng hàm onOpen().
Cách định dạng danh sách các đối tượng JSON đã được tìm nạp thành một trang dữ liệu mới bằng Apps Script.

Từ khóa chính:

Đang hoạt động (trạng thái): Cho biết bảng tính, trang tính, dải ô hoặc ô được chỉ định là bảng tính mà người dùng bảng tính hiện đang xem hoặc đánh dấu.
ô đang hoạt động: Ô được làm nổi bật duy nhất trong trang tính hoạt động với tiêu điểm con trỏ.
Dải ô đang hoạt động: Một nhóm gồm một hoặc nhiều ô hiện được đánh dấu trong trang tính hoạt động.
API: Giao diện lập trình ứng dụng là dịch vụ mà các chương trình hoặc tập lệnh có thể gọi để yêu cầu thông tin hoặc thực hiện một số hành động nhất định.
Ủy quyền: Quá trình người dùng cấp quyền cho phép Apps Script truy cập dữ liệu người dùng trong các dịch vụ liên quan của Google hoặc thực hiện hành động khác thay mặt cho người dùng.
Tập lệnh liên kết với vùng chứa: Mọi tập lệnh được liên kết và tạo từ một tài liệu trên Google Workspace, chẳng hạn như Google Trang tính hoặc Google Tài liệu.
Hàm trình trợ giúp: Một hàm thường là riêng tư mà một hàm khác gọi là một hàm khác để hoàn thành một việc phụ cần làm nhỏ. Hàm trợ giúp được sử dụng khi việc cần làm phụ cần hoàn thành nhiều lần ở các vị trí khác nhau.
JSON: Định dạng tệp để xác định đối tượng dữ liệu và đặc điểm của các đối tượng đó trong văn bản.
onOpen(): Nếu được xác định trong tập lệnh, thì đây là trình kích hoạt đơn giản sẽ kích hoạt bất cứ khi nào tệp mở hoặc tải lại.
Các hàm riêng tư: Một hàm không thể được gọi là một phần của thư viện hoặc bởi các ứng dụng thông qua việc giao tiếp giữa các máy chủ. Các hàm riêng tư của Apps Script có tên kết thúc bằng _.
Dải ô**: Một nhóm gồm một hoặc nhiều ô liền kề trong một trang tính. Lớp Range** cho phép bạn đọc và cập nhật các dải ô trong một trang tính.
Trình chỉnh sửa tập lệnh: Trình chỉnh sửa mã trong trình duyệt cho Apps Script.
Trình kích hoạt đơn giản: Loại phụ của trình kích hoạt trong Apps Script có tên hàm kích hoạt dành riêng và một số hạn chế nhất định.
Trang tính**: Một trang duy nhất của bảng tính. Đôi khi, tệp này dùng để tham chiếu đến một tệp Google Trang tính trong Google Drive. Lớp Sheet** cho phép bạn truy cập và sửa đổi trang tính.
SpreadsheetApp: Lớp này đóng vai trò là lớp mẹ cho dịch vụ Bảng tính và cung cấp điểm bắt đầu cho mã đọc hoặc thao tác với dữ liệu Google Trang tính.
Bảng tính**: Một tệp Google Trang tính nằm trong Google Drive. Lớp Spreadsheet** cho phép bạn truy cập và sửa đổi bảng tính.
Dịch vụ bảng tính: Dịch vụ Apps Script cho phép các tập lệnh tạo, truy cập và sửa đổi tệp Google Trang tính.
Trình kích hoạt: Một sự kiện được gán cho chức năng Apps Script. Khi sự kiện (chẳng hạn như bảng tính đang được mở) xảy ra, trình kích hoạt "fires" và tự động thực thi hàm Apps Script được liên kết.
Dịch vụ tìm nạp URL: Dịch vụ Apps Script cho phép các tập lệnh kết nối với các điểm cuối URL để đưa ra yêu cầu và nhận phản hồi.
Hàm Hàm: Hàm hàm đơn giản dùng để gọi các hàm phức tạp hơn trong khi chuyển hàm này một nhóm tham số cụ thể.

Bước tiếp theo

Lớp học lập trình tiếp theo trong danh sách phát này hướng dẫn bạn cách sử dụng Apps Script để thể hiện dữ liệu dưới dạng hình ảnh trong một biểu đồ và xuất các biểu đồ sang bản trình bày của Google Trang trình bày.

Tìm lớp học lập trình tiếp theo tại Biểu đồ và dữ liệu trình bày trong Trang trình bày.