谷歌表格_如何使用宏自动执行Google表格

谷歌表格

Google Sheets lets you automate repetitive tasks with macros, and you can then bind them to keyboard shortcuts to execute them quickly. They work using Google Apps Script to capture your actions for later use.

Google表格可以让您使用宏自动执行重复性任务，然后可以将其绑定到键盘快捷键以快速执行它们。他们使用Google Apps脚本来捕获您的操作以供以后使用。

什么是宏？ (What are Macros?)

A macro—or macroinstruction—is a specific sequence of actions that let you automate a series of steps to increase productivity. They work by recording your actions and saving them into a file that’s bound to the spreadsheet in which they were recorded.

宏(或宏指令)是特定的操作序列，可让您自动化一系列步骤以提高生产率。它们的工作方式是记录您的操作并将其保存到与记录了这些操作的电子表格绑定的文件中。

When you record a macro in Google Sheets, it automatically creates an Apps Script with all the code to replicate your actions for you. This means you can create complex macros without knowing how to write code. The next time you run it, Sheets will do everything you did when you recorded the macro. Essentially, you’re teaching Google Sheets how to manipulate a document to your liking with a single command.

当您在Google表格中记录宏时，它会自动创建一个包含所有代码的Apps脚本，以为您复制操作。这意味着您可以在不知道如何编写代码的情况下创建复杂的宏。下次运行它时，Sheets将完成您在录制宏时所做的所有操作。本质上，您是在教Google表格如何通过单个命令来按自己的喜好操纵文档。

Macros are a powerful feature that can do basically anything Sheets is capable of doing. Here are just a few examples of its functionality:

宏是一项强大的功能，基本上可以完成Sheets能够执行的任何操作。以下是其功能的一些示例：

Apply formatting and styles.
应用格式和样式。
Create completely new spreadsheets.
创建全新的电子表格。
Use any Google Sheets function, toolbar, menu, or feature.
使用任何Google表格功能，工具栏，菜单或功能。

The sky is the limit.

天空才是极限。

如何在Google表格中记录宏 (How to Record a Macro in Google Sheets)

Fire up a Google Sheet and click Tools > Macros > Record Macro.

启动Google表格，然后单击工具>宏>记录宏。

This opens the recording menu at the bottom of the window, with two options for capturing your actions:

这将打开窗口底部的“录制”菜单，其中有两个用于捕获动作的选项：

Absolute References: The macro will do tasks only on the exact cells you record. If you italicize cell B1, the macro will only ever italicize B1 regardless of what cell you clicked on.

绝对引用：宏将仅在您记录的确切单元格上执行任务。如果您将单元格B1变为斜体，则无论您单击哪个单元格，宏都只会将其斜体为B1。
Relative References: The macro will do tasks on the selected cells, regardless of where they are in the sheet. If you italicize B1 and C1, you can re-use the same macro to italicize cells D1 and E1 later.

相关参考：宏将在选定的单元格上执行任务，无论它们在表中的位置如何。如果将B1和C1斜体化，则可以在以后重新使用同一宏来斜体化单元格D1和E1。

Select whether you want an absolute or relative reference, then you can start to click, format, and teach Sheets what order you want these actions to replicate.

选择您要的是绝对参考还是相对参考，然后可以单击，格式化和教导Sheets您希望这些操作复制的顺序。

After you’ve captured all the actions for this macro, click “Save.”

在捕获了该宏的所有动作之后，单击“保存”。

Enter a name for your macro. Google also lets you create shortcuts for up to ten macros. If you want to bind a macro to a keyboard shortcut, enter a number from 0-9 in the space provided. When you finish, click “Save.”

输入宏的名称。 Google还允许您创建最多十个宏如果要将宏绑定到键盘快捷键，请在提供的空格中输入0-9之间的数字。完成后，单击“保存”。

If you need to tweak your macro’s name or shortcut, you can edit a macro by clicking Tools > Macros > Manage Macros.

如果需要调整宏的名称或快捷方式，可以通过单击工具>宏>管理宏来编辑宏。

From the window that opens, tweak as desired and then click “Update.”

在打开的窗口中，根据需要进行调整，然后单击“更新”。

The next time you press the shortcut associated with the macro, it will run without having to open the macro menu from the toolbar.

下次您按下与宏关联的快捷方式时，它将运行，而无需从工具栏打开宏菜单。

如何在Google表格中运行宏 (How to Run a Macro in Google Sheets)

If your macro is an absolute reference, you can run the macro by pressing the keyboard shortcut or go to Tools > Macros > Your Macro and then click the appropriate option.

