Hàm tuỳ 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 những hàm này không đáp ứng đủ nhu cầu của bạn, bạn có thể dùng Apps Script để viết các hàm tuỳ chỉnh rồi sử dụng chúng trong Trang tính giống như một hàm tích hợp sẵn.

Để xem ví dụ về các hàm tuỳ chỉnh, hãy xem các hướng dẫn sau:

Bắt đầu

Hàm tuỳ chỉnh được tạo bằng JavaScript tiêu chuẩn. Nếu bạn mới làm quen với JavaScript, Codecademy có một khoá học dành cho người mới bắt đầu. Khoá học này không phải do Google phát triển và không liên kết với Google.

Sau đây là một hàm tuỳ chỉnh có tên là 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 học, hãy kiểm tra cửa hàng tiện ích bổ sung của Google Workspace để xem có người 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 Trang tính.
Chọn mục Tiện ích > Apps Script trong trình đơn.
Xoá mọi mã trong trình chỉnh sửa tập lệnh. Đối với hàm DOUBLE đã trình bày trước đó, hãy 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 biểu tượng Lưu .

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

Tải một 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 Google Workspace cho 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 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.
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.
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 hàm tuỳ chỉnh mà bạn quan tâm, hãy nhấp vào Cài đặt để cài đặt tiện ích đó.
Một hộp thoại có thể cho bạn biết rằng tiện ích bổ sung cần được uỷ quyền. Nếu có, hãy đọc kỹ thông báo rồi nhấp vào Cho phép.
Tiện ích bổ sung này sẽ xuất hiện 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 đó rồi 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 mà bạn muốn dùng rồi nhấp vào biểu tượng Tuỳ 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, hàm đó sẽ được dùng giống như một hàm tích hợp:

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

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

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

Đặt tên hàm

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

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

Đối số

Giống như một hàm tích hợp sẵn, 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 bằng một tham chiếu đến một ô duy nhất làm đối số (chẳng hạn như =DOUBLE(A1)), thì đối số đó là giá trị của ô.
Nếu bạn gọi hàm bằng một tham chiếu đến một dải ô làm đối số (chẳng hạn như =DOUBLE(A1:B10)), thì đối số đó là một mảng hai chiều gồm các giá trị của ô. Ví dụ: trong ảnh chụp màn hình sau, các đối số trong =DOUBLE(A1:B2) được Apps Script diễn giải là double([[1,3],[2,4]]). Xin lưu ý rằng bạn cần sửa đổi mã mẫu cho DOUBLE được mô tả trước đó để chấp nhận một mảng làm dữ liệu đầu vào.
Đối số của hàm tuỳ chỉnh phải có tính xác định. Tức là các hàm bảng tính tích hợp sẵn trả về một kết quả khác nhau mỗi khi chúng 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 những hàm tích hợp biến động này, thì hàm đó sẽ hiển thị Loading... vô thời hạn.
Để kích hoạt việc tính toán lại, bạn phải truyền trực tiếp một ô hoặc dải ô được tham chiếu làm đối số cho hàm tuỳ chỉnh. Nếu không, hàm tuỳ chỉnh sẽ không tính toán lại cho đến khi bạn chỉnh sửa hàm hoặc thay đổi giá trị của một ô được tham chiếu. Nếu bạn sử dụng phương thức getValue trong các hàm tuỳ chỉnh, hãy lưu ý rằng dải ô được tham chiếu không được truyền trực tiếp dưới dạng đối số cho hàm tuỳ chỉnh.

Giá trị trả về

Mỗi hàm tuỳ chỉnh phải trả về một giá trị để hiển thị, chẳng hạn như:

Nếu một hàm tuỳ chỉnh trả về một giá trị, thì giá trị đó sẽ xuất hiện trong ô mà hàm được gọi.
Nếu một hàm tuỳ chỉnh trả về một mảng 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 việc này khiến mảng ghi đè nội dung hiện có của ô, thì hàm tuỳ chỉnh sẽ báo lỗi. Để biết ví dụ, hãy xem phần về tối ưu hoá các hàm tuỳ chỉnh.
Hàm tuỳ chỉnh không thể ảnh hưởng đến các ô khác ngoài những ô mà hàm đó trả về giá trị. Nói cách khác, hàm tuỳ chỉnh không thể chỉnh sửa các ô tuỳ ý, mà chỉ có thể chỉnh sửa các ô mà hàm được gọi và các ô liền kề. Để chỉnh sửa các ô tuỳ ý, hãy 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, ô sẽ hiển thị #ERROR! và ghi chú của ô là Exceeded maximum execution time (line 0).

