Hàm tuỳ chỉnh trong Google Trang tính

Google Trang tính cung cấp hàng trăm các chức năng tích hợp sẵn như AVERAGE! SUM và VLOOKUP Những trường hợp không cần đủ cho nhu cầu của mình, bạn có thể sử dụng Google Apps Script để viết các hàm tuỳ chỉnh — giả sử để chuyển đổi mét sang dặm hoặc tìm nạp nội dung trực tiếp từ Internet — sau đó sử dụng chúng trong Google Trang tính giống như một hàm tích hợp sẵn.

Bắt đầu

Các hàm tuỳ chỉnh được tạo bằng JavaScript chuẩn. Nếu bạn mới sử dụng JavaScript, Codecademy cung cấp khoá học hay cho người mới bắt đầu. (Lưu ý: khoá học này không được phát triển bởi và không được liên kết với Google.)

Dưới đây là một hàm tuỳ chỉnh đơn giản, có tên là DOUBLE, sẽ nhân một giá trị giá trị đầu vào tăng thêm 2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

Nếu bạn không biết cách viết JavaScript và không có thời gian để tìm hiểu, kiểm tra cửa hàng tiện ích bổ sung để xem người khác đã tạo hàm tuỳ chỉnh mà bạn cần chưa.

Tạo hàm tuỳ chỉnh

Cách viết hàm tuỳ chỉnh:

  1. Tạo hoặc mở một bảng tính trong Google Trang tính.
  2. Chọn mục trong trình đơn Tiện ích > Apps Script.
  3. Xoá bất kỳ mã nào trong trình chỉnh sửa tập lệnh. Đối với hàm DOUBLE ở trên, chỉ cần sao chép và dán mã đó vào trình chỉnh sửa tập lệnh.
  4. Ở trên cùng, hãy nhấp vào Lưu .

Bây giờ, bạn có thể sử dụng hàm tuỳ chỉnh.

Nhận hàm tuỳ chỉnh từ Google Workspace Marketplace

Google Workspace Marketplace cung cấp một vài tuỳ chỉnh có giá trị là tiện ích bổ sung dành cho Google Trang tính. Cách sử dụng hoặc khám phá những tiện ích bổ sung này:

  1. Tạo hoặc mở một bảng tính trong Google Trang tính.
  2. Ở trên cùng, hãy nhấp vào tiện ích bổ sung > Tải tiện ích bổ sung.
  3. Sau khi Google Workspace Marketplace mở ra, hãy nhấp vào hộp tìm kiếm ở góc trên cùng bên phải.
  4. Nhập "chức năng tuỳ chỉnh" rồi nhấn Enter.
  5. Nếu thấy một tiện ích bổ sung chức năng tuỳ chỉnh mà bạn quan tâm, hãy nhấp vào Cài đặt để cài đặt ứng dụng đó.
  6. Một hộp thoại có thể cho bạn biết rằng tiện ích bổ sung yêu cầu cấp phép. Nếu có, đọc kỹ thông báo, rồi nhấp vào Cho phép.
  7. Tiện ích bổ sung sẽ có trong bảng tính. Để sử dụng tiện ích bổ sung trong bảng tính khác, hãy mở bảng tính còn lại rồi nhấp vào ở trên cùng tiện ích bổ sung > Quản lý tiện ích bổ sung. Tìm tiện ích bổ sung mà bạn muốn sử dụng rồi nhấp vào Tuỳ chọn > Sử dụng trong trường hợp này tài liệu.

Sử dụng hàm tuỳ chỉnh

Sau khi viết hàm tuỳ chỉnh hoặc cài đặt hàm tuỳ chỉnh từ Google Workspace Marketplace, ứng dụng này dễ sử dụng như hàm tích hợp sẵn:

  1. Nhấp vào ô mà bạn muốn dùng hàm.
  2. Nhập dấu bằng (=), theo sau là tên hàm và giá trị nhập bất kỳ — ví dụ: =DOUBLE(A1) — rồi nhấn Enter.
  3. Ô sẽ hiển thị Loading... trong giây lát rồi trả về kết quả.

Nguyên tắc về hàm tuỳ chỉnh

Bạn cần biết một số nguyên tắc trước khi viết hàm tuỳ chỉnh của riêng mình.

Đặt tên

Ngoài các quy ước chuẩn để đặt tên cho các hàm JavaScript, nhận biết những điều sau:

  • Tên của hàm tuỳ chỉnh phải khác biệt với tên của các chức năng tích hợp sẵn như SUM().
  • Tên của một hàm tùy chỉnh không được kết thúc bằng dấu gạch dưới (_). biểu thị một hàm riêng tư trong Apps Script.
  • Bạn phải khai báo tên của hàm tuỳ chỉnh bằng cú pháp function myFunction(), chứ không phải var myFunction = new Function().
  • Viết hoa không quan trọng, mặc dù tên của các hàm trong bảng tính được viết hoa theo cách truyền thống.

Đối số

