撰寫 Google App 腳本的思考

Google 試算表是適合非程式設計師的絕佳平台

如果我是為了自己的目的撰寫這個腳本，我絕不會考慮使用 Google 試算表。我會直接將它寫成命令列腳本。但是，每當您撰寫程式時，您都必須考慮如何部署它。在這個案例中，我的朋友雖然是音訊視覺技術的專家，但他不是每天都使用命令列的程式設計師。此外，他使用的是 Windows，這是我不再熟悉的環境。（我聽說它的命令列已經改進，但門檻低到連蠕蟲都可以跳過去。）然而，就像許多電腦使用者一樣，他經常使用 Google 試算表。

試算表經常受到我們這些專業程式設計師的批評，我們經常觀察到許多企業依賴少數 Excel 試算表。我們在說這句話時會翻白眼，這種試算表通常是一團混亂的複雜程式碼，模組化不足、未經測試、沒有版本控制，散落在使用者的電腦上，沒有任何管理。試算表經常被強迫執行不適合它們的任務，通常充當拼湊起來的資料庫，其關聯性是一張錯綜複雜的 VLOOKUP 網路。

但這些試算表廣泛使用是有其原因的。表格比喻是一種處理結構化資料的簡單但有效的方式。透過使用 說明性程式設計，使用者可以在處理公式時立即看到結果。Google 試算表讓使用者可以輕鬆地與其他人分享試算表，讓多個人可以輕鬆地有一個儲存共用資料的共同空間。身為一個本能地討厭基於技能的障礙的人，我經常感到沮喪，因為軟體開發人員不會更多地參與其他專業人士使用試算表所做的事情。

就像許多其他人一樣，試算表為我的朋友執行這項任務提供了自然的環境。Google 試算表允許我建立新的選單，並輕鬆地將腳本繫結到選單項目，因此他可以輕鬆地執行我的腳本，查看腳本產生的資料，並將其他資料直接新增到試算表中。我們可以輕鬆地共用試算表，因此如果出現問題，我可以更新腳本或查看資料。他不必在他的機器上安裝任何軟體，或保持軟體為最新狀態。

如果我被公車輾過，他可以輕鬆地與其他人共用試算表，而其他人可以輕鬆地編輯和執行試算表。由於程式碼是 JavaScript，因此它是程式設計人員廣泛使用的語言。

最困難的部分是驗證

腳本中的「商業邏輯」非常簡單。比較他的 Slack 頻道上的成員清單與在 Patreon 上註冊的成員或他自己的獨立清單上的成員。然後使用比較來產生要從 Slack 中新增或移除的人員清單。它只需要從清單中提取電子郵件並執行幾個集合差分運算即可。

困難的部分是取得清單，不是從 REST URL 取得它們（這也很容易），而是滿足腳本被授權取得此資料的服務。這兩個服務都使用 OAuth 來整理驗證，但即使使用 Google App 腳本提供的程式庫，該標準也無法表示它是一種即插即用的練習。

最後，我最終半避開和完全避開驗證。Slack 有個不錯的機制，你可以透過它建立一個應用程式來存取 Slack 資料，給予它你需要的授權，它會在它的網站上給你一個簡單的存取權杖。對於這個應用程式，我只要將那個存取權杖放入腳本中即可。通常那是一種不佳的安全實務，但在此情況下，腳本與它下載的資料在同一個試算表中（而且該資料並非極度敏感）。那避開了 OAuth 呈現的大部分複雜性。

Patreon 資料的驗證既更尷尬，資料也更敏感。因此，我在這裡繞過驗證。Patreon 網路應用程式允許使用者將資料下載到 CSV 檔案中。所以我請我的朋友這麼做，並將資料匯入試算表中。

Google 在此處有真正的機會簡化整個驗證流程。我應該能夠在遠端服務上呼叫提取方法，並讓基礎架構整理驗證流程，而無需我自行調查和編寫程式。

Google 的文件並非零

這是對它最好的評價。有一個清單列出所有類別及其方法。透過閱讀它們，我通常可以找出一些東西。但除此之外沒有太多內容，而且即使像這樣一個簡單的任務，也會讓我陷入比我想要的更多尷尬的境地。

整理試算表

我沒有使用試算表編寫太多程式（我在 R 中執行一般資料整理和繪製圖表），所以我沒有足夠的經驗對結構良好的試算表提出有力的意見。可能在某處有關於如何設計良好的試算表的建議，但我找不到（如果有人知道任何好文章，請告訴我）。

