Hàm JavaScript của Google Trang tính

Google Trang tính là một công cụ bảng tính mạnh mẽ dựa trên đám mây cho phép bạn thực hiện 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à một 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

Mục lục

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ừ menu, sau đó chọn Trình chỉnh sửa tập lệnh

Thao tác này sẽ mở cửa sổ trình soạn thảo tập lệnh và mặc định là hàm có tên myfunction(). Đâ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 chức năng 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à 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ẽ giố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 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 chức năng này sẽ là

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

Khi bạn viết xong 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 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 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

Bây giờ bạn đã biết cách viết một chức năng tập lệnh cơ bản của Google Apps, hãy xem xét 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 các phép tính trên dữ liệu trong bảng tính của mình bằng cách viết 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 cách sử dụ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 vòng lặp for. 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 vào 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 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 đến một ô và hàng trong một biến mảng khác với tham chiếu đến một 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ừ 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 lưu trữ kết quả

Sử dụng appendRow để ghi kết quả

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 thì sao?

Điều này rất dễ thực hiện với chức năng appendRow. Hàm 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 có bội số của 2 trong cột Bộ đếm

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 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à 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]);
}
}

Như bạn có thể thấy, Xml. phân tích 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 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 từng mục trong nguồn cấp 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 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, 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 đường trả về vận chuyển

Bạn có thể đưa bảng tính RSS tiến 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, sau đó sử dụng các chức năng email để gửi cho mình một email có tóm tắt tất cả các bài đăng mới trong nguồn cấp RSS của trang web

Để thực hiện việc này, bên dưới 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 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 “các 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 “items” For

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Sau đó, ở cuối vòng lặp “items” for (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 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ẽ muốn có hai ký tự xuống dòng cho 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 “body” chứa 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ẽ là gửi “chủ đề” và “nội dung” qua email. Làm điều này trong Google Script rất dễ dàng

MailApp là một lớp rất tiện lợi bên trong 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 để gửi hoặc nhận email. Nhờ vậ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

Đây là giao diện của email kết quả.  

Kết hợp khả năng trích xuất nguồn cấp dữ liệu RSS của một trang web, lưu trữ nó trong Trang tính Google và gửi cho chính bạn kèm theo các liên kết URL, giúp bạn 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

Làm cách nào để sử dụng các hàm JavaScript trong Google Trang tính?

Tôi có thể sử dụng JavaScript trong Google Trang tính không?

Làm cách nào để thêm JavaScript trong Google Trang tính?

Bạn có thể sử dụng các hàm trong Google Trang tính không?