Giống như hàm tích hợp, hàm tuỳ chỉnh có thể lấy đối số làm giá trị đầu vào:

  • Nếu bạn gọi hàm có tham chiếu đến một ô làm đối số (như =DOUBLE(A1)), đối số sẽ là giá trị của ô.

  • Nếu bạn gọi hàm có tham chiếu đến dải ô là một đối số (như =DOUBLE(A1:B10)), đối số sẽ là hai chiều mảng ô giá trị. Ví dụ: trong ảnh chụp màn hình dưới đây, các đối số trong =DOUBLE(A1:B2) được Apps Script diễn giải là double([[1,3],[2,4]]). Lưu ý rằng mã mẫu cho DOUBLE từ trên cần phải được được sửa đổi để chấp nhận một mảng làm dữ liệu đầu vào.


  • Đối số của hàm tùy chỉnh phải mang tính tất định. Đó là các hàm bảng tính tích hợp trả về một kết quả mỗi lần chúng tính toán (chẳng hạn như NOW() hoặc RAND()) không được phép dùng làm đối số vào một hàm tùy chỉnh. Nếu một hàm tuỳ chỉnh cố gắng trả về một giá trị dựa trên một trong các hàm được tích hợp sẵn dễ tính toán lại này, nó sẽ hiển thị Loading... vô thời hạn.

Giá trị trả về

Mỗi hàm tuỳ chỉnh phải trả về một giá trị cần hiển thị, sao cho:

  • Nếu một hàm tuỳ chỉnh trả về một giá trị, thì giá trị đó sẽ xuất hiện trong ô hàm được gọi từ đó.
  • Nếu hàm tùy chỉnh trả về một mảng giá trị hai chiều, thì các giá trị tràn vào các ô liền kề miễn là các ô đó trống. Nếu việc này khiến mảng ghi đè lên nội dung ô hiện có, hàm tuỳ chỉnh sẽ thay vào đó sẽ gửi một lỗi. Để biết ví dụ, hãy xem phần về tối ưu hoá các chức năng tuỳ chỉnh.
  • Một hàm tuỳ chỉnh không thể ảnh hưởng đến các ô khác với những ô mà hàm này trả về giá trị. Nói cách khác, một hàm tuỳ chỉnh không thể chỉnh sửa các ô tuỳ ý, mà chỉ chỉnh sửa các ô các ô mà từ đó được gọi và các ô liền kề của các ô đó. Cách chỉnh sửa các ô tuỳ ý: hãy sử dụng trình đơn tuỳ chỉnh để chạy một hàm.
  • Lệnh gọi hàm tùy chỉnh phải trả về trong vòng 30 giây. Nếu không, ô hiển thị #ERROR! và ghi chú của ô là Exceeded maximum execution time (line 0).

Loại dữ liệu

Google Trang tính lưu trữ dữ liệu trong các định dạng khác nhau tuỳ thuộc vào bản chất của dữ liệu. Khi các giá trị này được sử dụng trong các hàm tùy chỉnh, Ứng dụng Tập lệnh coi chúng là loại dữ liệu thích hợp trong JavaScript. Sau đây là những vấn đề gây nhầm lẫn thường gặp nhất:

  • Giờ và ngày trong Trang tính trở thành Đối tượng Date (Ngày) trong Apps Script. Nếu bảng tính và tập lệnh sử dụng các múi giờ khác nhau (một vấn đề hiếm gặp), hàm tuỳ chỉnh sẽ cần phải bù đắp.
  • Giá trị thời lượng trong Trang tính cũng trở thành đối tượng Date, nhưng Việc làm việc với họ có thể phức tạp.
  • Giá trị phần trăm trong Trang tính trở thành số thập phân trong Apps Script. Cho ví dụ: một ô có giá trị 10% sẽ trở thành 0.1 trong Apps Script.

Tự động hoàn thành

Google Trang tính hỗ trợ tính năng tự động hoàn thành cho các hàm tuỳ chỉnh giống như các hàm tích hợp sẵn. Khi bạn nhập tên hàm vào ô, bạn sẽ thấy danh sách các hàm khớp với những gì bạn nhập.

Các hàm tùy chỉnh sẽ xuất hiện trong danh sách này nếu tập lệnh của chúng bao gồm JsDoc @customfunction, như trong ví dụ DOUBLE() bên dưới.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Nâng cao

Sử dụng dịch vụ Google Apps Script

Hàm tuỳ chỉnh có thể gọi một số Các dịch vụ Google Apps Script giúp các dịch vụ này phức tạp hơn công việc. Ví dụ: hàm tùy chỉnh có thể gọi phương thức Dịch vụ Ngôn ngữ dịch tiếng Anh sang tiếng Tây Ban Nha.

Không giống như hầu hết các loại Apps Script khác, các chức năng tùy chỉnh không bao giờ yêu cầu người dùng cho phép truy cập vào dữ liệu cá nhân. Do đó, họ chỉ có thể gọi dịch vụ không có quyền truy cập vào dữ liệu cá nhân, cụ thể là:

Dịch vụ được hỗ trợ Ghi chú
Bộ nhớ đệm Hoạt động, nhưng không đặc biệt hữu ích đối với các hàm tuỳ chỉnh
HTML Có thể tạo HTML, nhưng không thể hiển thị HTML (hiếm khi hữu ích)
JDBC
Ngôn ngữ
Khoá Hoạt động, nhưng không đặc biệt hữu ích đối với các hàm tuỳ chỉnh
Maps Có thể tính chỉ đường, nhưng không thể hiển thị bản đồ
Thuộc tính getUserProperties() chỉ nhận các thuộc tính của chủ sở hữu bảng tính. Trình chỉnh sửa bảng tính không thể đặt thuộc tính người dùng trong một hàm tùy chỉnh.
Bảng tính Chỉ đọc (có thể sử dụng hầu hết các phương thức get*(), nhưng không thể sử dụng set*()).
Không thể mở các bảng tính khác (SpreadsheetApp.openById() hoặc SpreadsheetApp.openByUrl()).
Tìm nạp URL
Phần mềm tiện ích
XML

Nếu hàm tuỳ chỉnh của bạn gửi thông báo lỗi You do not have permission to call X service., dịch vụ sẽ yêu cầu sự cho phép của người dùng nên không thể dùng trong hàm tuỳ chỉnh.

Để sử dụng một dịch vụ khác với các dịch vụ được liệt kê ở trên, hãy tạo một trình đơn tuỳ chỉnh chạy hàm của Apps Script thay vì viết một hàm tuỳ chỉnh. Một hàm được kích hoạt từ một trình đơn sẽ yêu cầu người dùng cấp phép nếu cần và do đó có thể sử dụng tất cả Các dịch vụ Apps Script.

Chia sẻ

Các hàm tuỳ chỉnh bắt đầu ràng buộc với trong bảng tính mà chúng được tạo. Điều này có nghĩa là một hàm tuỳ chỉnh được viết bằng không thể sử dụng một bảng tính trong các bảng tính khác trừ khi bạn sử dụng một trong các phương thức sau:

  • Nhấp vào Tiện ích > Apps Script để mở trình chỉnh sửa tập lệnh, sau đó sao chép văn bản tập lệnh từ bảng tính ban đầu và dán văn bản đó vào trình chỉnh sửa tập lệnh của một bảng tính khác.
  • Tạo bản sao của bảng tính chứa hàm tuỳ chỉnh bằng cách nhấp vào biểu tượng Tệp > Tạo bản sao. Khi một bảng tính được sao chép, mọi tập lệnh được đính kèm vào nó cũng được sao chép. Bất kỳ ai có quyền truy cập vào bảng tính đều có thể sao chép tập lệnh. (Những cộng tác viên chỉ có quyền xem không thể mở trình chỉnh sửa tập lệnh trong bảng tính ban đầu. Tuy nhiên, khi tạo một bản sao, họ trở thành chủ sở hữu bản sao và có thể xem tập lệnh).
  • Xuất bản tập lệnh dưới dạng tiện ích bổ sung Trình chỉnh sửa của Google Trang tính.

Tối ưu hoá

Mỗi lần có một hàm tuỳ chỉnh được sử dụng trong bảng tính, Google Trang tính sẽ tạo một lệnh gọi riêng đến máy chủ Apps Script. Nếu bảng tính của bạn chứa hàng chục (hoặc hàng trăm hoặc hàng nghìn lệnh gọi hàm tuỳ chỉnh, nên quá trình này có thể khá chậm.

Do đó, nếu bạn dự định sử dụng hàm tuỳ chỉnh nhiều lần trên dải ô dữ liệu, hãy xem xét sửa đổi hàm để hàm này chấp nhận dải ô đầu vào ở dạng mảng hai chiều, sau đó trả về giá trị hai chiều mà có thể tràn vào các ô thích hợp.

Ví dụ: có thể viết lại hàm DOUBLE() hiển thị ở trên để chấp nhận một ô hoặc dải ô như sau:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Phương pháp trên sử dụng phương thức map của đối tượng Array của JavaScript đến đệ quy gọi DOUBLE trên mọi giá trị trong mảng ô hai chiều. Hàm này trả về một giá trị mảng hai chiều chứa kết quả. Bằng cách này, bạn có thể gọi DOUBLE chỉ một lần nhưng cho phép tính cho một số lượng lớn ô cùng một lúc, như minh hoạ trong ảnh chụp màn hình dưới đây. (Bạn có thể thực hiện điều tương tự với if được lồng thay vì lệnh gọi map.)

Tương tự, hàm tuỳ chỉnh dưới đây tìm nạp nội dung trực tiếp một cách hiệu quả từ Internet và sử dụng mảng hai chiều để hiển thị hai cột kết quả với chỉ một lệnh gọi hàm duy nhất. Nếu mỗi ô yêu cầu lệnh gọi hàm riêng, phương thức sẽ mất nhiều thời gian hơn nhiều vì máy chủ Apps Script sẽ phải tải xuống và phân tích cú pháp nguồn cấp dữ liệu XML mỗi lần.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Những kỹ thuật này có thể áp dụng cho hầu hết mọi hàm tuỳ chỉnh được dùng lặp đi lặp lại trong một bảng tính, mặc dù chi tiết triển khai sẽ khác nhau tuỳ theo hành vi của hàm.