由於缺乏，我遵循了我的直覺。第一個直覺是將資料下載到具有最小操作的本地儲存體。因此，我從 Slack 下載資料的腳本只會選取我想要的欄位，並將它們傾印到試算表的一頁中。類似地，我的 Patreon 資料頁面假設從 Patreon 上傳 CSV 檔案。這兩個頁面都建置為在重新整理時清除並取代整個頁面。第三個頁面只包含手動維護的例外清單。這三個工作表都是純資料工作表，單一表格，第 1 列中的標題，沒有公式。比較腳本從這三個資料工作表讀取，執行（簡單的）應用程式邏輯，並將兩個清單發射到一個單獨的輸出工作表。

這非常類似於我在命令列應用程式中使用個別文字檔案的方式。它允許使用者看到原始下載的資料。我可以在不每次下載的情況下執行（並測試）應用程式邏輯。我可以設定一個包含測試資料的工作表。工作表和程式碼之間有明確的單向資料流。

不要使用 appendRow 來追加列

我第一次執行從 Slack 下載程式碼時感到沮喪，因為執行速度非常慢。可能只有大約一千列，但它們以每秒約一列的速度新增到試算表中。我們可以忍受，但這並不好。我確信一定有更快速的方法來執行此操作。

在 API 中搜尋，我看到許多試算表操作都仰賴在試算表中定義範圍。我使用 `Sheet.appendRow` 新增新列，但如果我定義範圍（可能是整個試算表），我可以改用 `Range.setValues`。一旦我這麼做，新增列就會立即生效。我在文件或網路上其他地方找不到任何提示嘗試這一點，這很重要，因為缺乏這類文件是人們更廣泛使用此平台的障礙。

允許 API 查詢查詢多個值

如上所述，我最後沒有使用 Patreon 資料的 REST 介面，因為驗證很複雜。但還有另一個原因支持 CSV 下載。Patreon API 包含一個資源，會告訴我所有贊助活動的人員，對於這些人員，它會提供他們的 Patreon ID 和姓名。但要與 Slack 清單交叉比對，我也需要他們的電子郵件。我可以透過取得由 ID 編制的索引資源來查詢。然而，我需要為數百人執行此操作，而且每個都需要個別 GET。

傳達給 API 設計人員的訊息如下。如果您提供按 ID 查詢資源資訊的功能，請支援一次提供多個 ID 資料的功能。[1]

我喜歡將應用程式邏輯與試算表 IO 分開

從試算表存取資料的方法是使用試算表的欄和列慣例（例如儲存格「B22」或範圍「A2:E412」）。對於許多指令碼任務來說，這是合理的，因為程式設計人員會從試算表中操作儲存格的角度來思考問題。

我傾向於以不同的方式思考事情，偏好以基本 JavaScript 資料結構的形式呈現我的資料，特別是因為我之後可以使用 JavaScript 的 集合管線 營運子。

有鑑於此，這是撰寫的方便函式，用於從試算表中擷取資料，並以 JavaScript 物件陣列的形式傳回。

extractData(sheetName, firstCol, lastCol, mapper) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
  const numRows = sheet.getLastRow()
  const range = `${firstCol}1:${lastCol}${numRows}`
  return sheet.getRange(range).getValues().map(mapper)
}

然後我可以使用類似這樣的程式碼

const mapper = row => ({email: row[3], name: row[0], slackId: row[1]})
slackData = this.extractData("raw-slack-download", "A", "D", mapper)

一旦我將其放入陣列中，找出結果就很容易了，儘管我必須撰寫一個簡單的 `Array.difference` 函式，因為我無法存取 lodash。

開發環境可以理解地很粗糙

要撰寫 JavaScript，我只要在試算表中選取一個選單項目，然後在一個粗糙的文字編輯器中輸入即可。這不是我習慣的舒適家園，但對於過夜來說還算不錯。

如果我要做一些更複雜的事情，我會研究設定一個更好的環境。一種可能性是看看我是否可以使用 Emacs 的超讚 Tramp 模式編輯腳本（它允許將遠端檔案編輯得好像它們是本機檔案一樣）。更好的方法是將本地檔案與 Google 雲端硬碟同步，讓我可以將原始碼保存在 git 儲存庫中。但是對於像這樣一個簡單的任務，大約 150 行程式碼，不值得研究看看這是否可行。

總結

在 Google 試算表上主機一個簡單的應用程式是一個有吸引力的部署平台，適用於一系列簡單的任務。它允許使用者執行程式碼，而無需在他們的機器上安裝任何東西，在熟悉的環境中輸入資料，並支援與同事輕鬆分享。這不是我聽說過很多討論的平台，但值得記住。特別是，對於任何將會是一個簡單的 shell 腳本的任務，但你的使用者不習慣主控台視窗和文字檔案。