Loại dữ liệu

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 dùng trong hàm tuỳ chỉnh, Apps Script sẽ coi các giá trị này là kiểu dữ liệu thích hợp trong JavaScript. Sau đây là những vấn đề dễ gây hiểu lầm thường gặp:

Thời gian và ngày trong Trang tính sẽ trở thành các đối tượng Date 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 cần phải bù đắp.
Các giá trị thời lượng trong Trang tính cũng trở thành đối tượng Date, nhưng việc xử lý các giá trị này có thể phức tạp.
Giá trị phần trăm trong Trang tính sẽ 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

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ư đối với hàm tích hợp. Khi nhập tên hàm vào một ô, bạn sẽ thấy danh sách các hàm tích hợp và hàm tuỳ chỉnh khớp với nội dung bạn nhập.

Hàm tuỳ chỉnh sẽ xuất hiện trong danh sách này nếu tập lệnh của hàm có thẻ JSDoc @customfunction, như trong ví dụ DOUBLE().

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

Nâng cao

Phần này đề cập đến các chủ đề nâng cao về hàm tuỳ chỉnh.

Sử dụng các dịch vụ của Google Apps Script

Hàm tuỳ chỉnh có thể gọi một số dịch vụ Apps Script để 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 để 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, hàm tuỳ 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 đó, các dịch vụ này chỉ có thể gọi những dịch vụ không có quyền truy cập vào dữ liệu cá nhân, cụ thể là những dịch vụ sau:

Các 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ị (hiếm khi hữu ích)
JDBC
Ngôn ngữ
Khoá	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 toán đường đi nhưng không hiển thị bản đồ
Thuộc tính	`getUserProperties()` chỉ nhận được 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 (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	Truy cập vào tài nguyên trên web bằng cách 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., thì dịch vụ này yêu cầu người dùng uỷ quyền và do đó không thể dùng trong hàm tuỳ chỉnh.

Để sử dụng một dịch vụ khác ngoài những dịch vụ trong danh sách trước đó, hãy tạo một trình đơn tuỳ chỉnh chạy một 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à do đó có thể sử dụng tất cả các dịch vụ của Apps Script.

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

Nhấp vào Tiện ích bổ sung > 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à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 có chứa hàm tuỳ chỉnh bằng cách nhấp vào 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 bảng tính đó cũng sẽ được sao chép. Bất cứ ai có quyền truy cập vào bảng tính đều có thể sao chép tập lệnh. (Cộng tác viên chỉ có quyền xem thì 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 Trình chỉnh sửa Trang tính.

Tất cả tập lệnh liên kết với vùng chứa đều dùng chung danh sách truy cập với vùng chứa của chúng. Điều này có nghĩa là bất kỳ ai có quyền chỉnh sửa bảng tính cũng có thể chỉnh sửa mọi mã Apps Script được đính kèm vào bảng tính đó. Để biết thêm thông tin, hãy xem phần quyền truy cập vào tập lệnh liên kết.

Tối ưu hoá

Mỗi khi một hàm tuỳ chỉnh được dùng trong bảng tính, Sheets sẽ thực hiện 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, thì quy trình này có thể diễn ra chậm. Một số dự án có nhiều hoặc các hàm tuỳ chỉnh phức tạp có thể gặp phải tình trạng chậm trễ tạm thời trong quá trình thực thi.

Do đó, nếu bạn dự định 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ưới dạng một 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() đã trình bày trước đó để chấp nhận một ô hoặc dải ô duy nhất 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 này sử dụng phương thức map của đối tượng Array trong JavaScript trên mảng hai chiều của các ô để lấy từng hàng, sau đó đối với mỗi hàng, phương thức này sẽ sử dụng lại map để trả về giá trị gấp đôi của mỗi ô. Hàm này trả về một mảng hai chiều chứa các kết quả. Bằng cách này, bạn chỉ cần gọi DOUBLE một lần nhưng có thể tính toán 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 sau. Bạn có thể thực hiện điều tương tự bằng 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 sau đây sẽ 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ột mảng hai chiều để hiển thị 2 cột kết quả chỉ bằng một lệnh gọi hàm duy nhất. Nếu mỗi ô đều yêu cầu một 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ỗi lần 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.

/**
 * 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 các kỹ thuật này cho hầu hết mọi hàm tuỳ chỉnh được dùng nhiều lần trong bảng tính, mặc dù thông tin chi tiết về cách triển khai sẽ khác nhau tuỳ thuộc vào hành vi của hàm.