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ố 3: Làm việc với dữ liệu

1. Giới thiệu

Chào mừng bạn đến với phần thứ ba của Kiến thức cơ bản về Apps Script có 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 sử dụng thao tác dữ liệu, trình đơn tùy chỉnh và truy xuất dữ liệu API công khai trong Apps Script để cải thiện Trải nghiệm\39 trên Trang tính. Bạn sẽ tiếp tục làm việc với các lớp học SpreadsheetApp, Spreadsheet, Sheet và Range mà những lớp học mã trước đây đã có trong danh sách phát này.

Kiến thức bạn sẽ học được

Cách nhập dữ liệu từ bảng tính cá nhân hoặc bảng tính được chia sẻ trong Drive.
Cách tạo trình đơn tùy chỉnh bằng hàm onOpen().
Cách phân tích cú pháp và thao tác với các giá trị dữ liệu chuỗi trong ô Google Trang tính.
Cách lấy và thao tác với dữ liệu đối tượng JSON từ một nguồn API công khai.

Trước khi bắt đầu

Đây là lớp học lập trình thứ ba 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

Các bài tập trong lớp học lập trình này yêu cầu một bảng tính để hoạt động. Hãy làm theo các bước sau để tạo một bảng tính mà bạn có thể sử dụng trong các bài tập sau:

Tạo một bảng tính trong Google Drive của bạn. Bạn có thể làm việc này trong giao diện Drive bằng cách chọn New > Google Trang tính. Thao tác này sẽ tạo và mở bảng tính mới của bạn. Tệp được lưu vào thư mục Drive của bạn.
Nhấp vào tiêu đề bảng tính và thay đổi từ "Bảng tính không có tiêu đề" thành " Thao tác dữ liệu và Trình đơn tùy chỉnh&quot. Trang tính của bạn phải có dạng như sau:

Để mở trình chỉnh sửa tập lệnh, hãy nhấp vào Tiện ích> Ứng dụng Apps Script
Nhấp vào tiêu đề dự án Apps Script rồi thay đổi tiêu đề từ "Dự án không có tiêu đề" thành " Thao tác dữ liệu và Trình đơn tùy chỉnh." Nhấp vào Đổi tên để lưu thay đổi tiêu đề.

Với một bảng tính và dự án trống, bạn đã sẵn sàng bắt đầu phòng thí nghiệm. Hãy chuyển đến phần tiếp theo để bắt đầu tìm hiểu về trình đơn tùy chỉnh.

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.

3. Tổng quan: Nhập dữ liệu với một mục trình đơn tùy chỉnh

Apps Script cho phép bạn xác định các trình đơn tùy chỉnh có thể xuất hiện trong Google Trang tính. Bạn cũng có thể sử dụng các trình đơn tùy chỉnh trong Google Tài liệu, Google Trang trình bày và Google Biểu mẫu. Khi xác định một mục trong trình đơn tùy chỉnh, bạn sẽ tạo một nhãn văn bản và kết nối nhãn đó với hàm Apps Script trong dự án tập lệnh của bạn. Sau đó, bạn có thể thêm trình đơn vào giao diện người dùng để trình đơn này xuất hiện trong Google Trang tính:

Khi người dùng nhấp vào một mục trong trình đơn tùy chỉnh, hàm Apps Script mà bạn đã liên kết với mục đó sẽ thực thi. Đây là một cách nhanh chóng để chạy các chức năng của Apps Script mà không cần phải mở trình chỉnh sửa tập lệnh. Bảng này cũng cho phép những người dùng khác của bảng tính thực thi mã của bạn mà không cần phải biết bất kỳ thông tin nào về cách thức hoạt động hoặc cách Apps Script hoạt động. Đối với họ, đó chỉ là một mục khác trong trình đơn.

Các mục trong trình đơn tùy chỉnh được xác định trong hàm onOpen() trình kích hoạt đơn giản mà bạn sẽ tìm hiểu trong phần tiếp theo.

4. Hàm onOpen()

Trình kích hoạt đơn giản trong Apps Script cung cấp cách chạy mã Apps Script cụ thể để phản hồi một số điều kiện hoặc sự kiện nhất định. Khi tạo trình kích hoạt, bạn xác định sự kiện nào khiến trình kích hoạt kích hoạt và cung cấp hàm Apps Script chạy cho sự kiện đó.

