Kiến thức cơ bản về Apps Script với Google Trang tính #3: Làm việc với dữ liệu

Kiến thức cơ bản về Apps Script với Google Trang tính #3:
Làm việc với dữ liệu

Thông tin về lớp học lập trình này

subjectLần cập nhật gần đây nhất: thg 9 18, 2024
account_circleTác giả: Google Workspace Developer Relations

1. Giới thiệu

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

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

  • Cách nhập dữ liệu từ một bảng tính cá nhân hoặc bảng tính dùng chung trong Drive.
  • Cách tạo một trình đơn tuỳ 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 các ô của 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 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 đó:

  1. Macro và hàm tuỳ chỉnh
  2. Bảng tính, Trang tính và Dải ô

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 String của ngôn ngữ này

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 bạn phải có một bảng tính để làm việc. Hãy làm theo các bước sau để tạo một bảng tính dùng trong các bài tập này:

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

545c02912de7d112.png

  1. Để mở trình chỉnh sửa tập lệnh, hãy nhấp vào Tiện ích mở rộng> Apps Script
  2. Nhấp vào tiêu đề dự án Apps Script rồi thay đổi từ "Dự án chưa có tiêu đề" thành "Xử lý dữ liệu và trình đơn tuỳ chỉnh". Nhấp vào Đổi tên để lưu nội dung thay đổi về tiêu đề.

Với một bảng tính và dự án trống, bạn đã sẵn sàng bắt đầu bài thực hành. Chuyển đến phần tiếp theo để bắt đầu tìm hiểu về trình đơn tuỳ chỉnh.

3. Tổng quan: Nhập dữ liệu bằng một mục trình đơn tuỳ chỉnh

Apps Script cho phép bạn xác định trình đơn tuỳ chỉnh có thể xuất hiện trong Google Trang tính. Bạn cũng có thể sử dụng trình đơn tuỳ 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 tuỳ chỉnh, bạn sẽ tạo một nhãn văn bản và kết nối nhãn đó với một hàm Apps Script trong dự án tập lệnh. Sau đó, bạn có thể thêm trình đơn vào giao diện người dùng để trình đơn xuất hiện trong Google Trang tính:

d6b694da6b8c6783.png

Khi người dùng nhấp vào một mục trong trình đơn tuỳ chỉnh, hàm Apps Script mà bạn liên kết với mục đó sẽ thực thi. Đây là cách nhanh chóng để chạy các hàm Apps Script mà không cần mở trình chỉnh sửa tập lệnh. Điều 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 biết bất cứ điều gì về cách mã hoặc Apps Script hoạt động. Đối với họ, đó chỉ là một món ăn khác trong thực đơn.

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

4. Hàm onOpen()

Điều kiện kích hoạt đơn giản trong Apps Script là một cách để chạy mã Apps Script cụ thể nhằm phản hồi một số điều kiện hoặc sự kiện nhất định. Khi tạo một điều kiện kích hoạt, bạn sẽ xác định sự kiện nào khiến điều kiện kích hoạt hoạt động và cung cấp một 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. Bạn có thể dễ dàng thiết lập các hàm này. Tất cả những gì bạn phải làm là 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 bảng tính được liên kết được mở hoặc tải lại:

