Google Sheets là công cụ bảng tính dựa trên đám mây mạnh mẽ, cho phép bạn thực hiện gần như mọi thứ có thể làm trong Microsoft Excel. Tuy nhiên, sức mạnh thực sự của Google Sheets nằm ở tính năng Google Apps Script đi kèm.
Google Apps Script là công cụ lập trình nền làm việc không chỉ trong Google Sheets mà còn trong Google Docs, Gmail, Google Analytics và hầu như mọi dịch vụ đám mây khác của Google. Nó cho phép bạn tự động hóa các ứng dụng riêng lẻ và tích hợp chúng với nhau.
Trong bài viết này, bạn sẽ học cách bắt đầu với Google Apps Script, tạo script cơ bản trong Google Sheets để đọc và ghi dữ liệu ô, cũng như các hàm script nâng cao hiệu quả nhất.
Cách Tạo Google Apps Script
Bạn có thể bắt đầu tạo script Google Apps đầu tiên ngay từ bên trong Google Sheets.
Thực hiện: chọn Tools (Công cụ) từ menu, sau đó chọn Script Editor (Trình soạn thảo script).
Cửa sổ trình soạn thảo script sẽ mở ra với hàm mặc định tên myFunction(). Đây là nơi bạn tạo và kiểm tra Google Script của mình.
Để thử nghiệm, hãy tạo một hàm script Google Sheets đọc dữ liệu từ một ô, thực hiện phép tính và xuất kết quả ra ô khác.
Hàm lấy dữ liệu từ ô là getRange() và getValue(). Bạn xác định ô bằng hàng và cột. Ví dụ: giá trị ở hàng 2, cột 1 (cột A), đoạn script đầu tiên sẽ như sau:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var data = sheet.getRange(row, col).getValue();
}
Đoạn mã này lưu giá trị từ ô đó vào biến data. Bạn có thể thực hiện phép tính trên dữ liệu, sau đó ghi kết quả vào ô khác. Phần cuối của hàm sẽ là:
var results = data * 100; sheet.getRange(row, col+1).setValue(results); }
Khi viết xong hàm, chọn biểu tượng đĩa cứng để lưu.
Lần đầu chạy script mới (chọn biểu tượng chạy), bạn cần cấp quyền Authorization cho script chạy trên tài khoản Google.
Cho phép quyền để tiếp tục. Sau khi script chạy xong, bạn sẽ thấy kết quả phép tính được ghi vào ô đích.
Bây giờ bạn đã biết cách viết hàm Google Apps Script cơ bản, hãy xem các hàm nâng cao hơn.
Sử Dụng getValues Để Tải Mảng
Bạn có thể đưa khái niệm tính toán trên dữ liệu bảng tính với script lên mức mới bằng cách sử dụng mảng. Nếu tải biến trong Google Apps Script bằng getValues, biến đó sẽ là mảng có thể tải nhiều giá trị từ trang tính.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
}
Biến data là mảng đa chiều chứa toàn bộ dữ liệu từ trang tính. Để thực hiện phép tính, bạn dùng vòng lặp for. Bộ đếm của vòng lặp sẽ duyệt qua từng hàng, cột giữ nguyên dựa trên cột cần lấy dữ liệu.
Trong bảng tính ví dụ, bạn có thể 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 script như trên. Bạn sẽ thấy tất cả kết quả được điền vào cột 2 trong bảng tính.
Bạn sẽ nhận thấy tham chiếu ô và hàng trong biến mảng khác với hàm getRange.
data[i][0] tham chiếu đến chiều mảng nơi chiều đầu tiên là hàng, chiều thứ hai là cột. Cả hai đều bắt đầu từ 0.
getRange(i+1, 2) tham chiếu đến hàng thứ 2 khi i=1 (vì hàng 1 là tiêu đề), và 2 là cột thứ 2 nơi lưu kết quả.
Sử Dụng appendRow Để Ghi Kết Quả
Điều gì xảy ra nếu bạn có bảng tính muốn ghi dữ liệu vào hàng mới thay vì cột mới?
Việc này dễ dàng với hàm appendRow. Hàm này không ảnh hưởng dữ liệu hiện có, chỉ thêm hàng mới vào trang tính hiện tại.
Ví dụ: tạo hàm đếm từ 1 đến 10 và hiển thị bộ đếm với bội số 2 trong cột Counter.
Hàm sẽ như sau:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
Kết quả khi chạy hàm này:
Xử Lý RSS Feed Với URLFetchApp
Bạn có thể kết hợp hàm script Google Sheets trước đó và URLFetchApp để lấy RSS feed từ bất kỳ trang web nào, và ghi một hàng vào bảng tính cho mỗi bài viết mới xuất bản.
Đây cơ bản là phương pháp DIY để tạo trình đọc RSS feed riêng trong bảng tính!
Script thực hiện việc này không quá phức tạp.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var item, date, title, link, desc;
var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
var doc = Xml.parse(txt, false);
title = doc.getElement().getElement("channel").getElement("title").getText();
var items = doc.getElement().getElement("channel").getElements("item");
// Phân tích từng mục trong RSS Feed
for (var i in items) {
item = items[i];
title = item.getElement("title").getText();
link = item.getElement("link").getText();
date = item.getElement("pubDate").getText();
desc = item.getElement("description").getText();
sheet.appendRow([title,link,date,desc]);
}
}
Như bạn thấy, Xml.parse tách từng mục khỏi RSS feed và chia mỗi dòng thành tiêu đề, liên kết, ngày và mô tả.
Sử dụng hàm appendRow, bạn có thể đưa các mục này vào cột phù hợp cho từng mục trong RSS feed.
Đầu ra trong trang tính sẽ trông như sau:
Thay vì nhúng URL RSS feed vào script, bạn có thể đặt một trường trong trang tính chứa URL, sau đó có nhiều trang tính – mỗi trang cho một trang web muốn theo dõi.
Nối Chuỗi và Thêm Ngắt Dòng
Bạn có thể đưa bảng tính RSS tiến xa hơn bằng cách thêm hàm thao tác văn bản, sau đó dùng hàm email để gửi cho mình email tóm tắt tất cả bài viết mới trong RSS feed của trang web.
Để làm này, dưới script bạn tạo ở phần trước, thêm script trích xuất tất cả thông tin trong bảng tính.
Bạn sẽ xây dựng dòng chủ đề và nội dung email bằng cách phân tích tất cả thông tin từ cùng mảng "items" dùng để ghi dữ liệu RSS vào bảng tính.
Để làm này, khởi tạo subject và message bằng cách đặt các dòng sau trước vòng lặp For "items".
var subject = 'Latest 10 articles published at mysite.com' var message = ''
Sau đó, ở cuối vòng lặp "items" (ngay sau hàm appendRow), thêm dòng sau.
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
Ký hiệu "+" sẽ nối tất cả 4 mục lại với nhau theo sau là "\n" để ngắt dòng sau mỗi dòng. Ở cuối mỗi khối dữ liệu tiêu đề, bạn cần hai ngắt dòng để định dạng thân email đẹp mắt.
Khi tất cả hàng được xử lý, biến "message" giữ toàn bộ chuỗi thông điệp email. Bây giờ bạn sẵn sàng gửi email!
Cách Gửi Email Trong Google Apps Script
Phần tiếp theo của Google Script sẽ gửi "subject" và "message" qua email. Việc này trong Google Script rất dễ dàng.
var emailAddress = myemail@gmail.com; MailApp.sendEmail(emailAddress, subject, message);
MailApp là lớp rất tiện lợi trong Google Apps Script cho phép truy cập dịch vụ email của tài khoản Google để gửi hoặc nhận email. Nhờ đó, một dòng với hàm sendEmail cho phép gửi bất kỳ email nào chỉ với địa chỉ email, chủ đề và nội dung.
Email kết quả sẽ trông như sau.
Kết hợp khả năng trích xuất RSS feed trang web, lưu vào Google Sheets và gửi đến mình kèm liên kết URL, tạo sự tiện lợi lớn để theo dõi nội dung mới nhất của bất kỳ trang web nào.
Đây chỉ là một ví dụ về sức mạnh của Google Apps Script trong tự động hóa hành động và tích hợp nhiều dịch vụ đám mây.