onOpen() là một ví dụ về trình kích hoạt đơn giản. Dễ dàng thiết lập—bạn chỉ cần viết một hàm Apps Script có tên là onOpen() và Apps Script sẽ chạy hàm này mỗi khi mở hoặc tải lại bảng tính liên kết:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Triển khai

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

Thay thế mã trong dự án tập lệnh của bạn bằng mã sau:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}

Lưu dự án tập lệnh của bạn.

Đá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 thể hiện 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.

Ba dòng tiếp theo tạo trình đơn (Book-list), thêm một mục trong trình đơn (Load Book-list) vào trình đơn đó, rồi thêm trình đơ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. Trong trường hợp này, việc chọn mục trong trình đơn Load Book-list sẽ khiến Trang tính cố chạy hàm loadBookList() (chưa tồn tại).

Kết quả

Chạy hàm này ngay bây giờ để thấy rằng hàm hoạt động:

Trong Google Trang tính, hãy tải lại bảng tính của bạn. Lưu ý: thao tác này thường đóng thẻ bằng trình chỉnh sửa tập lệnh của bạn.
Mở lại trình chỉnh sửa tập lệnh bằng cách chọn Công cụ và gt; trình chỉnh sửa tập lệnh.

Sau khi bảng tính tải lại, trình đơn Book-list mới sẽ xuất hiện trên thanh trình đơn:

Khi nhấp vào Danh sách sách, bạn có thể thấy trình đơn kết quả:

Phần tiếp theo tạo mã cho hàm loadBookList() và giới thiệu một cách bạn có thể tương tác với dữ liệu trong Apps Script: đọc các bảng tính khác.

5. Nhập dữ liệu bảng tính

Giờ đây, bạn đã tạo một trình đơn tùy chỉnh nên bạn có thể tạo các hàm mà bạn có thể chạy từ việc nhấp vào mục trong trình đơn.

Hiện tại, trình đơn tùy chỉnh Book-list có một mục trong trình đơn: Load Book-list. Hàm được gọi khi bạn chọn mục trong trình đơn Load Book-list, loadBookList(), không tồn tại trong tập lệnh của bạn. Vì vậy, việc chọn Danh sách sách > Tải danh sách sách sẽ gây ra lỗi:

Bạn có thể khắc phục lỗi này bằng cách triển khai hàm loadBookList().

Triển khai

Bạn muốn mục trình đơn mới lấp đầy bảng tính bằng dữ liệu hoạt động, vì vậy, bạn sẽ triển khai loadBookList() để đọc dữ liệu sách từ bảng tính khác và sao chép dữ liệu đó vào bảng tính sau:

Thêm mã sau vào tập lệnh của bạn trong onOpen():

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}

Lưu dự án tập lệnh của bạn.

Đánh giá mã

Vậy chức năng này hoạt động như thế nào? Hàm loadBookList() sử dụng các phương thức chủ yếu từ các lớp Spreadsheet, Sheet và Range mà các lớp học lập trình trước đó đã giới thiệu. Khi lưu ý đến những khái niệm này, bạn có thể chia mã loadBookList() thành bốn phần sau đây:

1: Xác định trang đích

Dòng đầu tiên dùng SpreadsheetApp.getActiveSheet() để lấy thông tin tham chiếu đến đối tượng trang tính hiện tại và lưu trữ đối tượng đó trong biến sheet. Đây là trang tính mà dữ liệu sẽ được sao chép vào.

2: Xác định dữ liệu nguồn

Một vài dòng tiếp theo thiết lập 4 biến tham chiếu đến dữ liệu nguồn mà bạn đang truy xuất:

