谷歌表格_如何使用宏自動執行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您希望這些操作復制的順序。

Choose either Absolute References or Relative References

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之間的數字。完成后，單擊“保存”。

Enter a name for your macro, then click Save

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

如果需要調整宏的名稱或快捷方式，可以通過單擊工具>宏>管理宏來編輯宏。

To create a keyboard shortcut, click Tools > Macros > Manage Macros

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

在打開的窗口中，根據需要進行調整，然后單擊“更新”。

Beside each macro, enter a number from 0-9 to bind to a shortcut

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.

如果您的宏是絕對引用，則可以通過按鍵盤快捷鍵或轉到“工具”>“宏”>“您的宏”，然后單擊適當的選項來運行宏。

Click Tools > Macros > YourMacroName to run your macro

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.

否則，如果您的宏是相對引用，請突出顯示電子表格中您要在其上運行宏的單元格，然后按相應的快捷方式，或者從“工具”>“宏”>“您的宏”中單擊它。

If you made a relative referenced macro, highlight the cells, then click Tools > Macros > YourMacroName, to run the 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.

使用您要復制的宏打開Goog??le表格，然后單擊工具>宏>管理宏。

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

接下來，點擊您要復制的宏旁邊的“更多”圖標，然后點擊“編輯腳本”。

Click the more icon, 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。確保將所有內容復制到包括分號在內的所有內容。

Highlight and copy the macro's function with Ctrl + C

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

現在，打開要將宏導入到的其他電子表格，然后單擊工具>宏>記錄宏。

Create a new macro in your other spreadsheet. 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.

再次單擊“保存”。

You don't need to worry about naming it, click Save

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.

通過單擊工具>腳本編輯器，打開Goog??le Apps腳本，然后從左窗格中打開macros.gs文件。刪除現有功能，然后按Ctrl + V從其他工作表粘貼到宏中。

In the macros.gs file, paste the macro's function from the first spreadsheet

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.

接下來，單擊工具>宏>導入。

Click Tools > Macros > Import to import the macros to this document

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

最后，在要添加的宏下單擊“添加功能”。

Finally, click Add Function next to 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表格中創建和使用宏的全部內容。您可以為電子表格創建的例程沒有限制，以限制您花費在執行這些重復任務上的時間。