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 những hàm này không đủ cho 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, chẳng hạn như chuyển đổi mét thành dặm hoặc tìm nạp nội dung trực tiếp từ Internet, sau đó sử dụng các hàm đó trong Google Trang tính giống như một hàm tích hợp sẵn.

Bắt đầu

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 do Google phát triển và không 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, 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 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:

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, bạ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 biểu tượng 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 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á 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 "custom function" (hàm tuỳ chỉnh) rồi nhấn Enter.
5. Nếu bạn thấy một tiện ích bổ sung hàm tuỳ chỉnh mà mình quan tâm, hãy nhấp vào Install (Cài đặt) để cài đặt tiện ích bổ sung đó.
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 cho phép. Nếu có, hãy đọ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 một bảng tính khác, hãy mở bảng tính đó và ở trên cùng, hãy nhấp vào tiện ích bổ sung > Quản lý tiện ích bổ sung. Tìm tiện ích bổ sung bạn muốn sử 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, 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:

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

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

Trước khi tự 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 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 gạch dưới này biểu thị một hàm riêng 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(), không phải bằng 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ư 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ị đầu vào:

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

• Nếu bạn gọi hàm có một tham chiếu đến một dải ô làm đối số (như =DOUBLE(A1:B10)), thì đối số đó sẽ là một mảng hai chiều của các giá trị của ô. 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]]). Xin lưu ý rằng bạn cần sửa đổi mã mẫu cho DOUBLE ở trên để chấp nhận một mảng làm dữ liệu đầu vào.

  
  

• Các đối số của hàm tuỳ chỉnh phải mang tính xác định. Tức là các hàm trong bảng tính tích hợp sẵn trả về một kết quả khác nhau mỗi lần tính toán (chẳng hạn như NOW() hoặc RAND()) không được phép dùng 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 sẵn dễ thay đổi 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 đều phải trả về một giá trị để hiển thị, sao cho:

• Nếu hàm tuỳ chỉnh trả về một giá trị thì giá trị đó sẽ hiển thị trong ô chứa hàm được gọi.
• Nếu một hàm tuỳ chỉnh trả về một mảng giá trị 2 chiều, thì các giá trị sẽ tràn sang 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ó, thì hàm tuỳ chỉnh sẽ hiển thị lỗi. Ví dụ: hãy xem phần tối ưu hoá các hàm tuỳ chỉnh.
• Một hàm tuỳ chỉnh không thể ảnh hưởng đến các ô không phải ô mà hàm 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ỳ ý, 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 sử dụng trình đơn tuỳ chỉnh để chạy 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ị lỗi: Internal error executing the custom function.

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

• Thời gian và ngày trong Trang tính sẽ 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), hàm tuỳ chỉnh sẽ cần bù trừ.
• 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 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 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 tương tự như các hàm tích hợp sẵn. Khi nhập tên hàm vào ô, bạn sẽ thấy danh sách các hàm tích hợp sẵn 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 chúng bao gồm 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ụ của Google Apps Script

Các hàm tuỳ chỉnh có thể gọi một số dịch vụ Google 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 (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 Scripts khác, hàm tuỳ chỉnh không bao giờ yêu cầu người dùng cấp quyền truy cập vào dữ liệu cá nhân. Do đó, họ 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ể như sau:

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 (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 chỉ đường nhưng không thể hiển thị bản đồ
Tài sản	getUserProperties() chỉ lấy 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 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*(), ngoại trừ 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., thì dịch vụ cần có 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 dịch vụ khác với các dịch vụ nêu trên, hãy tạo trình đơn tuỳ chỉnh chạy hàm Apps Script thay vì viế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 uỷ quyền 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 liên kết với bảng tính mà chúng được tạo. Điều này có nghĩa là hàm tuỳ chỉnh viết trong một bảng tính không thể dùng trong các bảng tính khác trừ phi 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 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. (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 trình chỉnh sửa của Google Trang tính.

Tất cả tập lệnh liên kết với vùng chứa đều có cùng danh sách truy cập như 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 đều có thể chỉnh sửa bất kỳ mã Apps Script nào được đính kèm với bảng tính đó.

Tối ưu hoá

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

Do đó, nếu bạn đị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 dữ liệu đầ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 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 đệ quy 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 yêu cầu tính toán cho nhiều ô cùng lúc, như 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 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 dưới đây tìm nạp hiệu quả nội dung trực tiếp từ Internet và sử dụng 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 ô yêu cầu lệnh gọi hàm riêng, thì thao tác sẽ mất nhiều thời gian hơn 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ể được áp dụng cho hầu hết mọi hàm tuỳ chỉnh được sử dụng lặp lại trong toàn bộ bảng tính, mặc dù các chi tiết triển khai sẽ thay đổi tuỳ thuộc vào hành vi của hàm.