bookSS lưu trữ thông tin tham chiếu đến bảng tính có chứa mã đọc dữ liệu. Mã này tìm bảng tính theo mã bảng tính. Trong ví dụ này, chúng tôi đã cung cấp mã nhận dạng của bảng tính nguồn để đọc và mở bảng tính bằng phương thức SpreadsheetApp.openById(id).
bookSheet lưu trữ một tệp tham chiếu đến một trang tính trong bookSS chứa dữ liệu mà bạn muốn. Mã này xác định trang tính cần đọc theo tên là codelab-book-list.
bookRange lưu trữ một tệp tham chiếu đến một loạt dữ liệu trong bookSheet. Phương thức Sheet.getDataRange() trả về dải ô chứa tất cả các ô không trống trong trang tính. Đây là một cách dễ dàng để đảm bảo bạn có một dải ô bao gồm tất cả dữ liệu trong một trang tính mà không bao gồm các hàng và cột trống.
bookListValues là một mảng 2D chứa tất cả giá trị được lấy từ các ô trong bookRange. Phương thức Range.getValues() tạo mảng này bằng cách đọc dữ liệu từ trang tính nguồn.

3: Sao chép dữ liệu từ nguồn đến đích

Phần mã tiếp theo sao chép dữ liệu bookListValues vào sheet, sau đó đổi tên trang tính:

Sheet.getRange(row, column, numRows, numColumns) dùng để xác định vị trí sao chép dữ liệu trong sheet.
Phương thức Range.getHeight() và Range.getWidth() được dùng để đo kích thước của dữ liệu và xác định phạm vi đích của cùng một thứ nguyên.
Range.setValues(values) sao chép mảng 2D của bookListValues vào dải ô đích, ghi đè mọi dữ liệu đã có ở đó.

4: Định dạng trang tính đích

Sheet.setName(name) được dùng để thay đổi tên trang tính đích thành Book-list. Dòng cuối cùng trong hàm sử dụng Sheet.autoResizeColumns(startColumn, numColumns) để thay đổi kích thước ba cột đầu tiên trong trang tính đích, cho phép bạn đọc dữ liệu mới dễ dàng hơn.

Kết quả

Bạn có thể thấy chức năng này trong thực tế. Trong Google Trang tính, hãy chọn Danh sách sách và gt; Tải danh sách sách để chạy hàm điền vào bảng tính:

Giờ đây, bạn đã có một trang tính với danh sách tên sách, tác giả và số ISBN gồm 13 chữ số. Trong phần tiếp theo, bạn sẽ tìm hiểu cách sửa đổi và cập nhật dữ liệu trong danh sách sách này bằng cách sử dụng thao tác chuỗi và trình đơn tùy chỉnh.

6. Tổng quan: Dọn sạch dữ liệu bảng tính

Bây giờ, bạn đã có thông tin sách trên trang tính. Mỗi hàng đề cập đến một cuốn sách cụ thể, liệt kê tên sách, tác giả và số ISBN của sách trong các cột riêng biệt. Tuy nhiên, bạn cũng có thể gặp một số vấn đề với dữ liệu thô này:

Đối với một số hàng, tiêu đề và tác giả được đặt cùng nhau trong cột tiêu đề, liên kết bằng dấu phẩy hoặc chuỗi " by &quot.
Một số hàng bị thiếu tiêu đề hoặc tác giả của cuốn sách.

Trong các phần tiếp theo, bạn sẽ khắc phục các vấn đề này bằng cách làm sạch dữ liệu. Đối với vấn đề đầu tiên, bạn sẽ tạo các hàm đọc cột tiêu đề và tách văn bản bất cứ khi nào có dấu phẩy hoặc " theo " tìm thấy dấu phân cách, đặt chuỗi con tác giả và tiêu đề tương ứng vào các cột chính xác. Đối với vấn đề thứ hai, bạn sẽ viết mã tự động tìm kiếm thông tin sách bị thiếu bằng cách sử dụng API bên ngoài và thêm thông tin đó vào trang tính của bạn.

7. Thêm món ăn vào thực đơn

Bạn có thể tạo ba mục trình đơn để kiểm soát các hoạt động dọn dẹp dữ liệu mà bạn sẽ triển khai.

Triển khai

Hãy cập nhật onOpen() để bao gồm các mục menu khác mà bạn cần. Thực hiện thao tác sau:

Trong dự án tập lệnh, hãy cập nhật mã onOpen() của bạn để khớp với mã sau:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .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 để bạn không phải tải lại bảng tính.

