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

Hàm tùy chỉnh trong Google Trang tính

Google Trang tính cung cấp hàng trăm hàm tích hợp sẵn như AVERAGE, SUM và VLOOKUP. Khi các công cụ này chưa đáp ứng được nhu cầu của bạn, 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 rồi 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 sẽ cung cấp một khoá học tuyệt vời 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 liên kết với Google.)

Sau đây là một hàm tuỳ chỉnh đơn giản có tên DOUBLE. Hàm này nhân giá trị đầu vào với 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, hãy kiểm tra cửa hàng tiện ích bổ sung để xem liệu có người nào khác đã tạo hàm tuỳ chỉnh mà bạn cần hay chưa.

Tạo hàm tuỳ chỉnh

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

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

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

Lấy hàm tuỳ chỉnh từ Google Workspace Marketplace

Google Workspace Marketplace cung cấp một số hàm tuỳ chỉnh dưới dạng tiện ích bổ sung cho Google Trang tính. Cách sử dụng hoặc khám phá các tiện ích bổ sung này:

Tạo hoặc mở một bảng tính trong Google Trang tính.
Ở trên cùng, hãy nhấp vào Tiện ích bổ sung > Tải tiện ích bổ sung.
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.
Nhập "hàm tuỳ chỉnh" rồi nhấn phím Enter.
Nếu bạn tìm thấy một tiện ích bổ sung của hàm tuỳ chỉnh mà bạn quan tâm, hãy nhấp vào Install (Cài đặt) để cài đặt.
Một hộp thoại có thể cho bạn biết rằng tiện ích bổ sung đó cần được cho phép. Nếu có, hãy đọc kỹ thông báo, sau đó nhấp vào Cho phép.
Tiện ích bổ sung này sẽ có trong bảng tính. Để sử dụng tiện ích bổ sung trong một bảng tính khác, hãy mở bảng tính khác, sau đó nhấp vào Tiện ích bổ sung > Quản lý tiện ích bổ sung ở trên cùng. Tìm tiện ích bổ sung bạn muốn sử dụng rồi nhấp vào biểu tượng Tùy chọn > Sử dụng trong tài liệu này.

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

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

Nhấp vào ô mà bạn muốn dùng hàm.
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) – và nhấn Enter.
Ô sẽ hiển thị Loading... trong giây lát, sau đó trả về kết quả.

Nguyên tắc đối với hàm tuỳ chỉnh

Trước khi viết hàm tuỳ chỉnh, bạn cần biết một số nguyên tắc.

Đặt tên

Ngoài các quy ước tiêu chuẩn để đặt tên cho hàm JavaScript, hãy lưu ý những điều sau:

Tên của một hàm tuỳ chỉnh phải khác với tên của các hàm tích hợp sẵn như SUM().
Tên của một hàm tuỳ chỉnh không được kết thúc bằng dấu gạch dưới (_) cho biết 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().
Cách viết hoa không quan trọng, mặc dù tên của hàm bảng tính được viết hoa theo truyền thống.

Đối số

Giống như hàm tích hợp sẵn, hàm tuỳ chỉnh có thể nhận các đối số làm giá trị nhập:

Nếu bạn gọi hàm có tham chiếu đến một ô duy nhất làm đối số (như =DOUBLE(A1)), thì đối số sẽ là giá trị của ô.
Nếu bạn gọi hàm có tham chiếu đến một dải ô dưới dạng đối số (chẳng hạn như =DOUBLE(A1:B10)), đối số sẽ là một mảng hai chiều giá trị của các ô. Ví dụ: trong ảnh chụp màn hình bên dưới, 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 ở trên cần được sửa đổi để chấp nhận một mảng làm dữ liệu đầu vào.
Các đối số hàm tuỳ chỉnh phải là xác định. Nghĩa là, các hàm trong bảng tính tích hợp trả về kết quả khác nhau mỗi khi tính toán – chẳng hạn như NOW() hoặc RAND() – không được phép làm đối số cho một hàm tuỳ 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 tích hợp biến động này, thì hàm đó 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ị để hiển thị, sao cho:

