如何使用脚本编辑器为您的Google Apps增压

If you use Google Apps, then chances are you’re not using them to their full extent. With Google Apps Script, you can add custom menus and dialogs, write custom functions and macros, and build add-ons to extend Google Docs, Sheets, and Slides.

如果您使用的是Google Apps，那么您可能没有充分利用它们。 使用Google Apps脚本，您可以添加自定义菜单和对话框，编写自定义功能和宏，以及构建扩展程序以扩展Google文档，表格和幻灯片。

什么是Google Apps脚本？ (What Is Google Apps Script?)

Google Apps Script is a cloud-based development platform for creating custom, light-weight web applications. You can build scalable applications directly inside your browser that integrate effortlessly with Google products.

Google Apps脚本是一个基于云的开发平台，用于创建自定义的轻量级Web应用程序。 您可以直接在浏览器内部构建可扩展的应用程序，这些应用程序可以轻松地与Google产品集成。

Apps Script uses the JavaScript language and brings together the familiarity of web development and Google products in one place, making it a perfect tool to customize apps for your business, organization, or just to automate mundane tasks.

Apps Script使用JavaScript语言，将对Web开发和Google产品的熟悉程度集中在一个地方，从而使其成为为您的企业，组织或自动执行日常任务定制应用程序的理想工具。

You can make two types of scrips with Google Apps Script:

您可以使用Google Apps脚本制作两种类型的脚本：

• Standalone: These scripts aren’t bound to any service—like Google Docs, Sheets, or Slides. They can perform system-wide functions, sort of like macros. They’re not ideal for sharing with a broader audience because you need to copy and paste the code to use them. Examples include searching your Drive for files with specific names or seeing who has access to your shared files and folders in Drive.

  独立：这些脚本不受任何服务的约束，例如Google Docs，表格或幻灯片。 他们可以执行系统范围的功能，类似于宏。 它们不是与更多受众共享的理想选择，因为您需要复制并粘贴代码才能使用它们。 例如，在云端硬盘中搜索具有特定名称的文件，或者查看谁有权访问您在云端硬盘中的共享文件和文件夹。

• Bound: These are linked to a Google Docs, Sheets, Forms, or Slides file. Bound scripts extend a file’s functionality and perform actions only in that specific file. Examples include adding custom menus, dialogs boxes, and sidebars to a service or a script that emails you notifications any time a particular cell in a Sheet changes.

  绑定：这些链接到Google文档，表格，表单或幻灯片文件。 绑定脚本扩展了文件的功能，并且仅在该特定文件中执行操作。 示例包括向服务或脚本添加自定义菜单，对话框和侧边栏，以在工作表中的特定单元格发生更改时通过电子邮件向您发送通知。

If you don’t know much JavaScript, or maybe you’ve never heard of it before, don’t let that scare you off from developing a script of your own. It’s super easy to get started using Apps Script, as it provides a wealth of documentation and examples for you to test out on your own. Below are a couple of simple examples to help you gain an understanding of how they work.

如果您不太了解JavaScript，或者也许您以前从未听说过JavaScript，请不要让这吓到您开发自己的脚本。 使用Apps Script上手非常容易，因为它提供了大量文档和示例供您自己进行测试。 以下是几个简单的示例，可帮助您了解它们的工作原理。

如何创建独立脚本 (How to Create a Standalone Script)

Now that you know what they are let’s go ahead and create your first standalone script. We’ll be using a code sample from Google to help us get the ball rolling, and we’ll provide explanations to the lines of code if you’re unfamiliar with GoogleScript or JavaScript.

现在您已经知道它们是什么，让我们继续创建您的第一个独立脚本。 我们将使用Google的代码示例来帮助我们解决问题，如果您不熟悉GoogleScript或JavaScript，我们将为代码行提供解释。

Head on over to Google Apps Script. In the top left corner, click the hamburger icon, then click “New Script.”

转到Google Apps脚本 。 点击左上角的汉堡包图标，然后点击“新脚本”。

A new untitled project opens with an empty function inside, but because we are using sample code from Google, you can go ahead and delete all the text in the file.