Trong mã mới này, phương thức Menu.addSeparator() sẽ tạo một đường phân cách trong trình đơn để sắp xếp các nhóm các mục có liên quan trong trình đơn một cách trực quan. Sau đó, bạn có thể thêm các mục mới vào trình đơn bên dưới bằng các nhãn Separate title/author at first comma, Separate title/author at last "by" và Fill in blank titles and author cells.

Kết quả

Trong bảng tính, hãy nhấp vào trình đơn Book-list để xem các mục trong trình đơn mới:

Việc nhấp vào các mục mới này sẽ gây ra lỗi vì bạn không triển khai các chức năng tương ứng, vì vậy hãy để điều đó tiếp theo.

8. Phân tách văn bản trên dấu phân tách bằng dấu phẩy

Tập dữ liệu bạn đã nhập vào bảng tính có một vài ô, trong đó tác giả và tiêu đề được kết hợp không chính xác trong một ô bằng dấu phẩy:

Tách chuỗi văn bản thành các cột riêng biệt là một thao tác phổ biến trong bảng tính. Google Trang tính cung cấp hàm SPLIT() chia các chuỗi thành các cột. Tuy nhiên, tập dữ liệu thường gặp các vấn đề không thể giải quyết được bằng Trang tính\39; các hàm tích hợp sẵn. Trong những trường hợp này, bạn có thể viết mã Apps Script để thực hiện các thao tác phức tạp cần thiết để dọn dẹp và sắp xếp dữ liệu của bạn.

Trước tiên, hãy xóa dữ liệu bằng cách triển khai một hàm có tên là splitAtFirstComma(). Thao tác này sẽ chia tác giả và tiêu đề thành các ô tương ứng khi phát hiện thấy dấu phẩy.

Hàm splitAtFirstComma() cần thực hiện các bước sau:

Lấy dải ô đại diện cho các ô hiện được chọn.
Kiểm tra xem các ô trong dải ô có dấu phẩy hay không.
Khi tìm thấy dấu phẩy, hãy chia chuỗi thành hai (và chỉ hai) chuỗi con ở vị trí dấu phẩy đầu tiên. Để đơn giản hóa mọi thứ, bạn có thể giả định mọi dấu phẩy biểu thị &"[authors], [title]" mẫu chuỗi. Bạn cũng có thể giả định nếu nhiều dấu phẩy xuất hiện trong ô, việc này sẽ phù hợp để phân tách bằng dấu phẩy đầu tiên trong chuỗi.
Đặt chuỗi con làm nội dung mới của ô tương ứng và ô tác giả.

Triển khai

Để triển khai các bước này, 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 đó, nhưng bạn cũng sẽ cần sử dụng JavaScript để thao tác với dữ liệu chuỗi. 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:

/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

Lưu dự án tập lệnh của bạn.

Đánh giá mã

Hãy xem lại mã mới bao gồm ba phần chính:

1: Truy xuất giá trị tiêu đề được đánh dấu

Ba dòng đầu tiên thiết lập 3 biến tham chiếu đến dữ liệu hiện tại trong trang tính:

activeRange đại diện cho phạm vi mà người dùng hiện đang đánh dấu khi hàm splitAtFirstComma() được gọi. Để làm bài tập này đơn giản, chúng ta có thể giả định rằng người dùng chỉ làm việc này khi đánh dấu các ô trong cột A.
titleAuthorRange đại diện cho một dải ô mới bao gồm các ô tương tự như activeRange, nhưng cũng bao gồm một cột khác ở bên phải. titleAuthorRange được tạo bằng phương thức Range.offset(rowOffset, columnOffset, numRows, numColumns). Mã này cần có phạm vi mở rộng này vì cần có một vị trí để đưa bất kỳ tác giả nào mà mã tìm thấy vào cột tiêu đề.
titleAuthorValues là một mảng dữ liệu 2D được trích xuất từ titleAuthorRange bằng Range.getValues().

2: Kiểm tra từng tiêu đề và tách trên dấu phân tách dấu phẩy đầu tiên tìm thấy

Phần tiếp theo sẽ kiểm tra các giá trị trong titleAuthorValues để tìm dấu phẩy. JavaScript dành cho vòng lặp dùng để kiểm tra tất cả các giá trị trong cột đầu tiên của titleAuthorValues. Khi tìm thấy một chuỗi con dấu phẩy (", ") bằng phương thức JavaScript String indexOf(), mã sẽ thực hiện những điều sau:

