Google Trang tính là một 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 hầu hết mọi việc bạn có thể làm trong Microsoft Excel. Nhưng sức mạnh thực sự của Google Trang tính là tính năng Google Scripting đi kèm với nó.
Tập lệnh Google Apps là công cụ tạo tập lệnh nền không chỉ hoạt động trong Google Trang tính mà còn cả Google Docs, Gmail, Google Analytics và gần 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 từng ứng dụng đó với nhau.
Trong bài viết này, bạn sẽ tìm hiểu cách bắt đầu với tập lệnh Google Apps, tạo tập lệnh cơ bản trong Google Trang tính để đọc và ghi dữ liệu ô cũng như các chức năng tập lệnh Google Trang tính nâng cao hiệu quả nhất.
Nội dung bài viết
Cách tạo tập lệnh Google Apps
Bạn có thể bắt đầu ngay bây giờ khi tạo tập lệnh Google Apps đầu tiên của mình từ bên trong Google Trang tính.
Để thực hiện việc này, hãy chọn Công cụ từ menu, sau đó Biên tập kịch bản.
Thao tác này sẽ mở cửa sổ soạn thảo tập lệnh và mặc định có một chức năng có tên là chức năng của tôi(). Đây là nơi bạn có thể tạo và kiểm tra Google Script của mình.
Để thử, hãy thử tạo một hàm tập lệnh Google Trang tính để đọc dữ liệu từ một ô, thực hiện phép tính trên ô đó và xuất lượng dữ liệu sang một ô khác.
Chức năng lấy dữ liệu từ một ô là getRange() Và getValue() chức năng. Bạn có thể xác định ô theo hàng và cột. Vì vậy, nếu bạn có một giá trị ở hàng 2 và cột 1 (cột A), phần đầu tiên của tập lệnh của bạn sẽ trông như thế này:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var data = sheet.getRange(row, col).getValue();
}
Điều này lưu trữ giá trị từ ô đó trong dữ liệu Biến đổi. Bạn có thể thực hiện phép tính trên dữ liệu, sau đó ghi dữ liệu đó vào một ô khác. Vì vậy, phần cuối cùng của chức năng này 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 để lưu.
Lần đầu tiên bạn chạy chức năng tập lệnh Google Trang tính mới như thế này (bằng cách chọn biểu tượng chạy), bạn sẽ cần cung cấp Ủy quyền để tập lệnh chạy trên Tài khoản Google của mình.
Cho phép quyền tiếp tục. Khi tập lệnh của bạn chạy, bạn sẽ thấy tập lệnh đã ghi kết quả tính toán vào ô đích.
Bây giờ bạn đã biết cách viết một hàm tập lệnh Google Apps cơ bản, hãy xem một số hàm nâng cao hơn.
Sử dụng getValues để tải mảng
Bạn có thể nâng khái niệm thực hiện các phép tính trên dữ liệu trong bảng tính của mình bằng tập lệnh lên một tầm cao mới bằng cách sử dụng mảng. Nếu bạn tải một biến trong tập lệnh Google Apps bằng getValues, biến đó sẽ là một 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 dữ liệu là một mảng đa chiều chứa tất cả dữ liệu từ trang tính. Để thực hiện tính toán trên dữ liệu, bạn sử dụng một vì vòng. Bộ đếm của vòng lặp for sẽ hoạt động qua từng hàng và cột không đổi, dựa trên cột mà bạn muốn lấy dữ liệu.
Trong bảng tính mẫu của chúng tôi, bạn có thể thực hiện các phép tính trên ba 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 này giống như bạn đã làm ở 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 của bạn.
Bạn sẽ nhận thấy rằng việc tham chiếu một ô và hàng trong một biến mảng khác với hàm getRange.
dữ liệu[i][0] đề cập đến các kích thước mảng trong đó kích thước đầu tiên là hàng và thứ hai là cột. Cả hai đều bắt đầu từ con số 0.
getRange(i+1, 2) đề cập đến hàng thứ hai khi i=1 (vì hàng 1 là tiêu đề) và 2 là cột thứ hai nơi lưu trữ kết quả.
Sử dụng phần bổ sungRow để viết kết quả
Điều gì sẽ xảy ra nếu bạn có một bảng tính mà bạn muốn ghi dữ liệu vào một hàng mới thay vì một cột mới?
Điều này dễ dàng thực hiện được với nối thêm hàng chức năng. Chức năng này sẽ không làm phiền bất kỳ dữ liệu hiện có nào trong trang tính. Nó sẽ chỉ thêm một hàng mới vào trang tính hiện có.
Ví dụ: tạo một hàm đếm từ 1 đến 10 và hiển thị bộ đếm có bội số của 2 trong một Quầy tính tiền cột.
Chức năng này sẽ trông như thế này:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
Đây là kết quả khi bạn chạy hàm này.
Xử lý nguồn cấp RSS bằng URLFetchApp
Bạn có thể kết hợp chức năng tập lệnh Google Trang tính trước đó và Ứng dụng tìm nạp URL để lấy nguồn cấp dữ liệu RSS từ bất kỳ trang web nào và viết một hàng vào bảng tính cho mỗi bài viết được xuất bản gần đây trên trang web đó.
Về cơ bản, đây là một phương pháp DIY để tạo bảng tính trình đọc nguồn cấp dữ liệu RSS của riêng bạn!
Kịch bản để làm điều này cũng 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");
// Parsing single items in the 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]);
}
}
Bạn có thể thấy, Xml.parse kéo từng mục ra khỏi nguồn cấp dữ liệu RSS và tách từng dòng thành tiêu đề, liên kết, ngày tháng và mô tả.
Sử dụng nối thêm hàng chức năng, bạn có thể đặt các mục này vào thích hợp columns cho từng mục trong nguồn cấp dữ liệu RSS.
Đầu ra trong trang tính của bạn sẽ trông giống như thế này:
Thay vì nhúng URL nguồn cấp dữ liệu RSS vào tập lệnh, bạn có thể có một trường trong trang tính có URL, sau đó có nhiều trang tính – một trang cho mỗi trang web bạn muốn theo dõi.
Nối các chuỗi và thêm một kết quả vận chuyển
Bạn có thể đưa bảng tính RSS tiến thêm một bước nữa bằng cách thêm một số chức năng thao tác văn bản, sau đó sử dụng các chức năng email để gửi cho chính bạn một email có bản tóm tắt tất cả các bài đăng mới trong nguồn cấp dữ liệu RSS của trang web.
Để thực hiện việc này, trong tập lệnh bạn đã tạo ở phần trước, bạn sẽ muốn thêm một số tập lệnh sẽ trích xuất tất cả thông tin trong bảng tính.
Bạn sẽ muốn xây dựng dòng chủ đề và nội dung văn bản email bằng cách phân tích cú pháp tất cả thông tin từ cùng một mảng “mục” mà bạn đã sử dụng để ghi dữ liệu RSS vào bảng tính.
Để thực hiện việc này, hãy khởi tạo chủ đề và thông báo bằng cách đặt các dòng sau trước vòng lặp For “item”.
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Sau đó, ở cuối vòng lặp for “items” (ngay sau hàmappendRow), hãy thêm dòng sau.
message = message + title + 'n' + link + 'n' + date + 'n' + desc + 'n' + 'n n';
Biểu tượng “+” sẽ nối tất cả bốn mục lại với nhau, theo sau là “n” để xuống dòng sau mỗi dòng. Ở cuối mỗi khối dữ liệu tiêu đề, bạn sẽ cần hai dấu xuống dòng để có nội dung email được định dạng đẹp mắt.
Sau khi tất cả các hàng được xử lý, biến “nội dung” sẽ giữ toàn bộ chuỗi thư email. Bây giờ bạn đã sẵn sàng để gửi email!
Cách gửi email trong tập lệnh Google Apps
Phần tiếp theo của Google Script của bạn sẽ là gửi “chủ đề” và “nội dung” qua email. Thực hiện việc này trong Google Script rất dễ dàng.
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
MailApp là một lớp rất tiện lợi bên trong các tập lệnh Google Apps cho phép bạn truy cập vào dịch vụ email trong Tài khoản Google của mình để gửi hoặc nhận email. Nhờ đó, một dòng duy nhất có chức năng sendEmail cho phép bạn gửi bất kỳ email nào chỉ với địa chỉ email, dòng chủ đề và nội dung.
Email kết quả sẽ trông như thế này.
Kết hợp khả năng trích xuất nguồn cấp dữ liệu RSS của trang web, lưu trữ nó trong Google Trang tính và gửi cho chính bạn kèm theo các liên kết URL, giúp việc theo dõi nội dung mới nhất của bất kỳ trang web nào trở nên rất thuận tiện.
Đây chỉ là một ví dụ về sức mạnh sẵn có trong tập lệnh Google Apps để tự động hóa các hành động và tích hợp nhiều dịch vụ đám mây.