一个新的无标题项目打开时，内部带有一个空函数，但是由于我们使用的是Google的示例代码，因此您可以继续删除文件中的所有文本。

Note: You need to be signed in to your Google account for this script to work.

注意：您需要登录Google帐户才能运行此脚本。

After you’ve deleted the code that’s preloaded in the file, paste in the following code:

删除文件中预加载的代码后，请粘贴以下代码：

//Initialize your function
 function createADocument() {
 
// Create a new Google Doc named 'Hello, world!'
 var doc = DocumentApp.create('Hello, world!');
 
// Access the body of the document, then add a paragraph.
 doc.getBody().appendParagraph('This document was created by Google Apps Script.');
 }

Before you can run the code, you have to save the script. Click “File” and then click “Save.”

在运行代码之前，您必须保存脚本。 单击“文件”，然后单击“保存”。

Rename the project to something that helps you remember what the script does, then hit “OK.”

将项目重命名为可以帮助您记住脚本功能的名称，然后单击“确定”。

To run your code, click the play icon located in the toolbar.

要运行代码，请单击工具栏中的播放图标。

You will have to grant the script some permissions to access your Google account via a popup window after you click “Run” the first time. Click “Review Permissions” to see what it needs to access.

首次单击“运行”后，您将必须为脚本授予一些权限以通过弹出窗口访问您的Google帐户。 单击“查看权限”以查看需要访问的内容。

Because this isn’t a Google verified app, you will get another warning. It basically says that, unless you know the developer (us) only proceed if you trust them. Click “Advanced,” then click “Go to CreateNewDoc” (or whatever you named this script).

由于这不是经过Google验证的应用，因此您将收到另一条警告。 它基本上是说，除非您知道开发人员(我们)只有在您信任他们的情况下才能继续进行。 单击“高级”，然后单击“转到CreateNewDoc”(或任何您将此脚本命名)。

Review the permissions the script requires, then click “Allow.”

查看脚本所需的权限，然后单击“允许”。

Great! Now, head over to your Drive and if everything worked out, the “Hello, World!” file should be there. Double-click it to open it.

大！ 现在，前往您的云端硬盘，如果一切顺利，“ Hello，World！” 文件应该在那里。 双击将其打开。

When you open the file, you’ll see the line of text from the code adds to your document.

打开文件时，您会看到代码中的一行文本添加到您的文档中。

Now, if you want to get an email notification when the document is created, you can add a few more lines of code to send one to your Google account automatically. Add the following lines of code after doc.getBody().appendParagraph('This document was created by Google Apps Script.'); but before the last curly brace } :

现在，如果您想在创建文档时收到电子邮件通知，则可以再添加几行代码，以自动将其中的一行发送到您的Google帐户。 在doc.getBody().appendParagraph('This document was created by Google Apps Script.');之后添加以下代码行doc.getBody().appendParagraph('This document was created by Google Apps Script.'); 但在最后一个花括号} :

// Get the URL of the document.
var url = doc.getUrl();
// Get the email address of the active user - that's you.
var email = Session.getActiveUser().getEmail();

// Get the name of the document to use as an email subject line.
var subject = doc.getName();

// Append a new string to the "url" variable to use as an email body.
var body = 'Link to your doc: ' + url;

// Send yourself an email with a link to the document.
GmailApp.sendEmail(email, subject, body);

Click the “Run” icon.

点击“运行”图标。

Because you added a couple of extra lines that require additional permissions, you have to go through the same process as before. Click “Review Permissions.”

因为添加了几行需要额外权限的额外行，所以您必须执行与之前相同的过程。 点击“查看权限”。

Click “Advanced,” then click “Go to CreateNewDoc.”

单击“高级”，然后单击“转到CreateNewDoc”。

Note: As Google is warning you about launching unverified apps, you will receive a security alert email notifying you as well. Google does this just in case you weren’t the one granting access to an unverified application.

注意：由于Google警告您有关启动未经验证的应用程序，因此您还将收到一条安全警报电子邮件，通知您。 Google只是在您不是授予未验证应用程序访问权限的情况下这样做。

Review the new set of permissions the script requires, then click “Allow.”