Giá trị chuỗi ô được sao chép vào biến titlesAndAuthors.
Vị trí dấu phẩy được xác định bằng phương thức JavaScript String indexOf().
Phương thức Chuỗi JavaScript string() được gọi hai lần để có được chuỗi con trước dấu phân cách bằng dấu phẩy và chuỗi con sau dấu phân tách.
Chuỗi con được sao chép lại vào mảng titleAuthorValues 2D, ghi đè các giá trị hiện có tại vị trí đó. Vì chúng ta giả định một &hoat;[authors], [title]" mẫu, thứ tự của hai chuỗi con được đảo ngược để đặt tiêu đề ở cột đầu tiên và tác giả ở cột thứ hai.

Lưu ý: Khi mã không tìm thấy dấu phẩy, mã này sẽ không thay đổi trong hàng.

3: Sao chép lại các giá trị mới vào trang tính

Sau khi tất cả các giá trị của ô tiêu đề được kiểm tra, mảng titleAuthorValues 2D đã được cập nhật sẽ được sao chép lại vào bảng tính bằng phương thức Range.setValues(values).

Kết quả

Giờ đây, bạn có thể thấy các tác động của hàm splitAtFirstComma() trong thực tế. Thử chạy mục này bằng cách chọn mục trong trình đơn Tên/tác giả riêng biệt tại dấu phẩy đầu tiên sau khi chọn...

...một ô:

...hoặc nhiều ô:

Bạn hiện đã tạo hàm Apps Script để xử lý dữ liệu Trang tính. Tiếp theo, bạn sẽ triển khai hàm chia thứ hai.

9. Phân tách văn bản trên "by" dấu phân cách

Khi xem dữ liệu gốc, bạn có thể thấy một vấn đề khác. Giống như một số tiêu đề và tác giả định dạng dữ liệu trong một ô duy nhất là "[authors], [title]", các tác giả và định dạng ô khác dưới dạng "[title] của [authors]":

41f0dd5ac63b62f4.png.

Triển khai

Bạn có thể giải quyết vấn đề này bằng cách sử dụng kỹ thuật tương tự từ phần cuối cùng, tạo một hàm có tên là splitAtLastBy(). Hàm này có công việc tương tự như splitAtFirstComma()—sự khác biệt duy nhất thực sự là tính năng tìm kiếm một mẫu văn bản hơi khác. Bạn có thể triển khai chức năng này bằng cách làm như 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:

/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

Lưu dự án tập lệnh của bạn.

Đánh giá mã

Có một số điểm khác biệt chính giữa mã này và splitAtFirstComma():

Chuỗi con " by " được dùng làm dấu phân tách chuỗi, thay cho ", "
Ở đây, phương thức JavaScript String.lastIndexOf(substring) được sử dụng thay cho String.indexOf(substring). Điều này có nghĩa là nếu có nhiều " by " chuỗi con trong chuỗi ban đầu, tất cả ngoại trừ &quot cuối cùng; by " được giả định là một phần của tiêu đề.
Sau khi chia chuỗi, chuỗi con đầu tiên được đặt làm tiêu đề và chuỗi thứ hai làm tác giả (đây là thứ tự ngược lại với splitAtFirstComma()).

Kết quả

Giờ đây, bạn có thể thấy các tác động của hàm splitAtLastBy() trong thực tế. Hãy thử chạy nội dung này bằng cách chọn mục trong trình đơn Tiêu đề/tác giả riêng biệt cuối cùng "by" sau khi chọn...

...một ô:

...hoặc nhiều ô:

Bạn đã hoàn thành phần này của lớp học lập trình. Giờ đây, bạn có thể sử dụng Apps Script để đọc và sửa đổi dữ liệu chuỗi trong một trang tính và sử dụng các trình đơn tùy chỉnh để thực thi các lệnh Apps Script khác nhau.

Trong phần tiếp theo, bạn sẽ tìm hiểu cách cải thiện thêm tập dữ liệu này bằng cách điền vào các ô trống có dữ liệu được lấy từ một API công khai.

