Google Trang tính^{(Google Sheets)} 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 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^{(Google Sheets)} là tính năng Google Scripting đi kèm với nó.

Tập lệnh Google Apps^{(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^{(in Google Sheets)} 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ẽ học 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^{(Google Sheets)} để đọ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.^{(Google Sheets)}

Cách tạo Tập lệnh Google Apps^{(How to Create a Google Apps Script)}

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 Apps)}Google Trang tính^{(Google Sheets)} . 

Để thực hiện việc này, hãy chọn Công cụ^(Tools) từ menu, sau đó chọn Trình^{(Script Editor)} chỉnh sửa tập lệnh .

Thao tác này sẽ mở ra cửa sổ trình soạn thảo tập lệnh và đặt mặc định cho một hàm được gọi là my Chức năng ()^{(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 một hàm tập lệnh Google Trang tính^{(Google Sheets)} 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ị 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 biến dữ liệu^(data) . 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^{(Google Sheets)} 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^{(Authorization)} để tập lệnh chạy trên Tài khoản Google^{(Google Account)} 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.

Bây giờ bạn đã biết cách viết một hàm script cơ bản của Google Apps , hãy cùng xem một số hàm nâng cao hơn.

Sử dụng getValues ​​để tải mảng^{(Use getValues To Load Arrays)}

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 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 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^(Save) 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. 

data [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 đều bắt đầu từ con số không.

getRange(i+1, 2) tham chiếu đế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ả^{(Use appendRow To Write Results)}

Đ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 hàm appendRow . 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ị một bộ đếm với bội số của 2 trong cột Bộ đếm .^(Counter)

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^{(Process RSS Feeds With URLFetchApp)}

Bạn có thể kết hợp chức năng tập lệnh Google Trang tính trước đó và ^{(Google Sheets)}URLFetchApp để kéo nguồn cấp dữ liệu 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 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.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 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 trong nguồn cấp RSS .

Đầ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^{(Concatenate Strings)} và thêm^(Add) một chuyến vận chuyển trở lại^{(Carriage Return)}

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, rồi 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, 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 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 For “các mục”.

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à “” 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 độc đáo.

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^{(How To Send Email In 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^(MailApp) 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^{(send any email)} chỉ với địa chỉ email, dòng tiêu đề và nội dung.

Đây là email kết quả sẽ như thế nào. 

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^{(Google Sheet)} 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 các 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.

5 Google Sheets Script Functions You Need to Know

Google Sheеts is a powerful cloud-based spreadsheеt tool that lets you do nearly everything you could do in Microsoft Excel. But the real power of Google Sheets іs the Google Scrіptіng feature that сomes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

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

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

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

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

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

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Related posts

• 4 cách chuyển đổi Excel sang Google Trang tính

• Google Trang tính và Microsoft Excel - Sự khác biệt là gì?

• Cách xóa đường viền bảng trong Google tài liệu

• Cách Trò chuyện của Google Tài liệu Giúp bạn Cộng tác trên Tài liệu

• Cách Chèn Hình mờ trong Word và Google Tài liệu

• 9 cách mở tài liệu Word mà không cần Word

• Cách sử dụng Hàm CONCATENATE trong Google Trang tính

• 10 công cụ để khôi phục mật khẩu Outlook PST bị mất hoặc quên

• Cách xóa dòng trống trong Excel

• Cách sử dụng Google Biểu mẫu làm công cụ theo dõi chi phí

• Cách xóa bộ nhớ cache trong Outlook

• Cách hiển thị số lượng từ trong Microsoft Word

• Chèn dấu đầu dòng vào giữa câu trong Word

• Microsoft Outlook sẽ không mở? 10 cách để sửa chữa

• Cách Chèn Trang tính Excel vào Tài liệu Word

• Cách khắc phục Outlook bị kẹt khi tải hồ sơ

• Google Docs và Microsoft Word - Sự khác biệt là gì?

• Cách thêm số trang chiếu vào bản trình bày PowerPoint

• Làm cho OpenOffice Writer có giao diện và chức năng giống Microsoft Word hơn

• Sửa lỗi thẻ kết thúc thẻ bắt đầu không khớp khi mở tệp DOCX