用於掌握宏的 Google Apps 腳本教程

已發表: 2022-03-11

高效的主管知道時間是限制因素……也許沒有什麼比他們對時間的溫柔關愛更能區分高效的主管了。

彼得·德魯克

時間是我們最寶貴的資源。 我們希望將其花在我們能做的最具影響力和最具價值增值的活動上，不僅因為這些活動通常具有最高的貨幣價值，而且是為了不斷挑戰自我並最大限度地提高我們的工作滿意度。

有很多方法可以提高您的效率和生產力，以便更好地利用您的時間。 在之前關於 Google 表格的文章中，我詳細闡述了在線協作的力量如何成為提高生產力的關鍵。

在另一篇文章中，我演示了 Python 編程語言如何成為金融專業人士的強大分析和任務自動化工具。

受此啟發，我現在想介紹一個 Google Apps 腳本教程。 Google Apps 腳本允許您使用 JavaScript 編寫腳本和程序，以自動化、連接和擴展 Google 的 G Suite 中的產品，包括表格、文檔、幻燈片、Gmail、雲端硬盤等。 學習它需要時間投資，編寫腳本也是如此，但是生產力的提高和它帶來的額外機會使得它非常值得。

作為第一步，讓我們從一個熟悉的概念開始：宏。

在 Google 表格中記錄和使用宏

如果您花費大量時間使用 Excel，那麼您一定會在某個時候接觸到 Excel 的 VBA（Visual Basic for Applications）宏接口。 要么通過自己錄製或編寫它們，要么通過其他人創建的捎帶。

宏是自動化重複和繁瑣工作流程的好方法。 VBA 可能不是您花費大量時間學習的語言，但它的美妙之處在於您實際上並不需要為了提高工作效率並創建自己的宏。 您可以簡單地記錄您想要自動化的工作流程，然後進入代碼並進行所需的任何小的更改以使宏更通用。

在某些方面，VBA 是如何向非技術人員介紹編碼的一個很好但被遺忘的課程。 您可以記錄動作然後填充代碼以供以後查看的方式確實是一種比閱讀教科書和被動觀看教程更實用的學習方式。

Google 表格中提供了與 VBA 相同的錄製功能。 這是一個如何使用它的簡單示例：

讓我們從一些示例數據開始，使用 IMPORTHTML 查詢來導入表。 在此示例中，我從 Wikipedia 下載了世界上 15 家最大對沖基金的列表。 不用說，但是，這是一個任意的例子； 目的是讓您更多地關注應用程序，而不是主題。

用於導入表的示例數據。

宏錄製過程通過以下菜單路徑啟動：工具 > 宏 > 錄製宏。

然後我們遍歷我們想要記錄的動作（PC 格式）：

  1. 選擇第一行
  2. 按 Shift + Ctrl + 向下箭頭選擇所有內容
  3. Ctrl + C 複製
  4. Shift + F11 創建新工作表
  5. 給工作表一個新名稱
  6. 按 Shift + Control + V 粘貼值

完成後，按底部宏窗口上的保存按鈕，為其命名和可選的鍵盤快捷鍵。

對於可以通過這些相同步驟完全複製的更簡單的操作，該過程將在此處結束，您可以立即開始使用您的宏。 但是，在這種情況下，我們需要在代碼可用之前進行一些更改。 例如，我們複製到的工作表每次都需要有不同的名稱。 讓我們看看如何做到這一點。

手動編寫 Google Apps 腳本

現在我們將第一次看到 Google Apps Script 的骨架； 在 Google 服務器上運行的編程平台。 這為我們的宏提供了動力，並允許您為應用程序本身創建非常複雜的工作流程，甚至是附加組件。 它不僅可以用來自動化電子表格工作，而且實際上幾乎可以用來自動化谷歌 G Suite 中相互關聯的任何東西。

Apps Script 的編程語言是JavaScript ，它是最流行的編程語言之一，這意味著有大量資源可供任何想要廣泛學習的人使用。 但是，就像使用 VBA 一樣，您實際上並不需要：您可以使用相同的 Record 功能，只需執行您希望能夠自動重複的步驟。 錄製的輸出可能看起來很粗糙，很可能與您想要完成的內容不完全匹配，但它會提供一個足夠可靠的起點。 現在讓我們為剛剛錄製的腳本執行此操作。