10. Tổng quan: Lấy dữ liệu từ các API công khai

Cho đến nay, bạn đã tinh chỉnh tập dữ liệu của mình để khắc phục một số vấn đề về định dạng tác giả và tiêu đề, nhưng tập dữ liệu vẫn thiếu một số thông tin, được làm nổi bật trong các ô bên dưới:

Bạn không thể lấy dữ liệu bị thiếu bằng cách sử dụng các thao tác chuỗi trên dữ liệu bạn hiện có. Thay vào đó, bạn sẽ cần lấy dữ liệu bị thiếu từ một nguồn khác. Bạn có thể thực hiện việc này trong Apps Script bằng cách yêu cầu thông tin từ các API bên ngoài. API này có thể cung cấp thêm dữ liệu.

API là giao diện lập trình ứng dụng. Đây là một thuật ngữ chung, nhưng về cơ bản, đây là dịch vụ mà các chương trình và tập lệnh của bạn có thể gọi để yêu cầu thông tin hoặc thực hiện các thao tác nhất định. Trong phần này, bạn đang gọi một API công khai để yêu cầu thông tin về sách mà bạn có thể chèn vào các ô trống trong trang tính của mình.

Phần này hướng dẫn bạn cách:

Yêu cầu dữ liệu sách từ một nguồn API bên ngoài.
Trích xuất tiêu đề và thông tin tác giả từ dữ liệu đã trả lại và ghi vào bảng tính của bạn.

11. Tìm nạp dữ liệu bên ngoài bằng UrlFetch

Trước khi tìm hiểu sâu hơn về mã hoạt động trực tiếp với bảng tính, bạn có thể tìm hiểu cách làm việc với các API bên ngoài trong Apps Script bằng cách tạo chức năng trợ giúp dành riêng cho việc yêu cầu thông tin về sách từ API Thư viện mở công khai.

Hàm trợ giúp của chúng tôi, fetchBookData_(ISBN), lấy số ISBN gồm 13 chữ số của một cuốn sách làm thông số và trả về dữ liệu về cuốn sách đó. Hàm này kết nối và truy xuất thông tin từ API Thư viện mở, sau đó phân tích cú pháp đối tượng JSON được trả về.

Triển khai

Bạn có thể triển khai chức năng trợ giúp này bằng cách làm như sau:

Trong trình chỉnh sửa Apps Script, hãy thêm mã sau vào cuối tập lệnh của bạn:

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}

Lưu dự án tập lệnh của bạn.

Đánh giá mã

Mã này được chia thành hai phần chính:

1: Yêu cầu API

Trong hai dòng đầu tiên, fetchBookData_(ISBN) kết nối với API Thư viện mở công khai bằng cách sử dụng điểm cuối URL của API và Dịch vụ tìm nạp URL của Apps Script\39;

Biến url chỉ là một chuỗi URL, giống như địa chỉ web. URL này trỏ đến một vị trí trên máy chủ Thư viện mở. Báo cáo này cũng có 3 thông số (bibkeys, jscmd và format) cho máy chủ Thư viện mở biết những thông tin mà bạn đang yêu cầu và cách cấu trúc nội dung phản hồi. Trong trường hợp này, bạn cung cấp số ISBN của sách và yêu cầu trả lại thông tin chi tiết ở định dạng JSON.

Khi bạn đã tạo chuỗi URL, mã sẽ gửi yêu cầu đến vị trí và nhận phản hồi. Việc này được thực hiện bằng phương thức UrlFetchApp.fetch(url, params). Tệp này sẽ gửi yêu cầu thông tin đến URL bên ngoài mà bạn cung cấp và lưu trữ phản hồi nhận được trong biến response. Ngoài URL, mã này còn đặt tham số không bắt buộc muteHttpExceptions thành true. Chế độ cài đặt này có nghĩa là mã của bạn sẽ không bị tạm dừng nếu yêu cầu dẫn đến lỗi API. Thay vào đó, hệ thống sẽ trả về phản hồi lỗi.