/**
 * 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 tuỳ chỉnh.

  1. Thay thế mã trong dự án tập lệnh 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();
}
  1. Lưu dự án tập lệ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.

Ba dòng tiếp theo sẽ 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. 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à hàm Apps Script chạy khi mục trong trình đơn được chọn. Trong trường hợp này, việc chọn mục Load Book-list trong trình đơn sẽ khiến Sheets cố gắng chạy hàm loadBookList() (hàm này chưa tồn tại).

Kết quả

Chạy hàm này ngay bây giờ để xem hàm có hoạt động hay không:

  1. Trong Google Trang tính, hãy tải lại bảng tính. Lưu ý: thao tác này thường đóng thẻ có trình chỉnh sửa tập lệnh.
  2. Mở lại trình chỉnh sửa tập lệnh bằng cách chọn Công cụ > 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:

687dfb214f2930ba.png

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

8a4a391fbabcb16a.png

Phần tiếp theo sẽ 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 tuỳ chỉnh và có thể tạo các hàm có thể chạy bằng cách nhấp vào mục trình đơn.

Hiện tại, trình đơn tuỳ chỉnh Book-list có một mục trình đơn: Load Book-list. Hàm được gọi khi bạn chọn mục 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, khi chọn Book-list > Load Book-list (Danh sách sách > Tải danh sách sách), bạn sẽ gặp lỗi:

b94dcef066e7041d.gif

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 điền dữ liệu vào bảng tính để xử lý, vì vậy, bạn sẽ triển khai loadBookList() để đọc dữ liệu sách từ một bảng tính khác và sao chép vào bảng tính này:

  1. 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);
}
  1. Lưu dự án tập lệnh.

Đánh giá mã

Vậy chức năng này hoạt động như thế nào? Hàm loadBookList() chủ yếu sử dụng các phương thức 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 nắm được những khái niệm này, bạn có thể chia mã loadBookList() thành 4 phần sau:

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

Dòng đầu tiên sử 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ữ thông tin đó 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 mà đoạn mã đang đọc dữ liệu. Mã này tìm bảng tính theo mã nhận dạng bảng tính. Trong ví dụ này, chúng ta đã cung cấp mã nhận dạng của một 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ữ thông tin tham chiếu đến một trang tính trong bookSS chứa dữ liệu bạn muốn. Mã này xác định trang cần đọc theo tên của trang đó, codelab-book-list.
  • bookRange lưu trữ một thông tin tham chiếu đến một dải 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 nhậ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 2 chiều chứa tất cả các giá trị lấy từ các ô trong bookRange. Phương thức Range.getValues() tạo ra 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 sang đích

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

4: Định dạng trang đí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 này sử dụng Sheet.autoResizeColumns(startColumn, numColumns) để đổi kích thước 3 cột đầu tiên trong trang tính đích, giúp bạn đọc dữ liệu mới dễ dàng hơn.

Kết quả

Bạn có thể xem chức năng này hoạt động. Trong Google Trang tính, hãy chọn Book-list > Load book-list (Danh sách sách > Tải danh sách sách) để chạy hàm này nhằm điền vào bảng tính:

3c797e1e2b9fe641.gif

Giờ đây, bạn đã có một trang tính chứa 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 xử lý chuỗi và trình đơn tuỳ chỉnh.

6. Tổng quan: Làm sạch dữ liệu trong bảng tính

Giờ đây, bạn đã có thông tin về 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 trong các cột riêng biệt. Tuy nhiên, bạn cũng có thể thấy một số vấn đề với dữ liệu thô này:

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

Trong các phần tiếp theo, bạn sẽ khắc phục những vấn đề này bằng cách dọn dẹp 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 tìm thấy dấu phẩy hoặc dấu phân cách " by ", đặt các chuỗi con tiêu đề và tác giả 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 còn thiếu về sách bằng cách sử dụng một API bên ngoài và thêm thông tin đó vào trang tính.

7. Thêm các mục trong trình đơn

Bạn sẽ muốn tạo 3 mục trong trình đơn để kiểm soát các thao tác dọn dẹp dữ liệu mà bạn sẽ triển khai.

Triển khai

Hãy cập nhật onOpen() để thêm các mục bổ sung trong trình đơn mà bạn sẽ cần. Hãy thực hiện như sau:

  1. Trong dự án tập lệnh, hãy cập nhật mã onOpen() sao cho khớp với đoạn 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();
}
  1. Lưu dự án tập lệnh.
  2. Trong trình chỉnh sửa tập lệnh, hãy chọn onOpen trong 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.

Trong mã mới này, phương thức Menu.addSeparator() sẽ tạo một đường phân chia ngang trong trình đơn để các nhóm mục trình đơn có liên quan được sắp xếp một cách trực quan. Sau đó, các mục mới trong trình đơn sẽ được thêm bên dưới mục này, với 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 mới trong trình đơn:

580c806ce8fd4872.png

Việc nhấp vào các mục mới 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 thực hiện việc này tiếp theo.

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

Tập dữ liệu mà bạn đã nhập vào bảng tính có một số ô mà 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:

ca91c43c4e51d6b5.png

Phân tách chuỗi văn bản thành các cột riêng biệt là một thao tác thường gặp trong bảng tính. Google Trang tính cung cấp hàm SPLIT() để chia chuỗi thành các cột. Tuy nhiên, các tập dữ liệu thường gặp phải những vấn đề mà bạn không thể dễ dàng giải quyết bằng các hàm tích hợp của Trang tính. 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 nhằm dọn dẹp và sắp xếp dữ liệu.

Bắt đầu dọn dẹp dữ liệu bằng cách triển khai trước tiên một hàm có tên là splitAtFirstComma(). Hàm này sẽ chia tác giả và tiêu đề thành các ô tương ứng khi tìm thấy dấu phẩy.

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

  1. Lấy dải ô đại diện cho các ô hiện được chọn.
  2. Kiểm tra xem các ô trong dải ô có dấu phẩy hay không.
  3. Khi tìm thấy dấu phẩy, hãy chia chuỗi thành 2 (và chỉ 2) chuỗi con tại vị trí của dấu phẩy đầu tiên. Để đơn giản hoá, bạn có thể giả định rằng dấu phẩy bất kỳ đều biểu thị một mẫu chuỗi "[tác giả], [tiêu đề]". Bạn cũng có thể giả định rằng nếu có nhiều dấu phẩy trong ô, thì bạn nên tách theo dấu phẩy đầu tiên trong chuỗi.
  4. Đặt các chuỗi con làm nội dung mới của các ô tiêu đề và tác giả tương ứng.

Triển khai

Để triển khai các bước này, bạn 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 bạn cũng cần dùng JavaScript để thao tác dữ liệu chuỗi. Hãy làm theo các bước sau:

  1. 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:
/**
 * 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);
}
  1. Lưu dự án tập lệnh.

Đánh giá mã

Hãy xem xét đoạn mã mới bao gồm 3 phần chính:

1: Truy xuất các giá trị tiêu đề được làm nổi bật

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 biểu thị dải ô mà người dùng hiện đã đánh dấu khi hàm splitAtFirstComma() được gọi. Để đơn giản hoá bài tập này, 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 biểu thị một dải ô mới bao gồm các ô giống như activeRange, nhưng cũng có thêm một cột ở 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í để đặt mọi tác giả mà mã tìm thấy trong cột tiêu đề.
  • titleAuthorValues là một mảng dữ liệu 2D được trích xuất từ titleAuthorRange bằng cách sử dụng Range.getValues().

2: Kiểm tra từng tiêu đề và chia theo dấu phân cá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. Vòng lặp For của JavaScript được 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 việc sau:

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

Lưu ý: Khi không tìm thấy dấu phẩy, mã này sẽ giữ nguyên dữ liệu trong hàng.

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

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

Kết quả

Giờ đây, bạn có thể thấy hiệu ứng của hàm splitAtFirstComma() đang hoạt động. Hãy thử chạy bằng cách chọn mục trình đơn Separate title/author at first comma (Tách tiêu đề/tác giả tại dấu phẩy đầu tiên) sau khi chọn...

...một ô:

a24763b60b305376.gif

...hoặc nhiều ô:

89c5c89b357d3713.gif

Giờ đây, bạn đã tạo một hàm Apps Script xử lý dữ liệu trên Trang tính. Tiếp theo, bạn sẽ triển khai hàm chia tách thứ hai.

9. Phân tách văn bản bằng dấu phân cách &quot;by&quot;

Khi xem dữ liệu ban đầu, bạn có thể thấy một vấn đề khác. Cũng giống như một số định dạng dữ liệu có tiêu đề và tác giả trong một ô dưới dạng "[authors], [title]", các ô khác định dạng tác giả và tiêu đề dưới dạng "[title] by [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 cùng một kỹ thuật trong phần cuối cùng, tạo một hàm có tên là splitAtLastBy(). Hàm này có chức năng tương tự như splitAtFirstComma() – điểm khác biệt thực sự duy nhất là hàm này đang tìm kiếm một mẫu văn bản hơi khác. Triển khai hàm này bằng cách làm như sau:

  1. 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:
/** 
 * 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);
}
  1. Lưu dự án tập lệnh.

Đánh giá mã

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

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

Kết quả

Giờ đây, bạn có thể thấy hiệu ứng của hàm splitAtLastBy() đang hoạt động. Hãy thử chạy bằng cách chọn mục trình đơn Separate title/author at last "by" (Tách tiêu đề/tác giả ở cuối "bởi") sau khi chọn...

...một ô:

4e6679e134145975.gif

...hoặc nhiều ô:

3c879c572c61e62f.gif

Bạn đã hoàn tất phần này của lớp học lập trình. Giờ đây, bạn có thể dùng Apps Script để đọc và sửa đổi dữ liệu chuỗi trong một trang tính, đồng thời dùng trình đơn tuỳ 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 dữ liệu lấy từ một API công khai vào các ô trống.

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

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

af0dba8cb09d1a49.png

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 trên chuỗi đối với dữ liệu mà bạn hiện có. Thay vào đó, bạn cần lấy dữ liệu còn 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 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à một 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 một số hành động nhất định. Trong phần này, bạn sẽ gọi một API có sẵn 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.

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

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

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

Trước khi đi sâu vào mã hoạt động trực tiếp với bảng tính, bạn có thể tìm hiểu về cách sử dụng các API bên ngoài trong Apps Script bằng cách tạo một hàm trợ giúp dành riêng cho việc yêu cầu thông tin về sách từ API Open Library 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 tham số và trả về dữ liệu về cuốn sách đó. Ứng dụng này kết nối và truy xuất thông tin từ Open Library API, sau đó phân tích cú pháp đối tượng JSON được trả về.

Triển khai

Triển khai hàm trợ giúp này bằng cách làm như sau:

  1. Trong trình chỉnh sửa Apps Script, hãy thêm đoạn mã sau vào cuối tập lệnh:
/**
 * 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];
}
  1. Lưu dự án tập lệnh.

Đá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 Open Library API 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.

Biến url chỉ là một chuỗi URL, chẳng hạn như địa chỉ web. Mã này trỏ đến một vị trí trên máy chủ của Open Library. API này cũng bao gồm 3 tham số (bibkeys, jscmd và format) cho biết thông tin bạn đang yêu cầu và cách cấu trúc phản hồi cho các máy chủ của Open Library. Trong trường hợp này, bạn cung cấp số ISBN của cuốn sách và yêu cầu trả về thông tin chi tiết ở định dạng JSON.

Sau khi bạn tạo chuỗi URL, mã sẽ gửi một yêu cầu đến vị trí và nhận được phản hồi. Việc này được thực hiện bằng phương thức UrlFetchApp.fetch(url, params). Thao tác này sẽ gửi một 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 dừng nếu yêu cầu dẫn đến lỗi API. Thay vào đó, phản hồi lỗi sẽ được trả về.

Yêu cầu này trả về một đối tượng HTTPResponse được lưu trữ trong biến response. 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 cần thiết ở đâ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 để xác định vị trí và trả về thông tin mong muốn.

2: Phân tích cú pháp phản hồi API và trả về thông tin cần thiết

Trong 3 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 một chuỗi. Chuỗi này ở định dạng JSON, nhưng Open Library API 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 một đối tượng JavaScript để bạn có thể dễ dàng trích xuất các phần khác nhau của dữ liệu. Cuối cùng, hàm này sẽ trả về dữ liệu tương ứng với số ISBN của cuốn sách.

Kết quả

Giờ đây, sau 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 của cuốn sách đó. Bạn sẽ dùng hàm này để điền vào các ô trong bảng tính.

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

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

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

Triển khai

Triển khai hàm mới này bằng cách làm như sau:

  1. 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:
/**
 * 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);
}
  1. Lưu dự án tập lệnh.

Đánh giá mã

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

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

3 dòng đầu tiên của hàm xác định các hằng số để giúp mã dễ đọc hơn. Trong 2 dòng tiếp theo, biến bookValues được dùng để duy trì bản sao cục bộ của thông tin về sách trong 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 các giá trị này trở lại bảng tính.

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

Đoạn mã này lặp lại trên từng hàng trong bookValues để tìm tên sách 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 suất, mã chỉ gọi API nếu các điều kiện sau đây là đúng:

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

Nếu các đ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ờ, bảng tính đó sẽ có thông tin còn thiếu mà bạn muốn chèn vào.

Nhiệm vụ duy nhất còn lại là thêm thông tin bookData vào bảng tính của chúng ta. Tuy nhiên, có một điểm cần lưu ý. Thật không may, các API công khai như Open Library Book API đôi khi không có thông tin mà bạn yêu cầu hoặc đôi khi có thể gặp phải một số vấn đề khác khiến API này không thể cung cấp thông tin. Nếu bạn giả định rằng mọi yêu cầu API sẽ thành công, thì mã của bạn sẽ không đủ mạnh mẽ để 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 xem phản hồi API có hợp lệ hay không trước khi cố gắng sử dụng. Sau khi có bookData, mã sẽ thực hiện một bước kiểm tra đơn giản để xác minh bookData và bookData.details có tồn tại hay không trước khi cố gắng đọc từ các tệp này. Nếu thiếu một trong hai, tức là API không có dữ liệu bạn muốn. Trong trường hợp này, lệnh continue sẽ 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 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 thông tin về tiêu đề và tác giả mà API trả về. Mã này chỉ cập nhật mảng bookValues nếu ô tiêu đề hoặc ô tác giả ban đầu trống và API trả về một giá trị mà bạn có thể đặt vào đó.

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à ghi mảng bookValues vừa cập nhật trở lại bảng tính bằng cách sử dụng Range.setValues(values).

Kết quả

Giờ đây, bạn có thể hoàn tất việc dọn dẹp dữ liệu sách. Hãy thực hiện như sau:

  1. Nếu bạn chưa làm, hãy đánh dấu dải ô A2:A15 trong trang tính của bạn, rồi chọn Book-list > Separate title/author at first comma (Danh sách sách > Tách tiêu đề/tác giả tại dấu phẩy đầu tiên) để khắc phục vấn đề về dấu phẩy.
  2. Nếu bạn chưa làm, hãy đánh dấu dải ô A2:A15 trong trang tính rồi chọn Book-list > Separate title/author at last "by" (Danh sách sách > Tách tiêu đề/tác giả ở phần "bởi" cuối cùng) để khắc phục vấn đề "bởi".
  3. Để điền vào tất cả các ô còn lại, hãy chọn Danh sách sách > Điền vào các ô trống về tiêu đề và tác giả:

826675a3437adbdb.gif

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 tuỳ chỉnh để kích hoạt các phần khác nhau trong mã Apps Script. Bạn cũng đã biết cách nhập dữ liệu vào Google Trang tính bằng cách sử dụng các dịch vụ Apps Script và API công khai. Đây là một thao tác phổ biến trong quá trình 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 đã thấy cách sử dụng các 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?

Kiến thức 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 tuỳ 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ừ một nguồn API công khai.

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.

bug_report Báo cáo lỗi