錄製時，請注意不要意外錄製您不想在最終錄製中捕獲的任何其他步驟，但有時很難避免：就像在按下停止之前選擇不同的單元格一樣簡單每次運行腳本時，錄製按鈕都會被捕獲並隨後重複。 編輯我們的腳本的第一步是清理它並刪除任何此類步驟。 讓我們進入文件菜單中的工具 > 腳本編輯器。

腳本編輯器

如果您了解 JavaScript，您會立即認出這一點，並且您可能還會驚訝地看到“var”關鍵字而不是現代 JavaScript 中的“let”或“const”。 這反映了 Apps Script 中的 JavaScript 版本相當老舊，並且不支持該語言的許多最新功能。 最後，我將為那些想要使用最新語言功能的人介紹一種解決方法。

當您第一次運行腳本時，它會請求授權，這是有道理的，因為腳本可以修改（並可能刪除）您的所有數據。 您很可能會從其他 Google 產品中識別出授權過程。

現在我們可以開始修改代碼了。 我們需要做的更改很小，但如果您是第一次這樣做，可能仍需要快速搜索 Sheets Apps 腳本文檔和/或快速查找 JavaScript 概念，例如處理日期。 JavaScript 是一種如此廣泛使用的語言這一事實就派上用場了：如果您以直截了當的方式表達您的搜索詞，通常可以快速找到您面臨的任何問題或功能的解決方案。

這個版本的腳本對原始錄製版本所做的更改是，我們現在用今天的日期命名，而不是我們創建的新工作表的硬編碼名稱。 此外，我們還將末尾的複制路徑更改為引用此新工作表。 最後四行還演示瞭如何執行一些格式化操作，例如更改單元格的值、調整列大小和隱藏網格線。

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

現在運行腳本將顯示新工作表確實以今天的日期命名，並且包含從主工作表複製為值（而不是公式）的信息。

現在可以使用相同的記錄過程添加圖表可視化。 我用它來創建三個簡單的圖表。

圖表可視化。

為每個清理代碼將如下所示：

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

同樣，如果某些選項看起來令人困惑，請不要擔心：這都是自動生成的，您只需要足夠了解即可刪除不必要的步驟，並且稍後可能會進行一些小調整。

高級 Google Apps 腳本示例：將工作表連接到 Google Drive 和幻燈片

現在一切都開始成形了，但是如果我們想要的實際輸出不是電子表格而是演示文稿怎麼辦？ 如果是這種情況，那麼這裡的大部分工作可能仍然是手動的，如果我們需要重複執行此操作，我們並沒有節省太多時間。

現在讓我們探索一下使用電子表格中的示例數據自動創建演示文稿會是什麼樣子。

這個練習現在變得更加先進，原因有兩個：

  1. 除了表格之外，我們還需要熟悉如何使用 Google 幻燈片（和 Google 雲端硬盤）。
  2. 在幻燈片中，或在一般 G Suite 應用程序之間工作時，沒有可用的“錄製宏”功能。 這意味著您需要對 Apps 腳本有足夠的了解（並且能夠輕鬆瀏覽每個 G Suite 產品的文檔）才能從頭開始編寫腳本。

下一個示例旨在提供一些基本構建塊來幫助您入門和熟悉。

首先，讓我們創建一個模板，稍後我們將使用我們的腳本來填充內容。 這是我放在一起的兩張簡單的演示幻燈片：

每週報告模板。

接下來，您將需要獲取此模板的 ID，因為您必須在腳本中引用它。 潛意識裡你會多次看到這個 ID，因為它實際上是你在瀏覽器的 URL 中看到的隨機的字符和數字序列：

https://docs.google.com/presentation/p/this_is_your_presentation_ID /edit#slide=id.p 。

現在我們必須將以下行添加到我們的原始腳本中。 這將再次提示授權，這次是訪問您的 Google 雲端硬盤。

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

如果您運行此代碼片段，您不會立即看到任何視覺反饋，但如果您查看存儲模板的 Google Drive 文件夾，您會發現它的副本確實已創建，並且它具有今天的文件名中的日期。 我們有了一個良好的開端！

現在讓我們使用更多的構建塊開始用內容填充它，以編程方式而不是手動。 將以下行添加到同一函數中：

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

