Google Sheets (Google Trang tính) là một ứng dụng hoạt động tương tự như Microsoft Excel. Tuy nhiên, nó vẫn có các tính năng khác biệt. Người dùng thậm chí có thể “tăng sức mạnh” của Google Sheets nếu biết dùng các tập lệnh nâng cao tính năng của nó.
Google Sheets là một phần của Google Suite - một trong số phần mềm văn phòng miễn phí tốt nhất hiện nay. Nó cho phép người dùng theo dõi, phân tích hoặc ghi lại mọi thứ mong muốn. Điều khiến ứng dụng này trở nên hấp dẫn hơn Microsoft Excel không phải vì nó miễn phí mà nhờ hỗ trợ Google Script - tập lệnh Google để người dùng cải tiến tính năng Google Sheets.
Lập trình trong Google Sheets nâng cao nghe có chút phức tạp. Ban đầu, bạn có thể nghĩ muốn dùng tập lệnh chắc hẳn cần kiến thức lập trình nâng cao. Tuy nhiên, trong trường hợp này, điều đó không cần thiết.
Bài viết sẽ cung cấp cho bạn một số tập lệnh Google Sheets hữu ích kèm theo ví dụ minh họa cụ thể để bạn dễ hình dung và tăng hiệu quả làm việc.
Tập lệnh làm Google Sheets trở nên mạnh mẽ hơn
1. Tạo các chức năng tùy chỉnh của riêng bạn
Tạo chức năng tùy chỉnh là một trong những cách tạo một Google Scripts dễ dàng nhất có thể nâng cao trải nghiệm Google Sheets của bạn. Google Sheets đã cung cấp một danh sách dài các chức năng có sẵn. Bạn có thể xem những cái phổ biến nhất bằng cách click vào biểu tượng chức năng trên trình đơn.
Sau đó nhấp chuột vào More functions... (Chức năng khác...) sẽ mở ra một danh sách các thuật toán, tài chính, kỹ thuật và nhiều chức năng khác. Tuy nhiên, Google Scripts cho phép bạn linh hoạt để tạo ra các công thức cá nhân của riêng bạn.
Để tạo chức năng tùy chỉnh đầu tiên của mình, đầu tiên bạn sẽ cần mở trình chỉnh sửa tập lệnh bằng cách nhấp vào Tools (Công cụ) > Script Editor (Trình chỉnh sửa tập lệnh).
Bạn sẽ thấy một cửa sổ hiện ra giống hoặc tương tự như dưới đây.
Điều bạn cần làm là thay thế những gì có trong cửa sổ này với chức năng tuỳ chỉnh của riêng bạn. Tên chức năng giống với chức năng mà bạn sẽ bắt đầu nhập vào một ô trong Google Trang tính sau biểu tượng "=" để gọi công thức của bạn. Một hàm để chuyển đổi Celsius sang Fahrenheit sẽ giống như sau:
function CSTOFH (input) {
return input * 1.8 + 32;
}Dán các chức năng trên vào cửa sổ mã và sau đó chọn File (Tệp) > Save (Lưu), đặt tên dự án là "CelsiusConverter" và nhấn OK.
Đó là tất cả những gì bạn phải làm. Bạn bắt đầu sử dụng chức năng mới của mình bằng cách gõ dấu "=", theo sau là chức năng của bạn, với con số nhập vào để chuyển đổi:
Nhấn Enter và bạn sẽ có kết quả.
2. Tự động tạo biểu đồ
Bạn có thể có một bảng tính mới mỗi tháng với dữ liệu mới và bạn muốn tự động tạo một biểu đồ mới mà không cần phải tạo lại nó mỗi tháng. Bạn có thể thực hiện việc này bằng cách tạo một chức năng sẽ tạo ra một biểu đồ mới cho bạn dựa trên dữ liệu trong bảng tính hiện tại bạn đã mở.
Giả sử bạn là giáo viên và vào cuối năm, bạn có bảng tính cho mỗi sinh viên với danh sách điểm thi hàng tháng:
Những gì bạn muốn làm là chạy một chức năng duy nhất trên bảng này sẽ tự động tạo ra một biểu đồ trong vài giây. Tập lệnh sẽ như thế này:
function GradeChart() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheets()[0];
var gradechart = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(sheet.getRange('A1:B11'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(gradechart);
}Tất cả những gì bạn phải làm là mở từng bảng tính của học sinh và nhấp vào biểu tượng chạy trong trình đơn trong Google Scripts để tự động tạo biểu đồ.
Bất cứ khi nào bạn nhấp vào biểu tượng chạy, nó sẽ chạy tập lệnh mà bạn đã tạo trên bảng tính "hoạt động" (một bảng tính bạn đã mở trong tab trình duyệt hiện tại của mình).
Đối với các báo cáo mà bạn phải tạo thường xuyên, như hàng tuần hoặc hàng tháng, loại chức năng biểu đồ tạo tự động này thực sự có thể giúp bạn tiết kiệm rất nhiều thời gian, vì bạn không phải tạo lại từ đầu mỗi khi bạn muốn nhập dữ liệu biểu đồ trên một trang tính mới.
3. Tạo Menu tùy chỉnh
Nếu bạn không muốn có một tập lệnh để tự động tạo biểu đồ mà muốn có chức năng đó nằm ngay trong tầm tay của mình trên hệ thống menu trong Google Trang tính thì bạn hoàn toàn có thể biến điều đó thành sự thực.
Để tạo ra một menu tùy chỉnh, bạn cần cho bảng tính thêm mục menu mới mỗi khi nó mở. Bạn có thể thực hiện việc này bằng cách tạo một hàm onOpen() trong cửa sổ Script Editor (Trình chỉnh sửa tập lệnh) ở phía trên chức năng Gradechart mà bạn vừa tạo.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{ name: 'Create Grade Chart...', functionName: 'GradeChart' }
];
spreadsheet.addMenu('Charts', menuItems);
}Lưu đoạn mã và sau đó tải lại bảng tính của bạn. Bạn sẽ khám phá ra rằng mục menu mới sẽ xuất hiện với tên mà bạn xác định nó trong tập lệnh của mình. Nhấp chuột vào menu và bạn sẽ thấy mục menu cho chức năng đó.
Nhấp vào mục trình đơn và nó sẽ chạy các chức năng giống như nó đã làm khi bạn nhấn vào biểu tượng chạy từ bên trong trình soạn thảo Google Scripts.
4. Dùng getValues để tải hàng loạt dữ liệu
Bạn có thể thực hiện tính toán dữ liệu trong bảng tính bằng tập lệnh Google Sheet nâng cao, đưa nó lên một tầm cao mới khi sử dụng các mảng (array). Nếu tải một biến trong tập lệnh Google Apps bằng getValues, biến đó sẽ là một mảng mà bạn có thể tải nhiều giá trị từ bảng tính.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();Biến dữ liệu là một mảng đa chiều, nắm giữ toàn bộ dữ liệu trong sheet. Để tiến hành tính toán dữ liệu, bạn cần dùng vòng lặp for. Bộ đếm của vòng lặp for sẽ triển khai trên từng hàng và cột thì không đổi, dựa trên cột tại vị trí bạn muốn tải dữ liệu.
Ở ví dụ dưới đây, bạn có thể triển khai tính toán trên 3 hàng dữ liệu như sau:
for (var i = 1; i < data.length; i++) {
var result = data[i][0] * 100;
sheet.getRange(i+1, 2).setValue(result);
}
}Lưu và chạy tập lệnh như bạn đã làm trước đó. Bạn sẽ thấy tất cả kết quả được điền vào cột thứ hai trong bảng tính.
Bạn sẽ thấy tham chiếu một ô và hàng trong phần biến dãy dữ liệu khác với hàm getRange.
- data[i][0] chỉ kích thước mảng, trong đó thứ nguyên đầu tiên là hàng, thứ hai là cột. Cả hai đều bắt đầu từ 0.
- getRange(i+1, 2) chỉ hàng thứ 2 khi i=1 (vì hàng 1 là hàng tiêu đề), 2 là cột thứ 2, nơi lưu kết quả.
5. Gửi báo cáo tự động
Tập lệnh cuối cùng là một tập lệnh sẽ gửi email từ bên trong Google Trang tính. Cách này có thể có ích là nếu bạn đang quản lý một nhóm nhiều người và bạn phải gửi nhiều email có cùng một chủ đề. Có thể bạn đã thực hiện đánh giá hiệu suất với từng thành viên trong nhóm và đã ghi lại nhận xét đánh giá của bạn cho mỗi người trong Bảng tính của Google. Sẽ rất thuận tiện và tiết kiệm thời gian khi chỉ cần chạy một tập lệnh duy nhất và những đánh giá sẽ được tự động gửi cho 50 hoặc 60 người cùng một lúc mà không cần phải tự tạo từng email và gửi cho từng cá nhân. Đó là sức mạnh của Google Scripting.
Tương tự như cách bạn tạo các tập lệnh ở trên trong bài viết này, bạn sẽ tạo ra một tập lệnh cho việc gửi báo cáo tự động bằng cách vào trình chỉnh sửa tập lệnh và tạo một hàm gọi là sendEmails (), như sau:
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 7; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 3)
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[2]; // Third column
var subject = "My review notes";
MailApp.sendEmail(emailAddress, subject, message);
}
}Vì vậy, giả sử bạn có bảng tính được tổ chức như được hiển thị ở đây.
Tập lệnh ở trên sẽ làm tất cả mọi việc thông qua mỗi hàng trong bảng tính và gửi một email đến địa chỉ trong cột thứ hai với thông điệp mà bạn đã nhập vào cột thứ ba.
Chức năng sendEmail trong Google Scripts là một trong những chức năng mạnh mẽ nhất trong Google Scripts bởi vì nó mở ra một thế giới tự động hóa email mà bạn chưa từng xem xét trước đây.
Nếu trong thực tế là bạn có người khác chịu trách nhiệm nhập dữ liệu vào bảng tính Google cho mình và nếu tự động phân phối email dựa trên dữ liệu đã nhập thì bạn có thể làm điều gì đó giống như gửi báo cáo hàng tháng đến sếp, mà không bao giờ thực sự cần phải mở email khách hàng của mình. Các tập lệnh có thể tự động làm tất cả các công việc cho bạn.
Tất cả những tính năng này của Google Scripts sẽ chỉ cho bạn thấy rằng chỉ với một vài dòng mã đơn giản, Google Scripts có quyền tự động hoá một phần hoặc toàn bộ Google Trang tính. Các tự động hóa này có thể được thiết lập để chạy theo lịch hoặc chạy bất cứ khi nào bạn muốn kích hoạt chúng. Dù bằng cách nào thì chúng cũng cung cấp cho bạn một cách để tự động hoá rất nhiều công việc mà không cần phải bỏ nhiều sức lực và thời gian.