查看脚本需要的一组新权限，然后单击“允许”。

When the document gets created, you receive an email with a link to the file in your Google Drive.

创建文档后，您会收到一封电子邮件，其中包含指向您Google云端硬盘中文件的链接。

Clicking the link brings you directly to the file, which is inside your Google Drive.

点击该链接可直接将您带到Google云端硬盘中的文件。

如何创建绑定脚本 (How to Create a Bound Script)

For this next example, let’s create a bound script for Google Sheets that parses an existing sheet for duplicate entries in a row and then deletes them.

在下一个示例中，让我们为Google表格创建一个绑定脚本，该脚本解析现有表格中连续的重复条目，然后将其删除。

If you remember from earlier, bound scripts work like an add-on to specific files, so to create one, let’s open up an existing Google Sheet spreadsheet that contains at least one duplicate data point.

如果您还记得以前的话，绑定脚本的作用就像特定文件的附件一样，因此要创建一个，让我们打开一个包含至少一个重复数据点的现有Google表格电子表格。

Click “Tools” then click “Script Editor.”

单击“工具”，然后单击“脚本编辑器”。

Google Apps Script opens in a new tab with an empty script. This time, however, the script is bound to the Sheet from which it opens.

Google Apps脚本会在一个带有空脚本的新标签页中打开。 但是，这一次，脚本已绑定到打开它的工作表。

Just like before, delete the empty function and paste in the following code:

和以前一样，删除空函数并粘贴以下代码：

//Removes duplicate rows from the current sheet.

 function removeDuplicates() {
//Get current active Spreadsheet
 var sheet = SpreadsheetApp.getActiveSheet();
//Get all values from the spreadsheet's rows
 var data = sheet.getDataRange().getValues();
//Create an array for non-duplicates
 var newData = [];
//Iterate through a row's cells
 for (var i in data) {
   var row = data[i];
   var duplicate = false;
   for (var j in newData) {
    if (row.join() == newData[j].join()) {
     duplicate = true;
    }
  }
//If not a duplicate, put in newData array
 if (!duplicate) {
  newData.push(row);
 }
}
//Delete the old Sheet and insert the newData array
 sheet.clearContents();
 sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Note: For the script to remove a duplicate, all cells in the row must match.

注意：要使脚本删除重复项，该行中的所有单元格必须匹配。

Save and rename your script, then hit the “Run” icon.

保存并重命名脚本，然后单击“运行”图标。

Again, as you encountered in the last script you created, you’ll have to review the permissions your script requires, and grant it access your spreadsheet. Click “Review Permissions” to see what access this script wants.

同样，如您在上一个创建的脚本中遇到的那样，您必须查看脚本所需的权限，并授予其访问电子表格的权限。 单击“查看权限”以查看此脚本需要的访问权限。

Accept the prompts and click “Allow” to authorize the script.

接受提示，然后单击“允许”以授权脚本。

After it finishes running, go back to your Sheet and, just like magic, all duplicate entries vanish from your file!

完成运行后，返回工作表，就像魔术一样，所有重复的条目都将从文件中消失！

Unfortunately, if your data is inside of a table—like the example above—this script will not resize the table to fit the number of entries in it.

不幸的是，如果您的数据在表中(如上面的示例一样)，此脚本将不会调整表的大小以适合表中的条目数。

---

---

Although these are two pretty straightforward examples of how to use Apps Script, the options are almost limitless, and it all depends on what you can dream up with these resources. But, in the meantime, head on over to the GSuite Devs Github page or Digital Inspiration and check out the stack of sample scripts you can deploy within your own services to get a better idea of what Apps Script is truly capable of doing.

尽管这是有关如何使用Apps Script的两个非常简单的示例，但是这些选项几乎是无限的，并且这完全取决于您可以利用这些资源实现什么。 但是，与此同时，请转到GSuite Devs Github页面或Digital Inspiration，并检查可在您自己的服务中部署的示例脚本堆栈，以更好地了解Apps脚本的真正功能。

翻译自: https://www.howtogeek.com/410451/how-to-supercharge-your-google-apps-with-the-script-editor/