現在事情變得更有趣了，因為我們已將第一頁更改為包含今天的日期。 在幻燈片中，與在表格中一樣，您使用對象（由類表示），每個對像都有屬性和方法（即附加功能）。 它們按層次結構組織，SpreadsheetsApp、DriveApp 或 SlidesApp 是頂級對象。 在上面的代碼片段中，我們需要一步一步地通過這個層次結構來到達我們想要編輯的元素，在這種情況下：文本框中的文本。 實際上，這意味著通過 Presentation、Slide、PageElement 和 Shape 對象，直到我們最終到達要編輯的 TextRange 對象。

跟踪您正在處理的對像類型可能會令人困惑，並且由於嘗試將操作應用於錯誤對象而導致的錯誤可能難以解決。 不幸的是，腳本編輯器本身的幫助功能和錯誤消息並不總是在這裡提供很多指導，一線希望是這種關注至少會改善您的質量控制實踐。

創建演示文稿並更新標題後，現在是時候將我們的一個新圖表插入其中了。 牢記對象的層次結構，下面的代碼現在應該是有意義的：

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

如果您運行完整的腳本，輸出演示文稿應如下所示：

樣品展示

希望此示例說明了原理並為您開始自己的實驗提供了靈感。 如果您考慮一下，我相信您至少可以找到一些在您的公司中完成的手動工作示例，這些示例確實應該以這種方式實現自動化。 服務於騰出時間思考、分析和應用判斷，而不是機械地將數據從一種格式和/或地點轉移到另一種格式。 改進開發體驗 如前所述，Google Apps 腳本支持的 JavaScript 版本較舊，在線腳本編輯器的功能非常有限。 如果您只是在錄製宏或編寫幾十行代碼，您將不會真正注意到。 但是，如果您確實有雄心勃勃的計劃來自動化每週或每月報告的各個方面，或者想要構建插件，那麼您會很高興知道有一個命令行工具可以讓您使用自己喜歡的開發環境進行開發.

如果您處於這樣的熟練程度，那麼您可能還想利用 JavaScript 必須提供的最新功能，甚至可能更多，因為使用命令行工具，您也可以在 TypeScript 中進行開發。

使用 Python 進行 Google 表格編程

如果您發現使用 Apps Script 不是您的菜，那麼還有其他選擇，具體取決於用例。 如果您想做更高級的數字運算、連接 API 或數據庫，或者只是更喜歡 Python 編程語言而不是 JavaScript，那麼 Google 的 Colaboratory 是一款無價的產品。 它為您提供了一個在 Google 服務器上運行的 Jupyter 筆記本，允許您編寫與您的 Google Drive 文件無縫集成的 Python 腳本，並且通過“gspread”庫，可以輕鬆處理您的電子表格數據。

我在一篇關於如何將 Python 用於財務功能的文章中概述了 Python 的許多好處，該文章還作為在商業和財務環境中使用 Python 和 Jupyter 筆記本的溫和介紹。 對我來說一個非常重要的好處是，與 Apps Script 不同，Colaboratory 中的 Python 筆記本是交互式的，因此您可以在執行每一行或一小段代碼後看到結果（或錯誤消息）。

自動化讓人上癮

這個 Google Apps 腳本教程展示了通過 Google 的編碼語言可以實現的功能。 可能性幾乎是無窮無盡的。 但是，如果您沒有技術背景，代碼示例可能看起來令人望而生畏，並且您可能會想，從學習 Google Apps 腳本中獲得的生產力收益可能不足以超過所需時間方面的大量投資學習它。

當然，這取決於許多因素，包括您將來擁有或期望擁有的角色類型。 但是，即使您不希望做與此處顯示的示例類似的任何事情，了解什麼是可能的以及大致需要多少工作才能實施可以引發有關如何提高公司生產力的想法和想法，因為你的客戶，或者你自己。

就我個人而言，我可以證明坐下來按下一個按鈕，在一分鐘內完成一個小時的繁瑣手工工作是一種滿足感。 在第 50 次這樣做之後，你會很感激一開始花了幾個小時把它們拼湊在一起，這最終讓你騰出時間去追求更多的增值。 一段時間後，這些可擴展性優勢確實會讓人上癮。