Yêu cầu này trả về một đối tượng HTTPResponse đã được lưu trữ trong biến response. Các phản hồi HTTP bao gồm mã phản hồi, tiêu đề HTTP và nội dung phản hồi chính. Thông tin về mối quan tâm ở đây là nội dung JSON chính, vì vậy, mã phải trích xuất nội dung đó rồi phân tích cú pháp JSON để tìm và trả lại thông tin mong muốn.

2: Phân tích cú pháp nội dung phản hồi của API và trả lại thông tin về mối quan tâm

Trong ba dòng mã cuối cùng, phương thức HTTPResponse.getContentText() sẽ trả về nội dung chính của phản hồi dưới dạng chuỗi. Chuỗi này ở định dạng JSON, nhưng API Thư viện mở xác định nội dung và định dạng chính xác. Phương thức JSON.parse(jsonString) chuyển đổi chuỗi JSON thành đối tượng JavaScript để có thể dễ dàng trích xuất các phần dữ liệu khác nhau. Cuối cùng, hàm trả về dữ liệu tương ứng với số ISBN của cuốn sách.

Kết quả

Giờ đây, khi bạn đã triển khai fetchBookData_(ISBN), các hàm khác trong mã của bạn có thể tìm thấy thông tin cho bất kỳ cuốn sách nào bằng số ISBN. Bạn sẽ sử dụng hàm này để giúp điền vào các ô trong bảng tính.

12. Ghi dữ liệu API vào một bảng tính

Giờ đây, bạn có thể triển khai hàm fillInTheBlanks() để thực hiện những việc sau:

Xác định tiêu đề và dữ liệu tác giả bị thiếu trong phạm vi dữ liệu đang hoạt động.
Truy xuất dữ liệu bị thiếu của một cuốn sách cụ thể bằng cách gọi API Thư viện mở bằng phương thức trợ giúp fetchBookData_(ISBN).
Cập nhật giá trị của tiêu đề hoặc tác giả bị thiếu trong các ô tương ứng.

Triển khai

Hãy triển khai chức năng mới này bằng cách thực hiện các thao tác sau:

Trong trình chỉnh sửa Apps Script, hãy thêm mã sau vào cuối dự án tập lệnh của bạn:

/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

Lưu dự án tập lệnh của bạn.

Đánh giá mã

Mã này được chia thành ba phần:

1: Đọc thông tin về sách hiện có

Ba dòng đầu tiên của hàm xác định hằng số nhằm giúp mã dễ đọc hơn. Trong hai dòng tiếp theo, biến bookValues được dùng để duy trì một bản sao cục bộ của thông tin về sách của trang tính. Mã này sẽ đọc thông tin từ bookValues, sử dụng API để điền thông tin còn thiếu và ghi lại các giá trị này vào bảng tính.

2: Tìm nạp thông tin còn thiếu bằng hàm trợ giúp

Mã này lặp qua từng hàng trong bookValues để tìm tiêu đề hoặc tác giả bị thiếu. Để giảm số lượng lệnh gọi API trong khi cải thiện hiệu quả, mã chỉ gọi API nếu đáp ứng những điều kiện sau:

Cột ISBN của hàng có giá trị.
Tiêu đề hoặc ô tác giả trong hàng trống.

Nếu điều kiện là đúng, mã sẽ gọi API bằng hàm trợ giúp fetchBookData_(isbn) mà bạn đã triển khai trước đó, và lưu trữ kết quả trong biến bookData. Bây giờ, trang này cần có thông tin còn thiếu mà bạn muốn chèn vào trang tính.

Việc còn lại là thêm thông tin bookData vào bảng tính. Tuy nhiên, có một lưu ý. Rất tiếc, các API công khai như API Thư viện mở đôi khi không có thông tin mà bạn yêu cầu hoặc đôi khi có thể gặp một số vấn đề khác ngăn không cho thông tin này cung cấp thông tin. Nếu bạn giả sử mọi yêu cầu API sẽ thành công, mã của bạn sẽ không đủ mạnh để xử lý các lỗi không mong muốn.