如果您的宏是绝对引用，则可以通过按键盘快捷键或转到“工具”>“宏”>“您的宏”，然后单击适当的选项来运行宏。

Otherwise, if your macro is a relative reference, highlight the cells in your spreadsheet on which you want the macro to run and then press the corresponding shortcut, or click on it from Tools > Macros > Your Macro.

否则，如果您的宏是相对引用，请突出显示电子表格中您要在其上运行宏的单元格，然后按相应的快捷方式，或者从“工具”>“宏”>“您的宏”中单击它。

如何导入宏 (How to Import Macros)

As mentioned earlier, when you record a macro, it gets bound to the spreadsheet on which you recorded it. But what if you want to import a macro from another spreadsheet? While it’s not a straightforward and simple task, you can do it using this little workaround.

如前所述，当您记录宏时，它会绑定到记录宏的电子表格。但是，如果要从另一个电子表格导入宏该怎么办？尽管这不是一项简单明了的任务，但是您可以使用此小解决方法来完成。

Because recorded macros are stored as functions in Google Apps Script, to import a macro, you need to copy the function and then paste it in the new sheet’s macro file.

由于录制的宏是作为函数存储在Google Apps脚本中的，因此要导入宏，您需要复制该函数，然后将其粘贴到新工作表的宏文件中。

Open the Google Sheet with the macro you want to copy and then click on Tools > Macros > Manage Macros.

使用您要复制的宏打开Google表格，然后单击工具>宏>管理宏。

Next, click the “More” icon next to the macro you’d like to copy and then click “Edit Script.”

接下来，点击您要复制的宏旁边的“更多”图标，然后点击“编辑脚本”。

All macros save to the same file, so if you have a couple of macros saved, you may have to sift through them. The function’s name is the same one you gave it when you created it.

所有宏都保存到同一个文件中，因此，如果您保存了几个宏，则可能必须在它们之间进行筛选。该函数的名称与您创建函数时使用的名称相同。

Highlight the macro(s) you want to copy, then press Ctrl + C. Be sure to copy everything up to and including the closing semi-colon.

突出显示要复制的宏，然后按Ctrl +C。确保将所有内容复制到包括分号在内的所有内容。

Now, open the other spreadsheet you’ll be importing the macro to and click Tools > Macros > Record Macro.

现在，打开要将宏导入到的其他电子表格，然后单击工具>宏>记录宏。

Immediately click “Save” without recording any actions to create a placeholder function in the sheet’s macro file for us. You’ll be deleting this a little later.

立即单击“保存”，而无需进行任何操作即可在工作表的宏文件中为我们创建占位符功能。您稍后会删除它。

Click “Save” again.

再次单击“保存”。

Open Google Apps Script by clicking Tools > Script Editor, and then open the macros.gs file from the left pane. Delete the existing function and then press Ctrl + V to paste in the macro from your other Sheet.

通过单击工具>脚本编辑器，打开Google Apps脚本，然后从左窗格中打开macros.gs文件。删除现有功能，然后按Ctrl + V从其他工作表粘贴到宏中。

Press Ctrl + S to save the script, close the tab, and return to your spreadsheet.

按Ctrl + S保存脚本，关闭选项卡，然后返回到电子表格。

Your spreadsheet reads the macros.gs file and looks for changes made to it. If a new function is detected, you can use the Import feature to add a macro from another sheet.

您的电子表格将读取macros.gs文件并查找对其所做的更改。如果检测到新功能，则可以使用“导入”功能从另一个工作表添加宏。

Next, click Tools > Macros > Import.

接下来，单击工具>宏>导入。

Finally, click “Add Function” under the macro you want to add.

最后，在要添加的宏下单击“添加功能”。

Unfortunately, you will have to bind the macro manually to a keyboard shortcut again. Just follow the instruction previously mentioned, and you’ll be all set to use this macro across multiple sheets.

不幸的是，您将不得不再次将宏手动绑定到键盘快捷键。只要按照前面提到的说明进行操作，就可以在多个工作表上使用此宏。

That’s all there is to creating and using macros in Google Sheets. There’s no limit to the routines you can create for your spreadsheets to limit the amount of time you spend doing these repetitive tasks.

这就是在Google表格中创建和使用宏的全部内容。您可以为电子表格创建的例程没有限制，以限制您花费在执行这些重复任务上的时间。

翻译自: https://www.howtogeek.com/413334/how-to-automate-google-sheets-with-macros/