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 làm gần như mọi thứ 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 Tài liệu, 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.
Cách tạo Tập lệnh Google Apps
Bạn có thể bắt đầu ngay bây giờ 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ừ trình đơn, sau đó đến Trình chỉnh sửa tập lệnh .
Thao tác này sẽ mở ra cửa sổ trình chỉnh sửa tập lệnh và đặt mặc định thành một hàm có tên là my Chức năng () . Đâ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 sẽ đọ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.
Hàm 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ị trong 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ẽ giống như sau:
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 một phép tính trên dữ liệu, rồi ghi dữ liệu đó vào một ô khác. Vì vậy, phần cuối cùng của hàm này sẽ là:
var results = data * 100; sheet.getRange(row, col+1).setValue(results); }
Khi bạn viết xong hàm của mình, hãy chọn biểu tượng đĩa để lưu.
Lần đầu tiên bạn chạy một 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 rằng tập lệnh đã ghi kết quả tính toán vào ô đích.
Giờ bạn đã biết cách viết một hàm tập lệnh Google Apps cơ bản, hãy cùng xem một số hàm nâng cao hơn.
Sử dụng getValues để tải mảng
Bạn có thể đưa 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 với tập lệnh lên một cấp độ 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, thì 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 nhiều chiều chứa tất cả dữ liệu từ trang tính. Để thực hiện phép tính trên dữ liệu, bạn sử dụng for 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 rằng tất cả cá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 một hàm getRange.
dữ liệu [i] [0] đề cập đến các kích thước mảng trong đó thứ nguyên đầu tiên là hàng và thứ hai là cột. Cả hai điều này đều bắt đầu từ con số không.
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 appendRow để 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 rất dễ thực hiện với appendRow 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 sẽ đếm từ 1 đến 10 và hiển thị bộ đếm với bội số của 2 trong Bộ đếm cột.
Hàm này sẽ giống như sau:
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 chức năng này.
Xử lý nguồn cấp RSS với URLFetchApp
Bạn có thể kết hợp chức năng tập lệnh Google Trang tính trước đó và URLFetchApp để lấy nguồn cấp RSS 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 báo đượ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!
Tập lệnh để 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]);
}
} Như bạn có thể thấy, Xml.parse kéo từng mục ra khỏi nguồn cấp 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 appendRow , bạn có thể đặt các mục này vào các cột thích hợp cho từng mục trong nguồn cấp dữ liệu RSS.
Kết quả đầu ra trong trang tính của bạn sẽ giống như sau:
Thay vì nhúng URL nguồn cấp RSS vào tập lệnh, bạn có thể có một trường trong trang tính của mình với URL và sau đó có nhiều trang tính - một trang cho mọi trang web bạn muốn theo dõi.
Kết nối các chuỗi và thêm một chuyến vận chuyển trở lại
Bạn có thể tiến xa hơn bảng tính RSS 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 mình một email với 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 tạo dòng chủ đề và nội dung 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 điệp bằng cách đặt các dòng sau trước vòng lặp “mục” For.
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Sau đó, ở cuối vòng lặp for “items” (ngay sau hàm appendRow), 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 với nhau, theo sau là “\ n” cho dấu xuống dòng sau mỗi dòng. Ở cuối mỗi khối dữ liệu tiêu đề, bạn sẽ muốn có hai ký tự xuống dòng cho nội dung email được định dạng đẹp.
Khi tất cả các hàng được xử lý, biến "body" sẽ giữ toàn bộ chuỗi thông báo 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ẽ là gửi “chủ đề” và “nội dung” qua email. Thực hiện điều 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 của 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 tiêu đề và nội dung.
Đây là email kết quả sẽ trông như thế nào.
Việc 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ữ trong Google Trang tính và gửi cho chính bạn với các liên kết URL đi kèm, giúp bạn theo dõi nội dung mới nhất cho bất kỳ trang web nào rất thuận tiện.
Đây chỉ là một ví dụ về sức mạnh có sẵn 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.