Google Sheets 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 Sheets là tính năng Google Scripting đi kèm với nó.
Tập lệnh Google Apps là một công cụ kịch bản nền hoạt động không chỉ trong Google Sheets 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 Sheets để đọc và ghi dữ liệu di động và Google Sheets tiên tiến hiệu quả nhất chức năng tập lệnh.
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 Sheets.
Để thực hiện việc này, chọn Công cụtừ menu, sau đó Trình chỉnh sửa tập lệnh.
Điều này sẽ mở cửa sổ soạn thảo tập lệnh và mặc định cho một chức năng gọi là myfeft (). Đây là nơi bạn có thể tạo và kiểm tra Google Script của mình.
In_content_1 all: [300x250] / dfp: [640x360]->Để chụp ảnh, hãy thử tạo chức năng tập lệnh Google Sheets sẽ đọc dữ liệu từ một ô, thực hiện phép tính trên 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à các hàm getRange ()và getValue (). 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 biến dữ liệu. Bạn có thể thực hiện tính toán trên dữ liệu và 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 bạn đã hoàn thành việc viết chức năng 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 Sheets 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 cho tập lệnh để chạy trên Tài khoản Google của mình.
Cho phép tiếp tục quyền. 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 ô mục tiêu.
Bây giờ bạn đã biết cách viết chức năng tập lệnh Google Apps cơ bản, hãy xem một số chức năng 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 tính toán trên dữ liệu trong bảng tính của mình với kịch bản 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, 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 biến đa chiều mảng 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 vòng lặp cho. 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 kéo dữ liệu.
Trong bảng tính mẫu của chúng tôi, bạn có thể thực hiện tính toán trên ba hàng của 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 hàm getRange.
data [i] [0]đề cập đến 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ừ 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 appendRow để ghi kết quả
Điều gì xảy ra nếu bạn có một bảng tính nơi bạn muốn ghi dữ liệu vào một cái mới hàng thay vì một cột mới?
Điều này rất dễ thực hiện với chức năng appendRow. Hàm này đã thắng làm phiền bất kỳ dữ liệu hiện có trong bảng. Nó sẽ chỉ nối 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ị một bộ đếm có bội số 2 trong Bộ đếm.
Hàm 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 chức năng này.
Bạn có thể kết hợp chức năng tập lệnh Google Sheets trước đó và URLFetchAppđể lấy nguồn cấp 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 cho trang web đó .
Về cơ bản, đây là phương pháp DIY để tạo bảng tính trình đọc nguồn cấp RSS của riêng bạn!
Kịch bản để thực hiện việc 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.parseké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 và mô tả.
Sử dụng chức năng appendRow, bạn có thể đặt các mục này vào các cột thích hợp cho mọi mục duy nhất trong nguồn cấp RSS.
Đầu ra trong trang tính của bạn sẽ trông đại loại như thế này:
Thay vào đó khi 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 bằng URL và sau đó có nhiều trang tính - một trang cho mỗi trang web bạn muốn theo dõi.
Nối chuỗi và thêm trả lại vận chuyển
Bạn có thể đưa bảng tính RSS đi thêm một bước bằng cách thêm một số chức năng thao tác văn bản và sau đó sử dụng các chức năng email để gửi cho mình một email 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, theo tập lệnh bạn đã tạo trong 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 có thể xây dựng dòng tiêu đề và nội dung văn bản email bằng cách phân tích tất cả các thông tin từ cùng một mục trên các vật phẩm của Mảng 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 của các mặt hàng đối với vòng lặp.
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Sau đó, ở cuối các mục trong vòng lặp cho các vòng lặp (ngay sau hàm appendRow), thêm dòng sau.
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
Biểu tượng + + sẽ kết hợp cả bốn mục với nhau theo sau bởi \ \ n Cho một chiếc xe ngựa trở về sau mỗi dòng. Ở cuối mỗi khối dữ liệu tiêu đề, bạn sẽ muốn có hai lần trả về vận chuyển cho phần thân email được định dạng độc đáo.
Sau khi tất cả các hàng được xử lý, biến cơ thể của Chiêu 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 của bạn sẽ được gửi chủ đề của người Viking và cơ thể người khác thông qua email. Làm đ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 thuận tiện 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 bạn để gửi hoặc nhận email. Nhờ vào điều này, một dòng 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 chính.
Đây là email kết quả sẽ như thế nào .
Kết hợp khả năng giải nén Nguồn cấp dữ liệu RSS của một trang web, lưu trữ nó trong Google Sheet và tự gửi nó cho bạn với các liên kết URL được bao gồm, rất thuận tiện để theo dõi nội dung mới nhất cho bất kỳ trang web nào.
Đâ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.