Để đảm bảo mã của bạn có thể xử lý các lỗi API, mã phải kiểm tra phản hồi API là hợp lệ trước khi sử dụng. Sau khi có bookData, mã này sẽ tiến hành kiểm tra đơn giản để xác minh rằng bookData và bookData.details tồn tại trước khi tìm cách đọc các mã đó. Nếu một trong hai thông tin bị thiếu, điều đó có nghĩa là API chưa có dữ liệu mà bạn muốn. Trong trường hợp này, lệnh continue yêu cầu mã bỏ qua hàng đó – bạn không thể điền vào các ô bị thiếu, nhưng ít nhất tập lệnh của bạn sẽ không gặp sự cố.

3: Ghi lại thông tin đã cập nhật vào trang tính

Phần cuối cùng của mã có các bước kiểm tra tương tự để xác minh API đã trả về thông tin tiêu đề và tác giả. Mã này chỉ cập nhật mảng bookValues nếu ô gốc hoặc ô tác giả trống và API trả về một giá trị bạn có thể đặt ở đó.

Vòng lặp sẽ thoát sau khi tất cả các hàng trong trang tính được kiểm tra. Bước cuối cùng là viết lại mảng bookValues hiện đã cập nhật vào bảng tính bằng Range.setValues(values).

Kết quả

Bây giờ, bạn có thể hoàn tất việc dọn dẹp dữ liệu sách của mình. Thực hiện thao tác sau:

Nếu bạn chưa & đánh dấu phạm vi A2:A15 trong trang tính của mình, hãy chọn Danh sách sách và gt; Phân tách tên/tác giả bằng dấu phẩy đầu tiên để làm sạch các vấn đề về dấu phẩy.
Nếu bạn chưa & đánh dấu phạm vi A2:A15 trong trang tính của mình, hãy chọn Danh sách sách > Tên/tác giả riêng lẻ tại "by".
Để điền tất cả các ô còn lại, hãy chọn Danh sách sách và gt; Điền các ô trống và tên tác giả:

13. 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 đã tìm hiểu cách tạo trình đơn tùy chỉnh để kích hoạt các phần khác nhau của mã Apps Script. Bạn cũng đã tìm hiểu cách nhập dữ liệu vào Google Trang tính bằng các dịch vụ Apps Script và API công khai. Đây là thao tác phổ biến trong việc xử lý bảng tính và Apps Script cho phép bạn nhập dữ liệu từ nhiều nguồn. Cuối cùng, bạn đã biết cách sử dụng dịch vụ Apps Script và JavaScript để đọc, xử lý và chèn dữ liệu bảng tính.

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 nhập dữ liệu từ bảng tính Google.
Cách tạo trình đơn tùy chỉnh trong hàm onOpen().
Cách phân tích cú pháp và thao tác với các giá trị dữ liệu chuỗi.
Cách gọi API công khai bằng Dịch vụ tìm nạp URL.
Cách phân tích cú pháp dữ liệu đối tượng JSON được truy xuất từ nguồn API công khai.

Khái niệm chính:

onOpen() là một trình kích hoạt đơn giản dùng để tạo các mục trong trình đơn. Trình kích hoạt đơn giản có nhiều hạn chế mà bạn phải tuân thủ.
Khi đọc hoặc viết dữ liệu ô trong bảng tính, tốt nhất bạn nên đọc hoặc viết toàn bộ một dải ô cùng một lúc với Range.getValues() hoặc Range.setValues(values). Không đọc hoặc đặt riêng từng ô, vì tốc độ đó sẽ chậm hơn.
Tên hàm Apps Script kết thúc bằng _ (dấu gạch dưới) được coi là riêng tư. Các tập lệnh khác không thể gọi những hàm này khi có trong thư viện hoặc do ứng dụng thông qua thông tin từ máy chủ đến máy khách. Nếu bạn biết một hàm chỉ được sử dụng bởi tập lệnh hiện tại, thì phương pháp hay nhất là kết thúc tên hàm bằng _.
Khi sử dụng dữ liệu do các API bên ngoài trả về, hãy xác thực dữ liệu trước khi cố gắng sử dụng. Điều này sẽ làm giảm lỗi trong mã của bạn nếu API trả về kết quả không mong muốn.

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.
Chức năng 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 là ứng dụng khách 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.

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ẽ đi sâu hơn vào cách định dạng dữ liệu trong bảng tính.

Tìm lớp học lập trình tiếp theo tại Định dạng dữ liệu.