Nếu một hàm tuỳ chỉnh trả về một giá trị, thì giá trị đó sẽ hiển thị trong ô mà hàm được gọi.
Nếu một hàm tuỳ chỉnh trả về một mảng các giá trị hai chiều, thì các giá trị đó sẽ tràn vào các ô liền kề, miễn là các ô đó trống. Nếu điều này khiến mảng ghi đè nội dung ô hiện có, thì hàm tuỳ chỉnh sẽ gửi lỗi. Để biết ví dụ, hãy xem phần về cách tối ưu hoá các hàm tuỳ chỉnh.
Một hàm tuỳ chỉnh không được ảnh hưởng đến các ô khác với những hàm được 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 gọi và các ô bên cạnh. Để 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 tuỳ chỉnh phải trả về trong vòng 30 giây. Nếu không, ô này sẽ hiển thị lỗi: Internal error executing the custom function.

Loại dữ liệu

Google Trang tính lưu trữ dữ liệu ở nhiều định dạng 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 tuỳ chỉnh, Apps Script sẽ coi các giá trị đó là loại dữ liệu thích hợp trong JavaScript. Sau đây là những vấn đề thường gây nhầm lẫn nhất:

Ngày và giờ trong Trang tính trở thành đối tượng 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), thì hàm tuỳ chỉnh sẽ cần bù đắp.
Giá trị thời lượng trong Trang tính cũng trở thành đối tượng Date, nhưng hoạt động với các đối tượng đó 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. Ví dụ: một ô có giá trị 10% sẽ trở thành 0.1 trong Apps Script.

Tự động hoàn thà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 nhập tên hàm trong một ô, bạn sẽ thấy danh sách các hàm tích hợp sẵn và tuỳ chỉnh phù hợp với những gì bạn nhập.

Các hàm tuỳ chỉnh sẽ xuất hiện trong danh sách này nếu tập lệnh của chúng có thẻ 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 các dịch vụ Apps Script

Các hàm tuỳ chỉnh có thể gọi một số dịch vụ Apps Script nhất định để thực hiện các tác vụ phức tạp hơn. Ví dụ: một hàm tuỳ chỉnh có thể gọi dịch vụ Language (Ngôn ngữ) để dịch một cụm từ 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 hàm tuỳ chỉnh không bao giờ yêu cầu người dùng uỷ quyền truy cập vào dữ liệu cá nhân. Do đó, người dùng chỉ có thể gọi các 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 trong 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ữ
Khóa	Hoạt động nhưng không đặc biệt hữu ích trong 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. Người 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 tuỳ chỉnh.
Bảng tính	Chỉ đọc (chỉ có thể sử dụng hầu hết các phương thức `get()`, nhưng không được 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ụ cần có sự uỷ quyền của người dùng và do đó không thể sử dụng trong hàm tuỳ chỉnh.

Để sử dụng một dịch vụ khác với những 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 Apps Script thay vì viết một hàm tuỳ chỉnh. Một hàm được kích hoạt từ trình đơn sẽ yêu cầu người dùng uỷ quyền nếu cần và có thể sử dụng tất cả dịch vụ Apps Script.

Các hàm tuỳ chỉnh bắt đầu liên kết với bảng tính mà các hàm này được tạo. Điều này có nghĩa là bạn không thể sử dụng hàm tuỳ chỉnh được viết trong 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 Phần mở rộng > 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 gốc rồi dá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 File > Make a copy (Tệp > Tạo bản sao). Khi một bảng tính được sao chép, mọi tập lệnh đính kèm với bảng tính đó 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 gốc. Tuy nhiên, khi tạo bản sao, họ sẽ trở thành chủ sở hữu của 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 của Google Trang tính.

Tối ưu hoá

Mỗi lần một hàm tuỳ chỉnh được sử dụng trong một bảng tính, Google Trang tính sẽ thực hiện 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 ngàn!) lệnh gọi hàm tùy chỉnh, thì quá trình này có thể khá chậm.

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

Ví dụ: bạn có thể viết lại hàm DOUBLE() hiển thị ở trên để chấp nhận một ô hoặc một 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 để gọi định kỳ DOUBLE trên mọi giá trị trong mảng ô hai chiều. Phương thức này trả về một 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 phải tính toán số lượng lớn ô cùng lúc, như minh họa trong ảnh chụp màn hình bên dưới. (Bạn có thể thực hiện tương tự với các câu lệnh if lồng nhau thay vì lệnh gọi map.)

Tương tự, hàm tuỳ chỉnh bên dưới tìm nạp nội dung trực tiếp từ Internet một cách hiệu quả và sử dụng một mảng hai chiều để hiển thị hai cột kết quả chỉ bằng một lệnh gọi hàm. Nếu mỗi ô đòi hỏi lệnh gọi hàm riêng, thì thao tác này sẽ mất nhiều thời gian hơn đáng kể, 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;
}

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