Google 表格(Google Sheets)是一款功能强大的基于云的电子表格工具，可让您完成几乎所有可以在Microsoft Excel 中(Microsoft Excel)完成的操作。但Google Sheets(Google Sheets)的真正强大之处在于它附带的Google Scripting功能。(Google Scripting)
Google Apps脚本是一种后台脚本工具，不仅适用于 Google 表格(in Google Sheets)，还适用于 Google Docs、Gmail、Google Analytics和几乎所有其他Google云服务。它使您可以自动化这些单独的应用程序，并将这些应用程序中的每一个相互集成。
在本文中，您将了解如何开始使用Google Apps脚本、在(Google Apps)Google 表格(Google Sheets)中创建基本脚本以读取和写入单元格数据，以及最有效的高级Google 表格(Google Sheets)脚本功能。
如何创建 Google Apps 脚本(How to Create a Google Apps Script)
您现在可以开始从Google 表格中创建您的第一个(Google Sheets)Google Apps脚本。
为此，请从菜单中选择工具，然后选择(Tools)脚本编辑器(Script Editor)。
这将打开脚本编辑器窗口并默认使用名为myfunction()的函数。您可以在此处创建和测试您的Google 脚本(Google Script)。
试一试，尝试创建一个谷歌表格(Google Sheets)脚本函数，该函数将从一个单元格读取数据，对其执行计算，并将数据量输出到另一个单元格。
从单元格获取数据的函数是getRange()和getValue()函数。您可以按行和列识别单元格。因此，如果您在第 2 行和第 1 列（A 列）中有一个值，则脚本的第一部分将如下所示：
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var data = sheet.getRange(row, col).getValue();
}
这会将来自该单元格的值存储在数据(data)变量中。您可以对数据执行计算，然后将该数据写入另一个单元格。所以这个函数的最后一部分是：
var results = data * 100;
sheet.getRange(row, col+1).setValue(results);
}
编写完函数后，选择要保存的磁盘图标。
第一次像这样运行新的Google 表格(Google Sheets)脚本功能时（通过选择运行图标），您需要提供Authorization以便脚本在您的Google 帐户(Google Account)上运行。
允许权限继续。脚本运行后，您会看到脚本将计算结果写入目标单元格。
现在您已经知道如何编写基本的Google Apps脚本函数了，让我们来看看一些更高级的函数。
使用 getValues 加载数组(Use getValues To Load Arrays)
您可以通过使用数组将使用脚本对电子表格中的数据进行计算的概念提升到一个新的水平。如果您使用 getValues 在Google Apps脚本中加载变量，则该变量将是一个可以从工作表加载多个值的数组。
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
数据变量是一个多维数组，包含工作表中的所有数据。要对数据执行计算，请使用for循环。for 循环的计数器将遍历每一行，并且该列保持不变，具体取决于您要提取数据的列。
在我们的示例电子表格中，您可以对三行数据执行如下计算。
for (var i = 1; i < data.length; i++) {
var result = data[i][0] * 100;
sheet.getRange(i+1, 2).setValue(result);
}
}
像上面一样保存(Save)并运行这个脚本。您会看到所有结果都填充到电子表格的第 2 列中。
您会注意到在数组变量中引用单元格和行与使用 getRange 函数不同。
data[i][0]指的是数组维度，其中第一个维度是行，第二个维度是列。这两个都从零开始。
getRange(i+1, 2)在 i=1 时引用第二行（因为第 1 行是表头），2 是存储结果的第二列。
使用 appendRow 写入结果(Use appendRow To Write Results)
如果您有一个想要将数据写入新行而不是新列的电子表格怎么办？
使用appendRow(appendRow)函数很容易做到这一点。此功能不会打扰工作表中的任何现有数据。它只会在现有工作表上附加一个新行。
例如，创建一个从 1 计数到 10 的函数，并在Counter列中显示一个具有 2 倍数的计数器。
这个函数看起来像这样：
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
以下是运行此函数时的结果。
使用 URLFetchApp 处理 RSS 提要(Process RSS Feeds With URLFetchApp)
您可以结合以前的Google Sheets脚本函数和URLFetchApp从任何网站提取RSS提要，并为最近发布到该网站的每篇文章在电子表格中写入一行。
这基本上是一种DIY方法来创建您自己的RSS提要阅读器电子表格！
执行此操作的脚本也不太复杂。
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]);
}
}
如您所见，Xml.parse将每个项目从RSS提要中提取出来，并将每一行分隔为标题、链接、日期和描述。
使用appendRow函数，您可以将这些项目放入(appendRow)RSS提要中每个项目的适当列中。
工作表中的输出将如下所示：
无需将RSS提要URL嵌入脚本，您可以在工作表中使用URL的字段，然后有多个工作表 - 每个您要监控的网站一张。
连接字符串(Concatenate Strings)并添加(Add)回车(Carriage Return)符
您可以通过添加一些文本处理功能使RSS电子表格更进一步，然后使用电子邮件功能向自己发送一封电子邮件，其中包含站点RSS提要中所有新帖子的摘要。
为此，在您在上一节中创建的脚本下，您需要添加一些脚本来提取电子表格中的所有信息。
您需要通过将用于将RSS数据写入电子表格的同一“items”数组中的所有信息一起解析来构建主题行和电子邮件文本正文。
为此，通过在“items” For 循环之前放置以下几行来初始化主题和消息。
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
然后，在“items”for 循环的末尾（就在 appendRow 函数之后），添加以下行。
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
“+”符号将所有四个项目连接在一起，然后在每行后面加上“”作为回车符。在每个标题数据块的末尾，您需要两个回车符以获得格式良好的电子邮件正文。
处理完所有行后，“body”变量将保存整个电子邮件消息字符串。现在您可以发送电子邮件了！
如何在 Google Apps 脚本中发送电子邮件(How To Send Email In Google Apps Script)
您的Google 脚本(Google Script)的下一部分将通过电子邮件发送“主题”和“正文”。在Google Script(Google Script)中执行此操作非常容易。
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
MailApp是Google Apps脚本中的一个非常方便的类，可让您访问您的 Google 帐户的电子邮件服务以发送或接收电子邮件。多亏了这一点，带有 sendEmail 功能的单行可以让您发送任何电子邮件(send any email)，只需电子邮件地址、主题行和正文。
这就是生成的电子邮件的样子。
结合提取网站的RSS提要、将其存储在Google 表格(Google Sheet)中并将其发送给您自己以及包含URL链接的功能，可以非常方便地跟踪任何网站的最新内容。
这只是Google Apps脚本中用于自动执行操作和集成多个云服务的强大功能的一个示例。
5 Google Sheets Script Functions You Need to Know
Goоglе Sheets is a powerful сloud-based spreadsheet tool thаt lets you do nearly everything you cоuld do in Microsoft Excel. But the real power of Google Sheets is the Google Scripting fеature that comes 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.
